Free Press

Účelem vytváření indexů SQL v databázových tabulkách je zvýšit výkonnost dotazů, které čtou data ze základní tabulky, tím, že urychlí procesy načítání dat. Tyto transakční tabulky však nejsou statické a v průběhu času se velmi často mění. Tyto změny, které se provádějí v databázové tabulce, zahrnují přidávání nových záznamů, úpravy nebo mazání stávajících záznamů. Protože tyto změny by měly být replikovány do indexů souvisejících tabulek, dojde časem k fragmentaci indexu tabulky.

Přehled fragmentace indexů SQL serveru

Existují dva hlavní typy fragmentace indexů SQL: interní a externí fragmentace. Vnitřní fragmentace je způsobena vložením nového záznamu nebo aktualizací existujícího záznamu s hodnotami, které se nevejdou do aktuálního volného místa v datové stránce, a výsledkem je rozdělení stránky na dvě stránky, aby se tam nová hodnota vešla. V tomto případě SQL Server Engine přesune přibližně 50 % dat z aktuální stránky na novou datovou stránku, která vznikne operací rozdělení, aby udržel vyvážený strom indexů SQL. Vnitřní fragmentaci mohou způsobit také náhodné operace mazání, jejichž výsledkem je prázdné místo na datových stránkách. Tento typ operací zanechává datové stránky nezaplněné a vede k větší velikosti indexu SQL Serveru a pomalejšímu výkonu kvůli nutnosti načíst více datových stránek pro získání požadovaných dat.

Vnější fragmentace je způsobena, když jsou datové stránky indexu SQL Serveru rozptýleny ve fyzickém souboru databáze, protože logické pořadí indexu SQL Serveru neodpovídá fyzickému pořadí na základní diskové jednotce. Externí fragmentace může být způsobena náhodnými operacemi mazání, které ponechají určitý počet stránek rozsahu prázdné, zatímco samotný rozsah je rezervován. Tento typ fragmentace má za následek zhoršení výkonu, protože přeskakování mezi rozptýlenými stránkami dat za účelem načtení požadovaných dat z podkladové diskové jednotky zabere více času a spotřebuje více prostředků.

Důvodem, proč nás fragmentace indexů SQL velmi zajímá, je to, že index, který jste vytvořili za účelem zvýšení výkonu systému, může po velké fragmentaci zhoršit výkon vašich dotazů. Představte si rozdíl v čase a počtu načtení z disku mezi načtením určitého počtu řádků ze zdravého indexu SQL, v němž jsou data uložena v souvislých datových stránkách v databázovém souboru, a načtením stejného počtu řádků z vysoce fragmentovaného indexu SQL Serveru, v němž jsou data rozptýlena v nesouvislých datových stránkách v databázovém souboru.

Pokud zjistíte, že index SQL tabulky je fragmentovaný, musíte problém s fragmentací vyřešit provedením reorganizace indexu SQL Serveru nebo obnovení indexu na základě procenta fragmentace indexu SQL. Například index SQL Serveru s procentem fragmentace menším než 30 % lze reorganizovat, přičemž index SQL Serveru s úrovní fragmentace vyšší než 30 % je třeba přestavět.

• Poznámka: Další informace naleznete v části Údržba indexů SQL Serveru.

Problémy s fragmentací indexů SQL Serveru a rozdělením stránek můžete aktivně překonat správným návrhem indexu a nastavením možností Fill Factor a pad_index při vytváření indexu pomocí správných hodnot.

• Poznámka: Další informace naleznete v části Operace s indexy SQL Serveru.

SQL Server Index Fragmentation Report

Za osvědčený postup se nepovažuje pouhé vytvoření indexu SQL v databázové tabulce, abyste využili výhod z vylepšení operací načítání dat, a ponechání tohoto indexu SQL Serveru navždy bez průběžného sledování jeho fragmentace a jeho údržby.

SQL Server nám poskytuje několik metod, které lze použít ke kontrole procenta fragmentace indexu SQL.

Vlastnosti indexu SQL Serveru

První metodou je stránka Fragmentace v okně Vlastnosti indexu SQL Serveru. Klepněte pravým tlačítkem myši na index, který potřebujete zkontrolovat, a vyberte možnost Vlastnosti. Na stránce Vlastnosti indexu SQL Serveru přejděte na stránku Fragmentace a v části Fragmentace se zobrazí procento fragmentace indexu a míra zaplnění stránek indexu SQL Serveru, jak je uvedeno níže:

V několika krocích můžete snadno zkontrolovat procento fragmentace indexu SQL Serveru vybraného indexu z okna vlastností indexu. Co když ale potřebujete mít přehled o procentu fragmentace všech indexů tabulky nebo všech indexů databázových tabulek? V takovém případě musíte jít a zkontrolovat je jeden po druhém. Výsledkem je, že na jednu zprávu o databázi nebude stačit jeden pracovní týden!“

sys.dm_db_index_physical_stats

Další metodou, kterou lze použít ke kontrole procenta fragmentace indexů databáze, je dotaz na funkci dynamické správy sys.dm_db_index_physical_stats, která byla poprvé zavedena v SQL Serveru 2005 jako náhrada za zastaralý příkaz DBCC SHOWCONTIG. Poskytuje informace o velikosti a procentuální fragmentaci databázových indexů.

Abyste z DMF sys.dm_db_index_physical_stats získali smysluplné informace o procentuální fragmentaci všech indexů v rámci zadané databáze, musíte ji spojit s příkazem sys.indexes DMV, jako v níže uvedeném skriptu T-SQL:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

Na základě svých schopností vývoje SQL můžete provést filtraci, kterou potřebujete k získání fragmentace indexů SQL v požadovaném rozsahu, bez možnosti získání souhrnu pro celkové databáze instancí, s ohledem na to, že zobrazené údaje jsou od posledního restartu služby SQL Server. Výsledek v našem případě bude vypadat takto:

SQL Server Index Physical Statistics Standard Report

SQL Server nám poskytuje několik standardních sestav, které obsahují užitečné statistické informace o vybrané databázi. Sestava Index Physical Statistics, je jednou ze standardních sestav SQL Serveru, která vrací statistické informace o oddílech indexu SQL Serveru, procento fragmentace a počet stránek na každém oddílu indexu SQL na úrovni databáze, od posledního restartu služby SQL Serveru, s doporučením, jak odstranit problém s fragmentací indexu, pokud existuje, na základě procenta fragmentace indexu SQL Serveru.

Chcete-li zobrazit sestavu Fyzická statistika indexů SQL Serveru, klikněte pravým tlačítkem myši na databázi, vyberte možnost Sestavy -> Standardní sestavy a poté vyberte sestavu Fyzická statistika indexů, jak je uvedeno níže:

A vygenerovaná sestava na úrovni databáze, kterou nelze filtrovat a je třeba ručně rozbalit výsledky jednotlivých indexů tabulek, bude vypadat, jak je uvedeno níže:

ApexSQL Defrag

Nástroj ApexSQL Defrag je nástroj třetí strany pro defragmentaci indexů SQL, který lze snadno použít ke kontrole informací o fragmentaci a využití indexů SQL Serveru a k provedení nebo naplánování vhodné opravy problému s fragmentací indexů.

Z jednoho centrálního místa můžete analyzovat a kontrolovat informace o fragmentaci a využití na různých úrovních, včetně informací o fragmentaci a využití indexů SQL na úrovni indexů SQL Serveru, na úrovni tabulek, na úrovni databáze a nakonec na celkové úrovni instance SQL Serveru. Poskytuje nám různé typy filtrů, které lze použít k zobrazení a údržbě pouze cílových indexů.

ApexSQL Defrag nám také umožňuje konfigurovat nastavení Fill Factor pro indexy tabulek, vytvářet upozornění pro všechny defragmentační úlohy a zásady a zasílat e-mailová oznámení o úspěšných nebo neúspěšných úlohách.

Zobrazené informace poskytnou dobrý přehled o velikosti indexů SQL a procentu fragmentace, které nebudou ovlivněny žádným restartem služby SQL Server nebo restartem serveru. Tyto informace totiž budou uloženy v centrální databázi úložiště pro nástroj ApexSQL Defrag.

Instalace nástroje ApexSQL Defrag

Nástroj ApexSQL Defrag lze snadno stáhnout z Centra stahování ApexSQL a nainstalovat na server podle přímočarého průvodce instalací, jak je uvedeno níže:

Nejprve budete vyzváni k zadání účtu služby, který bude použit jako kontext spouštění pro agenta ApexSQL Defrag a předává příkazy instanci SQL Serveru, a to následujícím způsobem:

Po zadání servisního účtu ApexSQL Defrag budete vyzváni k určení, kam se má nástroj ApexSQL Defrag nainstalovat a zda se má na ploše vytvořit ikona zástupce nástroje, jak je uvedeno níže:

Zkontrolujte, zda zadaná cesta obsahuje minimálně 127 MB volného místa, a nástroj ApexSQL Defrag bude na server úspěšně nainstalován. A zobrazí se následující oznámení, které vám poskytne možnost spustit ApexSQL Defrag přímo:

Pokud spustíte ApexSQL Defrag poprvé, budete vyzváni k potvrzení vytvoření centrální databáze úložiště, kde budou uložena tato historická a konfigurační data, a to následujícím způsobem:

Přidat nový server

Po vytvoření databáze úložiště se ApexSQL Defrag spustí bez instance SQL. Chcete-li zkontrolovat procento fragmentace indexů SQL na konkrétní instanci SQL Serveru, musíte tuto instanci přidat do tohoto nástroje. Toho dosáhnete klepnutím na tlačítko Přidat na kartě Domů, jak je znázorněno níže:

V okně Připojit k SQL Serveru budete vyzváni k zadání názvu instance SQL Serveru, která bude použita, a pověření, která jsou vyžadována pro připojení k tomuto serveru, jak je uvedeno níže:

Analýza indexů SQL serveru

Po přidání serveru můžete provést novou kontrolu všech informací o fragmentaci indexů SQL na kartě Fragmentace níže:

ApexSQL Defrag vám poskytuje možnost analyzovat všechny indexy dané instance na základě tří režimů:

• DETAILED: v němž budou během procesu skenování načteny všechny stránky dat a indexu SQL
• SAMPLED: v němž bude načteno pouze 1 % stránek s ohledem na to, že index SQL Serveru obsahuje více než 10 000 stránek
• LIMITED:

Chcete-li analyzovat informace o indexu SQL Serveru z připojené instance SQL Serveru, klikněte na tlačítko Analyzovat na kartě Fragmentace a vyberte, zda chcete provést rychlou nebo hloubkovou analýzu. Provedeme Hloubkovou analýzu, abychom získali úplné informace, jak je uvedeno níže:

Nadále sledujte ukazatel průběhu na spodní straně nástroje, abyste zkontrolovali průběh analýzy, jak je uvedeno níže:

A zobrazí se komplexní zpráva, která obsahuje úplné informace o všech indexech SQL vytvořených pod každou databází této instance SQL Serveru, jak je uvedeno níže:

S možností ponořit se mezi různými úrovněmi; instancí SQL Serveru, databází, tabulkou nebo indexem, a to jediným kliknutím pod stejným centrálním oknem, a filtrovat načtené výsledky na základě konkrétní úrovně fragmentace nebo prahové hodnoty, jak je znázorněno níže:

Kromě toho můžete přetažením libovolného sloupce ze sloupců sestavy seskupit celkovou sestavu na základě hodnot tohoto sloupce, jako například v příkladu níže, ve kterém jsme seskupili data sestavy na základě názvu tabulky:

Reporty ApexSQL Defrag

ApexSQL Defrag nám poskytuje řadu reportů, které zobrazují statistické informace o indexech databáze. Tyto sestavy lze konfigurovat a kontrolovat na kartě Sestavy níže:

První sestavou je sestava Total, která zobrazuje grafické znázornění fragmentace indexů SQL a využití místa na disku na zvolené úrovni. Pokud například kliknete na název instance, sestava vykreslí graf procentuální fragmentace a graf využití místa na úrovni instance serveru SQL, jak je uvedeno níže:

Pokud kliknete na libovolnou databázi v okně Průzkumník serveru, sestava automaticky změní zobrazení na graf na úrovni databáze, jak je uvedeno níže:

Druhou sestavou, kterou lze rovněž zobrazit na různých úrovních, je sestava Statistiky. Tento report zobrazuje statistické informace o celkové velikosti a fragmentaci indexů a rozložení typů indexů na zvolené úrovni, jak je znázorněno níže:

Třetím reportem je report Top 10, který zobrazuje 10 největších clusterových a neclusterových indexů s největší velikostí a nejvyšším procentem fragmentace indexů SQL, a to na zadané úrovni. Například následující sestava zobrazuje 10 nejdražších indexů na základě velikosti a fragmentace na úrovni instance SQL serveru:

Při přechodu na konkrétní databázi sestava automaticky zobrazí 10 nejdražších indexů na základě velikosti a fragmentace na úrovni vybrané databáze, jak je uvedeno níže:

Ponoříte-li se hlouběji do konkrétní tabulky v rámci dané databáze, sestava zobrazí 10 nejdražších indexů na základě velikosti a fragmentace, jak je uvedeno níže:

ApexSQL Defrag Report Export

ApexSQL Defrag nám umožňuje exportovat fragmentační reporty do různých formátů souborů, včetně PDF, IMG, HTML, CS a XML, s možností přizpůsobit nastavení reportu před jeho vygenerováním. Na kartě Sestavy klikněte na tlačítko Možnosti:

V okně Upravit možnosti exportu můžete nastavit různé možnosti, počínaje pojmenováním vygenerovaného souboru, cestou, kam bude soubor uložen, orientací stránek a konče obsahem jednotlivých sestav, jak je uvedeno níže:

Chcete-li například exportovat vygenerovanou sestavu Celkem na zadané úrovni, klikněte na tlačítko Export na kartě Sestavy a vyberte typ souboru, do kterého bude tato sestava Celkem exportována. Za několik sekund se vygeneruje sestava s dotazem, zda chcete vygenerovanou sestavu otevřít. Kliknutím na tlačítko Ano tuto sestavu přímo otevřete, jak je uvedeno níže:

Kromě toho můžete exportovat sestavu Top 10 na zadané úrovni, a to kliknutím na tlačítko Exportovat, na kartě Sestavy, a zadáním typu exportního souboru, přičemž se sestava rychle vygeneruje a zeptá se vás, zda chcete vygenerovanou sestavu přímo zobrazit. Kliknutím na tlačítko Ano otevřete vygenerované okno, jak je uvedeno níže:

Z předchozích příkladů je zřejmé, že pomocí nástroje ApexSQL Defrag můžeme snadno zkontrolovat a analyzovat informace o procentuální fragmentaci indexů SQL a ponořit se do nich na různých úrovních od úrovně instance SQL Serveru až po úroveň indexů SQL Serveru a zobrazit je v různých exportovatelných formátech, aniž by zobrazené informace byly ovlivněny restartem serveru nebo služby, protože budou uloženy v databázi úložiště.

.