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:
techcommunity....
learn.microsof...
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 - www.instagram....
🧑‍💼►Follow me on LinkedIn - / bobirmirzo-arslanov-54...
Watch all my playlists here:
🚀►Full course on Encrypting connections to SQL Server- • Full course on Encrypt...
🚀 ►SQL Server replication session: • SQL Server replication
🚀 ►SQL Server Always On Availability Group: • SQL Server Always On A...
🚀 ► SQL Server internals: • SQL Internals
🚀 ► Course in On-memory OLTP: • Course on In-Memory OLTP

[@AbrorAbror-j1b]

Very useful session
Helps a lot. thanks

[@umakantkesgire7720]

Very useful information. Thanks a lot for sharing

[@arslanov1886]

Happy you liked it

[@kimo_db]

Was really waiting for this one to drop. Gotta grab a snack and let’s gooo! Thanks!

[@arslanov1886]

🙌

[@kimo_db]

Interesting take on identity columns, never thought of that though it makes sense

[@arslanov1886]

Yeah. This is the design issue where many DBAs make mistake

[@arslanov1886]

The slides between 3:06 and 3:47 are misplaced. Please ignore the slides between this time. But the information I am speaking is correct. :)