(888) 685-3101 ext. 2

A while back, I was tasked with using extended events to see what information could be captured to help a customer out with troubleshooting a performance issue.

My first step was to see which SQL Server extended events were available on the server. With each version of SQL Server, more extended events become available. I issued the following query to determine the packages available:

SELECT
name,
description
FROM sys.dm_xe_packages
WHERE (capabilities IS NULL)

Results:

name description
sqlos Extended events for SQL Operating System
sqlserver Extended events for Microsoft SQL Server

 

Then I executed the following query to give me information about the events available from the packages:

SELECT packages.name AS packagesname,
Objects.name AS Objectsname,
Objects.description
FROM sys.dm_xe_packages AS packages
JOIN sys.dm_xe_objects AS Objects
ON packages.guid = Objects.package_guid
WHERE (packages.capabilities IS NULL)
AND (Objects.capabilities IS NULL)
AND Objects.object_type = 'event'

 

Results:

packagename Objectsname description
sqlserver broker_activation_stored_procedure_invoked Broker activation stored procedure invoked
sqlserver buffer_manager_database_pages Buffer manager database pages
sqlserver buffer_manager_free_pages Buffer manager free pages
sqlserver buffer_manager_page_life_expectancy Buffer manager page life expectancy in milliseconds
sqlserver buffer_manager_stolen_pages Buffer manager stolen pages
sqlserver buffer_manager_target_pages Buffer manager target pages
sqlserver buffer_manager_total_pages Buffer manager total pages
sqlserver buffer_node_database_pages Buffer node database pages
sqlserver buffer_node_free_pages Buffer node free pages

 

There is a lot of good information here and would be a great place to spend some time (Maybe in a future blog).  But, since I was just trying to focus on getting a general overview of what was happening on the system, I chose to start with the follow events to capture:

For Memory Evaluate:

sqlserver.buffer_manager_free_pages

sqlserver.buffer_manager_page_life_expectancy

sqlserver.buffer_manager_stolen_pages

sqlserver.buffer_manager_total_pages

 

For wait Stats:

sqlos.wait_info

sqlos.wait_info_external

 

For Lock information:

sqlserver.lock_acquired

sqlserver.lock_released

sqlserver.locks_lock_timeouts_greater_than_0

sqlserver.locks_lock_wait

 

For Long IO issues:

sqlserver.long_io_detected

 

There was a specific set of sql statements the customer was interested in knowing the type of waits when executed, so I added the following limitation to insure I was just getting information specific to the one connection.

Declare @spforSession varchar(100)
Select @spforSession = convert(varchar(100),@@SPID)
sqlos.wait_info
(Where sqlserver.session_id = ' + @spforSession + ')

I then wanted the output of the captured events saved to a file so the last bit of my script was the following:

Declare @FilePath varchar(300)
SET @FilePath = 'C:'
ADD TARGET package0.asynchronous_file_target
(SET FILENAME = N''' + @filepath + 'VDBATracewithSPID.xel'',
METADATAFILE = N'''+ @filepath + 'VDBATraceWithSPID.xem'')
WITH (max_dispatch_latency = 1 seconds)

I then executed the script creating the extended events capture, the sql statements from the customer, then ended the extended event capture.

The files created are in XML format, so the next step was to figure out how to analyze the raw data into something meaningful to myself and the customer. For each event type I execute the following sql to  review the collected information.

SELECT ObjectColumn.name AS column_name
FROM sys.dm_xe_packages AS package
JOIN sys.dm_xe_objects AS packageObject
ON package.guid = packageObject.package_guid
JOIN sys.dm_xe_object_columns AS ObjectColumn
ON packageObject.name = ObjectColumn.OBJECT_NAME
AND packageObject.package_guid = ObjectColumn.object_package_guid
WHERE packageObject.object_type = 'event'
AND packageObject.name = 'wait_info'
AND ObjectColumn.column_type <> 'readonly'

 

After quickly reviewing the captured data, I chose to load the data in to a SQL table to make the reporting easier.

Begin Tran
Insert into EventsResults
Select
data.value (
'(/event/@timestamp)[1]'
,'DATETIME') as [Time]
, data.value (
'(/event/@name)[1]'
,'varchar(100)') as [EVENTNAME]
, data.value (
'(/event/data/@name)[1]'
,'varchar(100)') as [DATANAME]
, data.value (
'(/event/data[@name=''KEYWORD'']/text)[1]'
,'VARCHAR(100)') as [KEYWORD]
, data.value (
'(/event/data[@name=''count'']/value)[1]'
,'BigInt') as [ObjectCount]
, data.value (
'(/event/data[@name=''wait_type'']/text)[1]'
,'VARCHAR(100)') as [Wait Type]
, data.value (
'(/event/data[@name=''opcode'']/text)[1]'
,'VARCHAR(100)') as [Op]
 
 
FROM
(select CONVERT(xml, event_Data) as data
from sys.fn_xe_file_target_read_file (' C:VDBATraceWithSPID*.xel',
' C:VDBATraceWithSPID*.xem', null , null))
data;
commit

 

Once all the data was loaded into the table, I was able to write SQL statement against it to pull the raw data and place it into an excel spreadsheet. A couple of pivot tables later, and voila! I had the information I needed to pinpoint the performance issue had to deal with Network IO waits. Passing this information back to the end user allowed them hard data to take to the system people to evaluate, troubleshoot, and in the end modify a setting for the NIC card that resolved their performance issues.

Share This