otrdiena, 2012. gada 3. jūlijs

SQL Server parametrs: "max degree of parallelism"

Ja SQL Server instance var izmantot vairāk kā vienu kodolu, tad atsevišķos gadījumos SQL Server arī izmantos vairāk kā vienu kodolu viena pieprasījuma apstrādāšanai. Tātad- vaicājuma izpilde notiks veicot vairākas paralēlas darbības. Atsevišķos gadījumos tas var paātrināt vaicājumu izpildi, bet citos- gluži otrādi.

Galvenokārt esmu strādājis ar OLTP sistēmām nevis ar datu noliktavām. OLTP ir raksturīgi ļoti daudz mazi pieprasījumi un tikai retos gadījumos pāris "smagāki" vaicājumi. SQL Server instancēm, kurām ir raksturīga šāda noslodze tiek ieteikts uzstādīt ierobežojumu, ka vaicājumi neizmanto vairāk kā vienu procesoru. Šis likums nav akmenī iecirsts- ļoti daudz kas var būt atkarīgs no niansēm, bet lielos vilcienos šis likums strādā.

Esmu pieredzējis, ka SQL Server vaicājums no 3 minūšu izpildes laika ieiet fāzē "nekad" (izpildes laiks ir lielāks par pacietību gaidīt izpildes rezultātu. Protams, uzreiz jāsaka, ka 3-minūtīgi vaicājumi nebūt nav OLTP sistēmu raksturojoši, gluži otrādi. Un tomēr- arī tādi mēdz gadīties). Toreiz šī problēma radās negaidīti- vienā brīdī visu laiku strādājošs vaicājums pārstāja normāli strādāt. Pēc izpildes plāna varēja redzēt, ka vaicājums sāk izmantot vairāk kā vienu procesoru. Vaicājums bija gana sarežģīts un SQL Server optimizators (matemātiski) nolēma, ka tā vajag- toreiz tā nebija pareiza izvēle.

Iemesli kļūdainai plāna izveidei var būt dažādi- piemēram, novecojusi statistika (SQL Server "domā", ka dati ir savādāk raksturojami, nekā tie ir patiesībā), vāji, neoptimāli uzrakstīti vaicājumi, jo ne vienmēr SQL Server spēj apstrādāt vaicājumu vislabākajā iespējamajā veidā, diski- piemēram, sākot paralēlu izpildi pēkšņi IO kļūst par šauro sistēmas vietu.. un vēl citi.
Pēc noklusējuma- visiem vaicājumiem, kuru plānotais izpildes laiks ir lielāks par 5 sekundēm tiek "apsvērta" iespēja izmantot paralēlu izpildes plānu.

Rekomendācijas, varianti, saites
MS lapās var atrast rekomendācijas "max degree of parallelism" uzstādīšanai:
Recommendations and Guidelines for 'max degree of parallelism' configuration option

Cits veids, kā kontrolēt cik kodoli tiek izmantoti, ir norādot pie kādas plānotā izpildes laika sākt "apsvērt" paralēlo izpildi. Tas ir vēl viens SQL Server konfigurācijas parametrs, par ko šajā rakstā plašāk ne, bet par to var šeit:
Cost threshold for parallelism Option

Vēl ir iespējams izmantot Query Hint (uj, nezinu kā to sauc latviski)- ja ir izmantot šis Hints, tad servera konfigurācijas uzstādījums tiek ignorēts. Piemēram, lai speciāli ierobežotu paralēlu izpildi:
Select SUM(LEN(SomeField))
From dbo.SomeHugeTable
OPTION (MAXDOP 1)
Par paralēlām darbībām ar indexiem:
Configure Parallel Index Operations
Manuprāt, ļoti interesants raksts par šo tēmu:
The ins and outs of MAXDOP (un sekojot saitēm- vairāki labi raksti)

Konfigurācijas apskatīšana un mainīšana
Šeit droši vien nav vērts pakavēties. Gluži tāpat kā citus SQL Server parametrus- gan ar TSQL, gan SSMS grafisko rīku palīdzību.
Bet msdn apraksts, ja tas nav darīts.
Apskatīt uzstādījumus var šādi:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max degree of parallelism'
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
Kad pievērst uzmanību
Idejiski ir tā- izpildot vaicājumu uz vairākiem kodoliem, izpildei jābūt sinhronizētai un darbībai jānotiek uz vairākiem kodoliem vienlaicīgi (citādi tā vairs nebūtu paralēla izpilde). Lai piekļūtu CPU resursiem nākas gaidīt, šādā gadījumā viss ir sarežģītāk, jo vajadzīgs nevis viens, bet uzreiz vairāki brīvi kodoli. Tas noved pie speciāla gaidīšanas tipa (CXPACKET). Informāciju par resursu gaidīšanu SQL Server var apskatīt DMV skatā sys.dm_os_wait_stats. Tajā tiek uzskrāta infromācija no iepriekšējās SQL Servera pārstartēšanas reizes.

Izpildīju šādu vaicājumu uz vairākiem serveriem:
-- konkrētais tips (atkomentē, ja vēlies)
--Select *
--From sys.dm_os_wait_stats
--Where wait_type = 'CXPACKET';

-- Procentuālais aprēķins
With SumAll as
(
    Select Sum(wait_time_ms) total
    From sys.dm_os_wait_stats
)
Select Sum(wait_time_ms)/Cast(Sum(total) as decimal(18,5)) * 100 [Procenti no kopējā]
From sys.dm_os_wait_stats
    cross join SumAll
Where wait_type = 'CXPACKET' ;
Iegūtais rezultāts:

Tā nu ir sanācis, ka es zinu- "Server 3" tiek izmantots OLTP sistēmām, zinu, ka visiem serveriem, kuriem "Procenti no kopējā = 0" ir uzstādīts, ka drīkst izmantot ne vairāk kā vienu kodolu vienam vaicājumam.
Lai arī šis skaitlis pats par sevi vien vēl neko nenozīmē, tomēr normālā gadījumā OLTP sistēmām būtu jābūt mazākam par 5% (MSDN un šeit). Tātad, Serverim 3 mainot "max degree of parallelism" parametru, potenciāli var uzlabot ātrdarbību.

* Starp citu, lai no SQL Server pārstartēšanas brīža uzkrāto statistiku notīrītu un sāktu atksaiti no jauna, var izmantot šādu komandu:
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

Nav komentāru:

Ierakstīt komentāru