Performance counter is very useful for analyzing OS and SQL Server performance. Most DBA knows how to collect performance counter, so I won’t write it in this post. Just in case You don’t know, check Brent’s post here. Once the counters are collected, the next step is to analyze to find performance problem. I posted about Perfmon analysis with PAL , but in this post I will show different method by importing to SQL Server and query it T-SQL.
Using T-SQL query has several advantages over PAL:
- You have full control on what counters to analyze and how to summarize (average, min. max, etc)
- You can compare several sets of data collection and see their trends. You can filter the query and build crosstab report to see the trending between different time interval
So the steps to do it are as follow:
- Create empty database in SQL Server
- Setup ODBC System DSN
- Import Perfmon to SQL Server with relog tool
- Analyze perfmon by using T-SQL
Create empty database in SQL Server
Create an empty database in SQL Server, as this will be a storage for imported data. Sizing is important because the data is quite big. In my case, 1GB of Perfmon file becomes 3GB after imported into SQL Server. It’s a massive growth, but You will know why after seeing the information stored in the database. I usually pre-size the data file to 5GB with 100MB growth to improve performance. Don’t forget to change recovery mode to simple to prevent unexpected growth of log file.
Setup ODBC System DSN
The next step is to create ODBC DSN to connect to SQL Server. This is straight forward, by using ODBC Administrator in control panel. I choose the 64 bit version because both my client machine and SQL Server is 64 bit version. Make sure also to select System DSN instead of other type of DSN.
For some reason, relog will only work with SQL Server legacy driver instead of SQL Server native client. So You need to choose “SQL Server” driver instead of SQL Server Native Client when selecting driver type.
The next screen will ask for connection name, SQL Server instance name, and credential to connect to SQL Server. Other important note to specify the default database name to the one created in previous step. I created empty database PerfDB, so I specify the same name as my default database here.
Once created, test the connection to make sure it’s properly configured.
My machine is Windows 8.1 64 bit with SQL Server 2012 (also 64 bit). I created System ODBC DSN (64 bit) with SQL Server driver version 6.xxx and relog was successfully connected to my SQL Server. Initially, I used SQL Server Native Client with 32 or 62 bit combination, but always encountered error when executing relog command.
Import Perfmon to SQL Server with relog tool
Once ODBC is configured, the next step is to import Perfmon data to SQL Server. Relog is installed by default in your Windows, so no need to add additional tool. The syntax is as follow:
relog perfmonFileName.blg -f SQL -o SQL:ODBCDriverName!CounterIdentifier
The CounterIdentifier is any name to tag your data collection set. For example, If I have 3 Perfmon files and all of them was collected during August 2013 then I can group them together under one identifier: DataAugust2013. This is very useful when I want to group several Perfmon files under different category. I can collect baseline data and import it under “Baseline” identifier. Then collect another one and tag it under “Tuning” identifier.
To run the relog command, follow the following steps:
Open command prompt and navigate to the location of perfmon file. To make it easy, You need to be in the same folder with perfmon file. Run the following command:
Let me explain the command here:
- The name of Perfmon file is DataCollector01.blg
- The output type is SQL Server, by using -f parameter
- The ODBC System DSN name is relog5
- The identifier is BaselineSept2013
The command will import all counters data to SQL Server. If you want to filter the data by applying additional parameter, please read full relog tutorial here and here.
See what’s We got after import
Let’s connect to PerfDB and see what We have after import: there are 3 tables created inside the DB.
Run the following simple query to see what We got in every tables:
The query yields the following result:
As You can see here, I put BaselineSept2013 as identifier and It’s reflected in the table under DisplayString column. I also imported another set of files and name it as TuningDec2013. In this case, I have 2 group of imported files that I can compare to see the improvement between the baseline and tuning period.
Now let’s play the interesting part: querying imported data to analyze SQL Server performance. I will post this in Part 2, stay tuned :).