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

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

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

Roman Hesteric
Roman Hesterichttp://www.priklady.eu
Pracuje ako QA Architekt v Swiss Re. Predtým CTO pre Java a .Net aplikácie. Autorizovaný spolupracovník na projekte Canoo Webtest. Držiteľ certifikátov MCTS a MCPD pre SharePoint server. V IT pracuje 25 rokov, od starého dobrého Turbo Pascalu od Borlandu, cez Javu, až po C#. Administrátor matematického portálu www.priklady.eu

Senior Python Developer/ka

Úspešný americký startup, ktorý umožňuje vývojárom vytvárať aplikácie a rozhrania API bez akýchkoľvek časových, priestorových a cloudových architektúr hľadá...

C#/.NET & React Fullstack Developer/ka

FullStack Developer/ka | Vývoj nového produktu | Security Pracoval/a by si na TPP s platom 2400 - 2800 EUR/brutto mesačne...

Data Visualization Senior Analyst

Pre medzinárodnú IT spoločnosť hľadáme Senior Data Visualization Analyst. Pracujú na veľkých projektoch pre top 100 svetových spoločností. Ide o...

Splunk Data Engineer

Chcel by si dlhodobú spoluprácu, dobré pracovné podmienky, seriózny prístup? Medzinárodná IT spoločnosť hľadá Splunk Data Engineera. Pracujú na veľkých...

Senior Java Developer

Úspešný americký startup, ktorý umožňuje vývojárom vytvárať aplikácie a rozhrania API bez akýchkoľvek časových, priestorových a cloudových architektúr hľadá...

Backend/Node.js Developer / REMOTE

Pre mladý startup vyvíjajúci softvér, ktorý prispieva hráčom k lepšiemu zážitku z hrania, hľadáme Backend/Node.js Developera. Založili ho dvaja...

Julia Developer / REMOTE

Projekt pre nadšencov Julia a machine learning. Pre spoločnosť, ktorá používa matematické metódy a metódy AI / ML na...

Máte vo firemných stretnutiach chaos? Česká firma našla riešenie, teraz s aplikáciou dobývajú svet

Vyvíjajú ju v Českej republike, pracujú na nej aj hendikepovaní kolegovia a používajú ju firmy v rôznych krajinách sveta....

Ondrej Kubovič – Digitálna bezpečnosť a riziká na internete

Tentokrát sme sa porozprávali s Ondrejom Kubovičom, špecialistom v spoločnosti ESET na populárnu tému digitálnej bezpečnosti. Ondrej nám uviedol...

Slovenskí tvorcovia hier dosiahli v roku 2020 rekordný obrat 72 miliónov EUR, zamestnávali 870 ľudí

Slovenský herný priemysel nezastavila ani pandémia, v roku 2020 opäť výrazne rástol. Podľa štatistík Slovak Game Developers Association prekonal...

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