Vidhya Sagar – Blog

Performance counters are useful to gather information about server status and we can analyze the server status later using these data. In my environment one of my client is not ready to pay or buy metrics tools (normally we will be using BMC, HP etc). Our windows team has written they own custom scripts to capture the counters related to them, then it comes to our team how we are going to capture these data. Immediately it strikes my mind that we have a DMV “sys.dm_os_performance_counters” to get SQL performance counter values. So I thought of utilizing this procedure to get SQL Performance counter values and storing it in a table which will help us in the future to analyze the data.

Now my idea is to get SQL counter values using the DMV and also CPU utilization by SQL and free physical memory so that for quick reference I don’t need these two values from our windows team. I have written a procedure to capture some of the SQL performance counter values to store it in a table, then I’ll run this procedure every 10 seconds to capture the data using SQL job. It sounds not good to store the value in the same server, probably you can also run this query from another server and store the details.

Applies to:

• SQL Server 2005 and Above

Version: 1

Usage

• Connect to SQL Server and select a database (to create the procedure and table)
• Create the Performance counter table using the script below

--Table to store perf counter values

IF EXISTS (SELECT OBJECT_ID('Perfcounters'))

DROP TABLE Perfcounters

GO

CREATE TABLE Perfcounters

(CapturedTime DATETIME NOT NULL,

SQLProcessorUtilization BIGINT, AvailablePhysicalMemory_Kb BIGINT,

Buffercachehitratio BIGINT, Pagelifeexpectancy BIGINT,

UserConnections BIGINT, FullScans_p_sec BIGINT, PageSplits_p_sec BIGINT

)

GO

• After creating the table run the below script to create the procedure, run it in same db.

--Stored Procedure to get counter values to table

IF EXISTS (SELECT OBJECT_ID('Perfcounter'))

DROP PROCEDURE Perfcounter

GO

CREATE PROCEDURE Perfcounter

AS

WITH ring_buffers(ring_id,SQLProcessorUtilization)

AS

(

SELECT record.value('(./Record/@id)[1]', 'int') as record_id

,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization

FROM (SELECT CONVERT(XML, record) AS record from sys.dm_os_ring_buffers 

WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%') AS x

) 

INSERT Perfcounters 

SELECT TOP 1 CURRENT_TIMESTAMP,ring_buffers.SQLProcessorUtilization 

,a.available_physical_memory_kb

,pt.* FROM(SELECT RTRIM(object_name) + ' : ' + counter_name CounterName

, cntr_value from sys.dm_os_performance_counters

where RTRIM(object_name) + ' : ' + counter_name in ('SQLServer:Buffer Manager : Buffer cache hit ratio'

,'SQLServer:Buffer Manager : Page life expectancy'

,'SQLServer:General Statistics : User Connections'

,'SQLServer:Access Methods : Full Scans/sec'

,'SQLServer:Access Methods : Page Splits/sec')) as SourceData

PIVOT

(SUM(cntr_value)

FOR CounterName IN 

([SQLServer:Buffer Manager : Buffer cache hit ratio]

,[SQLServer:Buffer Manager : Page life expectancy]

,[SQLServer:General Statistics : User Connections]

,[SQLServer:Access Methods : Full Scans/sec]

,[SQLServer:Access Methods : Page Splits/sec])) pt, ring_buffers,sys.dm_os_sys_memory a

• Now Create a SQL job and just execute this procedure and make this job to run every 5 or 10 or 15 seconds (based on your requirement) to capture the data.

That’s it you are done with it. You can use the data in the table “Perfcounters” to analyze the server status. For sample I’m capturing below counter values in this script, however  you can add any SQL performance counters (all counter details can be found in DMV page, click on the link given above) to get the data.

1. SQL Server processor Utilization
2. Free memory in KB
3. SQLServer:Buffer Manager : Buffer cache hit ratio
4. SQLServer:Buffer Manager : Page life expectancy
5. SQLServer:General Statistics : User Connections
6. SQLServer:Access Methods : Full Scans/sec
7. SQLServer:Access Methods : Page Splits/sec

To retrieve the data just select data from “Percounters” database

select * from perfcounters order by capturedtime desc

I know there are some more things need to be added or modified to make it a good alternative, if you have any suggestions or concern it’s most welcome.