PERFMON Counters Available via Extended Events (XEvents) in SQL Server 2008 R2 SP1


The interdependence between a RDBMS and the Operating System (OS) is significant.  When involved with performance analysis on SQL Server it is sometimes helpful to know what is happening in the operating system at the same time.  This can provide insight into both the query/batch (e.g. what impact it was having on OS resources) and also the context within which it is executing (e.g. how were OS resources being consumed at the time of execution).

 

There are over 70 Extended Events relating specifically to Operating System counters specific to SQL Server performance that are now available in Service Pack 1 for SQL Server 2008 R2 {which is build 10.50.2500}.  These provide a new way to integrate Operating System performance closely with SQL Server performance and query metrics.

 

Service Pack 1 for SQL Server 2008 R2 shipped in June of 2011.  You can either find the installation information by Searching for the string “SQL Server 2008 R2 SP1” in Bing, or going to KBA 2528583 and getting the download location from there.

 

Once on 2008 R2 SP1, you can observe a listing of these new Perfmon related counters with this query:

SELECT

  name, object_name, description

FROM

  sys.dm_xe_object_columns

WHERE

      object_name like '%perf%'

  AND description IS NOT NULL

  AND NOT name in ('ID', 'UUID', 'VERSION',

                   'CHANNEL', 'KEYWORD')

ORDER BY object_name, name

 

 

A simple XEvents trace is able to demonstrate the information that is available through these Events:

CREATE EVENT SESSION perfmon_process

ON SERVER

ADD EVENT sqlserver.perfobject_process

ADD TARGET package0.asynchronous_file_target

  (SET FILENAME = 'c:\temp\process.xel')

WITH (STARTUP_STATE = ON)

GO

 

ALTER EVENT SESSION perfmon_process

ON SERVER

STATE = START

GO

 

 

SELECT

  event_data

FROM

  sys.fn_xe_file_target_read_file (

    'c:\temp\process*.xel',

    'c:\temp\process*.xem', null, null)

GO

 

/*

Add other events if you wish

 

ALTER EVENT SESSION perfmon_process ON SERVER

ADD EVENT sqlserver.perfobject_system

 

ALTER EVENT SESSION perfmon_process ON SERVER

ADD EVENT sqlserver.perfobject_logicaldisk

 

ALTER EVENT SESSION perfmon_process ON SERVER

ADD EVENT sqlserver.perfobject_processor

 

*/

 

/*

Stop and/or Drop the trace

ALTER EVENT SESSION perfmon_process ON SERVER

STATE = STOP

 

DROP EVENT SESSION perfmon_process ON SERVER

 

*/

 

 

With this knowledge, you can further integrate OS metrics into your other performance analysis tools.  More information on Extended Events can be found in SQL Server Books Online either local on your machine or via MSDN here

 

Additional Information

See Clas Hortien’s blog post here:

http://blogs.msdn.com/b/saponsqlserver/archive/2010/06/16/analyzing-statements-with-extended-events-extended-events-part-ii.aspx

and then integrate these new OS Events with that post in order to get a more complete performance picture as to what is happening on the host computer where SQL Server is running.

 

m

Comments (0)

Skip to main content