Troubleshoot slow running queries with Pagelatch_* waits in SQL Server
Let us troubleshoot slow running queries with real examples.!
You will be able to understand how PAGELATCH occurs and step by step instructions to resolve this issue.
Let us get started !!!!
Recommended readings:
https://techcommunity.microsoft.com/t...
https://learn.microsoft.com/en-us/tro...
T-SQL used:
=====================
SELECT
req.session_id,
req.wait_type
, req.total_elapsed_time AS duration_ms
, req.cpu_time AS cpu_time_ms
, req.total_elapsed_time - req.cpu_time AS wait_time
, SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '),
1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY req.total_elapsed_time DESC;
=====================
📚 ►Full course on Troubleshooting sessions: • Troubleshooting
📷 ►Follow me on Instagram - https://www.instagram.com/arslanov_bo...
🧑💼►Follow me on LinkedIn - / bobirmirzo-arslanov-548960173
Watch all my playlists here:
🚀►Full course on Encrypting connections to SQL Server- • Full course on Encrypting connections...
🚀 ►SQL Server replication session: • SQL Server replication
🚀 ►SQL Server Always On Availability Group: • SQL Server Always On Availability Gro...
🚀 ► SQL Server internals: • SQL Internals
🚀 ► Course in On-memory OLTP: • Course on In-Memory OLTP