Základy monitorovania výkonu a riešenia problémov v SQL Serveri

V tomto článku sa zameriame na populárne nástroje, dotazy a skripty T-SQL na zisťovanie a riešenie rôznych možných problémov s výkonom servera SQL. Tento článok vám pomôže zistiť, kedy má váš server SQL nedostatočné zdroje (pamäť, CPU, IOP diskov), nájsť zámky a zistiť pomalé dotazy. Pozrime sa, aké sú vstavané nástroje a bezplatné skripty a pomôcky tretích strán na analýzu stavu servera Microsoft SQL Server..

obsah:

  • Diagnostické nástroje servera SQL
  • Zisťovanie a riešenie problémov s výkonom servera SQL

Diagnostické nástroje servera SQL

Ak ste problém správne diagnostikovali, polovica práce už bola vykonaná. Zvážte, aké nástroje bežne používa správca systému na diagnostiku rôznych problémov na serveri SQL Server:

  • T-SQL - Najvýkonnejší, najjednoduchší a nevyhnutný nástroj na riešenie problémov a analýzu výkonu servera SQL. Takmer všetky ostatné nástroje pre prácu s SQL Serverom používajú T-SQL. S T-SQL nemôžete robiť nič.
  • SQL Server Management Studio - Bez SSMS je takmer nemožné pracovať so serverom SQL Server. Pomocou SSMS môžete sledovať Monitor aktivity, analyzovať plán dotazov, prezerať parametre servera alebo databázy a mnoho ďalších vecí.
  • Protokoly chýb SQL Server a Windows - Ak sa niečo pokazí, protokol o chybách je prvým miestom, ktoré vyzerá správca systému. Protokol chýb servera SQL Server je možné prezerať prostredníctvom SSMS. Denníky systému Windows si môžete prezerať pomocou modulu snap-in eventvwr.msc.
  • Windows Resource Monitor - resmon.exe je nepostrádateľným nástrojom systému Windows na rýchle posúdenie stavu serverových prostriedkov. Využitie pamäte RAM a procesora je možné zobraziť prostredníctvom Správcu úloh, ale podrobné použitie siete a pevného disku je možné zobraziť iba prostredníctvom príkazu resmon a perfmon..
  • Monitor systému Windows (Monitor výkonu) - Perfmon.exe je hlavným nástrojom na monitorovanie systému Windows, obsahuje celý rad „počítadiel“, systémových metrík a aplikácií, vrátane servera SQL Server. Počítadlá perfmonov sa zvyčajne spracovávajú pomocou iných monitorovacích systémov, napríklad Zabbix, pretože v perfmone nie je vhodné uchovávať a prezerať údaje po uplynutý čas..
  • Aplikácie tretích strán - Existuje mnoho platených a bezplatných monitorovacích aplikácií SQL Server. Napríklad jedna z bezplatných aplikácií je dbForge Monitor od spoločnosti Devart. Aplikácia je nainštalovaná ako doplnok k SSMS a umožňuje vám zobraziť veľmi pohodlný informačný panel na zobrazenie aktuálneho stavu vášho servera SQL (informácie o využití pamäte, CPU, zaťažení, zámkoch, procesoch, informácie o zálohách, „ťažké dotazy SQL“, výkonnosť diskových subsystémov atď.) )..
  • Skripty Brentozar - Toto je populárne riešenie na diagnostiku nastavení a stavu servera SQL. Brentozar má veľa skriptov pre rôzne úlohy, ale pre diagnostiku nás zaujíma „sp_blitz“. Môžete si ho zadarmo stiahnuť z oficiálnej webovej stránky https://www.brentozar.com/blitz/. Spustením sp_Blitz.sql nainštalujte potrebné postupy a vykonajte ich. exec sp_blitz na diagnostiku. Tento nástroj je bezplatný a udržiavaný komunitou SQL Server. Sp_blitz identifikuje všetky populárne problémy so serverom a poradí, ako ich vyriešiť..
  • Sady skriptov T-SQL - je vhodné mať k dispozícii zbierky rôznych dotazov T-SQL na diagnostiku servera SQL, pretože nie vždy je čas na písanie vlastných dopytov, je lepšie sa vopred vyzbrojiť. Nasledujú odkazy na užitočné dotazy T-SQL / PowerShell, ktoré často používam pri diagnostike a ladení MS SQL:
    • https://github.com/SQLadmin/AwesomeSQLServer - množina otázok na monitorovanie CPU / RAM / Disk IO a ďalších parametrov.
    • https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - veľa užitočných skriptov a životných hackov
    • https://github.com/ktaranov/sqlserver-kit - Skripty a užitočné informácie. Mnoho príkladov práce s SQL Serverom cez PowerShell

Zisťovanie a riešenie problémov s výkonom servera SQL

Najbežnejším problémom, ktorému čelí správca systému pracujúci so serverom SQL, sú sťažnosti používateľov týkajúce sa výkonu dotazov a samotného servera: „spomaľuje“, „dotaz trvá dlho“ atď..

Najprv sa musíte uistiť, že server má dostatok zdrojov. Pozrime sa, ako rýchlo analyzovať využitie pamäte, procesora, diskov a zámkov na serveri SQL Server..

Analýza využitia pamäte SQL servera

Najprv musíte zistiť, koľko pamäte je k dispozícii na serveri SQL Server. Ak to chcete urobiť, spustite SSMS (SQL Server Management Studio), choďte na server a choďte na vlastnosti servera (RMB podľa názvu servera v prehliadači objektov).

Dostupná kapacita pamäte RAM vám nič nepovie. Toto číslo musíte porovnať s pamäťou použitou v Správcovi úloh a samotnom stroji SQL Server pomocou DMV.

V Správcovi úloh na karte Podrobnosti vyhľadajte sqlservr.exe a pozrite sa, koľko pamäte RAM tento proces používa.

  • Ak má napríklad server 128 GB pamäte RAM a proces sqlservr.exe používa 60 GB a server SQL nemá limity pamäte RAM, máte dostatok pamäte RAM.
  • Ak SQL Server používa 80-90% nastavenej alebo maximálnej pamäte RAM, musíte sledovať DMV. Nezabúdajte, že sqlservr.exe nebude môcť využívať všetky pamäte RAM. Ak má server 128 GB, potom sqlservr.exe môže používať iba 80 - 90% (100 - 110 GB), pretože zvyšok pamäte je vyhradený pre operačný systém..

Majte na pamäti, že proces SQL Server nevysiela RAM späť do systému. Napríklad váš server SQL Server zvyčajne používa 20 GB pamäte, ale s mesačným výkazom zvyšuje spotrebu na 100 GB a aj keď je výpočet správy dokončený a server pracuje v predchádzajúcom režime, proces servera SQL Server bude stále používať 100 GB, kým sa služba nereštartuje..

Aj keď ste si istí, že server má dostatok pamäte RAM, nebude zbytočné vedieť presne množstvo spotrebovanej pamäte RAM..

Môžete zistiť skutočné využitie pamäte RAM Dynamické pohľady na správu. DMV sú administratívni diváci. S DMV môžete diagnostikovať takmer akýkoľvek problém na serveri SQL Server.

Pozrime sa na sys.dm_os_sys_memory, pre zjednodušenie používame túto požiadavku:

SELECT total_physical_memory_kb / 1024 AS [Total Physical Memory], available_physical_memory_kb / 1024 AS [Available fyzické pamäte], total_page_file_kb / 1024 AS [Total page file (MB)], available_page_file_kb / 1024 AS [dostupný súbor stránky (MB)]], 100 - ( 100 * obsadenie (k dispozícii_physical_memory_kb AS DECIMAL (18, 3)) / obsadenie (total_physical_memory_kb AS DECIMAL (18, 3))) AS as 'Percentage Used', system_memory_state_desc AS [State Memory] FROM sys.dm_os_sys_memory;

Zvážte každý výstupný parameter:

  1. [Celková fyzická pamäť] - množstvo pamäte RAM dostupnej v operačnom systéme. Na niektorých serveroch sa môže zobraziť o niečo viac, ako je skutočne nainštalované.
  2. [Dostupná fyzická pamäť] - množstvo pamäte RAM dostupnej pre server SQL Server, okrem už zachyteného servera SQL.
  3. [Celkový súbor stránky (MB)] - Objem „záväzný limit“. Commit Limit = RAM + všetky odkladacie súbory. To znamená, že ak máte na serveri 32 GB RAM a 16 GB stránkovacieho súboru, limit potvrdenia bude 48 GB.
  4. [Dostupný súbor stránky (MB)] - Veľkosť stránkovacieho súboru.
  5. Použité percento - percento použitej pamäte RAM. V samotnom sys.dm_os_sys_memory taký parameter neexistuje, ale vypočíta sa pomocou vzorca k dispozícii_physical_memory_kb / total_physical_memory_kb
  6. [Stav pamäte] - stav pamäte RAM. Pole system_memory_state_desc obsahuje stav spotreby RAM vo forme textu. Hodnota tohto poľa sa posudzuje na základe ostatných dvoch: system_low_memory_signal_state a system_high_memory_signal_state. Môžete ich vybrať priamo, ak potrebujete booleovský / bitový formát údajov. Všetky polia sys.dm_os_sys_memory nájdete v dokumentácii https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact. -sql? view = sql-server-ver15

Všetky tieto údaje sú užitočné, ak chcete presne určiť, koľko váš server SQL spotrebuje RAM. Toto sa najčastejšie používa, ak existuje podozrenie, že pre inštanciu je pridelených príliš veľa pamäte RAM..

Ak sa chcete uistiť, že server má dostatok pamäte RAM, môžete sa pozrieť iba na polia system_low_memory_signal_state, system_high_memory_signal_state a system_memory_state_desc. Ak system_low_memory_signal_state = 1, server zjavne nemá dostatok pamäte RAM.

Využitie CPU na serveri SQL Server

Zaťaženie procesora je ľahšie určiteľné, ako je to možné v Správcovi úloh. Ak chcete zistiť aktuálne zaťaženie procesora, nájdite proces sqlservr.exe v Správcovi úloh

Ak chcete zistiť záťaž za minulý čas, môžete použiť dotaz:

Nezabudnite zmeniť @lastNMin na číslo, ktoré potrebujete, v priebehu niekoľkých minút.
DECLARE @ts BIGINT; DECLARE @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (SELECT cpu_ticks / (cpu_ticks / ms_ticks) OD sys.dm_os_sys_info); SELECT TOP (@lastNmin) Dateadd (ms, -1 * (@ts - [timestamp]), Getdate ()) AS [EventTime], sqlprocessutilization AS [SQL Server Utilization], 100 - systemidle - sqlprocessutilization AS [Other Process 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], [timestamp] FROM ( VYBERTE [timestamp], CONVERT (XML, record) AS [record] ZO sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'A zaznamenajte PODOBNÉ' %% ') AS x) AS Y OBJEDNÁVKA ZÁZNAMU record_id DESC;

Výsledkom je štatistika používania procesora za minútu.

Analýza zaťaženia disku SQL Server

Pozrime sa na načítanie diskov v operačnom systéme. Na tento účel spustite resmon.exe.

Potrebujeme kartu disk. V časti Aktivita disku zobrazujú sa prístupné súbory a ich rýchlosť čítania / zápisu v aktuálnom okamihu. Filtrujte túto sekciu podľa súčtu (kliknite na Celkom). Úplne na vrchole budú súbory, ktoré momentálne najviac využívajú jednotku. V prípade servera SQL Server to môže byť užitočné pri určovaní, ktorá databáza práve načítava disk najviac..

V časti Úložisko sa zobrazujú všetky jednotky v systéme. V tejto sekcii potrebujeme 2 parametre - Aktívny čas a Fronta disku. Aktívny čas ako percento zobrazuje zaťaženie disku, to znamená, že ak vidíte C: \ Aktívny čas na disku rovný 90, znamená to, že prostriedok na čítanie a zápis na disku sa v súčasnosti používa na 90%. Stĺpec Diskový front zobrazuje frontu prístupu na disk a ak hodnota frontu nie je nula, disk je načítaný na 100% a nedokáže zvládnuť zaťaženie. Ak je čas aktívneho času takmer 100, potom sa disk používa takmer na hranici svojich rýchlostí.

Zobraziť zámky na serveri SQL Server

Potom, čo sme sa ubezpečili, že server má dostatok zdrojov, môžeme pokračovať v prehliadaní zámkov.

Zámky si môžete prezerať prostredníctvom Sledovania aktivity v SSMS, ale použijeme T-SQL, pretože táto voľba je pohodlnejšia a vizuálnejšia. Spĺňame požiadavku:

NASTAVTE NOCOUNT NA VYBERTE SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR (10), "), CHAR (13),") AKO ŠARŽE DO #T OD sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text (R. SQL_HANDLE) T GO S BLOCKERS (SPID, BLOCKED, LEVEL, BATCH) AS (SELECT SPID, BLOCKED, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR)))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AKO ÚROVEŇ, ŠARŽE OD #TR KDE (BLOKOVANÉ = 0 ALEBO BLOKOVANÉ = SPIDÉ) A EXISTUJÚ (VYBERTE * OD #T R2, KTORÉ R2.BLOKOVÉ = R.SPIDOVÉ A R2.BLOKOVÉ R2.SPIDOVÉ) ÚNIA VŠETKY ZVOLIŤ R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + PRAVÝ (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS ÚROVEŇ, R.BATCH OD #T AS R VNÚTORNÉ ZAPOJENÉ BLOCKERS NA R. BLOCKED = BLOCKERS.SPID KDE R. BLOCKED> 0 A R. BLOCKED R.SPID) VYBERTE N "+ REPLIKÁT (N '|', LEN (ÚROVEŇ) / 4 - 1) + PRÍPAD, KDE ( LEN (LEVEL) / 4 - 1) = 0 THEN 'HEAD -' ELSE '| ------' END + CAST (SPID AS NVARCHAR (10)) + N "+ ŠARŽ AKO BLOCKING_TREE Z BLOCKERS OBJEDNÁVKA ÚROVEŇ ASC GO DROP TABLE #T GO

Tento dotaz vráti zoznam zámkov vo forme stromu. Toto je výhodné pri prevádzke, pretože zvyčajne, ak dôjde k jednému zámku, to vyvolá ďalšie. Podobne v nástroji Activity Monitor alebo na výstupe sp_who2 môžete vidieť pole „Blocked By“.

Ak sa požiadavka nevrátila, neexistujú žiadne zámky.

Ak žiadosť vrátila niektoré údaje, musíte reťaz analyzovať.

HEAD znamená, že táto požiadavka je príčinou všetkých ostatných zámkov pod stromom. 64 je identifikátor procesu (SPID). Potom je napísané telo žiadosti, ktoré spôsobilo zámok. Ak máte dostatok serverových prostriedkov, potom je pravdepodobné, že ide o samotnú žiadosť a o vzájomné odvolanie sa na niektoré objekty. Aby ste boli presnejší, musíte analyzovať konkrétnu požiadavku, ktorá spôsobila zámok.

Pravidlá servera SQL

Aj keď pre vás všetko funguje dobre a neexistujú žiadne sťažnosti, v skutočnosti môže nastať veľa problémov, ktoré sa objavia neskôr. SQL Server má na tento účel politiky..

Politika na serveri SQL Server zhruba kontroluje dodržiavanie danej hodnoty s pravidlom. Pomocou politík môžete napríklad zaistiť, aby bolo automatické zmenšenie vypnuté vo všetkých databázach na serveri. Zoberme si príklad importu a vykonávania politiky

V SSMS sa pripojte k serveru, na ktorom chcete vykonávať politiky (Správa -> Správa zásad).

Importujte súbor Auto Shrink.xml databázy. hit ohodnotiť

V inštancii uzlov 1, test1 a test2 existujú dve databázy. Autoshrink je na test2 povolený. Pozrime sa na podrobnosti.

Politika určila zahrnutý parameter AutoShrink, v popise popisov pravidiel sa zvyčajne píše. V tomto prípade sa uvádza vysvetlenie, prečo je automatické zmenšovanie lepšie..

Politiky sa môžu vykonávať buď podľa plánu alebo na požiadanie (jednorazovo). Výsledky vykonávania politiky sú uvedené v protokole politík..

Pri inštalácii SQL Server musíte vybrať iba použité komponenty DBMS a zadať nastavenia v súlade s hardvérovou konfiguráciou servera. Vždy sa uistite, že server má dostatok zdrojov a že na serveri nie sú zámky

Najúčinnejším diagnostickým nástrojom servera SQL sú T-SQL a DMV. Odporúča sa tiež zostaviť nepretržité monitorovanie servera SQL Server a jeho infraštruktúry, aby sa zistili všetky možné problémy..

Kategórie