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

Senior Java Consultant

Hľadáš, kam by si sa posunul? Možno ťa táto práca zaujme. Ide o Senior JAVA konzultanta v nadnárodnej IT...

PHP Developer / Homeoffice / Vhodné aj pre absolventa!

Aj pre absolventa je tu dobrý začiatok IT kariéry! Hľadáme PHP Developera pre spoločnosť, ktorá pôsobí v oblasti zdravia.Je...

Junior Java Developer / REMOTE

Máš rád svoju slobodu, pracuješ na projektoch, ideálne remote? Možno táto práca Java Developera bude pre Teba to najlepšie...

Ruby on Rails Developer / REMOTE

Je tvoj život príliš v “koľajniciach”? Urob si odbočku a skús sa pridať do tímu Ruby on Rails developerov.Ide...

Automation Tester / Java

Medzinárodná spoločnosť, ktorá vyvíja finančný softvér hľadá človeka na pozíciu Automation Tester.Práca je v Bratislave na TPP. Odmena sa...

Cloud Administrátor / REMOTE

Máš pocit, že chceš ísť ďalej? Možno práve v tomto období je ten správny čas. Hľadáme Cloud Administrátora.Práca je...

Integračný Administrátor

Poď sa učiť od profesionálov! Pre medzinárodnú spoločnosti, ktoré sa venujú vývoju databáz hľadáme integračného administrátora.Práca je REMOTE, na...

Schválené. Poznáme najlepšie projekty uplynulej výzvy!

Výzvu pre veľké projekty sme vyhlásili v polovici marca tohto roku a s napätím sme...

Bezáková: Otázky o ochrane osobných údajov je potrebné riešiť na začiatku, nie „potom“

Počas dvojdňového praktického workshopu DPO Bootcamp, ktorý sme zorganizovali 24.-25. júna 2020...

Stanovisko IT Asociácie Slovenska k dokumentu ÚHP Informatizácia 2.0 – revízia výdavkov

IT Asociácia Slovenska (ITAS) ako najväčšia profesijná organizácia IT firiem, ktorej členovia...

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