Identifying and Resolving Slow Queries in SQL Server 2019

🔍 Step 1: Identify Slow Queries Using Extended Events

Use Extended Events to capture long-running queries:
Create an Extended Events Session for Slow Queries

CREATE EVENT SESSION Slow_Queries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
WHERE duration > 1000000 -- Captures queries running longer than 1 second (1,000,000 microseconds)
)
ADD TARGET package0.event_file
(SET filename = 'C:\ExtendedEvents\Slow_Queries.xel');

ALTER EVENT SESSION Slow_Queries ON SERVER STATE=START;
🔹 Run this for a few hours and analyze the data.
Analyze Captured Slow Queries
Run this to read the event file:

SELECT
event_data.value('(event/@name)', 'NVARCHAR(50)') AS EventName,
event_data.value('(event/data[@name="duration"]/value)', 'BIGINT') AS Duration_ms,
event_data.value('(event/data[@name="cpu_time"]/value)', 'BIGINT') AS CPU_Time_ms,
event_data.value('(event/data[@name="sql_text"]/value)', 'NVARCHAR(MAX)') AS SQL_Text
FROM sys.fn_xe_file_target_read_file('C:\ExtendedEvents\Slow_Queries*.xel', NULL, NULL, NULL);

🔹 Look for queries with high duration and optimize them.

🛠 Step 2: Optimize Slow Queries
Once you've identified slow queries, use these optimization techniques:
✅ Check Indexes
Use this to find missing indexes:

SELECT * FROM sys.dm_db_missing_index_details;
If missing indexes are found, create them using:

CREATE INDEX idx_Column ON TableName(ColumnName);

✅ Update Statistics

UPDATE STATISTICS TableName;

✅ Rewrite Inefficient Queries
Avoid SELECT *,
specify required columns.
Use proper JOINs instead of subqueries when possible.
Consider partitioning large tables.