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.