Základy monitorování výkonu a odstraňování problémů na serveru SQL

V tomto článku se podíváme na populární nástroje, dotazy a skripty T-SQL pro detekci a řešení různých možných problémů s výkonem serveru SQL. Tento článek vám pomůže zjistit, kdy má váš SQL Server nedostatečné prostředky (paměť, CPU, IOP disků), najít zámky a detekovat pomalé dotazy. Podívejme se, jaké jsou vestavěné nástroje a bezplatné skripty a nástroje třetích stran pro analýzu stavu serveru Microsoft SQL Server.

Obsah:

  • Diagnostické nástroje serveru SQL
  • Zjišťování a řešení problémů s výkonem serveru SQL

Diagnostické nástroje serveru SQL

Pokud jste problém správně diagnostikovali, polovina práce již byla dokončena. Zvažte, jaké nástroje běžně používá správce systému k diagnostice různých problémů na serveru SQL:

  • T-sql - Nejvýkonnější, nejjednodušší a nepostradatelný nástroj pro odstraňování problémů a analýzu výkonu serveru SQL. Téměř všechny ostatní nástroje pro práci s SQL Serverem používají T-SQL. S T-SQL nemůžete nic dělat.
  • SQL Server Management Studio - Bez SSMS je téměř nemožné pracovat se serverem SQL. Pomocí SSMS se můžete podívat na Monitor aktivity, analyzovat plán dotazů, podívat se na parametry serveru nebo databáze a mnoho dalších věcí.
  • Protokoly chyb SQL Server a Windows - Pokud se něco pokazí, je protokol chyb prvním místem, které vypadá správce systému. Protokol chyb serveru SQL lze zobrazit prostřednictvím SSMS. Protokoly systému Windows lze zobrazit pomocí modulu snap-in eventvwr.msc.
  • Windows Resource Monitor - resmon.exe je nepostradatelným nástrojem Windows pro rychlé vyhodnocení stavu prostředků serveru. Využití paměti RAM a procesoru lze zobrazit prostřednictvím Správce úloh, ale podrobné použití sítě a pevného disku lze zobrazit pouze pomocí resmon a perfmon.
  • Monitor systému Windows (Monitor výkonu) - Perfmon.exe je hlavní nástroj pro monitorování systému Windows, obsahuje řadu „čítačů“, jak systémových metrik, tak aplikací, včetně SQL Serveru. Čítače perfmonů se obvykle zpracovávají pomocí jiných monitorovacích systémů, například Zabbix, protože v perfmonu není vhodné ukládat a prohlížet data po uplynulý čas..
  • Aplikace třetích stran - Existuje mnoho placených a bezplatných aplikací pro monitorování serveru SQL. Například jedna z bezplatných aplikací je dbForge Monitor od společnosti Devart. Tato aplikace je nainstalována jako doplněk k SSMS a umožňuje zobrazit velmi pohodlný dashboard, který zobrazuje aktuální stav vašeho serveru SQL (informace o využití paměti, procesoru, načtení, zámcích, procesech, informací o zálohách, „těžkých dotazech SQL“, výkonu diskových subsystémů atd.) .).
  • Skripty Brentozar - Toto je populární řešení pro diagnostiku nastavení a stavu serveru SQL. Brentozar má mnoho skriptů pro různé úkoly, ale pro diagnostiku nás zajímá „sp_blitz“. Můžete si ji zdarma stáhnout z oficiálních webových stránek https://www.brentozar.com/blitz/. Spusťte program sp_Blitz.sql, abyste nainstalovali potřebné postupy a provedli je. exec sp_blitz pro diagnostiku. Tento nástroj je bezplatný a spravovaný komunitou SQL Server. Sp_blitz identifikuje všechny populární problémy se serverem a poradí, jak je vyřešit..
  • Sady skriptů T-SQL - je vhodné mít po ruce sbírky různých dotazů T-SQL pro diagnostiku serveru SQL, protože není vždy čas psát vlastní dotazy, je lepší se předem vyzbrojit. Níže jsou uvedeny odkazy na užitečné dotazy T-SQL / PowerShell, které často používám při diagnostice a ladění MS SQL:
    • https://github.com/SQLadmin/AwesomeSQLServer - sada dotazů pro sledování CPU / RAM / Disk IO a dalších parametrů.
    • https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - mnoho užitečných skriptů a životních hacků
    • https://github.com/ktaranov/sqlserver-kit - Skripty a užitečné informace. Mnoho příkladů práce s SQL Serverem přes PowerShell

Zjišťování a řešení problémů s výkonem serveru SQL

Nejběžnějším problémem, kterému čelí správce systému pracující s SQL Serverem, jsou stížnosti uživatelů na výkon dotazu a samotný server: „zpomaluje“, „dotaz trvá dlouho“ atd..

Nejprve se musíte ujistit, že server má dostatek prostředků. Uvidíme, jak rychle analyzovat využití paměti, procesoru, disků a zámků na serveru SQL..

Analýza využití paměti SQL serveru RAM

Nejprve musíte zjistit, kolik paměti je k dispozici na serveru SQL. Chcete-li to provést, spusťte SSMS (SQL Server Management Studio), přejděte na server a přejděte na vlastnosti serveru (RMB podle názvu serveru v Prohlížeči objektů).

Dostupná kapacita paměti RAM vám nic neřekne. Musíte porovnat toto číslo s pamětí používanou ve Správci úloh a samotným strojem SQL Server pomocí DMV.

Ve Správci úloh na kartě Podrobnosti najděte sqlservr.exe a podívejte se, kolik paměti RAM tento proces používá.

  • Pokud má server například 128 GB RAM a proces sqlservr.exe používá 60 GB a SQL Server nemá limity RAM, máte dostatek paměti RAM.
  • Pokud SQL Server používá 80-90% nastavené nebo maximální RAM, musíte sledovat DMV. Mějte na paměti, že sqlservr.exe nebude moci používat všechny RAM. Pokud má server 128 GB, pak sqlservr.exe může používat pouze 80-90% (100-110 GB), protože zbývající část paměti je vyhrazena pro operační systém..

Mějte na paměti, že proces serveru SQL neodesílá RAM zpět do systému. Například váš server SQL obvykle používá 20 GB paměti, ale s měsíční zprávou zvyšuje spotřebu na 100 GB ai když je výpočet sestavy dokončen a server pracuje v předchozím režimu, proces serveru SQL bude stále používat 100 GB, dokud nebude služba restartována.

I když jste si jisti, že server má dostatek paměti RAM, nebude zbytečné přesně vědět, kolik paměti RAM bylo spotřebováno..

Můžete zjistit skutečné využití RAM Dynamické pohledy na správu. DMV jsou administrativní diváci. S DMV můžete diagnostikovat téměř jakýkoli problém na serveru SQL.

Podívejme se na sys.dm_os_sys_memory, pro větší pohodlí používáme požadavek:

SELECT total_physical_memory_kb / 1024 AS [Total Physical Memory], available_physical_memory_kb / 1024 AS [Available fyzické paměti], total_page_file_kb / 1024 AS [Total Page File (MB)], available_page_file_kb / 1024 AS [dostupný soubor stránky (MB)]], 100 - ( 100 * Obsazení (k dispozici_physical_memory_kb AS DECIMAL (18, 3)) / Obsazení (total_physical_memory_kb AS DECIMAL (18, 3))) AS 'Použito procento', system_memory_state_desc AS [Stav paměti] OD sys.dm_os_sys_memory;

Zvažte každý výstupní parametr:

  1. [Celková fyzická paměť] - množství paměti RAM dostupné v operačním systému. Na některých serverech se může zobrazit o něco více, než je skutečně nainstalováno.
  2. [Dostupná fyzická paměť] - množství paměti RAM dostupné pro SQL Server, s výjimkou již zachyceného SQL Serveru.
  3. [Celkový soubor stránky (MB)] - Objem „závazek“. Commit Limit = RAM + všechny odkládací soubory. To znamená, že pokud máte na serveru 32 GB RAM a 16 GB stránkovacího souboru, bude limit odevzdání 48 GB.
  4. [Dostupný soubor stránky (MB)] - Velikost stránkovacího souboru.
  5. Použité procento - procento použité RAM. V samotném sys.dm_os_sys_memory neexistuje žádný takový parametr, ale vypočítá se pomocí vzorce available_physical_memory_kb / total_physical_memory_kb
  6. [Stav paměti] - stav paměti RAM. Pole system_memory_state_desc obsahuje stav spotřeby RAM ve formě textu. Hodnota tohoto pole je zvažována na základě dalších dvou: system_low_memory_signal_state a system_high_memory_signal_state. Můžete je vybrat přímo, pokud potřebujete booleovský / bitový datový formát. Všechna pole sys.dm_os_sys_memory naleznete v dokumentaci 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šechna tato data jsou užitečná, pokud chcete přesně určit, kolik SQL serveru spotřebovává RAM. To se nejčastěji používá, pokud existuje podezření, že je pro instanci přiděleno příliš mnoho paměti RAM..

Pokud se chcete ujistit, že server má dostatek paměti RAM, můžete se podívat pouze na pole system_low_memory_signal_state, system_high_memory_signal_state a system_memory_state_desc. Pokud system_low_memory_signal_state = 1, pak server zjevně nemá dostatek paměti RAM.

Využití CPU na serveru SQL

Zatížení procesoru je snadnější určit, jak to lze provést ve Správci úloh. Chcete-li zjistit aktuální zatížení procesoru, vyhledejte proces sqlservr.exe ve Správci úloh

Pokud chcete znát zátěž za minulý čas, můžete použít dotaz:

Nezapomeňte změnit @lastNMin na číslo, které potřebujete během několika minut.
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 ( ZVOLTE [timestamp], CONVERT (XML, záznam) AS [záznam] OD sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'A záznam LIKE' %% ') AS x) AS Y OBJEDNÁVKA záznam_ID DESC;

Výsledkem je statistika využití procesoru za minutu.

Analýza zatížení disku serveru SQL

Pojďme se podívat na načítání disků v operačním systému. Chcete-li to provést, spusťte resmon.exe.

Potřebujeme kartu Disk. V sekci Disková aktivita soubory, ke kterým se přistupuje, se zobrazí a jejich rychlost čtení / zápisu v aktuálním okamžiku. Filtrujte tuto sekci podle Celkem (klikněte na Celkem). Na samém vrcholu budou soubory, které v současné době využívají jednotku maximálně. V případě serveru SQL to může být užitečné určit, která databáze aktuálně načítá disk nejvíce..

Sekce Úložiště zobrazuje všechny jednotky v systému. V této sekci potřebujeme 2 parametry - Aktivní čas a Fronta disku. Aktivní čas jako procento zobrazuje zatížení na disku, to znamená, že pokud na disku vidíte C: \ Aktivní čas rovný 90, znamená to, že prostředek pro čtení a zápis na disku je aktuálně používán na 90%. Sloupec Disková fronta zobrazuje přístupovou frontu k disku a pokud hodnota fronty není nula, je disk 100% načten a nemůže zvládnout zatížení. Pokud je aktivní čas téměř 100, pak se disk používá téměř na hranici svých možností rychlosti.

Zobrazit zámky na serveru SQL

Poté, co jsme se ujistili, že server má dostatek prostředků, můžeme přistoupit k zobrazení zámků.

Zámky lze prohlížet pomocí Sledování aktivity v SSMS, ale použijeme T-SQL, protože tato možnost je pohodlnější a vizuální. Splňujeme požadavek:

NASTAVTE NOCOUNT NA GO SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR (10), "), CHAR (13),") JAKO DATUM 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)) JAKO ÚROVEŇ, ŠARŽE OD #TR KDE (BLOKOVÁNO = 0 NEBO BLOKOVÁNO = SPIDA) A EXISTUJÍ (VYBERTE * OD #T R2 KDE R2.BLOCKED = R.SPID A R2.BLOCKED R2.SPID) UNION ALL SELECT R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, R.BATCH OD #T AS R VNITŘNÍ ZAPOJENÍ BLOCKERS ON R. BLOCKED = BLOCKERS.SPID KDE R. BLOCKED> 0 A R. BLOCKED R.SPID) VYBERTE N "+ REPLIKÁT (N '|', LEN (ÚROVEŇ) / 4 - 1) + PŘÍPAD WHEN ( LEN (LEVEL) / 4 - 1) = 0 THEN 'HEAD -' ELSE '| ------' END + CAST (SPID AS NVARCHAR (10)) + N "+ ŠARŽE JAKO BLOCKING_TREE OD BLOKOVACÍCH OBJEDNÁVKŮ ÚROVEŇ ASC GO DROP TABLE #T GO

Tento dotaz vrátí seznam zámků ve formě stromu. To je výhodné při provozu, protože obvykle, pokud dojde k jednomu zámku, to provokuje ostatní. Podobně v Monitoru aktivity nebo ve výstupu sp_who2 můžete vidět pole „Blocked By“.

Pokud žádost nic nevrátila, neexistují žádné zámky.

Pokud žádost vrátila některá data, musíte analyzovat řetězec.

HEAD znamená, že tato žádost je důvodem pro všechny ostatní zámky pod stromem. 64 je identifikátor procesu (SPID). Poté je napsáno tělo požadavku, které způsobilo zámek. Pokud máte dostatek serverových prostředků, pak je s největší pravděpodobností záležitost v samotném požadavku a ve vzájemném odvolání na některé objekty. Abychom byli přesnější, musíte analyzovat konkrétní požadavek, který způsobil zámek.

Zásady serveru SQL

I když pro vás všechno funguje dobře a neexistují žádné stížnosti, ve skutečnosti může existovat mnoho problémů, které se objeví později. SQL Server má zásady pro toto..

Zásady v SQL Serveru zhruba řečeno kontrolují, zda pravidlo vyhovuje dané hodnotě. Například pomocí zásad můžete zajistit, aby bylo automatické zmenšení vypnuto ve všech databázích na serveru. Zvažte příklad importu a provádění zásad

V SSMS se připojte k serveru, na kterém chcete provádět zásady (Správa -> Správa zásad).

Importujte soubor Auto Shrink.xml databáze. Klikněte na Vyhodnotit

V instancích uzlů1, test1 a test2 jsou dvě databáze. Autoshrink je na test2 povolen. Podívejme se na podrobnosti.

Zásady určily zahrnutý parametr AutoShrink, v popisu jsou obvykle psána vysvětlení pravidel. V tomto případě je vysvětleno, proč je automatické smršťování lepší..

Zásady lze provádět buď podle plánu nebo na vyžádání (jednorázově). Výsledky provádění politiky jsou uvedeny v protokolu politik..

Při instalaci serveru SQL musíte vybrat pouze použité komponenty DBMS a zadat nastavení v souladu s hardwarovou konfigurací serveru. Vždy se ujistěte, že server má dostatek prostředků a že na serveru nejsou žádné zámky

Nejúčinnějším diagnostickým nástrojem serveru SQL jsou T-SQL a DMV. Doporučuje se také sestavit nepřetržitě monitorování serveru SQL a jeho infrastruktury, aby se zjistily všechny možné problémy..

Kategorie