MS SQL

1. Trwające zapytania

SELECT 
	sqltext.TEXT,
	session_id,
	STATUS,
	command,
	cpu_time,
	total_elapsed_time,
	wait_type
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE req.session_id <> @@SPID AND req.session_id > 50

Duży total_elapsed_time przy małym cpu_time = zapytanie czeka, nie liczy. Wtedy patrz w wait_type. Czasy w milisekundach.

2. Fragmentacja

SELECT
    S.name AS [Schema],
    T.name AS [Table],
    I.name AS [Index],
    DDIPS.index_type_desc AS [IndexType],
    DDIPS.avg_fragmentation_in_percent AS [FragmentationPercent],
    DDIPS.page_count AS [PageCount],
    DDIPS.page_count * 8.0 / 1024 AS [SizeMB]
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
) AS DDIPS 
INNER JOIN sys.tables T ON T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND I.index_id = DDIPS.index_id
WHERE
    DDIPS.database_id = DB_ID()
    AND T.is_ms_shipped = 0
    AND I.index_id > 0
    AND I.name IS NOT NULL
    AND DDIPS.page_count >= 100
    AND DDIPS.avg_fragmentation_in_percent > 10
ORDER BY
    DDIPS.avg_fragmentation_in_percent DESC;

Fragmentacja	Co z tym
poniżej 5%	olej
5–30%	REORGANIZE
powyżej 30%	REBUILD

page_count >= 100, bo mniejsze indeksy i tak siedzą w pamięci — przebudowa to strata czasu i logu.

'LIMITED' zostaw. 'DETAILED' czyta każdą stronę indeksu i potrafi zajechać produkcję.

3. Rozmiary indeksów

SELECT
    SCHEMA_NAME(t.schema_id) AS [SchemaName],
    t.name AS [TableName],
    i.name AS [IndexName],
    i.type_desc AS [IndexType],
    SUM(ps.used_page_count) * 8.0 / 1024 AS [IndexSizeMB],
    SUM(ps.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
JOIN sys.tables t ON ps.object_id = t.object_id
WHERE i.index_id > 0
GROUP BY
    t.schema_id, t.name, i.object_id, i.index_id, i.name, i.type_desc
HAVING SUM(ps.used_page_count) * 8.0 / 1024 > 1
ORDER BY [IndexSizeMB] DESC;

* 8.0 / 1024 — strona ma 8 KB

index_id > 0 wycina sterty. index_id = 1 to klaster, czyli same dane tabeli — zawsze na górze listy, nie ma co się przejmować.

4. Indeksy i użycie

S Tier do debugowania

Opus magnum optymalizatora ms sql, zamieszczam je jako ostatnie, bo godni go są tylko Ci, którzy poświęcili czas na przeczytanie poprzednich zapytań

SELECT
    SCHEMA_NAME(T.schema_id) AS Schema_Name,
    T.name AS Table_Name,
    IX.name AS Index_Name,
    IX.type_desc AS Index_Type,
    IX.is_unique AS IsUnique,
    IX.is_primary_key AS IsPrimaryKey,
    IX.is_unique_constraint AS IsUniqueConstraint,
    SUM(PS.row_count) AS 'RowCount',
    SUM(PS.used_page_count) * 8 AS IndexSizeKB,
    SUM(PS.used_page_count) * 8.0 / 1024 AS IndexSizeMB,
    ISNULL(IXUS.user_seeks, 0) AS NumOfSeeks,
    ISNULL(IXUS.user_scans, 0) AS NumOfScans,
    ISNULL(IXUS.user_lookups, 0) AS NumOfLookups,
    ISNULL(IXUS.user_updates, 0) AS NumOfUpdates,
    IXUS.last_user_seek AS LastSeek,
    IXUS.last_user_scan AS LastScan,
    IXUS.last_user_lookup AS LastLookup,
    IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
JOIN sys.tables T ON T.object_id = IX.object_id
JOIN sys.dm_db_partition_stats PS ON PS.object_id = IX.object_id AND PS.index_id = IX.index_id
LEFT JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.database_id = DB_ID() AND IXUS.index_id = IX.index_id AND IXUS.object_id = IX.object_id
WHERE T.is_ms_shipped = 0 AND IX.index_id > 0
GROUP BY
    T.schema_id, T.name, IX.name, IX.type_desc, IX.is_unique, IX.is_primary_key, IX.is_unique_constraint, IXUS.user_seeks, IXUS.user_scans,
	IXUS.user_lookups, IXUS.user_updates, IXUS.last_user_seek, IXUS.last_user_scan, IXUS.last_user_lookup, IXUS.last_user_update
ORDER BY IndexSizeMB DESC;

TL;DR:

dużo seeks     → zazwyczaj indeks jest użyteczny
dużo scans     → trzeba sprawdzić, dlaczego skanuje
dużo lookups   → trzeba sprawdzić, czy indeksy są covering
dużo updates   → koszt utrzymania indeksu

W większości przypadków:

Operacja	Typowo	Co oznacza
Index Seek	🟢 dobrze	SQL Server trafia w konkretny zakres indeksu
Index Scan	🟡 zależy	czyta dużą część albo cały indeks
Table Scan / Heap Scan	🟡/🔴 zależy	czyta tabelę bez użytecznego indeksu
Key/Bookmark Lookup	🟡/🔴	po znalezieniu rekordów w indeksie musi osobno dociągać kolumny z tabeli/clustered indexu (spróbuj INCLUDE)

Najważniejsze: sys.dm_db_index_usage_stats zeruje się przy restarcie usługi. Zero odczytów nie znaczy „nieużywany” — może znaczyć „serwer wstał wczoraj”.

Sprawdź, od kiedy w ogóle liczy:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;