DMV trūkstošo indeksu iegūšanai

Vēl viens raksts par indeksiem un informāciju, ko par tiem var uzzināt izmantojot DMV (Dynamic Management Views and functions). Šoreiz- par indeksiem, kas potenciāli varētu uzlabot SQL Server ātrdarbību.

Tālāk aprakstītā iespēja ir visnotaļ aizraujoša, tomēr izmantot to vajadzētu uzmanīgi, liekot lietā zināšanas gan par SQL Server darbību, gan par lietojumiem, kas izmanto datu bāzi, gan arī datiem. Ja tā būtu perfekta, tad visdrīzāk indeksi kā tādi vispār būtu jāmet no galvas ārā, jo SQL Server pats varētu tos izveidot un dzēst atkarībā no katras konkrētās datu bāzes uzbūves.

Vaicājums potenciāli trūkstošo indeksu iegūšanai
Vaicājums strādā vienas datu bāzes ietvaros.

SELECT statement AS table_name,
Case When (SELECT create_date FROM sys.databases WHERE NAME='tempdb') > modify_Date
    Then (SELECT create_date FROM sys.databases WHERE NAME='tempdb') Else modify_Date End StatistikaKops,
DateDiff(dd, Case When last_user_scan Is Null OR last_user_seek > last_user_scan Then last_user_seek Else last_user_scan End, GetDate())
PedejoreizNoderetuDienas,
    user_seeks,user_scans, avg_user_impact, Round(avg_total_user_cost,2) avg_total_user_cost,
    Round(avg_total_user_cost * avg_user_impact * 0.01 *(user_seeks + user_scans),0) Ietekme,
    equality_columns, inequality_columns, included_columns,
    'Create NonClustered Index IX_' + t.name + '_missing_'
        + CAST(mid.index_handle AS VARCHAR(10))
        + ' On ' + STATEMENT
        + ' (' + IsNull(equality_columns,'')
        + CASE WHEN equality_columns IS Not Null
            And inequality_columns IS Not Null THEN ','
                ELSE '' END
        + IsNull(inequality_columns, '')
        + ')'
        + IsNull(' Include (' + included_columns + ');', ';'
        ) Skripts
FROM sys.dm_db_missing_index_details AS mid
    INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle
    Left Join sys.objects AS t
        ON mid.OBJECT_ID = t.OBJECT_ID and t.Type = 'U'
Where database_id = DB_ID()
ORDER BY avg_total_user_cost * avg_user_impact * 0.01 * (user_seeks + user_scans) Desc;

Rezultāti

• table_name - pilns tabulas nosaukums (datubaze.shema.tabulasnosaukums)
• StatistikaKops- SQL Server trūkstošo indeksu skatos uzkrāj informāciju (un piedāvā variantus) tikai no pēdējās servera pārstartēšanas. Uzkrātā informācija tiek dzēsta, ja tabulā notiek izmaiņas, kas nav vienkārša datu mainīšana- piemēram, pielikta vai noņemta kolonna, izveidots/dzēsts indekss. Šajā kolonnā parādu brīdi, no kura uzkrāta informācija. Ja tabula nav mainīta, tad tas būs datums, kad pārstartēts serveris. Ja tabula ir mainīta- tabulas mainīšanas datums.
• PedejoreizNoderetuDienas- Parāda, cik dienas pagājušas, kopš pēdējo reizi piedāvātais indeks būtu noderējis vaicājumā.
• user_seeks- meklēšanas operāciju skaits, kurās šis indekss būtu bijis izmantojams.
• user_scans- indeksa pārlasīšanas operāciju skaits, kurās šis indekss būtu bijis izmantojams.
• avg_user_impact- par cik procentiem tiktu samazinātas vaicājumu izpildes izmaksas, ja būtu šāds indekss.
• avg_total_user_cost- Vidējās "Izmaksas" vaicājumiem, kam šis indekss būtu noderīgs. Skaitlis nav pie citām vērtībām tiešā veidā piesaistīts (piemēram, izpildes laiks, procesora laiks utt). Tas noderīgs, lai salīdzinātu savā starpā vienas SQL Server instances ietvaros.
• Ietekme- Relatīvs skaitlis, kas norāda cik daudz "Izmaksas" varētu ietaupīt izveidojot šādu indeksu. Skaitlis tiek iegūts- saskaitot reizes, cik indekss tika izmantots, sareizināts ar vidējām vaicājuma "Izmaksām", tad sareizināts ar procentu, kādu izdotos ietaupīt izveidojot indeksu. Atkal- jāņem vērā, ka skaitlis nav tiešā veidā saistīts ar konkrētu lielumu. Tas gan noder lai savstarpēji varētu salīdzināt.
• equality_columns- Kolonnas, kas izmantotas lai meklētu datus pēc "vienāds ar" principa.
• inequality_columns- Kolonnas, kas izmantotas vaicājuma "Where" daļā, bet nav izmantots "vienāds ar". Piemēram, >, <, Between, Like utt.
• included_columns- Kolonnas, kas nepieciešamas lai indekss būtu "Pārklājošs" indekss.
• Skripts- No raksta. Skripts satur indeksa izveides skriptu.

Ierobežojumi
Pilns ierobežojumu saraksts ir pieejams MSDN rakstā. Galvenā doma ir tāda, ka šī metode nav ideāla. Tā parāda potenciālas idejas par iespējamajiem indeksiem, dažkārt pat precīzi. Tomēr jāliek lietā zināšanas par datiem, zināšanas par to, kā datu bāze tiek lietota. Tāpat arī- tiek piedāvāti vairāki indeksi vienai tabulai. Viena indeksa izveide var padarīt citu indeksu par nevajadzīgu. Indeksi ir piedāvāti ņemot vērā to datu bāzes lietojumu, kāds tas ir bijis pēdējā laikā (kolonna StatistikaKops), līdz ar to testa vidē šis skripts parādīs testa videi potenciāli piemērotus indeksus un tas var būtiski atšķirties no produkcijas vides.

Avoti
Rakstā izmantoti 3 DMV skati (MSDN par trūkstošiem indeksiem var lasīt šeit):
* sys.dm_db_missing_index_group_stats informācija par trūkstošajiem indeksiem- to izmantošanas biežumu un iespējamo ietekmi, kāda būtu, ja tiktu izveidots indekss.
* sys.dm_db_missing_index_groups starptabula.
* sys.dm_db_missing_index_details  informācija par trūkstošajiem indeksiem- kuras kolonnas un kā tiek izmantotas.
Viens OCV (Object Catalog View)
* sys.objects- atlasot tikai informāciju par Tipu- 'U'. No šī skata var iegūt objekta izveides un mainīšanas laikus, kā arī objekta nosaukumu.
Citi izmantoti raksti:

* paņemts indeksa izveides skripta kolonna no raksta par šīm iespējām (tīri labs raksts, 'est_impact' aprēķins šķiet neprecīzs, tāpat vēlme redzēt vairāk informācijas).
* Izmantots "Ietekmes" aprēķins un skaidrojums no raksta.
* Kad SQL Server tika pārstartēts (SQL Server UpTime)
* MSDN apraksts par trūkstošajiem indeksiem.