What I learned today – the RELOG utility

At the Chicago SQL User group meeting on 1/14/2010 John Jones of NetApp gave a talk on storage performance. it was an informative meeting. For instance, did you know that the latency of a 15K Fibre Channel drive is the same as a 15K SAS drive? But SAS drives are smaller; NetApp is able to put 24 drives in a 4 U rack.

During the presentation, John mentioned the RELOG utility and how it can convert a perfmon counter log from the default .blg extension to a .csv file. I’d never heard of RELOG before so I did a little reading up on it. I googled RELOG and found lots of information. The sources I used for this article were Ask The Performance Team, Microsoft TechNet, and this blog post at My Torn Data Pages. And I found that RELOG can do much more than just reformat a file. It can also filter the original counter log to show data from a sample time slice or only pull out certain counters.

RELOG is a command line utility. It’s available in Windows XP or higher machines, and it’s located in the \system32 directory. If you don’t have it you can get from Microsoft downloads. Also the utility isn’t just for SQL counters. you can run it against any perfmon counter file.

Here’s a list of the switches that RELOG uses:

In my examples I’m using a benchmark counter log I capture from our production servers. It’s already saved as a .csv file so I don’t need to convert it but I certainly could if I needed to. Also I’m typing RELOG in caps, though it’s not necessary.

Using RELOG with just the filename of the counter log will give you the start and end time of the capture plus the number of samples in the file…

 

…and using the –q switch will list the counters used in the file.

Adding the –o switch and specifying a file name will output the counter names to a separate file (RELOG SQL1_20100106.csv –q –o counters.txt).

So now that I know the times and the counters in my baseline, I can filter for the counters between 9 and 10 AM on 1/5(RELOG SQL1_20100106.csv -b "1/5/2010 9:00:00AM" -e "1/5/2010 10:00:00 AM" –f csv -o SQL1_filtered.csv). I can see that I’ve filtered out most of the sampling and now I’ve got the 237 samples I took between 9 and 10.

And I can also filter by individual counters.Let’s say I want to see the transactions per second against a specific database between 9 and 10 AM: RELOG SQL1_20100106.csv -b "1/5/2010 9:00:00AM" -e "1/5/2010 10:00:00 AM" -c "\MSSQL$MISDB:Databases(tempdb)\Transactions/sec" –f csv -o SQL1_filtered2.csv. Running this, I see the same 237 samples returned, but if I open the file, I’ll just see the counters I asked for:

I can also use a wild card to see the transactions/sec counters against all databases: RELOG SQL1_20100106.csv -b "1/5/2010 9:00:00AM" -e "1/5/2010 10:00:00 AM" -c "\MSSQL$MISDB:Databases(*)\Transactions/sec" –f csv -o SQL1_filtered3.csv

I haven’t tried to save the results to a database yet. According to My Torn Data Pages, there’s a problem with the syntax cited by Microsoft TechNet. I want to test both scenarios, using a DSN and the Native Client.

Quick access to your common scripts

Everyone has their own library of SQL scripts they run often, and everyone has their own methods for storing them. These a a few ways you may not be aware of.

My first method is pretty well documented. Have you every just clicked Ctrl + F1 and see the results of sp_who? Well, you can add your own procedure calls here. In Management Studio simply open Tools > Options and select Keyboard. Here you’ll see 12 slots with shortcuts of Alt + F1, Ctrl + F1, and Ctrl + 1 through 0. Three of these are already defined; sp_who, sp_help, and sp_lock. You can leave these or replace them with your own, and you can add your own in any available slot. So now you have a way of calling 12 procedures with just a keyboard shortcut. 

The second way I ran across by accident and works best for small queries. In Visual Studio you have the ability to store snippets of code in the Toolbox. One day I thought I’d try it in SSMS, since it’s similar to the VS IDE. And it worked. What you need to do is open the Toolbox ( Ctrl + Alt + X). You’ll most likely only see the General tab with no controls; that’s fine since there aren’t any. Right click in the Toolbox, select Add Tab, and give it a name; mine is simply Scripts. Next, open or write your script in a query window. Then highlight the script and drag it onto the Toolbox. You probably won’t see it yet; right click the Toolbox again and make sure both List All and Show All are selected. And voila! There’s your script! To run it just right click the snippet, copy the code, and paste in a new query window.

If you hover the mouse over the snippet you just added you’ll see the entire code. Notice this is added with Text: appended to the start. you can change this by right clicking the snippet and choosing rename, then just replace the word “Text’ with something more meaningful. 

The third way is even better. Most people know that there are templates with SQL scripts you can use. Again, you can add your own and have them available anytime you open SSMS.

If you open the Template Explorer (Ctrl + Alt + T) you’ll see the templates that Microsoft gives. There’s two ways to add your own templates. Right click SQL Server Template and choose New > Template. You can also choose Folder to categorize your scripts, otherwise the new template will be in the root. Give your new template or folder a descriptive name. Then right click the template, choose edit, write your code and save it. Now just double click the template to open it in a new query window.

The other way to add your script to the Template Explorer is to add it to folder where the other templates are. On an XP or a Windows 2003 Server that’s at Documents And Settings\your account\Application Data\Microsoft\Microsoft SQL Server\100 (for SQL 2008) or 90 (for SQL 2005)\Tools\Shell\Templates\Sql. On a Vista or Windows 7 box you can find it in Users\your account\AppData\Roaming\Microsoft\Microsoft SQL Server\100 (for SQL 2008) or 90 (for SQL 2005)\Tools\Shell\Templates\SQL.

One word of warning. If you do save your scripts as templates, remember to back them up occasionally. It would be easy to overlook them if you ever rebuild your computer and need to restore them. But of course you already knew that.

How I track database size

One of my DBA tasks is to track the space used on our database servers. I want to know how big the database file sizes are, how big the tables are, and how much free space is available on each server. This is a common task for DBAs, and there’s probably a different method for each DBA. This is mine.

When I first started, I created a database on each production server to store all stored procedures, tables, and views that I use as a DBA. The tables are mostly repositories of server state at the time I run a query or procedure. In the case of database sizes I created the procedure GetDBFileStats to get the filesize, total space, free space, and space used for each file in each database: 

CREATE PROCEDURE [dbo].[GetDBFileStats]
AS

EXEC sp_msforeachdb 'USE [?]

CREATE TABLE #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
                  
INSERT #tmpspc EXEC (''DBCC SHOWFILESTATS'')
 
INSERT INTO PerfDB.dbo.dbStats            
SELECT GETDATE() AS RunDate,
    ''?'', 
    s.name AS [Name],
    s.physical_name AS [FileName],
    s.size * CONVERT(float,8) AS [Size],
    CAST(tspc.UsedExtents * CONVERT(float,64) AS float) AS [UsedSpace],
    (s.size * CONVERT(float,8) - CAST(tspc.UsedExtents*convert(float,64) AS float)) AS FreeSpace,
    s.file_id AS [ID]
FROM sys.filegroups AS g
    INNER JOIN sys.master_files AS s ON (s.type = 0 AND s.database_id = db_id() AND (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
    LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
ORDER BY [ID] ASC

DROP TABLE #tmpspc

INSERT INTO PerfDB.dbo.dbStats            
SELECT GETDATE() AS RunDate,
    ''?'', 
    s.name AS [Name],
    s.physical_name AS [FileName],
    s.size * CONVERT(float,8) AS [Size],
    CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float) * CONVERT(float,8) AS [UsedSpace],
    (s.size * CONVERT(float,8) - CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8)) AS FreeSpace,
    s.file_id AS [ID]
FROM sys.master_files AS s
WHERE (s.type = 1 AND s.database_id = db_id())
ORDER BY [ID] ASC'
   
GO

This was as far as I went until a few months ago. At that time I started to think of using SSIS for some of my daily tasks. So I created this simple package that gets the results from my procedure and puts them into a central database repository. I also truncate the tables and change the default date to just the date; 

Finally I created a report that got the data back. I send 2 parameters; a from date and a to date. Now when I run the report I can view what the database size was for the two dates and easily see how much the files have grown. I also created a sub report that, when I click on the database in the main report, opens to show the growth history of that database on a week to week basis.

Here’s the table I use to store the data and the procedure I use to populate the summary report. I also run it as an ad-hoc query;

CREATE TABLE [dbo].[dbStats](
    [Servername] [sysname] NOT NULL,
    [RunDate] [datetime] NOT NULL,
    [DBName] [sysname] NOT NULL,
    [Name] [sysname] NOT NULL,
    [FileName] [nchar](520) NOT NULL,
    [TotalSize] [float] NOT NULL,
    [UsedSpace] [float] NOT NULL,
    [FreeSpace] [float] NOT NULL,
    [FileID] [int] NOT NULL
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[prc_DBSizeCompare] (
    @minDate    DATETIME,
    @maxDate    DATETIME)
    
AS

SET NOCOUNT ON

;WITH DBCTE(ServerName, DBName, Name, MaxRunDate, MinRunDate) AS
(
    SELECT ServerName, DBName, Name, 
        MAX(RunDate) AS MaxRunDate,
        MIN(RunDate) AS MinRunDate
    FROM dbo.dbStats
    WHERE RunDate BETWEEN @minDate AND @maxDate
        AND DBName <> 'tempdb'
    GROUP BY ServerName, DBName, Name
)
SELECT db.Servername, db.DBName, db.Name, db.RunDate, db.TotalSize, db.UsedSpace, db.FreeSpace 
FROM dbo.dbStats db
    INNER JOIN DBCTE CTE ON db.Servername = CTE.ServerName
        AND db.Name = CTE.Name
WHERE db.RunDate = CTE.MinRunDate OR db.RunDate = CTE.MaxRunDate
ORDER BY db.Servername, db.DBName, db.FileID, db.RunDate

GO

Booting to a VHD in Windows 7

This is something I tried before but didn’t complete for some reason. Now that I’m on vacation this week and I’ve got some extra time to play, I went back to finish it.

As you probably know by now, Windows 7 has the ability to boot into a .vhd file. This is awesome, as you can create a virtual testing environment that you can run directly against you hardware.

There are a few gotchas, though. You’re limited in the OS’s you can run on the virtual side to Windows 7 and Windows 2008. I’ve seen post of people getting other OS’s to run but I haven’t tried. And I’ve seen warnings not to do this on a laptop, though I’ll try it, once I install my new bigger laptop hard drive next week.

Anyway, this is how I did it. I had created a Windows 7 virtual machine in Virtual PC 2007. I used the vhd from that VM instead of creating a new one, though you certainly could if you wanted to.

The first thing I did was to run sysprep inside my VM. I’m not an expert on sysprep, I just followed instructions I found on the web. Briefly, sysprep is a GUI tool that prepares the image to be configured to use the hardware on the new server. You’ll find it in C:\Windows\system32\sysprep. Run it as an admin. Choose the default target of out of the box experience and also choose to generalize. Also choose to shut down. After sysprep is finished it will power down your VM.

I didn’t do this, but it’s probably a good idea to make a copy of the vhd at this point.

The next thing I did was to set up Windows bootloader to see the vhd file. I opened a admin command prompt and ran the following: bcdedit /copy {current} /d “Win 7 VHD”. This returns a GUID I saved to notepad. “Win 7 VHD” is the description I wanted to see on the boot menu. After that I ran these three commands:

bcdedit /set {guid} device vhd=[C:]\VM\Win7\Win7.vhd

bcdedit /set {guid} osdevice vhd=[C:]\VM\Win7\Win7.vhd

bcdedit /set {guid} detecthal on

In my example I replaced guid with the guid I saved earlier. VM\Win7\ is the path to my vhd file, and Win7.vhd is the file I’m using. Note that the drive letter is in square brackets: [C].    

And that’s just about it. Once I restarted my computer I could see both my original Windows 7 installation and my new vhd boot option. When I chose the vhd, Windows started and applied the hardware changes. After that I just logged in and ran my Windows vhd. Once in the virtual environment, I can see all the drives on the computer, including those for my “real” Windows 7. Notice Disk 1 is a blue icon; this shows that it’s a vhd file. It also shows the reserved system partition. I can also see the files on the other physical drives.

I don’t get this if I’m running my physical Windows 7. I can mount the vhd file (on the menu go to Action > Attach VHD). But it doesn’t stay mounted between reboots. I haven’t tried mounting it with DISKPART yet, I’ll try that when I create my laptop VM.

 

The only drawback is the vhd is not portable, and I can’t run it in Virtual PC 2007 anymore. I can probably run sysprep again to get it back, but I think I’ll keep it as it is for now.

Year End Wrap-up

This is my wrap-up for 2009 and a preview for 2010.

A big chunk of my year was testing virtualizing our production servers with different configurations. After months of testing we learned that our workload wasn’t conducive to virtualization.

During the last few months I began testing SnapManager from IBM for use as a backup tool. While SnapManager is a great tool for bringing a server online quickly, I can’t see a way of restoring individual objects. Unfortunately in our shop it’s far more likely to have a table accidentally updated or deleted than have a whole database go corrupt. I’d like to find a way for both to work, having the best of both worlds.

I started to learn Microsoft’s BI stack. I’m already pretty familiar with Reporting Services, but I’ve only played around with Integration Services and I’ve done nothing with Analysis Services. So far I’ve just scheduled a simple package to collect database sizes so I can track growth. I’ve got a long way to go.

I’m on vacation this week, not going back to work until January 4th. I’m using my downtime to create a virtual Win 7 pc to run all the 2010 betas coming down. I also want to set it so I can boot directly to it, using the new functionality in Windows 7.

So what’s coming up next year?

• I’ll finish my testing of SnapManager and I’ll post my results.
• I’m going to attend the first SQL Saturday in the Chicago area in April. I’ll post info about this early next month.
• Continue my education into BI using the SQL tools.

One of my resolutions for next is to continue studying new technologies – new to me that is. I want to become more proficient in Powershell and Silverlight, among others.

Happy holidays to all, and I’ll see you next year.

Virtualization – final wrapup

This is another topic I want to wrap up before the end of the year.

A quick overview. earlier this year, I did some testing of virtualizing our production servers hosted on VMWare servers. I captured a typical workload from our busiest server and replayed it on servers set up for testing; a 32-bit physical server with 16 CPU set up as a production server, the same server with hyper threading turned off, a 32-bit and a 64-bit virtual server with 4 CPUs,and a 32-bit and a 64-bit virtual server using vSphere and 8 CPUs. All servers had 16 GB of RAM.

After running the workload multiple times on each server configuration we compared results. What we saw was understandable – neither the 4 CPU or 8 CPU servers matched the 16 core baseline. Even the physical server with HT off fell short.

What I didn’t show in earlier posts was the counter for latches. Latches are the #1 wait type on our servers, and this held true on all testing servers. Our servers aren’t running optimized and this is being magnified in a virtual environment. Having 16 cores lessens the performance hit from all those latches in production.

So we’re not going to virtualize,at least the production servers. While virtualization is useful and can be used for SQL, it’s not a good idea to try and virtualize servers with performance problems. We’re currently using VMWare to run our development and testing environments as well as a few smaller, less used SQL servers, and we’re not seeing any issues with them.

I’m not going to published a summary of all my counters, but I’ll make them available on request if anyone is interested.

Testing Google feeds - ignore

Testing Google cross-posting