Sometimes I like to see and know what SQL server is doing under certain operations and recently I wrote an extended events script to see what sort of I/O patterns my query was doing (This is a fun post). The important event here is sqlserver.file_read_completed.

I clear cache and let the fun begin.

CREATE EVENT SESSION [ReadMe] ON SERVER
ADD EVENT sqlserver.file_read_completed(SET collect_path=(1)
ACTION(sqlserver.database_name,sqlserver.sql_text)
WHERE ([sqlserver].database_name=N'AdventureDB'))
ADD TARGET package0.ring_buffer
(SET max_memory = 500, max_events_limit = 200 )

GO

ALTER EVENT SESSION [ReadMe] ON SERVER STATE=START
GO

I run queries such as:

SELECT COUNT(*) from sales.SalesOrderDetailEnlarged WITH (INDEX=1)
SELECT COUNT(*) FROM sales.SalesOrderHeader

I then use the following to shred the XML.

DECLARE @ShredMe XML;
SELECT @ShredMe = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = N'ReadMe';

SELECT
QP.value('(@timestamp)[1]', 'datetime2') AS [timestamp logged],
QP.value('(data[@name="database_id"]/value)[1]', 'INT') as [DatabaseID],
QP.value('(data[@name="mode"]/text)[1]', 'VARCHAR(MAX)') as [ReadType],
QP.value('(data[@name="size"]/value)[1]', 'INT') as [ReadSize]
FROM @ShredMe.nodes('RingBufferTarget/event[@name=''file_read_completed'']') AS q(QP)

GO

Oh look the ever so efficient Scatter/Gather. Quite interesting right? Don’t forget to clean up.

ALTER EVENT SESSION [ReadMe] ON SERVER STATE=STOP
GO
DROP EVENT SESSION [ReadMe] ON SERVER