Collect SQL Server Performance Counter Sets Using Logman
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?
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.
logman [create | query | start | stop | delete| update | import | export | /?] [options]
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.
|-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|
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:
- Performance Monitor Users
- 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).
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.
This is tested in SQL Server 2008 R2 Enterprise edition and the behavior may be different on other versions/editions.
- References: https://technet.microsoft.com/en-us/library/bb490956.aspx
- Check out these related tips:
About the author
View all my tips
Article Last Updated: 2016-08-12