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