Capture SQL Performance Counters through TSQL

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.

  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

sql_perf_counters_v1_1

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.


Posted

in

by

Comments

9 responses to “Capture SQL Performance Counters through TSQL”

  1. Fabio avatar

    Great article!
    Thanks
    Fabio

  2. Bhushan avatar
    Bhushan

    Hi VidhyaSagar,

    I used above scripts to check server performance in 2005 but i got an error ” invalid object name ‘sys.dm_os_sys_memory ‘ “. later i found this DMV does not supports in sql server 2005 and I have to implement in sql server 2005..will you provide me the same script with modification in order to execute in SQL Server 2005..

    Thanks..

    1. VidhyaSagar avatar
      VidhyaSagar

      @Bhushan — Yes you are right, that dmv doesn’t available in SQL Server 2005. I have modifed the script to get the value from ring buffers however available memory is taken from last captured value and it’s not real time so you will be getting approximate value for this counter. Try the script below and let me know incase of any issues.

      SQL 2005 Script

      --THIS SCRIPT WORKS FROM SQL2005 HOWEVER THE AVAILABLE
      --PHYSICAL MEMORY IS TAKEN FROM LAST KNOWN VALUE AND ITS
      --NOT REAL TIME DATA SO IT WILL BE APPROXIMATE VALUE
      USE DBName
      GO
      --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, Last_Known_AvailablePhysicalMemory_Kb BIGINT,
      Buffercachehitratio BIGINT, Pagelifeexpectancy BIGINT,
      UserConnections BIGINT, FullScans_p_sec BIGINT, PageSplits_p_sec BIGINT
      )
      GO
      IF OBJECT_ID('Perfcounter') IS NOT NULL
      DROP PROCEDURE Perfcounter
      GO
      CREATE PROCEDURE Perfcounter
      AS
      BEGIN
      SET NOCOUNT ON
      DECLARE @APM TABLE (last_known_available_physical_memory_kb BIGINT)
      INSERT @APM
      SELECT
      TOP 1 cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB] 
      FROM sys.dm_os_ring_buffers rbf where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' ORDER BY rbf.timestamp desc
      ;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.last_known_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,@APM a
      SET NOCOUNT OFF
      END
      
  3. venkatesh avatar
    venkatesh

    Hi Vidya Sagar,

    this post helped me alot but
    I ran the above query,the procedure and table is created but when am running the select from perfcounters its not returning any data..please help me.

    1. VidhyaSagar avatar

      @Venkat – what is the error message you are getting?

      1. venkatesh avatar
        venkatesh

        Hi Vidyasagar,

        am not getting errors but when am perform a query
        select * from perfcounters…no rows are retrived from table.basically it has to return some counters right.
        please help me,

        thanks,
        venkat.

        1. VidhyaSagar avatar

          Let me know if the below query returns any row

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

          If its not returning any row then you have to reconfigure performance counters for sql server, run the below in command prompt

          lodctr /R:C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlctr.ini

  4. Abhi avatar
    Abhi

    How it will work for named instance?

    1. VidhyaSagar avatar

      It will automatically picks the counter value with respect to that instance.

Leave a Reply

Your email address will not be published. Required fields are marked *