Are we seeing hotspots? Using Extended Events and the histogram target

I am working with a customer in a performance and scalability lab. One of the functions they are testing is a home grown type of Change Tracking. They apply triggers, and capture the data to move to another database for analysis. They were experiencing ‘hotspots’ on data insert (PAGELATCH_EX) so they created a pseudo-partitioning that uses a hash partition to distribute the data to limit the hotspots.

On SQL Server 2012 they had no visibility into the details. We are currently testing in SQL 2017. My first inclination was to create an Extended Event Session on the wait_info event. Unfortunately, the only information about the resource is a memory pointer in that event.

However, in SQL Server 2016 a new event was added: sqlos.wait_completed which contains a wait_resource action which is identical to the wait_resource found in sys.dm_os_waiting_tasks. So we use a histogram to count occurrences by the DBID:FILEID:PAGE so we can see if any specific page is a hotspot. For more info on the histogram target see my previous post

Thanks to Pam Lahoud @SQLGoddess for reminding me of this new event.

[sql]
—
CREATE EVENT SESSION [RaptorPageLatchEX] ON SERVER
ADD EVENT sqlos.wait_completed(SET collect_wait_resource=(1)
WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'<yourDatabaseNameHere’)
AND [package0].[greater_than_uint64]([duration],(0)) — only want significant durations
AND [wait_type]=(52)))
ADD TARGET package0.histogram(SET filtering_event_name=N’sqlos.wait_completed’,slots=(16384),source=N’wait_resource’,source_type=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
—
[/sql]

Next is translating the DBID : FILEID : PAGEID format into an actual object.
[sql]
—
DECLARE @dbid int = 13
DECLARE @fileid int = 6
DECLARE @pageid int = 178120

— output DBCC to the console
DBCC TRACEON(3604)
— fourth parameter is the output type – 1 works fine.
DBCC PAGE (@dbid,@fileid,@pageid,1)

–sample output from DBCC PAGE
–scroll down to metadata area of the header and set @object_id = Metadata: ObjectId
–Metadata: AllocUnitId = 72057594695516160
–Metadata: PartitionId = 72057594681163776 Metadata: IndexId = 1
–Metadata: ObjectId = 959342482 m_prevPage = (4:429096) m_nextPage = (0:0)

DECLARE @object_id int = 959342482
select object_schema_name(@object_id) +’.’+ object_name(@object_id) as [Object Name]
—
[/sql]