Collect SQL Server Performance Counter Sets Using Logman

By: Susantha Bathige   |   Updated: 2016-08-12   |   Comments   |   Related: > Performance Tuning

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.

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.)

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

• References: https://technet.microsoft.com/en-us/library/bb490956.aspx
• Check out these related tips:
  • Collecting performance counters and using SQL Server to analyze the data
  • Automate Performance Monitor Statistics Collection for SQL Server and Windows

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips