Ebben a cikkben a népszerű eszközöket, a T-SQL lekérdezéseket és a szkripteket vizsgáljuk meg az SQL Server teljesítményével kapcsolatos különböző lehetséges problémák felismerésére és megoldására. Ez a cikk segít kitalálni, mikor van az SQL Server elégtelen erőforrásokkal (memória, CPU, lemezek IOP-ja), megtalálni a lezárásokat és felismerni a lassú lekérdezéseket. Nézzük meg, hogy melyek a Microsoft SQL Server állapotának elemzésére szolgáló beépített eszközök és ingyenes gyártói parancsfájlok és segédprogramok.
Tartalom:
- SQL Server diagnosztikai eszközök
- Az SQL Server teljesítményével kapcsolatos problémák észlelése és megoldása
SQL Server diagnosztikai eszközök
Ha helyesen diagnosztizálta a problémát, akkor a munka fele már elkészült. Fontolja meg, hogy az eszközöket a rendszergazda általában használja az SQL Server különféle problémáinak diagnosztizálására:
- T-SQL - A legerősebb, egyszerű és nélkülözhetetlen eszköz az SQL Server teljesítményének hibaelhárításához és elemzéséhez. Az SQL Server használatához szinte minden egyéb eszköz a T-SQL-t használja. Semmit nem tehet a T-SQL.
- SQL Server Management Studio - SSMS nélkül szinte lehetetlen az SQL Serverrel dolgozni. Az SSMS használatával megnézheti a tevékenységfigyelőt, elemezheti a lekérdezési tervet, megnézheti a szerver vagy az adatbázis paramétereit és még sok más dolgot.
- SQL Server és Windows hibanaplók - Ha valami rosszul fordul elő, akkor a rendszergazda először a hibanaplót keresi. Az SQL Server hibanaplója SSMS-en keresztül tekinthető meg. A Windows-naplók az eventvwr.msc beépülő modulon keresztül tekinthetők meg.
- Windows Resource Monitor - A resmon.exe nélkülözhetetlen Windows eszköz a kiszolgálói erőforrások állapotának gyors felméréséhez. A RAM és a processzor felhasználása a Task Manager segítségével tekinthető meg, de a hálózat és a merevlemez részletes felhasználása csak resmon és perfmon segítségével tekinthető meg..
- Windows rendszermonitor (Performance Monitor) - A Perfmon.exe a Windows figyelésének fő eszköze, számos „számlálót” tartalmaz, mind a rendszer mutatóit, mind az alkalmazásokat, beleértve az SQL Server alkalmazást is. Általában a perfmon számlálókat más megfigyelő rendszerek, például a Zabbix alkalmazásával dolgozzák fel, mivel a perfmonban kényelmetlen az adatok tárolása és megtekintése az eltelt idő alatt..
- Harmadik féltől származó alkalmazások - Számos fizetett és ingyenes SQL Server figyelő alkalmazás létezik. Például az egyik ingyenes alkalmazás a dbForge Monitor a Devart cégtől Az alkalmazás az SSMS kiegészítéseként van telepítve, és lehetővé teszi egy nagyon kényelmes műszerfal megjelenítését az SQL Server aktuális állapotának megjelenítéséhez (információk a memóriahasználatról, a CPU-ról, a betöltésekről, a zárolásokról, a folyamatokról, a biztonsági mentésekről szóló információk, „nehéz SQL lekérdezések”, a lemez alrendszer teljesítményének stb.) )..
- Szkriptek Brentozar - Ez egy népszerű megoldás az SQL Server beállításainak és állapotának diagnosztizálására. A Brentozar-nak számos szkripte van a különféle feladatokhoz, de a diagnosztika szempontjából az „sp_blitz” érdekli. Ingyenesen letöltheti a hivatalos weboldalon: https://www.brentozar.com/blitz/. Futtassa az sp_Blitz.sql fájlt a szükséges eljárások telepítéséhez és végrehajtásához. exec sp_blitz a diagnózishoz. Ez az eszköz ingyenes, és az SQL Server közösség karbantartja. Az Sp_blitz azonosítja az összes népszerű problémát a szerverrel, és tanácsot ad ezek megoldására..
- T-SQL szkriptkészletek - kényelmes, ha kéznél vannak különféle T-SQL lekérdezések gyűjteményei az SQL Server diagnosztikához, mivel nem mindig van idő saját lekérdezések megírására, jobb, ha előre élesíti magát. Az alábbiakban hivatkozunk a hasznos T-SQL / PowerShell lekérdezésekre, amelyeket gyakran használok az MS SQL diagnosztizálásakor és hangolásakor:
- https://github.com/SQLadmin/AwesomeSQLServer - a CPU / RAM / Disk IO és más paraméterek figyelésére szolgáló lekérdezések.
- https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - sok hasznos szkript és életcsapda
- https://github.com/ktaranov/sqlserver-kit - Szkriptek és hasznos információk. Sok példa az SQL Server használatára a PowerShell segítségével
Az SQL Server teljesítményével kapcsolatos problémák észlelése és megoldása
Az SQL Serverrel működő rendszergazdák által a legelterjedtebb problémák a lekérdezés teljesítményével és magával a kiszolgálóval kapcsolatos panaszok: „lelassul”, „a lekérdezés hosszú időt vesz igénybe” és így tovább.
Először is ellenőriznie kell, hogy a kiszolgálónak elegendő erőforrása van. Lássuk, hogyan lehet gyorsan elemezni a memória, a CPU, a lemezek és a zárak használatát az SQL Serverben..
SQL Server RAM használat elemzése
Először meg kell határoznia, mennyi memória áll rendelkezésre az SQL Serverben. Ehhez indítsa el az SSMS-t (SQL Server Management Studio), lépjen a kiszolgálóra, és keresse meg a kiszolgáló tulajdonságait (RMB kiszolgálónév szerint az Objektum-böngészőben).
A rendelkezésre álló RAM kapacitás önmagában semmit sem fog mondani. Össze kell hasonlítania ezt a számot a Feladatkezelőben használt memóriával és magának az SQL Server motornak a DMV használatával.
A Feladatkezelő Részletek lapon keresse meg az sqlservr.exe fájlt, és nézze meg, mennyi RAM-ot használ ez a folyamat.
- Ha például a kiszolgáló 128 GB RAM-mal rendelkezik, és az sqlservr.exe folyamat 60 GB-ot használ, és az SQL Server nem rendelkezik RAM korlátozással, akkor elegendő RAM van.
- Ha az SQL Server a beállított vagy a maximális RAM 80-90% -át használja, akkor figyelnie kell a DMV-t. Ne feledje, hogy az sqlservr.exe nem fogja tudni használni az összes RAM-ot. Ha a szerver 128 GB-val rendelkezik, akkor az sqlservr.exe csak 80–90% -ot (100–110 GB) használhat, mivel a memória fennmaradó része az operációs rendszer számára van fenntartva.
Ne feledje, hogy az SQL Server folyamata nem küld vissza RAM-ot a rendszerhez. Például az SQL Server általában 20 GB memóriát használ, de havi jelentéssel 100 GB-ra növeli a felhasználást, és még akkor is, ha a jelentés kiszámítása befejeződik, és a kiszolgáló az előző módban működik, az SQL Server folyamat továbbra is 100 GB-ot fog használni, amíg a szolgáltatás újraindul..
Még akkor sem, ha biztos benne, hogy a kiszolgálón elegendő RAM van, nem lesz szükség pontosan megismerni az elfogyasztott RAM mennyiségét..
Megtalálhatja a RAM valódi felhasználását a Dinamikus kezelési nézetek. A DMV-k adminisztratív nézők. A DMV segítségével szinte bármilyen problémát diagnosztizálhat az SQL Server alkalmazásban.
Lásd: sys.dm_os_sys_memory, a kényelem érdekében a kérést használjuk:
SELECT total_physical_memory_kb / 1024 AS [Teljes fizikai memória], available_physical_memory_kb / 1024 AS [Rendelkezésre álló fizikai memória], total_page_file_kb / 1024 AS [Összes oldal fájl (MB)], available_page_file_kb / 1024 AS [Elérhető oldal fájl (MB)], 100 - ( 100 * leadott (elérhető_fizikai_memória_kb AS DECIMAL (18, 3)) / leadott (összesen_fizikai_memory_kb AS DECIMAL (18, 3))) AS „Használt százalék”, system_memory_state_desc AS [Memóriaállapot] FROM sys.dm_os_sys_memory;
Vegye figyelembe az egyes kimeneti paramétereket:
- [Teljes fizikai memória] - az operációs rendszerben rendelkezésre álló RAM mennyiség. Néhány kiszolgálón ez valószínűleg egy kicsit többet jelenít meg, mint ahogyan valóban telepítve van.
- [Rendelkezésre álló fizikai memória] - az SQL Server számára rendelkezésre álló RAM mennyiség, a már elfoglalt SQL Server kivételével.
- [Összes oldalfájl (MB)] - Kötet „kötelezettségvállalási határ”. Kötelezettségvállalási határ = RAM + minden cserefájl. Vagyis ha 32 GB RAM-mal és 16 GB lapozófájllal rendelkezik a szerveren, akkor az átadási határ 48 GB.
- [Elérhető oldalfájl (MB)] - lapozó fájl mérete.
- Használt százalék - a felhasznált RAM százaléka. Magában a sys.dm_os_sys_memory-ban nincs ilyen paraméter, de kiszámítása az elérhető_physical_memory_kb / total_physical_memory_kb képlettel történik.
- [Memória állapota] - RAM állapot. A system_memory_state_desc mező szöveges formában tartalmazza a RAM-fogyasztás állapotát. A mező értékét a másik kettő alapján vesszük figyelembe: system_low_memory_signal_state és system_high_memory_signal_state. Közvetlenül kiválaszthatja őket, ha logikai / bites adatformátumra van szüksége. Az összes sys.dm_os_sys_memory mező megtalálható a dokumentációban: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact -sql? view = sql-server-ver15
Ezek az adatok akkor hasznosak, ha pontosan meg akarja határozni, hogy az SQL Server mekkora memóriát fogyaszt. Ezt leggyakrabban akkor használják, ha gyanú merül fel, hogy túl sok RAM-ot allokáltak a példányhoz..
Ha meg kell győződnie arról, hogy a kiszolgálón elegendő RAM van-e, akkor csak a system_low_memory_signal_state, system_high_memory_signal_state és system_memory_state_desc mezőket tekintheti meg. Ha system_low_memory_signal_state = 1, akkor a kiszolgálónak nyilvánvalóan nincs elég RAM-ja.
CPU-használat az SQL Server-ben
A processzor terhelését könnyebb meghatározni, mivel ezt meg lehet tenni a Feladatkezelőben. A processzor aktuális terhelésének megismeréséhez keresse meg az sqlservr.exe folyamatot a Feladatkezelőben
Ha meg akarja tudni az elmúlt idő terhelését, használhatja a következő lekérdezést:
Ne felejtsd el megváltoztatni a @lastNMin számot, amire perceken belül szüksége van.DECLARE @ts BIGINT; DECLARE @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (KIVÁLASZT cpu_ticks / (cpu_ticks / ms_ticks) FELT sys.m_os_sys_info-tól); SELECT TOP (@lastNmin) Dateadd (ms, -1 * (@ts - [időbélyeg]), Getdate ()) AS [EventTime], sqlprocessutilization AS [SQL Server Utilization], 100 - systemidle - sqlprocessutilization AS [Más folyamat CPU_Utilization] , systemidle AS [System Idle] FROM (SELECT record.value ('(./ Record / @ id) [1]', 'int') AS record_id, record.value ('(./ Record / SchedulerMonitorEvent / SystemHealth / SystemIdle ) [1], 'int') AS [SystemIdle], record.value ('(./ Record / SchedulerMonitorEvent / SystemHealth / ProcessUtilization) [1]', 'int') AS [SQLProcessUtilization], [időbélyeg] FROM ( SELECT [időbélyeg], CONVERT (XML, record) AS [record] FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'and rekord LIKE' %% ') AS x) AS y ORDER by record_id DESC;
Ennek eredményeként percenként statisztikákat kapunk a processzor használatáról.
SQL Server lemezterhelés-elemzés
Nézzük meg a lemezek betöltése az operációs rendszerben. Ehhez futtassa a resmon.exe fájlt. 
Szüksége van egy fülre tárcsa. Szakaszban Lemez tevékenység Megjelennek a hozzáférés alatt álló fájlok, és az olvasási / írási sebességük az aktuális pillanatban. Szűrje ezt a részt a teljes érték alapján (kattintson az Összes elemre). A legtetején lesznek azok a fájlok, amelyek jelenleg a legtöbbet használják a meghajtón. Az SQL Server esetében ez hasznos lehet annak meghatározásához, hogy melyik adatbázis tölti be a legjobban a lemezt..
A Tárolás szakasz megjeleníti a rendszer összes meghajtóját. Ebben a szakaszban 2 paraméterre van szükségünk - Active Time és Disk Queue. Az aktív idő százalékban megjeleníti a lemez terhelését, azaz ha a C: \ aktív idő jelenik meg a lemezen 90-nél, akkor ez azt jelenti, hogy a lemez olvasási / írási erőforrása jelenleg 90% -on van felhasználva. A Lemez sor oszlopban megjelenik a lemezhozzáférési sor, és ha a sor értéke nem nulla, akkor a lemez 100% -ban betöltve, és nem tudja kezelni a betöltést. Továbbá, ha az aktív idő közel 100, akkor a lemezt szinte a képességeinek sebességén korlátozzák.
Zárak megtekintése az SQL Serverben
Miután megbizonyosodott arról, hogy a kiszolgálónak elegendő erőforrása van, folytathatjuk a zárak megtekintését.
A zárak az Activity Monitoron keresztül megtekinthetők az SSMS-ben, de a T-SQL-t fogjuk használni, mivel ez az opció sokkal kényelmesebb és vizuálisabb. Teljesítjük a kérést:
NOCOUNT BEÁLLÍTÁSA A HÁLÓ SZÖVEG, BLOKKOLT, CSERÉJE (Kicserélés (T.TEXT, CHAR (10), "), CHAR (13),") TÖRTÉNT TÖRTÉNT A TÖBB SZÖVETÉSBEN sys.sysprocesses-ekből SQL_HANDLE) T KAPCSOLATOS BLOKKOKKAL (SPID, BLOCK, LEVEL, TÉTEL) AS (SELECT SPID, BLOCKed, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR)))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) SZINTEN, TÖRLÉSE #TR-Tól, HOGY (BLOCK = 0 VAGY BLOCKED = SPID) ÉS MEGLÉVŐK (VÁLASZT * * #T R2-Tól, HOGY R2.BLOCKED = R.SPID ÉS R2.BLOCKED R2.SPID) UNIÓ MINDEN KIVÁLASZT R.SPID, R. BLOCKED, CAST (BLOCKERS.LEVEL + JOBB (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS SZINT, R. TÖRTÉNŐ #T AS-től) R BELSŐ CSATLAKOZÓK AZ R. BLOCKED = BLOCKERS.SPID, Ahol R. BLOCKED> 0 ÉS R. BLOCKED R.SPID) N "+ VÁLASZTÁS + NEM VÁLASZT (N '|', LEN (LEVEL) / 4-1) + ESET, MIKEN ( LEN (SZINT) / 4 - 1) = 0, Akkor 'FEJ -' ELSE '| ------' VÉG + HASZNÁLAT (SPID AS NVARCHAR (10)) + N "+ TÉTELEK MEGKÖTÉS A BLOCKING_TREE SZABÁLYOKBÓL RENDELTETTEN GO csepegés Táblázat # GO Ez a lekérdezés faj formájában ad vissza zárak listáját. Ez kényelmesen működik, mivel általában, ha egyik zár bekövetkezik, akkor másokat is provokál. Hasonlóképpen, az Activity Monitorban vagy az sp_who2 kimenetében láthatja a „Blocked By” mezőt.
Ha a kérelem semmit nem adott vissza, akkor nincsenek zárak.
Ha a kérés bizonyos adatokat adott vissza, akkor elemeznie kell a láncot.
A FEJ azt jelenti, hogy ez a kérés az összes többi, a fa alatti zárolás oka. A 64 a folyamat-azonosító (SPID). Ezt követően a kérés törzsét megírják, ami lezárta. Ha elegendő kiszolgálói erőforrás van, akkor valószínűleg a kérdés maga a kérelemben és egyes objektumok kölcsönös fellebbezésében rejlik. A pontosabb meghatározás érdekében elemeznie kell a zárolást okozó konkrét kérelmet.
SQL Server házirendek
Még akkor is, ha minden jól működik, és nincs panasz, valójában sok probléma merül fel, amelyek később felmerülnek. Az SQL Server ehhez irányelveket tartalmaz..
Az SQL Server házirendje durván szólva egy szabály ellenőrzése az adott értéknek való megfelelés szempontjából. Például házirendek segítségével ellenőrizheti, hogy az Auto Shrink ki van-e kapcsolva a kiszolgáló összes adatbázisán. Vegyünk egy példát az importálásra és a házirend végrehajtására
Az SSMS rendszerben csatlakozzon annak a kiszolgálónak, amelyen végrehajtani kívánja az irányelveket (Kezelés -> Házirendek kezelése szakasz).
Importálja az Adatbázis automatikus Shrink.xml fájlt. hit értékelje
Két adatbázis létezik a csomópont1, a teszt1 és a teszt2 példányán. Az autoshrink engedélyezve van a test2-en. Nézzük meg a részleteket. 
A házirend meghatározta a benne szereplő AutoShrink paramétert, a leírásban általában a szabályok magyarázata van. Ebben az esetben magyarázatot kap, miért jobb az automatikus zsugorodás..
A házirendek végrehajthatók ütemterv szerint vagy igény szerint (egyszeri). A házirend-végrehajtás eredményei megtalálhatók a házirend-naplóban..
Az SQL Server telepítésekor csak a használt DBMS összetevőket kell kiválasztania, és a kiszolgáló hardverkonfigurációjának megfelelően meg kell határoznia a beállításokat. Mindig ellenőrizze, hogy a kiszolgálón elegendő erőforrás van-e, és hogy nincsenek-e zárolások a kiszolgálón
A legerősebb SQL Server diagnosztikai eszköz a T-SQL és a DMV. Ajánlott továbbá az SQL Server és annak infrastruktúrájának éjjel-nappal történő megfigyelése az összes lehetséges probléma észlelése érdekében..
