Kuri indeksi tiek izmantoti?

Veidojot indeksu, vienmēr to mēģina izveidot pēc iespējas labāku, noderīgāku. Bet ne vienmēr tas izdodas. Dodot ieteikumus indeksu izveidei, parasti līdzi nāk teksts "neaizmirstiet visu testēt, testēt un vēlreiz testēt". Ir jau skaisti, tik ne vienmēr veidojot testus iznāk izdarīt pareizus pieņēmumus par reālo vidi. Kādā no turpmākajiem rakstiem būs plašāk apskatīts kā SQL Server "izdomā" vai indeksus izmantot. Tas ļaus izdarīt labākus pieņēmumus par to indeksu nepieciešamību. Šajā rakstā savukārt- kā uzzināt vai un kuri izveidotie indeksi tiek izmantoti? (tātad- fakta konstatēšana)

Iepriekš rakstā Informācijas par indeksiem apskatīšana tika aprakstīts, kā var izmantot DMV (Dynamic Management Views and functions), lai iegūtu informāciju par indeksiem (to stāvokli). Arī šajā gadījumā, lai uzzinātu kuri indeksi tiek izmantoti, jāizmanto DMV. Tiks aprakstīts viens DMV skats un viena funkcija:

1. funkcija sys.dm_db_index_operational_stats (lielāka detalizācijas pakāpe)
2. skats sys.dm_db_index_usage_stats

Kopīgais un atšķirīgais

Kopīgs skatam un funkcijai ir tas, ka abos informācija parādās ne senāka, kā kopš pēdējās servera pārstartēšanas reizes (vai datu bāze ir atvienota (detached) vai aizvērta (shut down))! Tāpat der ņemt vērā, ka testēšana un produkcijas vidēs rezultāti var būt ļoti atšķirīgi- testējot slodze visbiežāk nav tāda pati, kā produkcijas vidē.

Būtiskās atšķirības starp skatu un funkciju ir tādas (par atšķirībām var lasīt arī šeit):

• Skats parāda cik reizes kāda operācija tiek reāli izmantota konkrētos vaicājumos.
  Funkcija parāda cik reizes konkrētā operācija ir izsaukta.
  Piemēru no raksta- Informācijas par indeksiem apskatīšana. Izveidojot jaunu indeksu uz kolonnas "Skaitlis". izpildot vaicājumu:

  Select skaitlis
  From dbo.TestTable
  Where skaitlis in (1,56,49,23)

  pētot funkcijas un skata atgrieztos rezultātus redzēsiet, ka funkcija atgriezīs range_scan_count = 4 (katram skaitlim "in" daļā viens range_scan!), kamēr skats atgriezīs atgriezīs user_seeks = 1, jo vienreiz viena vaicājuma izpildē tika izmantots šis indekss!
• Indekss parādīsies "lietojumu" skatā tikai tad, kad tiks reāli izmantots.
  funkcijas atgrieztajos rezultātos indekss parādīsies tiklīdz metadati tiks ielasīti SQL Server atmiņā- piemēram, iegūstot vaicājuma izpildes plānu, bet reāli neizpildot vaicājumu.
• Funkcija atgriež rezultātus tad, ja metadati ir kešatmiņā savukārt skats strādā tik senā pagātnē, cik ilgi datu bāze ir bijusi darbībā (piemēram, ja izmanto indeksu un tad ilgstoši neizmanto, tad funkcija rezultātus var neatgriezt, savukārt skats atgriezīs rezultātus no pēdējās servera pārstartēšanas, atvienošanas, aizvēršanas). Tiesa- biežāk izmantotie indeksi visdrīzāk būs saglabāti kešatmiņā.

Funkcija sys.dm_db_index_operational_stats
Piemēram, lai iegūtu rezultātus par visiem indeksiem datubāzē, pie kuras šobrīd esam pieslēgušies, būtu jāizpilda šāds vaicājums:

Select *
From sys.dm_db_index_operational_stats(DB_ID(), null, null, null)

Sintakse izskatās šādi:

sys.dm_db_index_operational_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | 0 | NULL | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
)

Parametru skaidrojumi:

• 1. parametrs- Datubāzes ID. Lai to iegūtu var izmantot funkciju DB_ID(). Ja parametrā norāda Null, 0, vai DEFAULT un nenorāda object_id, index_id un partition_number, tad tiek atgriezta informācija par visiem indeksiem kas ir visās servera datu bāzēs.
• 2. parametrs- Objekta ID. (tabulas vai skata). Lai to iegūtu var izmantot funkciju object_id(). Ja parametrā norāda Null, 0, vai DEFAULT un nenorāda index_id un partition_number, tad tiek atgriezta informācija par visiem indeksiem datu bāzē.
• 3. parametrs- Indeksa ID. Lai to iegūtu var izmantot funkciju object_id() (jānorāda tabulas nosaukums + '.' + tad indeksa nosaukums piemēram- object_id('dbo.TestTable.PK__TestTable__67152DD3')). Ja parametrā norāda Null, -1, vai DEFAULT un nenorāda partition_number, tad tiek atgriezta informācija par visiem indeksiem kas izveidoti tabulai. Ja parametrā norāda 0, tad tiek atgriezta informācija par 'kaudzi'- sīkāk var skatīt rakstiņā Tabula bez klusterētā (clustered) indeksa.
• 4. parametrs- Partīcijas numurs. Šobrīd ārpus tā, kas tiek aprakstīts. Null, -1, vai DEFAULT nozīmē, ka atgriezīs informāciju par visām partīcijām objektiem (tas ir tas, kas mums šobrīd vajadzīgs)

Viens variants kā iegūst vieglāk lasāmus rezultātus varētu būt šāds (kolonnas, protams, var salikt kādas vien vajag):

SELECT DB_NAME(s.[database_id]) DatuBaze, OBJECT_NAME(s.[object_id]) Objekts,
y.name Indekss, s.leaf_insert_count, s.leaf_delete_count, s.leaf_update_count,
s.range_scan_count, s.singleton_lookup_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
    left join sys.indexes y on s.object_id = y.object_id and s.index_id = y.Index_id
WHERE  OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY DB_NAME(s.[database_id]), OBJECT_NAME(s.[object_id]), y.name

Atgriezto rezultātu skaidrojumi šobrīd netiks doti. Daļa rezultātu pārklājas ar turpmāk aprakstītā skata atgrieztiem rezultātiem (protams, ņemot vērā aprakstītās atšķirības) vai ir pašas sevi paskaidrojošas. Savukārt liela daļa no atgrieztajiem rezultātiem skar lietas, kas šajā emuārā nav līdz šim aprakstītas. Piemēram, resursu slēgšana, datu kompresija un citas.

Skats sys.dm_db_index_usage_stats
Tā kā tas ir skats, sinatkse ir salīdzinoši vienkārša:

Select *
From sys.dm_db_index_usage_stats

Atgrizto rezultātu skaidrojums (pirmās trīs kolonnas norāda, par kuru objektu iet runa. Tad 4 kolonnas norāda indeksa noslodzi un nākamās 4- attiecīgi pēdējo reizi, kad indekss tika izmantots. Pēdējās 8 kolonnas attiecas uz sistēmas vaicājumiem):

• database_id- Datu bāzes ID
• object_id- Objekta ID
• index_id- Indeksa ID
• user_seeks- cik reizes notikusi meklēšana indeksā
• user_scans- cik reizes notikusi indeksa pilna nolasīšana
• user_lookups- cik reizes parastais indekss vērsies pie tabulas, lai iegūtu datus no kolonnām, kas nav indeksā (lielākai skaidrībai ko tas nozīmē, var skatīt- datu meklēšanas sadaļu rakstā Indeksi (non-clustered indexes)).
• user_updates- cik reizes indeksa dati ir mainīti.
• last_user_seek- datums kad
• last_user_scan- datums kad
• last_user_lookup- datums kad
• last_user_update- datums kad
• system_seeks
• system_scans
• system_lookups
• system_updates
• last_system_seek
• last_system_scan
• last_system_lookup
• last_system_update

Lai iegūtu rezultātus vieglāk lasāmā formā varētu izveidot, piemēram, šādu vaicājumu:

SELECT DB_NAME() DB, OBJECT_NAME(s.[object_id]) Objekts,
i.name IndeksaNosaukums, s.user_seeks, s.user_scans, s.user_lookups,
s.user_updates
FROM sys.dm_db_index_usage_stats s
Inner Join sys.indexes i On s.[object_id] = i.[object_id]
And s.index_id = i.index_id
WHERE s.database_id = DB_ID()
And OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY DB_NAME(), OBJECT_NAME(s.[object_id]), i.name

Vēl viens veids kā apskatīt šo pašu
Abu skatu atgriezto informāciju var apskatīt arī pārskata veidā. Šobrīd tikai tiks parādīts kā var iegūt attiecīgo pārskatu. Pie viena arī parādot iespēju, kas parādījās kopā ar SQL Server 2005 (un DMV). Papētot tālāk var redzēt, ka pārskata veidā var iegūt arī citu interesantu informāciju.
Tātad- lai iegūtu pārskatu, SSMS labā poga uz datu bāzes -> Reports -> Standart Reports -> Index Usage Statistics. Attēlā:

 

Paldus literatūra
* Raksti par indeksu lietošanas biežumu- šeit un šeit.
* Rakts par citiem DMV un indeksu optimizāciju (labs, plašāks nekā apskatīts emuārā līdz šim) šeit.