Informācijas par indeksiem apskatīšana

Aprakstīts kā apskatīt informāciju par indeksiem- divos veidos- (1) ar SSMS grafiskā interfeisa palīdzību un (2) izmantojot DMV (Dynamic Management Views and functions) funkciju sys.dm_db_index_physical_stats. Dots skaidrojums iegūtajiem rezultātiem- lapu 'pilnums', lapu skaits, ierakstu skaits, indeksa dziļums un citi.

Piemēra sagatavošana

Turpmākajā rakstā izmantošu to pašu piemēru, kas tika izmantots rakstā Piemērs: Indeksi, vaicājumu analīze. skripts bija šāds:

Create Table dbo.TestTable
(
   TestTableID int Identity primary key,
   Skaitlis int,
   TrisBurti char(3),
   MainigsGarums NVarChar(20),
   GarsTeksts NVarChar(100)
)
Declare @i int
Set @i = 0
While @i < 10000
Begin
   Insert Into dbo.TestTable(Skaitlis, TrisBurti, MainigsGarums, GarsTeksts)
      Values(Cast(Rand()*1000 as int), 'abc', Replicate('x', Cast(Rand()*20 as int)), Replicate('x',100));
   Set @i = @i + 1;
End

Indeksa izveidei nav speciāli rakstīts skripts, bet jāatceras, ka ja tabulai tiek veidota primārā atslēga un tai nav norādīts klusterētais indekss, tad tas tiek izveidots automātiski (nosaukums arī tiek piešķirts, bet tāds- ne gluži smuks)!

Informācijas iegūšana izmantojot SSMS

Datu bāzē, kurā izveidojām piemēra tabulu- atrodam to, indeksi, dubulklikšķis uz indeksa nosaukuma (jāņem vērā, ka lai arī piemēra skripts ir viens un tas pats, indeksa nosaukums var atšķirties, jo nosaukums tiek ģenerēts automātiski).

Pēc tam atveras logs, kurā mūs interesē sadaļa "Fragmentation". Ja detalizētāk interesē arī pārējās lapās attēlotā informācija, tad var apskatīt rakstu- Indeksu izveide. Atkarībā no indeksa lieluma, laiks kurā tiek attēlota informācija aizņem vairāk vai mazāk laika (lielu indeksu gadījumā laiks ir pat vairākas minūtes):

Attiecīgās informācijas skaidrojums raksta beigās (no sākuma aprakstīts, kā šo pašu informāciju iegūt izmantojot DMV)

Informācijas iegūšana izmantojot DMV

Jau agrāk dažos piemēros tika izmantota aprakstītā funkcijas (Piemērs: Lapu dalīšanās (Page splits), Piemērs: pāredresētie ieraksti (Forwarded records)...). No piemēriem aptuveni var nojaust kādiem nolūkiem šis skats var noderēt- indeksu "efektivitātes" daļējai izvērtēšanai. Kāpēc daļējai? Tāpēc, ka pat ideālā kārtībā uzturēts indekss nav uzskatāms par efektīvu, ja to neizmanto.

Lai iegūtu informāciju ir jāizpilda sekojošs vaicājums (Sākot ar SQL Server 2005. iepriekšējām versijām jāizmanto DBCC SHOWCONTIG):

SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('TestTable'), NULL, NULL, NULL)

Tātad vaicājums tiek izpildīts uz funkciju- šajā gadījumā funkciju var uzskatīt kā skatu ar parametriem, jo atgrieztais rezultāts ir tabula.
Sintakse izskatās šādi:

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | 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)
• 5. parametrs- režīms, kas tiek izmantot lai iegūtu informāciju. Pieļaujamās vērtības DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. (norādod režīmu tas jāliek pēdiņās)
• LIMITED (ja parametrā padod vērtības DEFAULT vai NULL tiek izmantota šis režīms)- pats ātrākais, bet arī neprecīzākais veids. Netiek apskatītas beigu virsotnes indeksam (vairāk informācijas par indeksa uzbūvi Tabulas ar klusterētu (clustered) indeksu un Indeksi (non-clustered indexes))
• SAMPLED- Pārlasa statistikas iegūšanai 1% no visām indeksa lapām. Ja indeksā mazāk par 10 000 lapām, tad visas indeksa lapas tiek skanētas.
• DETAILED- Pārlasa pilnīgi visas indeksa lapas.

Piemēram:

Šeit neliela nianse. kā redzams, tiek ielasīti dati 'Detailed' režīmā. Tas nozīmē, ka tiek pārlasītas pilnīgi visas indeksa lapas. Un vēl- tiek atgriezta informācija par katru indeksa koka līmeni- katram līmenim viena rindiņa. Ja datus ielasītu 'Limited' režīmā, tad katram indeksam atbilstu viena rinda.

Rezultātu analīze 

Iepriekš doti divi veidi kā iegūt to pašu informāciju. Ar vizuālo rīku palīdzību iegūtie apzīmējumi laukiem ir ierakstīti aiz DMV veidā iegūtās tabulas kolonnas nosaukuma- iekavās.
Nianse- dati ar vizuālajo rīku palīdzību tiek ielasīti 'Sampled' režīmā un tiek rādīta nultā indeksa līmeņa informācija (tātad- analizē koka beigu virsotnes, ja iedomājamies indeksu kā koku).
Tālāk atgrieztās tabulas kolonnu satura apraksts. Svarīgākie kolonnu nosaukumi ir trekninātiem burtiem.

• database_id- datu bāzes id
• object_id- objekta id
• index_id- indeksa id
• partition_number (Partition ID)- partīcijas id
• index_type_desc (Index type)- indeksa tips
• HEAP- Nozīmē, ka tabula bez klusterētā indeksa- Tabula bez klusterētā (clustered) indeksa 
• CLUSTERED INDEX- klusterēts indeks- Tabulas ar klusterētu (clustered) indeksu 
• NONCLUSTERED INDEX- parasts indeks- Indeksi (non-clustered indexes) 
• Citi, kas netiek apskatīti (PRIMARY XML INDEX, SPATIAL INDEX, XML INDEX)
• alloc_unit_type_desc- principā interesē IN_ROW_DATA, pārējās iespējamās vērtības ir gadījumos, ja tiek izmantoti lauki ar lieliem datu apjomiem (ja rindā ir mainīga garuma lauki, kas aizņem vairāk vietas par 8 kb- tātad neietilps vienā lapā)
• index_depth (Depth)- indeksa dziļums- koka līmeņu skaits. Jo mazāks, jo labāks (par to kāds izskatās indeksa koks var apskatīties gandrīz jebkurā teorijas rakstā par indeksiem. piemēram, rakstā Tabulas ar klusterētu (clustered) indeksu).
• index_level- ja datus ielasa limited vai sampled režīmā, tad vienmēr būs '0' līmenis. detailed režīmā tiek ielasīta informācija par katru indeksa līmeni. Visinteresantākais ir tieši '0' līmenis.
• avg_fragmentation_in_percent (Total fragmentation)- šobrīd šeit netiek apskatīts (par šo un nākamo divu kolonnu sniegto informāciju var skatīt rakstā- Indeksu fragmentācija).
• fragment_count- Datu bāzes failā- ja visas datu lapas ir piešķirtas fiziski secīgi viena aiz otras, tad ir viens fragments. Ja fiziski datu lapas neatrodas viena aiz otras- tad šis skaitlis kļūst tik liels, cik fragmentu ir indeksā.
• avg_fragment_size_in_pages- iespaido 'range scan' efektivitāti. Šobrīd šeit netiek apskatīts.
• page_count (Pages)- lapu skaits indeksa līmenī. 0-tā indeksa līmenī (datu lapas):
• klusterētā indeksa gadījumā tieši atkarīgs no datu daudzuma
• parasta indeksa gadījumā- atkarībā kādi dati iekļauti indeksa atslēgā un klusterētā indeksa atslēgā, tāpat arī no iekļauto kolonnu datu apjoma
• avg_page_space_used_in_percent (Page fullness)- indeksa lapu aizpildes %. ļoti svarīgs lielums. Samazinās lapu dalīšanās rezultātā- teorija rakstā Tabulas ar klusterētu (clustered) indeksu un dots arī Piemērs: Lapu dalīšanās (Page splits). Indeksa veidošanas laikā īpaši jāpiedomā, lai lapas 'pilnums' būtu pēc iespējas "labāks" (pēdiņās, jo ir praktiski neiespējami izdomāt pašu labāko variantu), kā aprakstīts rakstā Indeksu izveide- šajā kontekstā rakstā jāpievērš uzmanība parametram- FILLFACTOR, tur arī plašāks skaidrojums un daži ieteikumi kā pareizāk izvēlēties parametra vērtību.
• record_count (Leaf-level rows)- ierakstu skaits indeksā. Tabulai bez klusterētā indeksa šis skaitlis var atšķirties no realitātes piemēram (bet ne tikai), dēļ pāradresētajiem ierakstiem (kā rodas- Piemērs: pāredresētie ieraksti (Forwarded records)...- izpildot to piemēra daļu, kur rodas pāradresētais ieraksts un salīdzinot reālo rindu skaut ar šajā laukā ierakstīto vērtību iegūsiet atšķirīgus rezultātus)
• ghost_record_count (Version ghost rows)- šobrīd netiek apskatīts.
• version_ghost_record_count (Version Row )- šobrīd netiek apskatīts.
• min_record_size_in_bytes (Minimum record size)- īsākās rindas garums
• max_record_size_in_bytes (Maximum record size)- garākās rindas garums
• avg_record_size_in_bytes (Average row size)- vidējais rindas garums
• forwarded_record_count (Forwarded records)- pāradresēto ierakstu skaits (teorija kas tas ir-Tabulai bez klusterētā indeksa, kā rodas- Piemērs: pāredresētie ieraksti (Forwarded records)...)

Pēc sevis satīrām:

drop table dbo.TestTable