Cesta k vysokému výkonu, aneb defragmentujete pravidelně SQL indexy?

Zcela jistě se najde mnoho programátorů, které zaplaví opojný pocit spokojenosti poté, co vytvoří a vyladí databázové indexy. Bohužel si někdy neuvědomí, že nové indexy je třeba neustále udržovat tak, aby byl zajištěn optimální výkon.
Jan Svoboda a Michal Rogozný: Zcela jistě se najde mnoho programátorů, které zaplaví opojný pocit spokojenosti poté, co vytvoří a vyladí databázové indexy. Bohužel si někdy neuvědomí, že nové indexy je třeba neustále udržovat tak, aby byl zajištěn optimální výkon. Po čase se stane, že reakční doby SQL serveru jsou pomalejší a pomalejší, přičemž začas mohou dosahovat až několikanásobku optimálních hodnot. Na zoubek pravděpodobné příčině se podíváme právě v tomto článku a přineseme vám řešení v podobě uložené procedury.

Fragmentace dat

K fragmentaci dat neustále dochází při běžných databázových operacích, jako např. při Updatech, Insertech ap., a to díky dělení stránek.

Fragmentace zásadně ovlivňuje dvě věci:

  • Velikost tabulek (nárůst)
  • Výkon (snížení)
Snížení výkonu může být velmi podstatné zejména u dotazů nad rozsáhlými tabulkami a lze je pozorovat zejména ve výběrových dotazech, protože se při načítání všech řádků, ke kterým dotaz přistupuje, musí zpracovat větší počet stránek než v ideálním případě.

Ideální je spouštět reindexaci (přebudování indexů) v pravidelných intervalech, nebo nejpozději při patrném snížení výkonu databáze.

Nebojte se – abyste nemuseli tyto operace provádět ručně nebo pomocí systémových procedur pro každý index jednotlivých tabulek, napsali jsme uloženou proceduru spReindexTables, která defragmentuje indexy na všech tabulkách v aktuální databázi, vyjma systémových tabulek založených SQL serverem. Tuto proceduru si vytvořte ve vaší databázi a můžete ji rovnou začít používat. My osobně řešíme defragmentaci indexů tak, že vykonávání této uložené procedury zadáme jako pravidelný Job SQL Serveru v termínech, kdy je databáze obvykle málo využívána.

Než uloženou defragmentační proceduru spustíte poprvé ...

Možná neuškodí, když si nejdříve zjistíte stav fragmentace na některé z vašich tabulek. K tomuto účelu jsme napsali dvě jednoduché uložené procedury, které po vytvoření ve vaší konkrétní databázi můžete rovněž používat.

1. Vytvořme si tedy jako první krok jednoduchou uloženou proceduru s názvem spCheckFragmentStatus, která nám ukáže stav fragmentace celé tabulky.

CREATE PROCEDURE spCheckFragmentStatus
@strTable sysname
AS
DECLARE @table_id int
Set @table_id = OBJECT_ID(@strTable)
If @table_id IS NOT NULL
  BEGIN
    DBCC SHOWCONTIG (@table_id)
  END
ELSE
Print `Objekt ` + @strTable + ` v databázi neexistuje !`

Nyní uloženou proceduru v Query Analyzeru spustíme. Jediným parametrem bude název tabulky.

USE jméno_databáze
Exec spCheckFragmentStatus @strTable=`tblVašeTabulka`

2. Druhá uložená procedura spCheckFragmentIndexStatus bude zjišťovat míru fragmentace konkrétního indexu v libovolné tabulce.

CREATE PROCEDURE spCheckFragmentIndexStatus

@strTable sysname,

@strIndex sysname

AS
DECLARE @table_id int
DECLARE @index_id int
Set @table_id = OBJECT_ID(@strTable)
SELECT @index_id = indid FROM sysindexes WHERE name = @strIndex
IF @table_id IS NULL
  BEGIN
    Print `Tabulka ` + @strTable + ` neexistuje v databázi !`
  END
ELSE
  BEGIN
    IF @index_id IS NULL
      BEGIN
        Print `Index` + @strIndex + ` neexistuje v tabulce ` + @strTable + "!"
      END
    ELSE
      BEGIN
        DBCC SHOWCONTIG (@table_id,@index_id)
      END
  END

U této procedury budeme používat již dva parametry: název tabulky a název indexu.

USE jméno_databáze
EXEC spCheckFragmentIndexStatus @strTable=`tblKWords`,@strIndex=`PK_tblKWords_1`

3. Těmito dvěma procedurami zjistíme stav fragmentace a nyní přistoupíme k proceduře, která nám přeindexuje všechny uživatele založené tabulky. Doba vykonávání reindexačního procesu je závislá na počtu tabulek, indexů a objemu samotných dat v tabulkách. Dobrou zprávou je, že reindexaci lze spouštět za chodu, i když v tuto dobu SQL server obvykle vykazuje zpomalení při vykonávání dotazů, protože některé z indexů přestávají dočasně existovat. U systémů s desítkami tisíc záznamů v tabulkách může reindexace trvat i několik minut.

Jak tato procedura funguje, zjistíme snadno z komentářů. Procedura nepřebírá žádné parametry.

CREATE PROCEDURE spReindexTables
AS
DECLARE
@Name sysname,
@ID int,
@Count int

Set @Count = 0

/*Vytvoříme kurzorovou tabulku, která bude obsahovat pouze objekty typu „User Table“*/
DECLARE tbl_tables CURSOR
  FOR
  SELECT name , id
  FROM sysobjects
  WHERE type = `U`

/*Tuto tabulku otevřeme a vlastností „IsMSShipped“ vybereme objekty, které nebyly vytvořeny při  instalaci SQL serveru*/
OPEN tbl_tables
FETCH NEXT FROM tbl_tables INTO @Name, @ID
WHILE(@@FETCH_STATUS = 0)
  BEGIN
    IF  OBJECTPROPERTY (@ID,`IsMSShipped`) = 0
      BEGIN
        dbcc dbreindex (@Name)
        PRINT `Table: ` + @Name+char(10)
        Set @Count = @Count + 1
      END
    FETCH NEXT FROM tbl_tables INTO @Name, @ID
  END
/*Vypíšeme si, jaké tabulky se reindexovaly, a jejich celkový počet*/

PRINT `Celkový počet reindexovaných tabulek: ` + STR(@Count)

/*Nakonec kurzorovou tabulku zavřeme a dealokujeme*/
CLOSE tbl_tables
DEALLOCATE tbl_tables

Proceduru spustíme a zkontrolujeme hlášení o přebudovaných indexech ve všech tabulkách.

Na závěr můžeme použít naše dvě první procedury a zjistit aktuální stav fragmentace. Měli bychom dosáhnout těchto výsledků:

.....
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
.....

Nyní by vaše dotazy využívající indexů měly být rychlostně optimalizovány. Nezapomeňte ovšem na pravidelnou defragmentaci indexů. Za zmínku rovněž stojí, že na chod indexu má také vliv nastavení voleb FILLFACTOR (volba zaplnění) a PAD_INDEX. Rovněž nezapomeňte své indexy vyladit pomocí Index Tuning Wizardu. Ale o tom snad někdy příště ...

Testováno na: MS SQL 7

Diskuze (6) Další článek: Computer 20/00

Témata článku: Software, Programování, Scan, Index, Uložení, Pravděpodobný parametr, CES, Cesta, Ideální volba, Ind, From, Tuning, SQL, Print, Elsa, Job


Určitě si přečtěte

13 praktických tipů a triků pro Mapy.cz, které možná neznáte

13 praktických tipů a triků pro Mapy.cz, které možná neznáte

** Mapy.cz neslouží jen k zobrazení podkladů a plánování tras ** Nabízejí celou řadu dalších praktických funkcí a možností ** Vybrali jsme třináct tipů a triků, o kterých možná (ne)víte

Karel Kilián | 31

HTTPS byl pouze první krok. Chrome zavádí DoH, tedy šifrované DNS. Dopady mohou být obrovské

HTTPS byl pouze první krok. Chrome zavádí DoH, tedy šifrované DNS. Dopady mohou být obrovské

** Šifrovaný web je dnes už samozřejmost ** Jeden díl skládačky ale ještě chybí – DNS ** Firefox už začal a teď se na šifrované DNS chystá i Chrome

Jakub Čížek | 94



Aktuální číslo časopisu Computer

Megatest 20 procesorů

Srovnání 15 True Wireless sluchátek

Vyplatí se tisknout fotografie doma?

Vybíráme nejlepší základní desky