Analyze queries
How to analyze queries to identify performance problems. A step-by-step method.
Troubleshooting
SQL Server troubleshooting procedures: analyze queries, extract deadlocks, trace execution plans, retrieve error logs, use sp_whoisactive.
Extract deadlock information
Extract deadlock graphs from the SQL Server system_health session with a query, then save them as .xdl files to open them in SSMS.
Retrieve the error logs
Find the path of the SQL Server ERRORLOG files with SERVERPROPERTY, identify the rotated logs, and zip them to send them for analysis.
Identify excessive memory grants
Diagnose memory grants that are too large or too small in SQL Server: varchar(max) sizing, actual plans, the query_memory_grant_usage event, tempdb spills.
Use sp_whoisactive to see the active queries
Useful sp_whoisactive parameters to see the queries running now: execution plans, blocking chain leaders, memory grant information, average duration.
Trace execution plans
Capture actual execution plans with an Extended Events session on SQL Server 2017 CU21 and 2019, filtered by database, login or duration.
Detect an active transaction
Find the transaction that holds the log in SQL Server: DBCC OPENTRAN, log_reuse_wait_desc, session DMVs, and what to know before running KILL.