SQL Server I/O

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

scattergath

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
Advertisements

One thought on “SQL Server I/O

  1. Pingback: Dew Drop - December 12, 2018 (#2858) - Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s