SQL Server legjobb gyakorlatok, 1. rész konfigurációja
Főoldal »Az SQL Server legjobb gyakorlatai, 1. rész: Konfiguráció
A Microsoft SQL Server bevált gyakorlati útmutatóinak elolvasása elég nehéz feladat. Miután megküzdöttünk a technikai útmutatókkal, a legjobb gyakorlatokkal foglalkozó útmutatókkal, a TechNet cikkekkel és az SQL szakértők blogszövegeivel, arra a következtetésre jutottunk, hogy hasznos lenne egy könnyen érthető szöveg ebben a témában.
Célunk nem az, hogy minden egyes beállításba belemerüljünk, hanem hogy elmagyarázzunk néhány dolgot, amelyek segítenek a teljesítmény beállításában és tapasztalataiban.
Megosztott és dedikált példányok
Ha egy alkalmazás nagyszámú sémát/tárolt eljárást használ, ez potenciálisan ronthatja az alkalmazások teljesítményét ugyanazon példány használatával. A rendelkezésre álló erőforrások felbonthatók vagy blokkolhatók. Ez ahhoz vezethet, hogy más adatbázis-alapú alkalmazások lelassulnak a megosztott SQL Server példányon. A teljesítményproblémák kijavítása nehézkes lehet, mert meg kell találnia, melyik példány okozza a problémát. És ez gyakran nem olyan egyszerű.
Azt, hogy a vállalatok hogyan válaszolnak erre a kérdésre, általában az operációs rendszerek és az SQL licencek költségeivel mérik. Ha az alkalmazás teljesítménye kiemelt fontosságú, akkor kifejezetten ajánlott egy dedikált példány.
A Microsoft nem SQL Server licenceket ad el szerverekhez példányonként, hanem magonként. Költségek miatt az adminisztrátorok általában annyi SQL Server példányt telepítenek, amennyit egy szerver csak kezelni tud, ami hosszú távon hatalmas teljesítményproblémákhoz vezethet.
Amikor csak lehetséges, érdemes dedikált SQL-példányokat választania.
SQL fájlok felosztása különböző adathordozókra
Az SQL Server nagyon különböző I/O mintákat használ az adatok és a naplófájlok olvasásához. Az adatfájlokhoz való hozzáférés általában véletlenszerű, a tranzakciós naplófájlokhoz pedig szekvenciálisan. Forgó merevlemezek esetén az olvasófejet át kell helyezni a véletlenszerű I/O hozzáféréshez. A szekvenciális adatelérés tehát hatékonyabb, mint a véletlenszerű. Ha különféle hozzáférési mintákkal rendelkező fájlokat választ el, akkor az olvasási fejmozgások számát is minimalizálja, és ezáltal optimalizálja a tárolási teljesítményt.
Használja a RAID-10 rendszereket bináris fájlokhoz, adatokhoz, naplófájlokhoz és tempdb adatbázisokhoz a lehető legjobb teljesítmény és rendelkezésre állás érdekében.
A tempdb adatbázisok méretezése
A lemez töredezettségének elkerülése érdekében állítsa a tempdb adatbázisok fájlméretét a maximális értékre.
Konfliktusok merülhetnek fel a GAM, SGAM és PFS oldalakon, amikor az SQL-nek specifikus rendszeroldalakra kell írnia az új objektumok kiosztásához. A reteszek ezeket az oldalakat védik (zárják) a memóriában. Túlterhelt SQL szerveren hosszú időbe telhet, amíg egy rendszerfájl reteszelődik a tempdb adatbázisban, ami hosszabb lekérdezési időkhöz vezet. Ezt a jelenséget "reteszelődésnek" nevezik.
Alapszabály a tempdb adatfájlok létrehozásához:
- 8 magra
- 8 tempdb adatfájl
Az SQL Server 2016-tól kezdve a telepítés során az operációs rendszer számára látható CPU-magok száma automatikusan meghatározódik. Ennek alapján az SQL Server kiszámítja és konfigurálja az optimális teljesítményhez szükséges tempdb fájlok számát. Ez jelentős előrelépés a korábbi verziókhoz képest - köszönet a Microsoftnak!
Tárolási konfiguráció
- Min. Szerver memória
- Max. Szerver memória
- Max. Munkásszálak száma
- Tárhely az index létrehozásához
- Min. Munkamemória lekérdezésenként
Min. Szerver memória
A "Min. Kiszolgálómemória ”meghatározhatja az SQL Server példány minimális memóriamennyiségét. Mivel az SQL Server igazi memória-disznó, minden rendelkezésre álló RAM-memóriát használ, ezt a beállítást általában akkor használják, ha az operációs rendszer túl sok memóriát igényel az SQL Server-től. Ez a hozzáállás azonban a virtualizációs technológiák miatt egyre fontosabbá válik.
Max. Szerver memória
A „Max. Kiszolgálómemória ”, az SQL Server példány maximális memóriája van beállítva. Különösen fontos, ha az SQL Server mellett más alkalmazások is futnak, és biztosítani akarja, hogy elegendő memória álljon rendelkezésre számukra.
Néhány alkalmazás egyszerűen használja az indításkor rendelkezésre álló memóriát, és nem igényel több RAM-ot. Még akkor sem, ha erre szükség lenne. Itt jön a „Max. Szerver memória ”kerül játékba.
Egy SQL Server-fürtben vagy farmon több SQL Server-példány is versenyezhet az erőforrásokért. Ha minden SQL Server-példányhoz memóriakorlátot állít be, elkerülheti ezt a versenyt a RAM-okért, és biztosítja az optimális teljesítményt.
Ne felejtsen el legalább 4-6 GB RAM-ot hagyni az operációs rendszer számára a teljesítményproblémák megelőzése érdekében.
Max. Munkásszálak száma
Ez az opció a teljesítmény optimalizálására szolgál, ha nagyszámú ügyfél csatlakozik az SQL szerverhez. Általában minden egyes lekérdezési kérelemhez külön operációs rendszer szál jön létre. Ha azonban lekérdezésenként egy szálat használ a kiszolgálóval való kapcsolatok százaihoz, akkor nagy rendszererőforrások kerülhetnek fel. A „Max. Munkavállalói szálak száma ”segít a teljesítmény javításában, mert az SQL Server lehetővé teszi olyan munkaszálak készletének létrehozását, amelyek nagyobb számú lekérdezési kérelmet képesek kezelni.
Az alapértelmezett érték 0, amely lehetővé teszi az SQL Server számára, hogy indításakor automatikusan konfigurálja a dolgozói szálak számát. Ez a beállítás a legtöbb rendszerhez alkalmas. "Max. Munkafonalak száma ”egy speciális lehetőség, amelyet csak tapasztalt adatbázis-adminisztrátorral együttműködve szabad megváltoztatni.
Mikor kell beállítani az SQL Server alkalmazást több munkásszál használatára? Ha az ütemezési modulonkénti átlagos várakozási idő hosszabb, mint 1, akkor van értelme növelni a szálak számát - de csak akkor, ha a terhelés nincs CPU-ra kötve, vagy egyébként hosszú várakozási idő van. Ha a kettő közül bármelyik igaz, akkor nincs értelme további szálakat hozzáadni, mert azok is a sorba kerülnének.
Tárhely az index létrehozásához
Ez egy speciális beállítás is, amelyet általában nem szabad megváltoztatni. Ez szabályozza az indexek létrehozásához elkülönített RAM maximális mennyiségét. Ennek az opciónak az alapértelmezett értéke 0, ami azt jelenti, hogy az SQL Server automatikusan konfigurálja ezt a beállítást. Ha azonban nehézségei vannak az index összeállításával, növelheti ezt az értéket.
Min. Munkamemória lekérdezésenként
A lekérdezés végrehajtásakor az SQL Server megpróbálja az optimális memóriamennyiséget lefoglalni hozzá. Alapértelmezés szerint minden lekérdezéshez minimum 1024 KB van. Javasoljuk, hogy hagyja az alapértelmezett beállítást 0-nál, hogy az SQL Server dinamikusan tudja kezelni az index létrehozásához lefoglalt memóriát. Ha azonban az SQL Server több RAM-mal rendelkezik, mint ami a program hatékony végrehajtásához szükséges, akkor egyes lekérdezések teljesítménye növelhető a szám növelésével. Amíg van szabad memória a szerveren, amelyet nem az SQL Server, más alkalmazások vagy az operációs rendszer használ, az érték növelése javíthatja az SQL Server általános teljesítményét. Ha azonban nincs szabad RAM, a művelet meglehetősen negatívan befolyásolja az általános teljesítményt.
A processzor konfigurációja
Hyperthreading
A Hyperthreading az egyidejű többszálas (SMT) speciális megvalósítása az Intel processzoraiban, hogy javítsa a számítások párhuzamosságát (multitasking) x86 mikroprocesszorokban. A hiperszálat használó hardver miatt a logikai hiperszálas CPU-k fizikai operációs rendszerekként jelennek meg az operációs rendszerben. Ezután az SQL Server felismeri azokat a fizikai processzorokat, amelyeket az operációs rendszer jelez. Ily módon kihasználhatja a Hypherthreading processzorok előnyeit.
Az egyetlen fogás az, hogy az SQL Server minden verziójának megvan a saját számítási kapacitáskorlátja.
NUMA (nem egységes memória-hozzáférés)
A NUMA egy módszer a memória elérésének optimalizálására. Segítségükkel a processzor sebessége növelhető anélkül, hogy növelnék a processzor busz kihasználtságát. Az SQL Server támogatja a NUMA-t, és jól működik a NUMA hardvereken, különösebb konfiguráció nélkül.
A processzor csatlakozása
Ha nem merül fel teljesítményprobléma, nem valószínű, hogy valaha is módosítania kell az alapértelmezett processzor társítási beállítást. Ennek ellenére érdemes többet tudni róla.
Az SQL Server két maszk opcióval támogatja a processzor affinitását:
- Affinity Mask (más néven CPU affinitás-maszk)
- I/O affinitás
Az SQL Server az operációs rendszer összes rendelkezésre álló CPU-ját használja. Ütemezési modulokat hoznak létre az összes CPU számára az erőforrások optimális felhasználása érdekében. Többfeladatos végrehajtáskor az operációs rendszer vagy az SQL szerver egyéb alkalmazásai a folyamat szálait mozgathatják a processzorok között. Mivel az SQL Server nagy mennyiségű erőforrást használ, ez befolyásolhatja a teljesítményt. Ennek a hatásnak a minimalizálása érdekében a processzorok úgy konfigurálhatók, hogy az SQL Server terhelése egy korábban kiválasztott processzorcsoporthoz legyen hozzárendelve. Ez a CPU affinitás maszkon keresztül lehetséges.
Az I/O affinitás opció az SQL Server lemez I/O-t a CPU-k egy meghatározott részhalmazához köti. Online tranzakciós feldolgozási (OLTP) környezetekben ez a fejlesztés javíthatja az I/O műveleteket okozó SQL Server szálak teljesítményét.
Jegyzet: Az egyes lemezekhez vagy lemezvezérlőkhöz való hardver-affinitás nem támogatott.
A párhuzamosság max. Foka (MAXDOP)
Alapértelmezés szerint az SQL Server az összes rendelkezésre álló processzort használja a lekérdezések végrehajtására. Bár ez nagy lekérdezéseknél előnyös, befolyásolhatja a teljesítményt és az egyidejűséget. Jobb megközelítés, ha korlátozzuk a párhuzamosságot a processzor foglalatában lévő fizikai magok számával. Például a MAXDOP értékét 4-re kell állítani egy SQL kiszolgálón, két fizikai processzor foglalattal, mindegyik négy maggal, függetlenül a hipermenetes funkciótól. A MAXDOP nem tudja meghatározni, hogy melyik processzort használja. Inkább korlátozza az egyetlen lekérdezéshez használható processzorok maximális számát.
Az egyidejűség költségküszöbe
Ennek az opciónak az alapértelmezett értéke 5. A lekérdezés-optimalizáló a költségküszöb segítségével határozza meg, hogy van-e értelme párhuzamos tervek létrehozására a lekérdezésekhez. Az 5 egy nagyon alacsony érték, amely csak tiszta OLTP alkalmazásokhoz alkalmas (ide tartozik egyébként a DatAdvantage is).
Nem OLTP rendszerek esetén azt javasoljuk, hogy először állítson 50 körüli értéket, és szükség szerint állítsa be. Egy alkalmazáson belül mindenképpen módosítania kell a kritikus lekérdezések értékét.
Egyéb fontos beállítások
Azonnali fájl inicializálás
Ha megadja az SQL Server számára a Windows „Kötetfenntartási feladatok végrehajtása” jogosultságát, akkor jobb teljesítményt fog elérni az adatfájlok növelésekor.
Általában a Windows sok nullát ír, amikor a felhasználónak helyre van szüksége. 1MB-os fájl létrehozása esetén a Windows 1MB nullát ír a lemezre a fájl inicializálásához. Ha az SQL Server rendelkezik a „Kötet karbantartási feladatok végrehajtása” jogosultsággal, a Windows felszólítja a szükséges helyet elfoglaltként megjelölni, és azonnal visszaadja az SQL Server-nek. Ez lehetővé teszi a fájlok gyorsabb nagyítását.
Biztonsági mentés tömörítése
Az SQL Server 2008r2 verziótól kezdve a biztonsági másolat tömörítését egy jelölőnégyzet segítségével lehet aktiválni.
Ez azt jelenti, hogy a biztonsági másolatok kevesebb tárhelyet igényelnek, kevesebb időt vesznek igénybe, és még gyorsabbak a visszaállításuk. Feltétlenül használja ezt a beállítást.
Dedikált távoli rendszergazdai kapcsolat
Csak akkor van szüksége erre a beállításra, ha valami nem stimmel az SQL Server szolgáltatással.
Ha egy dedikált rendszergazdai kapcsolaton (DAC) keresztül ér el, az SQL Server dedikált kapcsolatot, CPU-ütemezőt és memóriát biztosít. Az SQL Server példány távoli hibaelhárítása, amely folyamatosan 100% -os CPU-használat mellett van, sokkal könnyebb, ha dedikált erőforrásokkal rendelkezik! A távoli DAC használatához fizikailag a konzolon keresztül vagy távolról RDP-n keresztül kell csatlakoznia. Ezt a beállítást is feltétlenül használja. Miután aktiválta, újra elfelejtheti!
Következtetés
Az SQL Server biztosítja a termelési adatbázis-alkalmazások támogatásához szükséges teljesítményt és méretezhetőséget, feltéve, hogy a legjobb gyakorlatokat követik.
Következő blogbejegyzésünkben megvizsgáljuk az SQL Server bevált módszereit virtualizált környezetekben.