Osvedčené postupy pre SQL Server, konfigurácia časti 1
Domovská stránka »Osvedčené postupy pre SQL Server, časť 1: Konfigurácia
Čítať príručky s osvedčenými postupmi pre server Microsoft SQL Server môže byť dosť nepríjemná práca. Po tom, čo sme sa prebojovali cez technické príručky, príručky najlepších postupov, články TechNet a texty blogov od odborníkov na SQL, sme dospeli k záveru, že by bol užitočný ľahko pochopiteľný text na túto tému.
Naším cieľom nie je ísť do každého nastavenia, ale vysvetliť niekoľko vecí, ktoré vám pomôžu pri nastavovaní a riešení problémov s výkonom.
Zdieľané a vyhradené inštancie
Ak aplikácia používa veľké množstvo schém/uložených procedúr, môže to potenciálne znížiť výkon aplikácií pri použití tej istej inštancie. Dostupné zdroje sa môžu rozdeliť alebo zablokovať. To môže viesť k spomaleniu ďalších databázových aplikácií na zdieľanej inštancii servera SQL Server. Oprava problémov s výkonom môže byť nepríjemná práca, pretože musíte zistiť, ktorá inštancia spôsobuje problém. A to často nie je také ľahké.
To, ako spoločnosti odpovedajú na túto otázku, sa zvyčajne zvažuje oproti nákladom na operačné systémy a licencie SQL. Ak je výkon aplikácie najvyššou prioritou, vysoko sa odporúča vyhradená inštancia.
Spoločnosť Microsoft nepredáva licencie na server SQL Server pre jednotlivé inštancie, ale pre jednotlivé jadrá. Z dôvodu nákladov majú preto správcovia tendenciu inštalovať toľko inštancií servera SQL Server, koľko server dokáže spracovať, čo môže z dlhodobého hľadiska viesť k veľkým problémom s výkonom.
Kedykoľvek je to možné, mali by ste sa preto rozhodnúť pre vyhradené inštancie SQL.
Rozdelenie súborov SQL na rôzne dátové nosiče
SQL Server používa na čítanie údajov a súborov protokolov veľmi odlišné I/O vzory. Prístup k dátovým súborom je zvyčajne náhodný, k súborom denníkov transakcií postupne. Pri rotujúcich pevných diskoch je potrebné premiestniť čítaciu hlavu, aby sa zabezpečil náhodný vstup/výstup. Postupný prístup k dátam je preto účinnejší ako náhodný. Ak oddeľujete súbory s rôznymi prístupovými vzormi, minimalizujete tiež počet pohybov čítacej hlavy a tým optimalizujete výkon úložiska.
Pre čo najlepší výkon a dostupnosť používajte systémy RAID-10 pre binárne súbory, údaje, súbory denníkov a databázy tempdb.
Dimenzovanie databáz tempdb
Nastavte veľkosť súborov databáz tempdb na maximálnu hodnotu, aby ste predišli fragmentácii disku.
Konflikty môžu nastať na stránkach GAM, SGAM a PFS, keď musí SQL zapisovať na konkrétne stránky systému, aby pridelil nové objekty. Západky chránia (zamykajú) tieto stránky v pamäti. Na preťaženom serveri SQL môže trvať dlho, kým sa systémový súbor zablokuje v databáze tempdb, čo povedie k dlhším časom dotazovania. Tento jav sa nazýva „závora závory“.
Pravidlo pre vytváranie súborov tempdb:
- Pre 8 jadier
- 8 tempdb dátových súborov
Od verzie SQL Server 2016 sa počet jadier CPU viditeľných pre operačný systém určuje automaticky počas inštalácie. Na základe toho SQL Server vypočítava a konfiguruje počet súborov tempdb potrebných na optimálny výkon. Toto je výrazné zlepšenie oproti predchádzajúcim verziám - sláva Microsoftu!
Konfigurácia úložiska
- Min. Pamäť servera
- Max. Pamäť servera
- Max. Počet pracovných vlákien
- Úložisko na vytváranie indexov
- Min. Pracovná pamäť na jeden dotaz
Min. Pamäť servera
S možnosťou „Min. Pamäť servera “môžete určiť minimálne množstvo pamäte pre inštanciu servera SQL Server. Pretože SQL Server je skutočná pamäť, ktorá využíva každú dostupnú pamäť RAM, toto nastavenie sa zvyčajne používa, keď operačný systém vyžaduje od servera SQL Server príliš veľa pamäte. Vďaka virtualizačným technológiám je však tento prístup dôležitejší.
Max. Pamäť servera
S možnosťou „Max. Pamäť servera “, je nastavená maximálna pamäť pre inštanciu servera SQL Server. Je to obzvlášť dôležité, keď sú okrem servera SQL Server spustené aj iné aplikácie a chcete sa uistiť, že je pre ne k dispozícii dostatok pamäte.
Niektoré aplikácie jednoducho využívajú pamäť dostupnú pri štarte a nevyžadujú viac pamäte RAM. Ani keby to bolo nevyhnutné. Tu prichádza možnosť „Max. Do úvahy prichádza serverová pamäť.
V klastri alebo farme servera SQL Server môže o zdroje konkurovať viac inštancií servera SQL Server. Nastavením limitu pamäte pre každú inštanciu servera SQL Server vyhnete sa tejto konkurencii pre RAM a zaistíte optimálny výkon.
Nezabudnite na operačný systém ponechať aspoň 4–6 GB RAM, aby ste predišli problémom s výkonom.
Max. Počet pracovných vlákien
Táto možnosť sa používa na optimalizáciu výkonu, keď je k serveru SQL pripojený veľký počet klientov. Spravidla sa pre každú požiadavku na dotaz vytvorí samostatné vlákno operačného systému. Použitie jedného vlákna na jednu požiadavku na dotaz pre stovky pripojení k serveru však môže spotrebovať veľké systémové prostriedky. Možnosť „Max. Počet pracovných vlákien ”pomáha zvyšovať výkon, pretože SQL Server vám umožňuje vytvárať skupiny pracovných vlákien, ktoré zvládnu väčší počet požiadaviek na dotazy.
Predvolená hodnota je 0, čo umožňuje serveru SQL Server automaticky nakonfigurovať počet pracovných vlákien pri štarte. Toto nastavenie je vhodné pre väčšinu systémov. „Max. Počet pracovných vlákien “je rozšírená možnosť a mala by sa meniť iba v spolupráci so skúseným správcom databázy.
Kedy by ste mali nakonfigurovať server SQL Server tak, aby používal viac pracovných vlákien? Ak je priemerná dĺžka frontu na plánovací modul viac ako 1, môže mať zmysel zvýšiť počet vlákien - ale iba ak zaťaženie nie je viazané na procesor alebo sú inak dlhé čakacie doby. Ak je ktorákoľvek z týchto dvoch možností pravdivá, nemá zmysel pridávať ďalšie vlákna, pretože by tiež skončili v rade.
Úložisko na vytváranie indexov
Toto je tiež rozšírená možnosť, ktorú by ste za normálnych okolností nemali meniť. Toto riadi maximálne množstvo pamäte RAM pridelenej na vytvorenie indexov. Predvolená hodnota pre túto možnosť je 0, čo znamená, že SQL Server automaticky nakonfiguruje toto nastavenie. Ak však máte problém s vytvorením indexu, môžete túto hodnotu zvýšiť.
Min. Pracovná pamäť na jeden dotaz
Po vykonaní dotazu sa server SQL Server pokúsi prideliť pre ňu optimálne množstvo pamäte. V predvolenom nastavení je pre každý dopyt minimálne 1 024 kB. Odporúčame ponechať predvolené nastavenie na 0, aby mohol server SQL Server dynamicky spravovať pamäť pridelenú na vytvorenie indexu. Ak má však server SQL Server viac pamäte RAM, ako je potrebné na efektívne vykonávanie programu, výkon niektorých dotazov možno zvýšiť zvýšením počtu. Pokiaľ je na serveri k dispozícii voľná pamäť, ktorú nevyužíva server SQL Server, iné aplikácie alebo operačný systém, zvýšenie hodnoty môže zlepšiť celkový výkon servera SQL Server. Ak však nie je k dispozícii voľná pamäť RAM, akcia má skôr negatívny vplyv na celkový výkon.
Konfigurácia procesora
Hyperthreading
Hyperthreading je špeciálna implementácia simultánneho multithreadingu (SMT) v procesoroch Intel na zlepšenie paralelizácie výpočtov (multitasking) v mikroprocesoroch x86. Hardvér, ktorý používa hyperthreading, spôsobuje, že logické procesory hyperthreadingu sa v operačnom systéme javia ako fyzické CPU. SQL Server potom zistí fyzické procesory, ktoré označuje operačný systém. Týmto spôsobom môžete využiť výhody procesorov Hypherthreading.
Jediným úlovkom je, že každá verzia servera SQL Server má svoj vlastný limit výpočtovej kapacity.
NUMA (nerovnomerný prístup do pamäte)
NUMA je metóda na optimalizáciu prístupu do pamäte. A s ich pomocou možno zvýšiť rýchlosť procesora bez zvýšenia vyťaženia zbernice procesora. SQL Server podporuje NUMA a funguje dobre na hardvéri NUMA bez potreby akejkoľvek špeciálnej konfigurácie.
Pridruženie procesora
Pokiaľ nemáte problémy s výkonom, je nepravdepodobné, že budete niekedy musieť zmeniť predvolené nastavenie asociácie procesora. Napriek tomu stojí za to vedieť o tom viac.
SQL Server podporuje príbuznosť procesora prostredníctvom dvoch možností masky:
- Affinity Mask (tiež známa ako CPU affinity mask)
- Afinita I/O
SQL Server používa všetky CPU dostupné v operačnom systéme. Pre všetky CPU sú vytvorené plánovacie moduly, aby sa zdroje využili optimálne. Pri multitaskingu môže operačný systém alebo iné aplikácie na serveri SQL presúvať vlákna procesu medzi procesormi. Pretože server SQL Server používa veľké množstvo zdrojov, môže to mať vplyv na výkon. Aby sa tento efekt minimalizoval, procesory je možné nakonfigurovať tak, aby sa zaťaženie servera SQL priradilo k predtým vybranej skupine procesorov. To je možné pomocou masky afinity CPU.
Možnosť I/O afinita viaže disk I/O disku SQL Server na konkrétnu podmnožinu procesorov. V prostrediach online transakčného spracovania (OLTP) môže toto vylepšenie vylepšiť výkon vlákien SQL Server, ktoré spôsobujú vstupno-výstupné operácie.
Poznámka: Hardvérová afinita pre jednotlivé disky alebo radiče diskov nie je podporovaná.
Max. Stupeň rovnobežnosti (MAXDOP)
V predvolenom nastavení používa server SQL Server na vykonávanie dotazov všetky dostupné procesory. Aj keď je to prospešné pre veľké dotazy, môže to mať vplyv na výkon a súbežnosť. Lepším prístupom je obmedziť paralelnosť na počet fyzických jadier v sokete procesora. Napríklad MAXDOP by mal byť nastavený na 4 na serveri SQL s dvoma zásuvkami fyzických procesorov so štyrmi jadrami, bez ohľadu na funkciu hyperthreading. MAXDOP nemôže určiť, ktorý procesor sa používa. Skôr to obmedzuje maximálny počet procesorov, ktoré je možné použiť na jeden dopyt.
Cenový limit pre súbežnosť
Predvolená hodnota pre túto možnosť je 5. Optimalizátor dotazov používa nákladovú hranicu na určenie, či má zmysel vytvárať paralelné plány pre dotazy. 5 je veľmi nízka hodnota, ktorá je vhodná iba pre čisté aplikácie OLTP (mimochodom vrátane DatAdvantage).
Pre systémy, ktoré nepoužívajú OLTP, odporúčame najskôr nastaviť hodnotu okolo 50 a podľa potreby ju upraviť. Určite by ste mali upraviť hodnotu pre kritické dotazy v rámci aplikácie.
Ďalšie dôležité nastavenia
Okamžitá inicializácia súboru
Ak udelíte serveru SQL Server oprávnenie „Vykonávať úlohy údržby zväzku“ systému Windows, budete mať pri rozširovaní údajových súborov lepší výkon.
Windows zvyčajne napíše veľa núl, akonáhle používateľ potrebuje miesto. Keď vytvoríte súbor s veľkosťou 1 MB, systém Windows zapíše na disk 1 MB núl, čím súbor inicializuje. Ak má server SQL Server oprávnenie „Vykonávať úlohy údržby zväzku“, vyzve systém Windows, aby označil požadovaný priestor ako obsadený, a okamžite ho vráti na server SQL Server. Toto umožňuje rýchlejšie zväčšenie súborov.
Záložná kompresia
Od verzie SQL Server 2008r2 možno kompresiu záloh aktivovať pomocou začiarkavacieho políčka.
Výsledkom je, že zálohy vyžadujú menej úložného priestoru, trvajú kratšie a ich obnova je ešte rýchlejšia. Toto nastavenie by ste mali určite použiť.
Vyhradené pripojenie vzdialeného správcu
Toto nastavenie potrebujete, iba ak niečo nie je v poriadku so serverom SQL Server.
Keď pristupujete cez vyhradené pripojenie správcu (DAC), server SQL Server poskytuje vyhradené pripojenie, plánovač procesora a pamäť. Vzdialené riešenie problémov s inštanciou servera SQL Server, ktorá je neustále na 100% využití procesora, je oveľa jednoduchšie, ak máte vyhradené zdroje! Ak chcete používať vzdialený DAC, musíte byť pripojení k serveru SQL Server buď fyzicky prostredníctvom konzoly, alebo vzdialene prostredníctvom protokolu RDP. Toto nastavenie by ste mali určite tiež použiť. Po aktivácii na ňu môžete znova zabudnúť!
Záver
SQL Server poskytuje výkon a škálovateľnosť požadovanú na podporu aplikácií produkčnej databázy za predpokladu, že sa budú dodržiavať osvedčené postupy.
V našom ďalšom blogovom príspevku sa pozrieme na osvedčené postupy pre SQL Server vo virtualizovaných prostrediach.