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.

MSSQL_3

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ť.

React FE Developer

Prejdi s novým projektom na remote! Ovládaš React a obzeráš sa po novom projekte? Pre medzinárodnú IT spoločnosť hľadáme...

Technical Writer / REMOTE

Si Technical Writer a hľadáš zmysluplný projekt, ktorý bude REMOTE? Teraz máš možnosť pomáhať zlepšovať kvalitu života pacientov prostredníctvom...

Junior/Medior DevOps Specialist / App pre solárne elektrárne

Ak si Junior alebo Medior DevOps Specialist, máš šancu vydať sa na cestu zvyšovania efektivity solárnych elektrární. Pracoval by...

DevOps Architect

Ak si alebo máš ambíciu stať sa DevOps Architect-om, zbystri pozornosť. Máme pre teba projekt!Pre medzinárodnú IT spoločnosť hľadáme...

.NET / Angular Developer

NET/Angular Developer má teraz príležitosť prejsť na REMOTE s novým projektom!Pre spoločnosť, ktorá patrí medzi najvýznamnejších producentov informačných systémov...

DevOps Architect

Ak si alebo máš ambíciu stať sa DevOps Architect-om, zbystri pozornosť. Máme pre teba projekt!Pre medzinárodnú IT spoločnosť hľadáme...

Fronted Developer / Aplikácie pre diabetikov

Páčila by sa Ti zmysluplná práca na produkte - aplikácií, ktorá pomáha ľuďom s tým najcennejším, čo majú -...

Bratislava Game Jam sa tento rok spojil s hernými konferenciami Game Days a Game Developers Session Praha.

Bratislava Game Jam je súťaž programátorov, scenáristov, grafikov, filozofov a ilustrátorov, ktorých...

Ako chutí tá pravá Java? Odpoveď nájdete na JavaDays 2020 ONLINE

Zaujíma vás svet Javy a všetko s ním spojené? Máte záujem načerpať...

Bratislavská coworkingová legenda The Spot ožíva

The Spot – prvý bratislavský coworkingový priestor, v ktorom vznikali a sídlili...

Čí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ť.