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 build10.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_namelike'%perf%'

  ANDdescriptionISNOTNULL

  ANDNOT name in ('ID','UUID','VERSION',

               'CHANNEL','KEYWORD')

ORDERBYobject_name, name

 

 

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

CREATEEVENTSESSION perfmon_process

ONSERVER

ADDEVENT sqlserver.perfobject_process

ADDTARGET package0.asynchronous_file_target

  (SETFILENAME='c:\temp\process.xel')

WITH (STARTUP_STATE =ON)

GO

 

ALTEREVENTSESSION perfmon_process

ONSERVER

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

 

ALTEREVENT SESSION perfmon_process ONSERVER

ADDEVENT sqlserver.perfobject_system

 

ALTEREVENT SESSION perfmon_process ONSERVER

ADDEVENT sqlserver.perfobject_logicaldisk

 

ALTEREVENT SESSION perfmon_process ONSERVER

ADDEVENT sqlserver.perfobject_processor

 

*/

 

/*

Stop and/or Drop the trace

ALTEREVENTSESSION perfmon_process ONSERVER

STATE= STOP

DROPEVENTSESSION perfmon_process ONSERVER

 

*/

 

 

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