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

 

Collect SQL Server Performance Counter Sets Using Logman


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

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


Problem

I've been given a SQL Server instance to troubleshoot performance issues. I know one of the tools that I could use is Windows Performance Monitor, commonly known as Perfmon. There is a GUI that I can use to create Perfmon counters. However there is also a command line tool called Logman which can be used to create counters for a local server or a remote server. How can I use Logman to create and manage Performance counters to monitor SQL Server performance?

Solution

Logman is a Windows tool which you can use to create Performance Data Collection Sets using scripts. I prefer this tool over the Perfmon GUI because it is easy to setup Perfmon counters both for local and remote servers.

How to use Logman

You can use Logman to collect Perfmon counters remotely and store them in a SQL format in a SQL Server table. See the below for the sample script.

Simply open a Windows command prompt and you can type the commands.

Syntax

logman [create | query | start | stop | delete| update | import | export | /?] [options]

Example

logman create counter <counter set name> -cf "W:\Logman\counter_list.txt" -si 00:00:30 -f SQL 
-o <DSN>!Perf_log -u Domain\perfmon_monitor <password> 

Let me describe some of the parameter options of the above sample.

Parameter  Description 
-cf <filename>  Specifies the file listing performance counters to collect. The file should contain one performance counter name per line. Logman uses the text file to create the counter set in the server that you execute Logman.
-si <[[hh:]mm:]ss>  Specifies the sample interval for performance counter data collectors. In the above example it is 30 seconds.
-f <bin|bincirc|csv|tsv|sql>  Specifies the log format for the data collector. Here I used SQL which means it stores the data in SQL Server tables.
-o <path|dsn!log>  Specifies the output log file or the DSN and log set name in a SQL database. 
-u  <user> Account name which you use. This account needs to be given special permissions on remote machines and the machine where you run the Data Collection Set. In order to run Perfmon for a specific account, that account needs to have proper permissions.  
-p <password>  Password of the account you specified in -u 

Permissions Needed

The domain account needs to be given special permissions on remote machines and the machine where you run the Data Collection Set.

It has to be the member of following two Windows Groups:

  1. Performance Monitor Users
  2. Performance Log Users

In addition to that, the account should have write permission for the database where the DSN is pointing to.

Contents of the counter_list text file

This shows the machine and the counter that you want to collect.  In this example I am using the IP address for the machine which is 9.9.999.99.

"\\9.9.999.99\MSSQL$I02:Databases(_Total)\Transactions/sec"
"\\9.9.999.99\MSSQL$I02:Latches(_Total)\Average Latch Wait Time (ms)"
"\\9.9.999.99\MSSQL$I02:SQL Statistics\Batch Requests/sec"
"\\9.9.999.99\MSSQL$I02:Buffer Manager\Checkpoint pages/sec"
"\\9.9.999.99\MSSQL$I02:Buffer Manager\Lazy writes/sec"
"\\9.9.999.99\Memory\% Committed Bytes In Use"
"\\9.9.999.99\Memory\Avialble MBytes"
"\\9.9.999.99\Memory\Page Faults/sec"
"\\9.9.999.99\Memory\Cache Faults/sec"
"\\9.9.999.99\Memory\Page Reads/sec"
"\\9.9.999.99\LogicalDisk(F:)\Avg. Disk Bytes/Read"
"\\9.9.999.99\LogicalDisk(F:)\Avg. Disk Bytes/Write"
"\\9.9.999.99\LogicalDisk(F:)\Avg. Disk sec/Read"
"\\9.9.999.99\LogicalDisk(F:)\Avg. Disk sec/Write"
"\\9.9.999.99\LogicalDisk(F:)\Disk Transfers/sec"
"\\9.9.999.99\LogicalDisk(F:)\Disk Bytes/sec"

The preceding text file contains the counter lists that you want to capture. Remember that some counters are specific to SQL Server instances. So you need to mention the SQL Server instance name along with the counter as shown above. e.g.: MSSQL$I02:Databases

How to capture Perfmon for remote machine

If we want to capture data from a production server, we don't want to run it locally on the server due to performance impact. Instead we should set up an Admin box to fetch the relevant counters remotely. You can achieve this by using the IP address of the remote machine alone with the Perfmon counters as shown below:

"\\9.9.999.99\MSSQL$I02:Databases(_Total)\Log Bytes Flushed/sec"

The above counter refers to the remote machine 9.9.999.99 (if the counter is a SQL instance specific then it should be the SQL instance IP) and the counter is "Log Bytes Flushed/sec". This is a SQL instance specific counter and this is for a named instance (MSSQL$I02).

Manage Logman

After creating a Data Collection Set you can start, stop and delete the counter set using following commands:

Start the data collection

logman start <counter set name> 

Once you start the data collection, it creates the below two tables automatically in the database which the DSN points to and starts collecting data. You can query the below two tables and see the data it collects.

SELECT * FROM dbo.CounterData 
SELECT * FROM dbo.CounterDetails 

Stop the data collection

logman stop <counter set name> 

Delete the data collection

logman delete <counter set name> 

Restart the counter set

Once you setup the counter set, it may stop due to various reasons so that it cannot collect data anymore (e.g.: If the node gets shutdown). There should be a mechanism to restart the collection set in case it stops. I tested two such mechanisms using PowerShell and CmdExec using SQL Server Agent Job.

Using Windows PowerShell

$server = Get-Content env:computername; 
$datacollectorset = new-object -COM Pla.DataCollectorSet; 
$name="Diagnose_Perf_Issues"; 
$datacollectorset.Query($name,$server); 
$datacollectorset.start($false); 

Include the above commands in a SQL Server Agent job, but it needs some additional security to get it work successfully.

For PowerShell, the SQL Server Service account needs to be given full control to the counter set security. (Get Properties of the counter set and then refer to the Security tab. See below image.)

User Defined Data Collector Sets for Performance Monitor

Using CmdExec Prompt

EXEC master.dbo.xp_cmdshell 'LOGMAN START Diagnose_Perf_Issues' 

Include the above commands in a SQL Server Agent Job, but it needs some additional security to get it work successfully.

For the CmdExec solution, the SQL Server Agent service account needs to have full control to the counter set security. Same as above.

Notes

This is tested in SQL Server 2008 R2 Enterprise edition and the behavior may be different on other versions/editions.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Susantha Bathige Susantha Bathige currently works at Pearson North America as a Production DBA. He has over ten years of experience in SQL Server as a Database Engineer, Developer, Analyst and Production DBA.

View all my tips
Related Resources





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     



Learn more about SQL Server tools