Praksē ir bijuši gadījumu, ka pēc indeksu pārbūves datu bāze sāk strādāt daudz, daudz ātrāk (jā jā, vairumā, protams, tas neko daudz nemaina, tomēr tie pāris gadījumi ir pūļu vērti).
Priekšvēsture
Iepriekšējos rakstos tika aprakstīti klusterētie un parastie indeksi. Tika aprakstīts kā indeksus izveidot. Tāpat tika aprakstīti materializētie (indeksētie) skati. Aprakstītas problēmas, kas var rasties indeksos, piemēram- (1) zems indeksu "pilnums" (skatīt rakstā "Informācijas par indeksiem apskatīšana" pie "avg_page_space_used_in_percent"), kas rodās lapu dalīšanās rezultātā un (2) indeksu fragmentācija.
Emuārā arī atrodams kā analizēt konkrētus vaicājumus lai noskaidrotu vai tiek izmantoti izveidotie indeksi. Tāpat aprakstīts veids, kā noskaidrot indeksu lietojumu. Aprakstīts arī, kā apskatīt indeksa "veselības" stāvokli.
Bet līdz šim nekas nav bijis apraksts, kā minētās problēmas risināt.
Indeksu apkope
Indeksu fragmentācija un zems pilnums negatīvi iespaido datu bāzes ātrdarbību. Lai to risinātu var izmantot vairākas metodes:
- Indeksu "pārbūve" (rebuild index)- izveido indeksu pa jaunu- tas tiek dzēsts un izveidots no jauna (SQL Server 2000 līdzvērtīgs variants bija DBCC DBREINDEX). Lai indeksu pārbūvētu izmanto komandu Alter Index. Indeksa pārbūve notiek kā viena transakcija, līdz ar to tas var negatīvi iespaidot sistēmas lietotāju darbu (daļēji to var mainīt izmantojot parametru "Online ON", bet tas nav iespējams visās SQL Server versijās un nav izmantojams XML indeksu gadījumā). Šī metode ir izpildes laikā traucējoša citiem procesiem, bet tajā pašā laikā- pamatīgākā. Piemēram:
ALTER INDEX IX_IndeksaNosaukums ON dbo.Tabula REBUILD;
- Indeksu "pārkārtošana" (reorganize index)- pārkārto indeksa datu lapas (SQL Server 2000 līdzvērtīgs variants bija DBCC INDEXDEFRAG). Indeksa pārkārtošanas laikā tiek patērēti salīdzinoši maz sistēmas resursi. Piemēram:
ALTER INDEX IX_IndeksaNosaukums ON dbo.Tabula REORGANIZE;
Jā- šeit lietoti precīzi nedefinēti lielumi- "salīdzinoši labs" un "ļoti fragmentēts". Bet diemžēl- katrs gadījums ir atsevišķi pārdomājams.. Tas, pie kāda principa pieturos- kamēr indeksu pārbūve ir īsāka par laiku, kurā man ir iespēja tos "apkopt"- tikmēr izmantoju indeksu pārbūvi kā visefektīvāko veidu. Ja būtu jāstrādā ar datu bāzi, kas pieejama 24x7, tad indeksu "pārkārtošana" un "pārbūve" izmantojot parametrus būtu nākamais variants, par ko nopietni sāktu domāt.
MSDN par šo tēmu var palasīt vairāk šeit.
Piemērs- universāls indeksu pārbūves skripts
Šo piemēru noteikti ieteiktu pamēģināt dzīvē un paskatīties un novērtēt rezultātus. Jāņem vērā, ka datubāzē, kura ir labā stāvoklī un kur indeksu fragmentācija ir zema un lapu pilnums- augsts (atbilst norādītajam), skripts neko nemainīs. Savukārt, jo sliktākā stāvoklī ir datubāze, jo labāks būs rezultāts pēc indeksa pārbūves.
Pieņemot to, ka skriptu visdrīzāk gribēsiet notestēt testa vidē- ieteiktu to darīt tā, ka (1) no sākuma datu bāzē noskaidrot vispārējo indeksu "veselības" stāvokli- ja ir indeksi, kas ir sliktā stāvoklī (= augsta fragmentācija- zemāk aprakstītais skripts uzlabo stāvokli tikai tad, ja indeksu pilnums atšķiras no vēlamā par 15%), tad (2) atrast vaicājumus, kas šos indeksus izmanto. Ātrs veids kas varētu noderēt lai pārbaudītu vai indeksi tiek izmantoti aprakstīts rakstā- Kuri indeksi tiek izmantoti?. (3) Atrastos vaicājumus derētu analizēt pirms un pēc skripta izpildes izmantojot SQL Server Profiler vai kādu citu metodi- piemēram, Vaicājuma statistika kombinējot kopā ar Vaicājuma statistika. Svarīgi ņemt vērā, ka šī metode visvairāk palīdz gadījumos, kad tiek ielasītas daudz secīgas rindas. Papildus tam, (4) pēc indeksu pārbūves samazinās aizņemtās vietas apjoms datu bāzē. Arī to var var salīdzināt pirms un pēc skripta izpildes.
Skripts ir tāds, ka tiek atrasti indeksi (kā apskatīt indeksa "veselības" stāvokli), kuru "pilnums" atšķiras no vēlamā vairāk par 15% indeksa rindas datos (ja indeksā ir LOB dati, tad analizējot indeksa stāvokli šajā skriptā tas netiek ņemts vērā), tāpat netiek ņemti vērā indeksi, kuros ir mazāk par 8 lapām. Skriptu vienkāršā veidā var pielāgot lai izveidotu automātisku procesu datu bāzē. Skripta izpildes laikā tiek izvadīta informācija par pārbūvētajiem indeksiem un laiku, ko tas aizņēmis.
[Papildināts: ja datu bāzē tiek izmantota tabulu dalīšana, skatīt rakstu Indeksu apkope II- ņem vērā dalītus (partitioned) indeksus]
Tātad, skripts:
Declare @DBName sysname
Declare @ObjName sysname
Declare @IndName sysname
Declare @SakRebuild DateTime
Declare @Com NvarChar(1000)
Declare @Statuss NvarChar(1000)
Select db_name(ind.[database_id]) DB, object_name(ind.[object_id]) Obj, si.[name] Ind
Into #TTable
From sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'Sampled') ind
Left Join sys.indexes si on si.Index_ID = ind.Index_id and si.object_id = ind.[object_id]
Where OBJECTPROPERTY(ind.[object_id], 'IsMsShipped') = 0
AND ABS(Case When [Fill_Factor] = 0 Then 100 Else [Fill_Factor] End - ind.avg_page_space_used_in_percent) > 15 -- Samērā daudz pa lieko
AND ind.alloc_unit_type_desc = 'IN_ROW_DATA' -- uz objektiem ārpus indeksa neskatamies..
AND ind.page_count > 8 -- tātad- vismaz 8 lapas liels (patiesībā mazs) indekss
Declare Cur Cursor For
Select DB, Obj, Ind
From #TTable
Open cur
fetch next from cur into @DBName, @ObjName, @IndName
while @@fetch_status = 0
Begin
SET @Com = N'Alter Index ' + @IndName + N' On ' + @DBName + N'.dbo.' + @ObjName + N' Rebuild';
SET @Statuss = N'Indekss: ' + @IndName + N' OBjekts: ' + @DBName + N'.dbo.' + @ObjName;
SET @SakRebuild = GetDate();
EXECUTE SP_EXECUTESQL @Com;
Set @Statuss = @Statuss + ' Izpildes laiks: ' + Cast(DATEDIFF(SECOND, @SakRebuild, GetDate()) as nvarchar(50)) + ' sekundes'
Print @Statuss
fetch next from cur into @DBName, @ObjName, @IndName
End
Drop Table #TTable
Close cur
Deallocate cur
Skriptu būtu iespējams papildināt, lai pārbūvētu tikai, piemēram, visvairāk izmantotos indeksus (Kuri indeksi tiek izmantoti?). Nianse ir tāda, ka pēc indeksa pārbūves indeksa lietošanas statistika tiks "nomesta" un tiks sākts to uzkrāt no jauna.
Sarežģītāks un vairāk pārdomāts skripts indeksu pārbūvei atrodams šeit. Atšķirība ir tā, ka skripts izmanto gan indeksu pārbūvi gan reorganizāciju, ņem vērā SQL Server versiju, emuāra autors ir daudz vairāk piestrādājis pie tā izveides. Skripts līdz ar to ir daudz garāks. Tas, ko ieteiktu izmantojot jebkuru variantu- saprast, ko tieši skripts dara, kādēļ, kāda no tā jēga un kam būtu jāmainās. Kādās situācijās skripts palīdz, kādās tam nav nekāda nozīme.
Nav komentāru:
Ierakstīt komentāru