Najcięższe zapytania (wg zużycia CPU / czasu / IO)
Pokazuje które zapytania były najwolniejsze średnio. Należy zwrócić uwagę na kolumny:
kolumny:
- Avg_Elapsed_Time_ms – czas trwania,
- Avg_CPU_Time_ms – obciążenie CPU,
- Avg_Logical_Reads – obciążenie pamięci (I/O logiczne).
SELECT TOP 20
qs.total_elapsed_time / qs.execution_count AS [Avg_Elapsed_Time_ms],
qs.total_worker_time / qs.execution_count AS [Avg_CPU_Time_ms],
qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
qs.execution_count,
qs.creation_time,
DB_NAME(st.dbid) AS [DatabaseName],
OBJECT_NAME(st.objectid, st.dbid) AS [ObjectName],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2)+1) AS [QueryText]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY [Avg_Elapsed_Time_ms] DESC;Zapytania z największym zużyciem I/O dyskowego
Pokazuje zapytania, które najbardziej „mielą” dane.
SELECT TOP 20
(total_logical_reads + total_physical_reads) / execution_count AS [Avg_IO],
qs.execution_count,
DB_NAME(st.dbid) AS [DatabaseName],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2)+1) AS [QueryText]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY [Avg_IO] DESC;Najczęściej blokowane lub blokujące zapytania
Pokazuje czy opóźnienia wynikały z blokad / czekania.
SELECT TOP 20
wt.session_id AS [BlokującySession],
wt.blocking_session_id AS [BlokowanyPrzez],
wt.wait_type,
wt.wait_duration_ms,
wt.resource_description,
t.text AS [Zapytanie]
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_requests r ON wt.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY wt.wait_duration_ms DESC;Analiza typów oczekiwań (wait stats)
pokazuje, na co serwer najczęściej czekał – CPU, dysk, blokady, sieć itd.
Najczęstsze typy:
- PAGEIOLATCH_* → wolne I/O (dysk),
- LCK_* → blokady,
- CXPACKET → złe równoległe plany,
- SOS_SCHEDULER_YIELD → CPU przeciążony.
SELECT TOP 30
wait_type,
wait_time_ms / 1000.0 AS [CalkowityCzas_s],
100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS [Procent],
signal_wait_time_ms / 1000.0 AS [SignalCzas_s],
waiting_tasks_count AS [LiczbaZdarzen]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
'SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH',
'WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',
'XE_TIMER_EVENT','XE_DISPATCHER_JOIN','BROKER_TO_FLUSH','BROKER_TASK_STOP',
'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT','BROKER_EVENTHANDLER',
'TRACEWRITE','BROKER_RECEIVE_WAITFOR','ONDEMAND_TASK_QUEUE',
'DBMIRROR_EVENTS_QUEUE','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'WAITFOR_TASKSHUTDOWN','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
'HADR_WORK_QUEUE','SP_SERVER_DIAGNOSTICS_SLEEP')
ORDER BY wait_time_ms DESC;Brakujące indeksy (wg kosztu szacowanego)
Pokazuje brakujące indeksy, które SQL Server sugeruje na podstawie planów zapytań.
UWAGA
Nie dodawaj ich bez przemyślenia — czasem są zduplikowane lub zbyt ogólne.
SELECT TOP 20
DB_NAME(mid.database_id) AS [DatabaseName],
OBJECT_NAME(mid.object_id, mid.database_id) AS [Tabela],
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS [SzacowanyZysk],
'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' +
REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') + ']'
+ ' ON ' + mid.statement + '(' + ISNULL(mid.equality_columns,'')
+ CASE WHEN mid.inequality_columns IS NULL THEN ''
ELSE ',' + mid.inequality_columns END + ')'
+ ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS [Propozycja]
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY [SzacowanyZysk] DESC;Fragmentacja indeksów
Wskazuje indeksy, które są pofragmentowane i mogą spowalniać zapytania.
SELECT
dbschemas.name AS [Schema],
dbtables.name AS [Table],
dbindexes.name AS [Index],
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstats
JOIN sys.tables dbtables ON dbtables.object_id = indexstats.object_id
JOIN sys.schemas dbschemas ON dbtables.schema_id = dbschemas.schema_id
JOIN sys.indexes AS dbindexes ON dbindexes.object_id = indexstats.object_id
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
ORDER BY indexstats.avg_fragmentation_in_percent DESC;Statystyki auto-update (czy są nieaktualne)
Sprawdza, czy statystyki są stare / przestarzałe = złe plany zapytań.
SELECT
name AS [Statystyka],
STATS_DATE(object_id, stats_id) AS [DataAktualizacji],
OBJECT_NAME(object_id) AS [Tabela]
FROM sys.stats
WHERE STATS_DATE(object_id, stats_id) < DATEADD(DAY, -7, GETDATE());
