V tomto článku se dozvíte, jak identifikovat a řešit fragmentaci indexů v SQL Serveru. Identifikace fragmentace indexů a údržba indexů jsou důležitou součástí úlohy údržby databáze. Server Microsoft SQL Server neustále aktualizuje statistiky indexů při činnosti vložení, aktualizace nebo odstranění nad tabulkou. Fragmentace indexu je hodnota výkonu indexu v procentech, kterou lze získat pomocí SQL Server DMV. Podle hodnoty výkonnosti indexu mohou uživatelé provést údržbu indexů revizí procenta fragmentace pomocí operace Rebuild nebo Reorganize.
Proč se procento fragmentace indexu mění?
Procento fragmentace indexu se mění, když pořadí logických stránek nekoordinuje s pořadím fyzických stránek při přidělování stránek indexu. Při úpravě dat v tabulce může dojít ke změně velikosti informací na datové stránce. Před operací aktualizace tabulky byla stránka nahoře plná. Při aktualizační operaci na tabulce se však na datové stránce mohlo najít volné místo. Uživatelé mohou pozorovat rušivé uspořádání stránky s operací hromadného mazání v tabulce. Spolu s operacemi aktualizace a odstranění nebude datová stránka nahoře plná ani prázdná. Proto nevyužité volné místo zvyšuje nesoulad pořadí mezi logickou a fyzickou stránkou s rostoucí fragmentací, a to může způsobit nejhorší výkonnost dotazu a také spotřebovává více prostředků serveru.
Podstatnější je zdůraznit, že fragmentace indexu ovlivňuje výkonnost dotazu pouze při skenování stránek. V takových případech zvyšuje pravděpodobnost špatného výkonu i dalších dotazů SQL, protože dotaz s vysokou fragmentací indexu nad tabulkou trvá déle a spotřebovává více zdrojů, jako je mezipaměť, CPU a IO. Proto je pro ostatní SQL požadavky obtížné dokončit operaci s nekonzistentními prostředky serveru. Dokonce může dojít k zablokování operací Update a Delete, protože optimalizátor při generování plánu provádění dotazu nezíská informace o fragmentaci indexu.
Nad jednou tabulkou může být vytvořeno několik indexů s kombinací různých sloupců a každý index může mít jiné procento fragmentace. Nyní musí uživatelé před provedením vhodného indexu nebo před jeho převzetím do údržby zjistit tuto prahovou hodnotu z databáze. Níže uvedený příkaz T-SQL představuje efektivní způsob, jak ji zjistit pomocí detailů objektu.
Zjištění stavu fragmentace indexu pomocí příkazu T-SQL příkazu
|
1
2
3
4
5
6
. 7
8
9
10
11
12
13
14
|
VYBRAT S.name as ‚Schema‘,
T.name as ‚Table‘,
I.name as ‚Index‘,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) 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 DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
|
Zde vidíme, že maximální průměrné procento fragmentace je patrné jako 99 %, což se musí zabývat akcí na snížení fragmentace s volbami REBUILD nebo REORGANIZE. REBUILD nebo REORGANIZE je příkaz pro údržbu indexu, který lze provést pomocí příkazu ALTER INDEX. Uživatelé mohou tento příkaz provést také pomocí SSMS.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Vyhledejte a rozbalte tabulku v Průzkumníku objektů >> Otevřete indexy >> Klepněte pravým tlačítkem myši na cílový index >> Rebuild or Reorganize.
Jak je vidět na výše uvedeném obrázku, REBUILD a REORGANIZE jsou dvě dostupné volby pro přehrání operace trimování nad stránkou. V ideálním případě by tato operace měla být provedena v době mimo špičku, aby se zabránilo jejímu dopadu na ostatní transakce a uživatele. Microsoft SQL Server Enterprise Edition podporuje funkce indexu online a offline s indexem REBUILD.
REBUILD INDEX
INDEX REBUILD vždy zruší index a reprodukuje jej novými indexovými stránkami. Tuto činnost lze spustit paralelně pomocí online volby (verze Enterprise) s příkazem ALTER INDEX, který neovlivňuje běžící požadavky a úlohy podobné tabulky.
REBUILD Index lze nastavit online nebo offline pomocí níže uvedených příkazů SQL:
|
1
2
3
4
5
|
–základní příkaz REBUILD
ALTER INDEX Index_Name ON Table_Name REBUILD
–REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Pokud uživatel provede REBUILD INDEXU offline, pak objektový prostředek (tabulka) indexu nebude přístupný až do ukončení procesu REBUILD. To ovlivní i řadu dalších transakcí, které jsou s tímto objektem spojeny. Operace Rebuild index znovu vytvoří index. Proto také vytváří nové statistiky a připojuje záznamy o indexu do souboru protokolu transakcí databáze.
Před obnovením indexu vezměme například aktuální příděl stránek pro index databáze AdventureWorks, tabulku Sales.OrderTracking a index s názvem IX_OrderTracking_CarrierTrackingNumber.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‚AdventureWorks‘), OBJECT_ID(‚Sales.OrderTracking‘),NULL, NULL, ‚DETAILED‘) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‚IX_OrderTracking_CarrierTrackingNumber‘
ORDER BY allocated_page_page_id
|
Zde, V databázovém souboru tohoto rejstříku existuje 1961 stránek a prvních 5 stránek je 861, 862, 1627, 1628 a 1904 v pořadí podle čísla stránky. Nyní index znovu sestavíme pomocí SSMS.
Operace REBUILD indexu byla úspěšně dokončena a opět pomocí stejného dotazu T-SQL převezmeme odkazy na přidělení stránek pro stejný index.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‚AdventureWorks‘), OBJECT_ID(‚Sales.OrderTracking‘),NULL, NULL, ‚DETAILED‘) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‚IX_OrderTracking_CarrierTrackingNumber‘
ORDER BY allocated_page_page_id
|
Po obnovení indexu je obnovený počet stránek 1457, což bylo předtím 1961. Pokud zkontrolujete prvních 5 přidělených stránek téhož indexu, byl změněn pomocí nových odkazů na stránky. Předpokládá se, že index byl zrušen a vytvořen znovu. Měli bychom zkontrolovat obnovené procento fragmentace stejného indexu, a jak je vidět níže, je nyní 0,1 %.
REBUILD clustered index nad tabulkou ovlivňuje i ostatní indexy tabulky, protože REBUILD clustered index obnoví i neclustered index tabulky. Proveďte operaci přestavení všech indexů tabulky nebo databáze společně; uživatel může použít příkaz DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (‚DatabaseName‘, ‚TableName‘);
|
REORGANIZACE INDEXU
Příkaz REORGANIZACE INDEXU změní pořadí indexové stránky vypuštěním volného nebo nevyužitého místa na stránce. V ideálním případě se indexové stránky přeuspořádají fyzicky v datovém souboru. Příkaz REORGANIZE nezruší a nevytvoří index, ale pouze změní strukturu informací na stránce. REORGANIZE nemá žádnou volbu offline a ve srovnání s možností REBUILD nemá REORGANIZE vliv na statistiky. REORGANIZE se provádí vždy online.
Například před provedením REORGANIZE nad indexem vezměme odečet fragmentace pro databázi „AdventureWorks“, tabulku „Sales.OrderTracking“ a index s názvem „IX_OrderTracking_SalesOrderID“.
Zde je procento fragmentace indexu před provedením REORGANIZE 98,39. V případě, že je index před provedením REORGANIZE fragmentován, je procento fragmentace indexu vyšší. Na obrázku níže je seznam alokačních stránek do indexu.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‚AdventureWorks‘), OBJECT_ID(‚Sales.OrderTracking‘),NULL, NULL, ‚DETAILED‘) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‚IX_OrderTracking_CarrierTrackingNumber‘
ORDER BY allocated_page_page_id
|
Zde je ve výše uvedeném obrázku uvedeno celkem 459 stránek a prvních pět stránek je 1065, 1068, 1069, 1944 a 1945. Nyní proveďme příkaz REORGANIZE na indexu pomocí níže uvedeného příkazu T-SQL a podívejme se znovu na alokaci stránek.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Zde se celkový počet stránek sníží na 331, což předtím bylo 459 stránek. Navíc v seznamu prvních pěti stránek nevidíme nové stránky, z čehož vyplývá, že data jsou pouze restrukturalizována – nikoli znovu naplněna. Dokonce byste mohli vidět i nové stránky, to se stává v situaci, kdy je velký index silně fragmentován a přeuspořádání nad daty používá novou stránku.
Pro provedení operace REORGANIZE indexu na všech indexech tabulky nebo databáze dohromady může uživatel použít příkaz DBCC INDEXDEFRAG():
|
1
|
DBCC INDEXDEFRAG(‚DatabaseName‘, ‚TableName‘);
|
Jak je vidět, mezi příkazy REBUILD a REORGANIZE indexu je podstatný rozdíl. Zde mají uživatelé na výběr jednu z alternativ podle procenta fragmentace indexu. Můžeme pochopit, že neexistují žádné dokumentované standardy, nicméně správce databáze se řídí standardní rovnicí podle požadavku na velikost indexu a typ informací.
Obvyklé určení použití rovnice :
- Když je procento fragmentace mezi 15-30. Pokud je procento fragmentace v rozmezí 15-30, je možné použít rovnici, kterou se řídí správce databáze: REORGANIZE
- Když je Fragmentace větší než 30: REBUILD
Možnost REBUILD je užitečnější s možností ONLINE, když databáze není dostupná pro údržbu indexu mimo špičku.
Závěr
Fragmentace indexu je vnitřní fragmentace v datovém souboru. Základními parametry rychlého výkonu databáze jsou architektura databáze, návrh databáze a zápis dotazů. Dobrý návrh indexů s údržbou vždy zvyšuje výkonnost dotazů v databázovém stroji.
- Autor
- Nejnovější příspěvky
Zobrazit všechny příspěvky od Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Jak automatizovat rozdělování tabulek v SQL Serveru – 7. července 2020
- Konfigurace skupin dostupnosti SQL Serveru Always On na AWS EC2 – 6. července 2020