Blog MS-SQL: Skutočne používa databáza nami definované indexy?!

MS-SQL: Skutočne používa databáza nami definované indexy?!

Každý kto programuje akúkoľvek aplikáciu, ktorá uchováva dáta v SQL databáze určite potrebuje aktuálny prehľad o stave indexov pre jednotlivé tabuľky. Ak sa totiž použiva nejaký objektovo-relačný mapping medzi SQL tabuľami a objektmi v programovacom jazyku, po nejakom čase môže dôjsť k strate informácií o indexoch [rozsychronizovaniu], ktoré boli pôvodne navrhnuté v čase vývoja a aktuálne používaných v produkčnej databáze.

Ak je totiž aplikácia odovzdaná do produkcie, niekedy si produkčný databázoví administrátori pomôžu pri performance problémoch vytvorením indexu, ktorý im navrhol  „Database Engine Tuning Advisor“ ako je to v prípade SQL servera od Microsoftu. 

Ako v takom prípade elegantne zistiť všetky indexy, ktoré sú aktuálne platné pre produkčnú databázu? Je niekoľko možností. Prvou je postupné rozklikávanie tabuliek a zobrazenie indexov.

MS-SQL: Skutočne používa databáza nami definované indexy?!

Dalšou je systémová stored procedúra „sp_help table_name“. Obe možnosti sú však veľmi otravné, preto odporúčam sql-script, query ktorá dokáže požadované informácie získať:

 

SELECT
  OBJECT_NAME(si.object_id) as 'Table Name',
  si.name as 'Index Name',
  (CASE is_primary_key WHEN 1 THEN 'Yes' ELSE '' END) as 'Primary Key',     
  (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+ 
  (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+    -- B=basic, C=Clustered, X=XML

  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 7,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 8,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id, index_id, 9,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,10,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+  '' as 'Type',

  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 1) as Key1,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 2) as Key2,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 3) as Key3,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 4) as Key4,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 5) as Key5,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 6) as Key6,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 7) as Key7,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 8) as Key8,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id), index_id, 9) as Key9,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,10) as Key10

FROM sys.indexes as si

LEFT JOIN sys.objects as so on so.object_id=si.object_id

WHERE index_id>0 -- bez default heap
  and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- bez systemovych tabuliek
  and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams')
-- bez diagramov
ORDER BY [Table Name],[Index Name]

Výsledkom query je pekný prehľad o všetkých tabuľkách a pre ne platných indexoch, vrátane dodatočných informácii o type a kľúčoch použitých v indexe.

Po tomto prehľade bude ešte veľmi užitočná query, ktorá zistí skutočné využitie indexov počas produčnej prevádzky:

select distinct OBJECT_NAME(u.object_id) as TableName, si.name  as IndexName, 
*  from sys.dm_db_index_usage_stats u, sys.indexes si
 where (u.object_id = si.object_id) and (u.index_id = si.index_id)


Dobrý článok? Chceš dostávať ďalšie?

Už viac ako 6 200 ITečkárov dostáva správy e-mailom. Nemusíš sa báť, nie každé ráno. Len občasne.



Súhlasím so spracovaním mojich osobných údajov. ( Viac informácií. )

Tvoj email neposkytneme 3tím stranám. Posielame naňho len informácie z robime.it. Kedykoľvek sa môžeš odhlásiť.

Junior / Senior Java Backend Software Engineer

JOB Position: Java Backend Software Engineer Contract type: TPP, Contract Location: Bratislava Salary Junior: 1500 EUR/Brutto Salary Senior: 2200 EUR/Brutto Rate: 3000 -4000 EUR/monthlyPrimary Job...

Senior Sieťový Špecialista

PRÁCA Pozícia: Sieťový špecialista Pracovný pomer:  TPP Miesto práce: Bratislava, on-site Plat: od 1700+ EUR/Brutto/mesačneHlavné zodpovednosti:Navrhuje architektúru, štruktúru a IP adresácie multiplatformových LAN/MAN/WAN sietí ...

Data Mining Expert

PRÁCA Pozícia: Data Mining Expert Pracovný pomer:  TPP Miesto práce: Bratislava, on-site Plat: od 2000+ EUR/Brutto/mesačneAké výzvy ťa čakajú?Vynikajúca pracovná príležitosť pre nadšenca práca...

Artificial Intelligence Consultant

JOB Position: Artificial Intelligence Consultant Contract type: Full-Time Location: Bratislava Salary: 2000 EUR/Brutto/monthsResponsibilities:conceptual processing and development of the new solutions in the area e.g....

Artificial Intelligence Expert

JOB Position: Artificial Intelligence Expert Contract type: Full-Time Location: Bratislava Salary: 2000 EUR/Brutto/monthsSuccessful adaptation of technologies like machine learning, deep learning, artificial inteligence will determine companies...

Senior Front-end / Angular Developer / Tvorba serverovej a aplikačnej logiky

PRÁCA Pozícia: FE/ Angular developer Pracovný pomer:  TPP, živnosť Miesto práce: Bratislava, Prievidza, Žilina Plat: od 1700+ EUR/Brutto/mesačneNáplň práce:Spolupráca na vývoji SW...

Senior .NET/Angular developer

PRÁCA Pozícia: .NET/Angular developer Pracovný pomer:  TPP Miesto práce: Bratislava, on-site Plat: od 1700+ EUR/Brutto/mesačneČo bude Vašou náplňou práce:spolupodieľanie sa na vývoji SW riešenia...

Nexteria – IT Club

Prednášky, diskusie, workshopy priamo od expertov z praxe.    Prepájame študentov a ľudí, ktorí...

Campus cowork

Campus cowork je medzinárodný coworking v Bratislave s dvoma lokáciami. Campus MLYNY...

Čo sme stihli v roku 2019

Každý rok je niečím špeciálny a rok 2019 nie je výnimkou. V...

Čítaj ďalej:

Dobrý článok? Chceš dostávať ďalšie?

Už viac ako 6 200 ITečkárov dostáva správy e-mailom. Nemusíš sa báť, nie každé ráno. Len občasne.

Súhlasím so spracovaním mojich osobných údajov. ( Viac informácií. )

Tvoj email neposkytneme 3tím stranám. Posielame naňho len informácie z robime.it. Kedykoľvek sa môžeš odhlásiť.