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 dont 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 Ill 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 2008 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 OBJECT_ID('Perfcounters') IS NOT NULL 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 OBJECT_ID('Perfcounter') IS NOT NULL
DROP PROCEDURE Perfcounter
GO
CREATE PROCEDURE Perfcounter
AS
BEGIN
SET NOCOUNT ON
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 '%%') 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
SET NOCOUNT OFF
END
- 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.
Thats it you are done with it. You can use the data in the table Perfcounters to analyze the server status. For sample Im 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.
- SQL Server processor Utilization
- Free memory in KB
- 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
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 its most welcome.
Leave a Reply