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

159

MSSQL_1Kaž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. 

MSSQL_2

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.

MSSQL_4

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)

MSSQL_5

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

Už viac ako 4 200 z vás dostáva správy e-mailom. Nemusíš sa báť, nie každé ráno. Len občasne.

I agree to have my personal information transfered to MailChimp ( more information )

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