MSSQL Indexy: Proč je tvá databáze líná jak pondělí a co s tím?

Pomalé selecty, zamrzlé reporty, uživatelé refreshují stránku rychleji než poštovní doručovatel běží k výdejnímu okénku. V 90 % případů za to může špatný, chybějící nebo zruinovaný index. Pojďme mu dát dietu, fit-ko a trochu statistiky.

1 · Index ≠ bonus, ale základ

Index v relační databázi funguje jako rejstřík knihy: místo listování 800 stranami rovnou skočíš na správnou kapitolu. Bez indexu dělá plánovač TABLE SCAN – pročesává celé úložiště, polyká CPU i I/O a blokuje ostatní dotazy. V dnešním světě SSD a paměti se 512 GB se to „nějak“ udýchá, ale účet v cloudu nebo večerní špička tě probere k realitě.

1.1 Co říká Microsoft

Podle oficiální dokumentace je správné indexování nejdůležitější single-factor tuningu SQL Serveru. Každý nový release přidává inteligentní funkce (Adaptive Memory Grants, Intelligent Query Processing), ale žádný AI-čkový zázrak nepřepere špatný design tabulek.

2 · Kdy index zpomalí víc, než pomůže

INSERT/UPDATE overhead – každý zápis musí modifikovat datové i indexové stránky.
Fragmentace disku – rozsekané list pages brzdí čtení.
Velké indexy v paměti – zabírají buffer pool, zmenšují cachování dat.
Zastaralé statistiky – špatný odhad kardinality ⇒ špatný plán.
Duplicitní nebo překrývající se indexy – žerou místo i čas bez přínosu.

3 · Typy indexů: clustered, nonclustered, columnstore…

3.1 Clustered index

Fyzické uspořádání řádků v tabulce. Může být jen jeden. Primární klíč bývá dobrý kandidát, ale ne vždy (např. GUID vs. IDENTITY).

3.2 Nonclustered index

Samostatná B-tree struktura, drží klíče + row locator (RID / clustered key). Lze mít desítky. Hodí se na vysoce selektivní filtry (WHERE email='@').

3.3 Included columns

Přidáš neklíčový sloupec do leaf pages ⇒ COVERING index, query nemusí sahat do tabulky.

3.4 Filtered index

WHERE IsDeleted = 0 – dramaticky zmenší velikost, super pro soft-delete pattern.

3.5 Columnstore

Kolonkové ukládání, ideální pro BI/reporting, komprese 10:1. Od SQL 2019 dostupné clustered i nonclustered s deltastore zápisem.

4 · Statistiky: mentální GPS plánovače dotazů

Statistiky popisují distribuci dat v indexu. Pokud jsou staré, plánovač podstřelí/overstřelí odhady a vybere horší strategii (nested loops vs. hash join). Auto Update Stats se spouští, když se změní cca 20 % + 500 řádků, ale to nestačí u velkých tabulek. Můžeš:

Zapnout Trace Flag 2371 (od SQL 2016 on by default) ⇒ dynamický práh.
Používat UPDATE STATISTICS v maintenance window.
Pro kritické tabulky nasadit ASYNC_STATS.

5 · Diagnostika pomalé DB – kde hledat viníka

5.1 DMV (sys.dm_db_index_usage_stats)

SELECT object_name(s.object_id) AS TableName,
       i.name AS IndexName,
       user_seeks + user_scans AS Reads,
       user_updates AS Writes
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes              AS i ON
     i.object_id = s.object_id AND i.index_id = s.index_id
WHERE database_id = DB_ID() AND user_seeks + user_scans = 0
      AND user_updates > 0
ORDER BY user_updates DESC;

Indexy s 0 čteními a tisíci zápisů – pryč s nimi.

5.2 Missing Index DMV

sys.dm_db_missing_index_group_stats + _detail ti řeknou, jaké potenciální indexy by zrychlily workload.

5.3 Query Store

Zapni v SQL 2019+, sleduj regrese plánů (Forced Plan) a vylaď dotazy před masivním přestavováním indexů.

6 · Skript #1 – najdi chybějící indexy

-- MissingIndexes.sql
SELECT TOP 20
       migs.avg_total_user_cost * (migs.avg_user_impact/100.0) * (migs.user_seeks + migs.user_scans) AS ImprovementMeasure,
       'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) +
       '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') +
       ' ON ' + OBJECT_SCHEMA_NAME(mid.object_id) + '.' + OBJECT_NAME(mid.object_id) +
       '(' + ISNULL(mid.equality_columns,'') +
       ISNULL(',' + mid.inequality_columns,'') + ')' +
       ISNULL(' INCLUDE (' + mid.included_columns + ')','') AS CreateStatement
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig  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 ImprovementMeasure DESC;

Skript generuje CREATE INDEX statement, ale NESPUŠTĚJ bez revize! Mohlo by vzniknout 50 duplicit.

7 · Skript #2 – rebuild vs. reorganize

7.1 Rozhodovací strom

Fragmentace (%)	Akce	Dopad
< 5 %	ignoruj	nula
5–30 %	`ALTER INDEX ... REORGANIZE`	online, log minimal
> 30 %	`ALTER INDEX ... REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON)`	více logu, ale lepší komprese

7.2 Automatický job

EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow       = NULL,
    @FragmentationMedium    = 'INDEX_REORGANIZE',
    @FragmentationHigh      = 'INDEX_REBUILD_ONLINE',
    @LogToTable = 'Y';

Pokud používáš Ola Hallengren maintenance script, máš vyhráno.

8 · Skript #3 – update stats bez downtime

-- UpdateStatsSmart.sql
DECLARE @cmd nvarchar(max) = N'';
SELECT @cmd = @cmd + N'UPDATE STATISTICS '
       + QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
       + QUOTENAME(name) + ' WITH RESAMPLE;'
FROM sys.tables
WHERE is_memory_optimized = 0
  AND modify_date > DATEADD(day,-7,GETDATE());
EXEC (@cmd);

Aktualizuje jen tabulky, do kterých se poslední týden sahalo. Běží rychleji a nezahltí log.

9 · Skript #4 – monitoring fragmentace v Grafaně

-- FragmentationExport.sql
SELECT DB_NAME(database_id)          AS db,
       OBJECT_NAME(object_id, database_id) AS tbl,
       index_id,
       avg_fragmentation_in_percent  AS frag,
       page_count
FROM sys.dm_db_index_physical_stats
     (NULL, NULL, NULL, NULL, 'SAMPLED')
WHERE page_count > 1000;

Exportuj výsledek do CSV každou hodinu → Promtail → Loki → Grafana dashboard. Trigger alert na frag > 40 %.

10 · Case study: e-shop s 10 milionů objednávek

Online prodejce zaznamenal v sezonním piku průměrné latency 1 200 ms na stránce košíku. Analýza DMV ukázala chybějící index na kombinaci (CustomerId, Status). Po vytvoření covering indexu a rebuild stávajících se průměrná odezva snížila na 130 ms, CPU load spadl z 85 % na 35 % a Azure SQL DTU spotřeba klesla o 46 % – úspora 420 € měsíčně.

11 · Checklist pravidelné údržby

Zapnout Query Store pro všechny produkční DB.
Měsíčně projít sys.dm_db_index_usage_stats a zahodit zombie indexy.
Týdně spustit IndexOptimize (reorg/rebuild podle % fragmentace).
Týdně UPDATE STATISTICS WITH FULLSCAN pro kritické tabulky.
Každé čtvrtletí otestovat Filtered/Columnstore na readonly reporty.
Verze SQL Serveru držet na posledním CU (cumulative update).
Nikdy nemít víc než 5 indexů na jednu OLTP tabulku bez jasného benefitu.
U GUID klíčů použít NEWSEQUENTIALID() (menší fragmentace).
Do CI/CD pipeline přidat sp_BlitzIndex jako kvalitu brány.

12 · TOP 5 mýtů o indexech

„Víc indexů = rychlejší DB.“ – Do určitého počtu; pak inserty umřou.
„Clustered musí být primární klíč.“ – Může, ale nemusí (např. časové řady).
„Fragmentace SSD nevadí.“ – Vadí buffer poolu i plánovači.
„Auto Create Index v Azure vyřeší vše.“ – Vyřeší něco, ale i vytvoří zbytečnosti.
„Columnstore je jen pro datová jezera.“ – Od SQL 2019 funguje skvěle i pro OLTP hybrid (HTAP).

13 · Závěr

Nedostatek (nebo přebytek) indexů promění tvůj SQL Server v pondělního psa – líného a bručícího. Stačí pár správných skriptů, disciplína v údržbě a poznání, že index je investice: zaplatíš malou daň při zápisu, ale získáš raketový čtení a spokojené uživatele. Vezmi checklist, hoď joby do SQL Agentu a připrav se na to, že místo restartu služeb začneš řešit jen to, kam si odložit kávu.

Klíčová slova

mssql indexy, sql performance, slow query, db tuning, clustered index, nonclustered, fragmentation, rebuild index, update stats, query plan, t-sql optimize, dba tips

↥ Zpět nahoru