Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Collecting performance counters and using SQL Server to analyze the data


By:   |   Read Comments (7)   |   Related Tips: 1 | 2 | 3 | 4 | More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

Quite frequently I find myself in situation where I need to get detailed information on performance monitor counters. For example I need to determine which processes are consuming all CPU at certain times. I find it handy to push the performance monitor counters into SQL Server where I can query it or perhaps display it in Reporting Services.

Solution

The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved.

Select Counters

I first need to collect my data.

I use TypePerf to display a list of performance monitor counters on the machine I wish to collect from. TypePerf is installed by default on all machines after Windows 2000.

I use the following query to get a list of all the counters that can be used on the machine I am monitoring.  Although the counters are generally the same they may be different from machine to machine..  This is run in a DOS command window.

typeperf -q 

You can return the entire list and store this list in a text file by using the following command.

typeperf -q > counterstxt

Once you have the counters from the machine you want to monitor you need to edit the list  to keep only the counters you wish to collect.

I normally collect all counters for the following objects.  The * after the counter group specifies that all counters should be collected.  After you have the list of counters you want to collect save this as counterstxt.  This is a list of counters that were available on the machine that I wanted to analyze again not all of these counters may exist on your machine that you are monitoring.

\.NET CLR Data(*)\SqlClient: (for connection pooling)
\SQLServer:SSIS Pipeline\*
\SQLServer:SSIS Service \*
\MSFTESQLServer\*
\MSOLAPServer\*
\LogicalDisk(*)\*
\Memory\*
\Network Interface(*)\*
\Paging File(*)\*
\PhysicalDisk(*)\*
\Process(*)\*
\Processor(*)\*
\Server\*
\SQLServer:Access Methods\*
\SQLServer:Buffer Manager\*
\SQLServer:General Statistics\*
\SQLServer:Latches\*
\SQLServer:Locks(*)\*
\SQLServer:Memory Manager\*
\SQLServer:SQL Statistics\*
\System\* 

Collect Data

I then use logman to create my collection

logman create counter MyCollection -s %computername% -cf counterstxt

I then start the collection like this:

logman MyCollection start

Once I have collected a representative sample I stop the collection as follows:

logman MyCollection stop

By default on Vista and Windows 2008 servers, your performance monitor counters will be stored in %systemdrive%\PerfLogs\Admin and will be named after your collection name (in our case they will be called MyCollection.blg (blg is the extension for perfmon counters). On Windows 2000, 2003 and XP machines they will be stored by default in %systemdrive%\PerfLogs.

Load to SQL Server

Now that I have collected my perfmon counters I am ready to push them into SQL Server. To do this I use relog.

To use relog to input your performance monitor counters into SQL Server you must first select a database you wish to push them into and then create a system DSN to this SQL Server database (any version of SQL Server will work from SQL 2000 to SQL 2008). I use Windows Authentication as I don't want to worry about saving the account and password in the DSN.

  • Open up the Data Sources (ODBC) (In the Control Panel applet in the Administrative Tools section)
  • Under "User DSN" click "Add" and select SQL Server for your driver and click "Finish"
  • Give your System DSN a name - I call it "relog", and then point to a SQL Server in the drop down list or type in the server name and click "Next"
  • Select Windows Authentication (ensure that your windows login has dbo rights in the database that you wish to write your performance monitor counters to). and click "Next"
  • Select your database from the dropdown and click "Next"
  • Click "Finish"
  • Click "Test Data Source..." to test your data source
  • If the test was successful click "OK" and click "OK" again and then close this applet

Now push your performance monitor counters to your SQL Server database by using the following command.  ServerName is the name of the server which I collected the data on. This name will be written to the SQL Server table DisplayToID that is created and I can query on it when I want to look at my counters.

You will want to run this command in the folder that has the "blg" file that was created or you will need to specify the path too.  Also, you need to make sure the filename that was created is what is used for the command.

relog MyCollection.blg -f SQL -o SQL:relog!ServerName

Analyze the Data

Now that the data has been loaded it is time to query the data.

The collection tables that are created when the data is loaded are the following:

  • DisplayToID - table containing information about your collection
  • CounterDetails - contains details about your perfmon counters
  • CounterData - contains the actual counter data

Here is a sample query illustrating how to access your perfmon counter data. Here we are looking for context switches (Context Switches/sec).  This will group the data in one minute intervals.

SELECT MachineName,
   CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime)) as [Date],
   AVG(CounterValue) as Average,
   MIN(CounterValue) as Minimum,
   MAX(CounterValue) as Maximum
FROM CounterDetails
   JOIN CounterData ON CounterData.CounterID = CounterDetails.CounterID
   JOIN DisplayToID ON DisplayToID.GUID = CounterData.GUID
WHERE CounterName = 'Context Switches/sec'
GROUP BY MachineName,
   CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime)) 

Here is a sample result set.  (note the gap in time is because there were

machine name
Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, February 26, 2014 - 4:17:31 AM - gaurav kaushik Back To Top

Hi,

While running the below code i am getting the below error.

 

 

Error:

The network path was not found.

 

C:\Users\egakaus>logman create counter MyCollection -s E788AE1DACA99D -cf countertxt

 

Error:

Access is denied.

You're running with a restricted token, try running elevated.

 

C:\Users\egakaus>cd C:\Users\egakaus\Desktop\DATABASE

 

C:\Users\egakaus\Desktop\DATABASE>typeperf -q >countertxt

 

C:\Users\egakaus\Desktop\DATABASE>logman create counter MyCollection -s E788AE1DACA99D -cf countertxt

 

Error:

Access is denied.

You're running with a restricted token, try running elevated.

 

C:\Users\egakaus\Desktop\DATABASE>logman create counter MyCollection -s %E788AE1DACA99D% -cf countertxt

 

Error:

The network path was not found.

 

C:\Users\egakaus\Desktop\DATABASE>typeperf -q >counter.txt

 

C:\Users\egakaus\Desktop\DATABASE>logman create counter MyCollection -s %E788AE1DACA99D% -cf counter

 

Error:

The system cannot find the file specified.

 

C:\Users\egakaus\Desktop\DATABASE>logman create counter MyCollection -s %E788AE1DACA99D% -cf counter.txt

 

Error:

The network path was not found.

 

C:\Users\egakaus\Desktop\DATABASE>logman create counter MyCollection -s E788AE1DACA99D -cf counter.txt

 

Error:

Access is denied.

You're running with a restricted token, try running elevated.

 

C:\Users\egakaus\Desktop\DATABASE>logman create counter MyCollection -s E788AE1DACA99D -cf counter.txt

 

Error:

Access is denied.

You're running with a restricted token, try running elevated.

 

C:\Users\egakaus\Desktop\DATABASE>logman create counter MyCollection -s E788AE1DACA99D -cf counter.txt

 

Error:

Access is denied.

You're running with a restricted token, try running elevated.

 

C:\Users\egakaus\Desktop\DATABASE>

 
 
Please help.

Friday, July 05, 2013 - 9:30:27 AM - SQLDBA Back To Top

I caputure relog it will only 21 samples.How to increase saples ??

 


Friday, July 05, 2013 - 8:00:22 AM - SQLDBA Back To Top

hi ,

Nice article  well explain but I am getting some eeror.

C:\WINDOWS\system32>logman.exe create counter -s w-mum-asdintrn -cf reloftst.txt
 -cf counterstxt.txt
Duplicate argument found for counterfile: -cf
Missing parameter:

The syntax of the command is incorrect.

C:\WINDOWS\system32>logman.exe create counter -s w-mum-asdintrn -cf counterstxt.
txt
Missing parameter:

The syntax of the command is incorrect.

 

Please advice me.

 


Sunday, June 12, 2011 - 10:30:01 PM - James Back To Top

Hi Hilary

Quoting you:

 

  • Under "User DSN" click "Add" and select SQL Server for your driver and click "Finish"
  • Give your System DSN a name – I call it "relog", and then point to a SQL Server in the drop down list or type in the server name and click "Next"

I know it doesn't matter to people who know the difference between System and User DSN, but to those who don't, they'd be confused by your inconsistent use. Just would like to point out.

 

Thanks

 

James


Monday, May 04, 2009 - 11:46:04 PM - ihar_ku Back To Top

to ajm_otn

I have had the same problem after creation the DSN-name based on "SQL Native Client" provider. The problem went away after the DSN-name was changed with "SQL Server" provider as it written in tip.

 


Thursday, April 09, 2009 - 10:27:39 AM - admin Back To Top

So is "PMonCounter" the name of the dsn you created?


Thursday, April 09, 2009 - 9:25:45 AM - ajm_otn@poriver.com Back To Top

Nice article. When I tried to impor the collection into SQL Server using;

relog Collection_000001.blg -f SQL -o SQL:PMonCounter!My_ServerName

I got the following error. 

Error:
Call to SQLExecDirect failed with (null).

Looking that up here http://support.microsoft.com/kb/906859

Says there is a hotfix for it but that was back in 2005. Not sure I want to apply the hotfix.

Any thoughts on this?

Thanks


Learn more about SQL Server tools