how to take performance counters in SQL table or Flat Files using Command Prompt

Introduction:

In this post, We will use TYPEPERF command prompt utility to capture the perfmon counters.

Demo:

Capture performance counters in to SQL Tables:

You need to create an ODBC connection to SQL instance in order to write the perfmon data in SQL Tables. You can check the blog Here to learn, How to create a ODBC connection to SQL Instance.

Now create a text file with the list of counters:

For Named Instance(Replace SQL2012 with instance name):

\Memory\Available MBytes
\Paging File(_Total)\% Usage
\PhysicalDisk(* *)\% Disk Time
\PhysicalDisk(* *)\Avg. Disk Queue Length
\PhysicalDisk(* *)\Avg. Disk sec/Read
\PhysicalDisk(* *)\Avg. Disk sec/Write
\PhysicalDisk(* *)\Disk Reads/sec
\PhysicalDisk(* *)\Disk Writes/sec
\Processor(*)\% Processor Time
\MSSQL$SQL2012:Buffer Manager\Checkpoint pages/sec
\MSSQL$SQL2012:Buffer Manager\Buffer cache hit ratio
\MSSQL$SQL2012:Buffer Manager\Page life expectancy
\MSSQL$SQL2012:General Statistics\User Connections
\MSSQL$SQL2012:Memory Manager\Memory Grants Pending
\System\Processor Queue Length

For Default Instance
\Memory\Available MBytes
\Paging File(_Total)\% Usage
\PhysicalDisk(* *)\% Disk Time
\PhysicalDisk(* *)\Avg. Disk Queue Length
\PhysicalDisk(* *)\Avg. Disk sec/Read
\PhysicalDisk(* *)\Avg. Disk sec/Write
\PhysicalDisk(* *)\Disk Reads/sec
\PhysicalDisk(* *)\Disk Writes/sec
\Processor(*)\% Processor Time
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:General Statistics\User Connections
\SQLServer:Memory Manager\Memory Grants Pending
\System\Processor Queue Length

Copy these counter in a text file and save the file.

Now open command prompt as an administrator and redirect to the counters list location. In my case the file is saved in C:\counters

Now copy and paste below command in cmd and hit enter

TYPEPERF -f SQL -s <ServerName> -cf "Counters.txt" -si 1 -o SQL:<ODBCConnection name>!Batch1 -sc 100

Replcae <ServerName> with the name of your server.
Replace Counters.txt with the file name of your counters list.
-si is the interval to capture the counter value. In my case it is 1 so it will take the counter value in ever 1 sec
Replace <ODBC Connection name> with your ODBC connection name.
-sc is number of counters. In my case it is 100 so it will take the 100 samples.

Note: Command has !Batch1 after ODBC Connection Name. This is to differentiate the samples taken at different time. So
Next time when you will take sample again just change it with !Batch2.
As my sample interval is 1 and sample count is 100 so this command will execute for 100 seconds.

This command will create three SQL Tables
CounterData
CounterDetails
DisplayToID

Use below Query to read the counter values from SQL tables

SELECT MachineName ,
CounterName ,
InstanceName ,
CounterValue ,
CounterDateTime ,
DisplayString
FROM dbo.CounterDetails cdt
INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
INNER JOIN DisplayToID d ON d.GUID = cd.GUID
WHERE MachineName = 'ServerName'
ORDER BY CounterDateTime,Displaystring

You can use below Query to get the Avg of each counter

Select distinct
MachineName ,
InstanceName ,
[% Disk Time]
[% Processor Time],
[% Usage],
[Available MBytes],
[Avg. Disk Queue Length],
[Avg. Disk sec/Read],
[Avg. Disk sec/Write],
[Buffer cache hit ratio],
[Disk Reads/sec],
[Disk Writes/sec],
[Memory Grants Pending],
[Page life expectancy],
[Processor Queue Length],
[User Connections]
From (SELECT MachineName ,
CounterName ,
InstanceName ,
CounterValue
FROM dbo.CounterDetails cdt
INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
INNER JOIN DisplayToID d ON d.GUID = cd.GUID
AND DisplayString='Batch1'
)
AS Dataset
Pivot
(AvG(CounterValue) FOR CounterName IN ([% Disk Time],
[% Processor Time],
[% Usage],
[Available MBytes],
[Avg. Disk Queue Length],
[Avg. Disk sec/Read],
[Avg. Disk sec/Write],
[Buffer cache hit ratio],
[Disk Reads/sec],
[Disk Writes/sec],
[Memory Grants Pending],
[Page life expectancy],
[Processor Queue Length],
[User Connections]
)) PivotTable

Capture performance counters in a Flat File:

Use below command to take perfmon counter in CSV file


TYPEPERF -f csv -s <ServerName> -cf "Counters.txt" -si 1 -o Output1.csv -sc 100

Replace <ServerName> with the name of the server.

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

1 thought on “how to take performance counters in SQL table or Flat Files using Command Prompt

Leave a Reply

Your email address will not be published.