Není žádným tajemstvím, že při zvažování problémů s konfigurací SQL serveru souvisejících se zvyšováním produktivity se většina IT specialistů rozhodne pro zvýšení hardwaru. Ale je to vždy oprávněné? Byly již použity všechny metody konfigurace serveru? Je známo, že práce s konfiguračními parametry a změna jejich výchozích hodnot může zlepšit výkon a další vlastnosti daného systému. Mezi těmito možnostmi konfigurace SQL je jedna možnost, která má mnoho otázek, tato možnost je Maximální stupeň paralelismu (DOP) - takže o ní budeme mluvit.
Možnost Maximum Degree of Parallelism (DOP) určuje počet vláken, na které může SQL Server paralelizovat dotaz, a označuje počet použitých serverových procesorů. Tento parametr má výchozí hodnotu 0 – maximální stupeň rovnoběžnosti. Máte-li například 24 jader, bude hodnota „maximálního stupně paralelismu“ rovna 24 a optimalizátor, pokud to považuje za nutné, může použít všechny procesory k provedení jedné instrukce, to znamená, že požadavek bude paralelizované do 24 vláken. To je dobré pro většinu případů, ale ne pro všechny. Také není vždy dobré používat výchozí hodnotu tohoto parametru. Konfigurace tohoto parametru může být nezbytná například v následující situaci: řekněme, že máme aplikaci, do které všichni zaměstnanci zadávají informace o denních transakcích, a v určitém časovém období každý z uživatelů spustí dotaz, který vytvoří hlásit všechny transakce uživatele za určité časové období. Pokud je časové období dlouhé, bude tento požadavek přirozeně trvat dlouho a se standardně nainstalovaným DOP zabere všechny dostupné procesory, což přirozeně ovlivní práci ostatních uživatelů. Změnou hodnoty DOP tedy můžeme prodloužit dobu odezvy SQL serveru pro ostatní uživatele, aniž bychom měnili samotný dotaz.
MS doporučuje nastavit hodnotu následovně:
Nastavení parametru na TSQL zcela pro server:
EXEC sp_configure "maximální stupeň paralelismu", 4; přenastavit
Tuto hodnotu můžete také nastavit pro konkrétní dotaz TSQL:
POUŽÍVEJTE AdventureWorks2008R2 ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO
V tomto příkladu změní nápověda maxdop výchozí hodnotu parametru maximálního stupně paralelismu na 2. Aktuální nastavení můžete zobrazit takto:
EXEC sp_configure "Zobrazit pokročilé",1; PŘENASTAVIT; EXEC sp_configure "maximální stupeň paralelismu"
Nyní se podívejme, jak tato hodnota ovlivňuje rychlost provádění dotazu. Aby se výše napsaný testovací dotaz prováděl delší dobu, přidáme k němu další select. Žádost bude mít následující formu:
< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty
Na mém testovacím počítači je hodnota 'maximální stupeň paralelismu' nastavena na 0. MSSQL běží na počítači se 4jádrovým procesorem. Provedl jsem sérii experimentů s různými hodnotami MAXDOP: rovna 1 – bez paralelizace dotazů; rovná se 2 - použití pouze 2 jader; rovná se 4 – použití všech a žádné nápovědy k určení možnosti, která používá výchozí pokračování. Chcete-li získat statistiku provádění, musíte do dotazu zahrnout volbu NASTAVIT ČAS STATISTIKY ZAPNUTO a také povolit tlačítko zobrazení plánu dotazů v Management studiu. Abych zprůměroval výsledky, spustil jsem každý dotaz ve smyčce 3krát. Výsledky můžete vidět níže:
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 1); SQL Server Execution Times: CPU time = 45942 ms, elapsed time = 46118 ms. SQL Server Execution Times: CPU time = 45926 ms, elapsed time = 46006 ms. SQL Server Execution Times: CPU time = 45506 ms, elapsed time = 45653 ms.
Plán dotazů ukazuje, že při instalaci nápovědy (MAXDOP 1) byl dotaz proveden bez paralelizace. Průměrná doba provádění dotazu 45925,66 ms
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 2); SQL Server Execution Times: CPU time = 51684 ms, elapsed time = 28983 ms. SQL Server Execution Times: CPU time = 51060 ms, elapsed time = 26165 ms. SQL Server Execution Times: CPU time = 50903 ms, elapsed time = 26015 ms.
Při instalaci nápovědy (MAXDOP 2) byl požadavek spuštěn paralelně na 2 cpu, je to vidět v počtu spuštění v plánu provádění dotazu. Průměrná doba provádění dotazu 27054,33 ms
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 4); SQL Server Execution Times: CPU time = 82275 ms, elapsed time = 23133 ms. SQL Server Execution Times: CPU time = 83788 ms, elapsed time = 23846 ms. SQL Server Execution Times: CPU time = 53571 ms, elapsed time = 27227 ms.
Při instalaci nápovědy (MAXDOP 4) byl požadavek spuštěn paralelně na 4 cpu. Průměrná doba provádění dotazu 24735,33 ms
SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty SQL Server Execution Times: CPU time = 85816 ms, elapsed time = 23190 ms. SQL Server Execution Times: CPU time = 85800 ms, elapsed time = 23307 ms. SQL Server Execution Times: CPU time = 58515 ms, elapsed time = 26575 ms.
požadavek byl proveden paralelně, také 4 cpu. Průměrná doba provádění dotazu 24357,33 ms
odkazy: http://support.microsoft.com/kb/2023536
Maximální stupeň paralelismu (DOP) je další možnost konfigurace serveru SQL Server, která byla předmětem mnoha otázek a publikací. V tomto příspěvku na blogu autor doufá, že poskytne určitou jasnost v tom, co tato možnost dělá a jak by měla být použita.
Za prvé, autor by rád objasnil všechny pochybnosti, že uvedená možnost nastavuje, kolik procesorů může SQL Server používat při obsluhování více připojení (nebo uživatelů) - to ne! Pokud má SQL Server přístup ke čtyřem nečinným procesorům a je nakonfigurován tak, aby používal všechny čtyři procesory, bude používat všechny čtyři procesory bez ohledu na maximální stupeň paralelismu.
Co tedy tato možnost dělá? Tato možnost nastavuje maximální počet procesorů, které může SQL Server použít pro jeden dotaz. Pokud dotaz na SQL Server musí vrátit velké množství dat (mnoho záznamů), má někdy smysl jej paralelizovat a rozdělit na několik malých dotazů, z nichž každý vrátí svou vlastní podmnožinu řádků. SQL Server tedy může používat více procesorů, a proto na víceprocesorových systémech může být potenciálně vráceno velké množství záznamů celého dotazu rychleji než na jednoprocesorovém systému.
Existuje mnoho kritérií, která je třeba vzít v úvahu, než SQL Server vyvolá „paralelismus uvnitř dotazu“ (rozdělení dotazu do více vláken), a nemá smysl je zde uvádět. Najdete je v BOL vyhledáním "Stupeň paralelismu". Říká, že rozhodnutí o paralelizaci je založeno na dostupnosti paměti pro procesor a zejména na dostupnosti samotných procesorů.
Proč bychom tedy měli uvažovat o použití této možnosti – protože její ponechání na výchozí hodnotě (SQL Server dělá svá vlastní rozhodnutí o paralelizaci) může mít někdy nežádoucí účinky. Tyto efekty vypadají asi takto:
- Paralelní dotazy běží pomaleji.
- Časy provádění dotazů se mohou stát nedeterministickými, což může uživatele obtěžovat. Doba provedení se může změnit, protože:
- Dotaz se může někdy paralelizovat a někdy ne.
- Požadavek lze zablokovat paralelním požadavkem, pokud byly procesory dříve přetíženy prací.
Než budeme pokračovat, autor by rád poukázal na to, že není třeba se ponořit do vnitřní organizace paralelismu. Pokud vás to zajímá, můžete si přečíst článek „Paralelní zpracování dotazů“ v Books on Line, který tyto informace popisuje podrobněji. Autor se domnívá, že o vnitřní organizaci souběžnosti je třeba vědět pouze dvě důležité věci:
- Paralelní dotazy mohou vytvářet více vláken, než je uvedeno ve volbě "Maximální stupeň paralelismu". DOP 4 může vytvářet více než dvanáct vláken, čtyři pro dotazování a další vlákna používaná pro řazení, proudy, agregace a sestavy atd.
- Paralelní požadavky mohou způsobit, že různé SPID budou čekat s typem čekání CXPACKET nebo 0X0200. To lze použít k nalezení těch SPIDs, které jsou ve stavu čekání během paralelních operací a mají waittype v sysprocesses: CXPACKET. Pro usnadnění tohoto úkolu autor navrhuje použít uloženou proceduru dostupnou na jeho blogu: track_waitstats.
A tak „Dotaz může být při paralelizaci pomalejší“ proč?
- Pokud má systém velmi nízkou propustnost diskových subsystémů, pak při analýze požadavku může jeho rozklad trvat déle než bez paralelismu.
- Může docházet k zkreslení dat nebo blokování datových rozsahů pro procesor způsobené jiným paralelně používaným procesem a spuštěným později atd.
- Pokud na predikátu není žádný index, výsledkem je prohledávání tabulky. Paralelní operace v rámci dotazu může zakrýt skutečnost, že dotaz by byl dokončen mnohem rychleji s plánem sekvenčního provádění a správným indexem.
Výše uvedené účinky paralelismu by vás přirozeně měly vést k přesvědčení, že vnitřní mechanika paralelizace dotazů není vhodná pro použití v aplikacích OLTP. Jedná se o aplikace, pro které může být změna doby provádění dotazu pro uživatele obtěžující a pro které je nepravděpodobné, že by server obsluhující mnoho souběžných uživatelů zvolil plán paralelního provádění kvůli profilu zátěže procesoru, který je vlastní těmto aplikacím.
Pokud tedy budete paralelismus používat, pak jej s největší pravděpodobností budete potřebovat pro úlohy získávání dat (datový sklad), podporu rozhodování nebo reportingové systémy, kde není mnoho dotazů, ale jsou poměrně těžké a jsou prováděny na výkonném server s velkým množstvím paměti RAM.
Pokud se rozhodnete použít paralelismus, jakou hodnotu byste měli nastavit pro DOP? Dobrá praxe pro tento mechanismus je, že pokud máte 8 procesorů, nastavte DOP = 4 a toto bude s největší pravděpodobností optimální nastavení. Není však zaručeno, že to takto bude fungovat. Jediný způsob, jak mít jistotu, je otestovat různé hodnoty DOP. Kromě toho chtěl autor nabídnout svou empirickou radu, aby toto číslo nikdy nenastavovalo více než polovinu počtu dostupných procesorů. Pokud by autor měl méně než šest procesorů, nastavil by DOP na 1, což jednoduše zakáže paralelizaci. Mohl by udělat výjimku, pokud by měl databázi, která podporuje pouze jeden uživatelský proces (některé technologie získávání dat nebo reportovací úlohy), v takovém případě by bylo možné jako výjimku nastavit DOP na 0 (výchozí hodnota), což umožňuje samotnému SQL Serveru rozhodnout, zda dotaz paralelizovat.
Před dokončením článku vás autor chtěl upozornit, že vytváření paralelního indexu závisí na čísle, které nastavíte pro DOP. To znamená, že jej můžete chtít změnit během vytváření nebo opětovného vytváření indexů, abyste zlepšili výkon této operace, a samozřejmě můžete v dotazu použít nápovědu MAXDOP, která vám umožní přepsat hodnotu nastavenou v konfiguraci a může používat mimo špičku.
A konečně, váš dotaz se může při paralelizaci zpomalit kvůli chybám, takže se ujistěte, že váš server má nainstalovanou nejnovější aktualizaci service pack.
REATE proc track_waitstats@num_samples int = 10
,@delaynum int = 1
,@delaytype nvarchar ( 10 )="minuty"
TAK JAKO
-- T. Davidson
-- Tato uložená procedura je poskytována =JAK JE= bez záruk,
-- a neuděluje žádná práva.
-- Použití přiložených ukázek skriptů podléhá podmínkám
-- specifikováno na http://www.microsoft.com/info/cpyright.htm
-- @num_samples je počet, kolikrát se mají zachytit statistiky čekání,
-- výchozí hodnota je 10krát. výchozí interval zpoždění je 1 minuta
-- delaynum je interval zpoždění. delaytype určuje, zda
-- interval zpoždění jsou minuty nebo sekundy
-- vytvořte tabulku waitstats, pokud neexistuje, jinak zkrátit
nepočítat
pokud neexistuje (vyberte 1
ze sysobjects, kde name = "waitstats" )
vytvořit tabulku waitstats ( varchar ( 80
),
požadavky numerické ( 20
,1
),
číselné ( 20
,1
),
číselné ( 20
,1
),
nyní datum a čas výchozí getdate())
else trucate table waitstats
dbcc sqlperf (waitstats,clear) -- vymazat waitstats
deklarovat @i int
,@delay varchar ( 8
)
,@dt varchar ( 3
)
,@nyní datum a čas
,@totalwait číselné ( 20
,1
)
,@čas ukončení datum a čas
,@začátek datum a čas
,@hr int
,@min int
,@sec int
vyberte @i = 1
vyberte @dt = malá a velká písmena (@delaytype)
když "minuty" pak "m"
když "minuta" pak "m"
když "min" tak "m"
když "mm" tak "m"
když "mi" tak "m"
když "m" tak "m"
když "sekundy" pak "s"
když "druhý" pak "s"
když "sec" pak "s"
když "ss" tak "s"
když "s" tak "s"
jinak @delaytype
konec
pokud @dt není v ("s" ,"m" )
začít
tisk "zadejte prosím typ zpoždění, např. sekundy nebo minuty"
vrátit se
konec
pokud @dt = "s"
začít
vyberte @sec = @delaynum % 60
vyberte @min = obsazení ((@delaynum / 60
) jako int )
vyberte @hr = obsazení ((@min / 60
) jako int )
vyberte @min = @min % 60
konec
pokud @dt = "m"
začít
vyberte @sec = 0
vyberte @min = @delaynum % 60
vyberte @hr = obsazení ((@delaynum / 60
) jako int )
konec
select @delay = right("0" + convert(varchar( 2
),@hr), 2
) + ":"
+
2
),@min), 2
) + ":"
+
+ right("0" +convert(varchar( 2
),@sec), 2
)
pokud @hr > 23
nebo @min > 59
nebo @sec > 59
začít
vybrat "hh:mm:ss zpoždění nelze > 23:59:59"
vyberte "interval zpoždění a zadejte: " + převést (varchar ( 10
)
,@delaynum) + "," + @delaytype + " převede na "
+ @zpoždění
vrátit se
konec
zatímco já<= @num_samples)
začít
vložit do waitstats (, požadavky,
,)
exec ("dbcc sqlperf(waitstats)" )
vyberte @i = @i + 1
čekej na zpoždění @delay
Konec
Vytvořte zprávu o čekání
spustit get_waitstats
--//--//--//--//--//--//--//--//--//-//--//--//--//--//--//--//--//--/
CREATE proc get_waitstats
TAK JAKO
-- Tato uložená procedura je poskytována =JAK JE= bez záruk a
-- Netýká se žádných práv.
-- Použití zahrnutých ukázek skriptů podléhá uvedeným podmínkám
-- na http://www.microsoft.com/info/cpyright.htm
--
-- tento proces vytvoří sestavu waitstats se seznamem typů čekání podle
-- procento
-- lze spustit při provádění track_waitstats
nepočítat
deklarovat @nyní datum a čas
,@totalwait číselné ( 20
,1
)
,@čas ukončení datum a čas
,@začátek datum a čas
,@hr int
,@min int
,@sec int
vyberte @nyní=max (nyní),@začátek=min (nyní),@koncový čas=max (nyní)
z waitstats kde = "Celkem"
Odečtěte waitfor, sleep a resource_queue od Total
vyberte @totalwait = sum() + 1
z waitstats
kde není v ("WAITFOR" ,"SLEEP" ,RESOURCE_QUEUE"
, "Celkem" , "***celkem***" ) a nyní = @nyní
Vložit upravené součty, seřadit podle procenta sestupně
smazat waitstats where = "***total***" a now = @now
vložit do waitstats vyberte "*** celkem***"
,0
,@totalwait
,@totalwait
,@Nyní
vybrat
,
,procento = obsazení ( 100
*/@totalwait jako číselné ( 20
,1
))
z waitstats
kde není v ("WAITFOR" , "SLEEP" , "RESOURCE_QUEUE" , "Celkem" )
a teď = @teď
pořadí podle procenta desc
Tento příspěvek se zaměří pouze na MS SQL Server. Pokud plánujete „zkusit štěstí“ v používání 1C s Oracle, DB2, Postrgre, budou vám tyto informace k ničemu. Musíte však pochopit, že v 1C jsou především specialisté na server MS SQL. Díky úsilí IBM se objevují i specialisté na DB2. Můžete se dlouho dohadovat, zda je tento DBMS dobrý nebo špatný, jedna věc je důležitá, 1C funguje nejvíce „hladce“ se serverem MS SQL. Soudě podle posledních zpráv z „předu“ se práce s DB2 stala víceméně slušnou. I když jsem měl osobně zkušenosti s nastavením 1C pro práci s DB2 již ve verzi 8.1, vše nějak nebylo moc dobré. Volba jiného DBMS musí být každopádně jasně zdůvodněna – buď schopnostmi, které v MS SQL nejsou (cluster s vyrovnáváním zátěže, Grid atd.), nebo financemi (Oracle je již zakoupen), případně platforma (vše je na Linuxu).
Takže, v pořádku, co je třeba udělat s MS SQL Server:
1) Nastavte minimální a maximální velikost paměti. Minimum je polovina systémové paměti. Maximum - systémová paměť bez 2GB. To se provádí pomocí Management Studio - ve vlastnostech serveru:
2) Pokud priorita není nastavena na záložce "Procesory", musíte ji nastavit
3) Nastavte maximální stupeň rovnoběžnosti na 1.
4) Zapněte SQL Server Agent, nakonfigurujte Database Mail - není tam nic složitého, nebudu to podrobně popisovat.
5) Nastavení servisních plánů:
Jsou běžné:
a) Aktualizace statistik - každé 2 hodiny
b) DBCC FREEPROCCACHE - každé 2 hodiny
Pro každou základnu:
a) Úplná záloha
b) Rozdílová záloha
c) Defragmentace indexů - každý den
d) Přestavba indexů - v noci o víkendech
e) Kontrola integrity databáze – jednou měsíčně v noci o víkendech
6) Doporučuji u každé databáze (ve vlastnostech) nastavit model obnovy jako Jednoduchý. Pokud nemáte systém 24/7 a méně než 1 000 uživatelů na základnu, neexistuje žádný cluster s podporou převzetí služeb při selhání a nepodepsali jste smlouvu SLA, ve které se zavazujete obnovit data s přesností až na sekundu (a ne od čas poslední zálohy) v případě selhání jakéhokoli zařízení by toto doporučení bylo rozumné. Jinak budete velmi brzy dlouho a horečně přemýšlet, co s přerostlým Tranzaction Logem dělat
7) Odeberte databázi tempdb z běžných databází na jiný disk – i kdyby to znamenalo překonfigurování pole RAID a snížení jeho výkonu. V opačném případě bude 1 uživatel schopen paralyzovat práci všech ostatních. Pokud máte místo pevného disku Hardware Accelereator, pak jej samozřejmě nemůžete oddělit a dát na něj tempdb, ale to je pouze v případě, že jej máte
8) nainstalujte si nějaký monitorovací nástroj - například se mi líbí spotlight http://www.quest.com/spotlight-on-sql-server-enterprise/
9) Otestujte se s best practice analyzátorem od Microsoftu - http://www.microsoft.com/download/en/details.aspx?id=15289 - skvělý nástroj, který pomáhá nejen s nastavením, ale také s řešením mnoha problémů .
Nyní krátce, proč jsme to všechno udělali:
1) Paměť. Minimální hodnota vás jednoduše ochrání před „závadami“, kdy SQL server z nějakého důvodu, který zná pouze on, nevyužívá veškerou paměť, kterou má k dispozici. Musí se to všechno sníst! Maximální hodnota vás ochrání před swapem, pokud stejný optimalizátor využití paměti serveru SQL rozhodne, že nemohl dělat nic jiného....
3) Velmi důležitý bod - IHMO musí být nastaven na 1 ve všech transakčních systémech. Za prvé to zabraňuje určitému blokování mezi různými procesy, které se snaží splnit 1 požadavek, a chrání nás to před některými „podivnými“ chybami. Za druhé... 1 „zabijácký“ požadavek může převzít všechny prostředky serveru, což je ve vztahu k ostatním uživatelům systému poněkud nespravedlivé. Parametr sám určuje, kolik procesorových jader dokáže zpracovat 1 požadavek.
5) Statistiky a vymazání procedurální mezipaměti jsou známé, ale často zapomínáme na reindexaci. Mezitím je tento postup poměrně důležitý, zejména s rostoucím objemem databáze roste její důležitost. Někdy se kvůli fragmentaci indexu ztratí až 60 % výkonu.
7) Pokud máte Hardwarový akcelerátor nebo jen 2 disky s různou rychlostí přístupu, doporučoval bych zamyslet se i nad alokací skupin souborů v databázích a rozdělením jednotlivých tabulek do různých diskových polí s různou dobou přístupu. Ostatně musíte uznat, že RN „zboží ve skladech“ a adresář „Úložiště doplňkových informací“ jsou 2 objekty, jejichž nároky na skladování jsou zásadně odlišné. Není nutné ukládat všechny soubory a obrázky v databázi na rychlé pole - můžete to rozdělit na samostatné, které není tak rychlé, ale kde je hodně místa (a pak se nebojte mimochodem nahrát do databáze spoustu souborů).
V této krátké poznámce bych rád pohovořil trochu o složitosti nastavení paralelismu v Microsoft SQL Server. Mnozí z vás již dlouho vědí o možnosti Max Degree od Parallelism, která je v SQL Serveru přítomna již velmi dlouho. Ve výchozím nastavení je nastavena na 0, což znamená, že SQL Server sám zvolí optimální stupeň paralelismu, tedy počet procesorů/vlákna použitých k provedení jedné instrukce. Nyní se nezastavím a nebudu diskutovat, na jakou hodnotu je lepší tuto možnost nastavit - toto je téma na samostatnou poznámku. Jen se podívám, jak hodnota této možnosti ovlivňuje provádění dotazů. Například na obrázku níže je tato možnost nastavena na 1, což znamená, že paralelní plány pro všechny dotazy jsou ve výchozím nastavení zakázány.
Tato možnost je také dostupná pro zobrazení pomocí následujícího příkazu T-SQL:
Jakýkoli plán dotazů bude ve výchozím nastavení sekvenční. Například:
Vývojář a každý uživatel má však stále možnost to ovlivnit pomocí nápověd. Chcete-li to provést, stačí zadat požadovaný stupeň paralelismu a vygeneruje se požadovaný plán dotazů, například:
A pokud se na tento dotaz podíváme prostřednictvím pohledu sys.dm_exec_query_profiles, uvidíme, že se skutečně provádí v 10 vláknech.
V systému tedy zůstává tajná díra, kterou mohou vývojáři a uživatelé využít ke „zrychlení“ (zde to dávám do uvozovek záměrně, protože vysoký stupeň paralelismu nevede vždy ke zkrácení doby provádění dotazu) dotazy zvýšením stupně paralelismu . Ale tímto způsobem mohou jednoduše „zabít“ server spuštěním mnoha nekontrolovaných paralelních požadavků současně. Co s tím můžeme dělat? Zde nám přichází na pomoc Resource Governor, velmi výkonný a zcela nedoceněný systém, který umožňuje velmi flexibilně distribuovat zdroje mezi různé skupiny uživatelů. Opět se nebudu nyní zabývat tím, jak funguje a jaké má schopnosti. Jen půjdu do podrobností o tom, jak to ovlivňuje nastavení limitu souběžnosti. Nejprve se podívejme na výchozí nastavení:
Opět vidíme, že ve výchozím nastavení je volba nastavena na 0 a rozhodnutí o výběru maximálního stupně je ponecháno na SQL Serveru. Nyní se podívejme, co se stane, když tuto hodnotu změním na 5. Pozor, za žádných okolností neprovádějte takové nastavení na skutečném systému, protože Dokonce jsem nedefinoval funkci klasifikace pro Resource Governor a měním výchozí skupinu. Ale pro otestování a pochopení toho, jak vše funguje konkrétně nyní v mém příkladu, to stačí. Omezuji tedy maximální míru paralelismu pro všechny na 5 vláken. Dovolte mi, abych vám připomněl, že možnost Maximální stupeň paralelnosti, na který jsme se podívali dříve, je stále nastaven na hodnotu 1. Pokud se nyní podíváme na plán provádění našeho počátečního dotazu, pak bude ve výchozím nastavení sekvenční a s možností maxdop 10 paralelní. Ale pokud spustíme paralelní plán, uvidíme něco zajímavého.
Nyní je náš požadavek proveden pouze v 5 vláknech, a to navzdory skutečnosti, že opce maxdop má hodnotu 10. A pokud pro požadavek zadáte možnost maxdop 4, bude proveden ve 4 vláknech (možnost v Resource Governor je nastavena na 5). V tomto případě nápověda maxdop nižší než nastavení Resource Governor, takže není uloženo žádné další omezení. To už nebudu uvádět příklad.
Resource Governor je tedy výkonnější nástroj, který ve skutečnosti omezuje maximální míru paralelismu pro dotazy a tento stupeň lze nastavit různě pro různé skupiny uživatelů. V tomto případě možnost Maximální stupeň paralelnosti stále funguje a přispívá svým dílem (nebo mírně mate administrátory, vývojáře a uživatele, když funguje ve spojení s Resource Governor). Dále jsou možnosti nastavení hodnot těchto 2 parametrů omezeny pouze vaší představivostí, ale je důležité si pamatovat pouze dvě věci: Maximální stupeň paralelnosti a naznačit maxdop u požadavku ovlivňuje, který plán bude vygenerován, maximální počet vláken, který bude pro tento požadavek možný, a správce zdrojů dále omezuje požadavek shora během provádění.
Parametr "maximální stupeň paralelismu" určuje maximální počet podprocesů, na které může SQL Server paralelizovat dotaz. Ve výchozím nastavení je tento parametr nula, což znamená, že je použit počet procesorů serveru. Máte-li například 24 jader, skutečná hodnota "maximálního stupně paralelismu" bude 24 a optimalizátor, pokud to považuje za nutné, může paralelizovat dotaz do 24 vláken. Obecně je to dobré, ale ne vždy. Také není vždy dobré používat výchozí hodnotu tohoto parametru.
Nyní se podívejme, proč to není dobré. Uvedu jeden příklad ze své praxe. Existuje dotaz, který by teoreticky měl používat určitý index, a to se nejprve stane. Spustí se dotaz, který prohledá index a vrátí potřebná data. Vše je v pořádku. Poté, jak databáze roste, do tabulky se přidávají další a další záznamy a v určitém okamžiku si optimalizátor uvědomí, že je možné provést dotaz rychleji: „Proč bych měl provádět indexové vyhledávání, když mám 24 jader? To znamená, že mohu skenovat seskupený index ve 24 vláknech a získat data, která potřebuji, rychleji!“ Pro tento konkrétní dotaz je to dobré - běží rychleji. Ale je to špatné pro všechny ostatní, protože... jsou nuceni čekat, až jim budou přiděleny prostředky procesoru. A v systémech s velkým počtem současně prováděných dotazů je taková paralelizace spíše špatná než dobrá. A místo zlepšování produktivity se to jen zhoršuje. Až donedávna jsem tento problém řešil nastavením nápovědy MAXDOP v úložištích, která se mi nelíbila. Ale teď jsem našel konkrétní doporučení společnosti Microsoft a aplikoval jsem je na své servery. Doporučení pro výběr optimální hodnoty pro "maximální stupeň paralelismu" jsou zde:Doporučení a pokyny pro možnost konfigurace "maximální stupeň paralelismu". . Cituji toto doporučení:
Pro servery SQL Server 2008 R2, SQL Server 2008 a SQL Server 2005 použijte následující pokyny: a. Pro servery, které mají osm nebo méně procesorů, použijte následující konfiguraci, kde N se rovná počtu procesorů: maximální stupeň paralelismu = 0 až N. b. Pro servery, které používají více než osm procesorů, použijte následující konfiguraci: max. stupeň paralelismu = 8. c. U serverů, které mají nakonfigurováno NUMA, by maximální stupeň paralelismu neměl překročit počet CPU, které jsou přiřazeny každému uzlu NUMA s maximální hodnotou omezenou na 8. To zvýší pravděpodobnost, že všechna paralelní vlákna dotazu budou umístěna v rámci NUMA Node a vyhněte se nákladnému vyhledávání dat vzdáleného uzlu. d. U serverů, které mají povoleno hyper-threading, by hodnota maximálního stupně paralelismu neměla překročit počet fyzických procesorů.
Z toho vyplývá, že pro systémy s více než 8 procesory se doporučuje nastavit „max stupeň paralelismu“ = 8. Následuje další vysvětlení, které říká, že 8 je obecné doporučení. A v systémech, kde je počet současně vykonávaných požadavků malý, má smysl nastavit větší hodnotu a v systémech s velkým počtem konkurenčních požadavků má smysl nastavit menší hodnotu. A při výběru konkrétního parametru je potřeba se podívat na jeho dopad na systém a otestovat ho na konkrétních požadavcích.