SQL server monitoring tools

v části 1 této série jsme pokryli řadu funkcí, které SQL Server poskytuje pro optimalizaci využití zdrojů. Můžete například upravit způsob, jakým jsou kompilovány dávky dotazů, nakonfigurovat vyrovnávací paměť tak, aby se v různých intervalech vypláchla, a vytvořit tabulky optimalizované pro paměť. Co nejvíce z těchto funkcí vyžaduje, abyste získali přehled o zdraví a výkonu serveru SQL Server v reálném čase. Zde budeme zkoumat nástroje pro monitorování serveru SQL v rámci vestavěných funkcí a běžně používaných aplikací, včetně:

• T-SQL dotazy: použijte dotazovací jazyk SQL Serveru pro shromažďování interně shromážděných dat
• SQL Server Management Studio (SSMS): získejte zobrazení v reálném čase do vašeho systému, diagramy dotazů T-SQL a zprávy na vyžádání
• Monitor výkonu: Korelace metrik ze serveru SQL Server s daty z hostitelů systému Windows

nástroje pro monitorování serveru SQL Server vám mohou pomoci získat přístup k metrikám, které jsme diskutovali v části 1. Některé z těchto nástrojů hlásí stejné metriky a můžete upřednostňovat jedno rozhraní před druhým. K získání metrik z čítačů výkonu SQL Serveru můžete například použít dotazy Performance Monitor nebo T-SQL. Můžete se rozhodnout pro grafy v reálném čase prvního versus schopnost skriptovat druhý. V tomto příspěvku vysvětlíme, jak používat nástroje pro monitorování serveru SQL Server k získání komplexního pohledu na databázovou infrastrukturu.

pomocí T-SQL dotazů

můžete monitorovat SQL Server pomocí vlastního dotazovacího jazyka, T-SQL, pro shromažďování metrik. Dotazy T-SQL jsou flexibilní. Můžete je spustit pomocí grafického nástroje pro správu, jako je SSMS nebo nástroj příkazového řádku, jako je sqlcmd. A protože jsou prováděny a vracejí data stejně jako jakýkoli jiný databázový dotaz, můžete je snadno začlenit do domácího automatizovaného monitorovacího řešení. V této části si ukážeme, jak mohou být dotazy T-SQL výkonným nástrojem pro monitorování serveru SQL, ať už používáte dynamické pohledy na správu, vestavěné funkce, uložené procedury nebo sady sběru systémových dat.

Dynamic management views

SQL Server sleduje data o svém vlastním zdraví a výkonu a tyto informace zpřístupňuje prostřednictvím Dynamic management views (DMV). Vzhledem k tomu, že DMV jsou zobrazeny jako virtuální tabulky, jsou vhodné pro ad-hoc i automatizované dotazování. Některé DMV vrátí aktuální hodnotu metriky nebo nastavení (např., aktuální velikost protokolu transakcí v megabajtech). Ostatní, zejména metriky pro sazby v rámci čítačů výkonu DMV, měří hodnoty v pravidelných intervalech a berou rozdíl mezi po sobě jdoucími vzorky (např. dávkové požadavky za sekundu). O konkrétních DMV si můžete přečíst v dokumentaci serveru SQL.

pokud monitorujete SQL Server s dynamickými pohledy na správu, pravděpodobně budete chtít dotazovat čítače výkonu DMV, sys.dm_os_performance_counters. Každý objekt výkonu serveru SQL (který může reprezentovat cokoli od Databáze po mezipaměť plánu) udržuje vlastní sadu čítačů výkonu, které mapují mnoho kategorií metrik diskutovaných v části 1: statistiky SQL, zámky a správce vyrovnávací paměti.

například můžete dotazovat čítače výkonu DMV pro zobrazení dat z objektu výkonu správce vyrovnávací paměti a omezit výsledky na metriky s nenulovými hodnotami:

SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name="SQLServer:Buffer Manager" AND cntr_value != 0;GO

dostanete výsledek podobný tomuto (ale s mnoha dalšími řádky!):

object_name counter_name cntr_value---------------------------------------------------------------------------------------SQLServer:Buffer Manager Buffer cache hit ratio 30SQLServer:Buffer Manager Buffer cache hit ratio base 30SQLServer:Buffer Manager Page lookups/sec 11091500SQLServer:Buffer Manager Database pages 5819SQLServer:Buffer Manager Target pages 212992SQLServer:Buffer Manager Integral Controller Slope 10

seznam dostupných dynamických zobrazení správy uspořádaných podle kategorií najdete zde. V každé kategorii jsou pohledy různorodé—zatímco některé vypočítávají metriky výkonu a výstupní čísla, jiné uvádějí názvy a vlastnosti. Můžete uvést uzly serveru SQL v klastru, načíst data o využití indexu a získat statistiky pro vaše plány provádění, jako je doba dokončení a využití zdrojů. A protože zobrazení dynamické správy se chovají jako tabulky, můžete použít vestavěné funkce k agregaci a hodnocení dat.

je důležité zkontrolovat dokumentaci pro všechny DMV, které chcete použít. Jedním z důvodů je, že DMV vyžadují různá oprávnění a dokumentace pro každý DMV vysvětluje požadovanou úroveň. Dalším důvodem je, že DMV mohou obsahovat tisíce řádků a sloupce se mohou měnit s novými verzemi serveru SQL Server.

vestavěné funkce

SQL Server také obsahuje vestavěné funkce, které vám pomohou získat přístup k systémovým informacím. Na rozdíl od dynamických pohledů na správu, které vracejí data ve formě virtuálních tabulek, vestavěné funkce vracejí systémová data jako jednotlivé číselné hodnoty vypočítané od posledního spuštění serveru. Každou vestavěnou funkci můžete volat jako argument příkazu SELECT. Můžete například použít vestavěnou funkci @@connections k vrácení součtu úspěšných a neúspěšných připojení v průběhu času:

SELECT @@connections AS "Total Connections";GO

obdržíte výstup podobný:

Total Connections----------------- 1571

vestavěné funkce se někdy podobají dynamickým pohledům na správu. @@connections je podobný čítači User Connections v objektu obecné statistiky. Ale zatímco User Connections sleduje počet aktuálně připojených uživatelů, @@connections se zvyšuje pokaždé, když se uživatel pokusí přihlásit (i když je pokus neúspěšný).

jedinou vestavěnou funkcí statistiky systému, která nevrací jedinou číselnou hodnotu, je sys.fn_virtualfilestats, která vrací tabulku s daty na disku I / O pro databázové soubory a poskytuje stejné informace jako sys.dm_io_virtual_file_stats dynamické zobrazení správy.

systémové uložené procedury

Poznámka redakce: SQL Server používá termíny „master“ k identifikaci své primární databáze. Datadog tento termín nepoužívá.

další vestavěnou funkcí, kterou můžete použít k dotazování metrik, je procedura uložená v systému. Většina uložených procedur pomáhá s administrativními úkoly, jako je připojení databáze nebo přidání přihlašovacího jména, ale některé uložené procedury hlásí metriky. Například sp_spaceused měří spotřebu disku v databázi. Zavoláte systémové uložené procedury s EXEC spíše než SELECT příkazy. Tento příkaz volá sp_spaceused uloženou proceduru, která vrátí informace o využití disku jako dvě sady výsledků (tj. dva řádky tabulky, každý řádek včetně různých sloupců):

EXEC sp_spaceused;GO

výstup bude mít podobný formát jako následující:

database_name database_size unallocated space--------------- ----------------- ---------------------master 6.00 MB 0.52 MBreserved data index_size unused--------------- ----------------- ------------------ ------------3568 KB 1536 KB 1600 KB 432 KB

sady pro sběr systémových dat

pokud používáte dotazy T-SQL ke shromažďování metrik ze serveru SQL Server a chcete mít možnost ukládat data a generovat sestavy, můžete zvážit použití sad sběru serveru SQL Server. Sběrná sada čerpá data z řady příkazů hlášení a dynamických pohledů na správu a odesílá data do specializované databáze zvané sklad dat pro správu.

proces se spoléhá na integrační služby SQL Server pro automatizaci úkolu dotazování databáze a zápisu výsledků do datového skladu pro správu.

například od SQL Server 2008 se sada disk usage collection dotazuje na sys.dm_io_virtual_file_stat dynamické zobrazení správy a další zobrazení, jako je sys.partitions a sys.allocation_units. Můžete také vytvořit vlastní sadu kolekcí, která ohradí posloupnost dotazů T-SQL do periodické úlohy, která běží na pozadí. Více informací o konfiguraci skladu pro správu dat naleznete zde.

SQL Server Management Studio

SQL Server Management Studio (SSMS) je grafické prostředí, které vám pomůže monitorovat váš systém několika způsoby:

• živé statistiky na monitoru aktivity
• datově bohatá mapa daného dotazu
• zprávy, které kombinují tabulky, grafy a text ve formátu vhodném pro tiskárnu

Chcete-li používat SSM, musíte si je stáhnout na jednom z vašich hostitelů, otevřít instalační program a postupovat podle pokynů. Tento software může sledovat vzdálené instance SQL Server, včetně všech instancí běžících na Linuxu. Chcete-li se připojit k hostiteli, přejděte do nabídky „Soubor“ a klikněte na „Připojit Průzkumník objektů. V následujícím dialogu zadejte hostitele a port do pole „Název serveru“ ve formátu 0.0.0.0,0000 (poznamenejte si čárku). V rozbalovací nabídce „ověřování“ vyberte „ověřování serveru SQL“ a vyplňte uživatelské jméno („přihlášení“) a heslo.

pokud jste se úspěšně připojili, uvidíte okno „Object Explorer“ naplněné stromem souborů, který zobrazuje komponenty instance serveru SQL, včetně databází. Poté budete moci sledovat svou instanci pomocí níže uvedených funkcí.

Monitor aktivity

Monitor aktivity umožňuje zobrazit metriky serveru SQL v reálném čase, s galerií grafů, přehledem procesů a statistikami o vašich dotazech. Pokud již používáte SSMS pro úkoly správy, jako je konfigurace fondů zdrojů nebo vytváření tabulek, Monitor aktivity lze snadno přidat do pracovního postupu. Chcete-li použít Monitor aktivity, zadejte „Ctrl-Alt-A“ nebo klikněte na ikonu na panelu nástrojů SSMS.

pomocí monitoru aktivity můžete získat informace o poptávce v reálném čase na instanci serveru SQL Server. Sekce „přehled“ zobrazuje čtyři grafy, které zobrazují metriky práce a zdrojů v reálném čase. Ve výchozím nastavení se tyto metriky obnovují každých 10 sekund, ale interval obnovení můžete aktualizovat kliknutím pravým tlačítkem myši na podokno „přehled“. Zatímco interval obnovení může být stejně častý jako jednou za sekundu, přichází s náklady na výkon častějších dotazů databáze.

podokno „nedávné drahé dotazy“ v monitoru aktivit může pomoci poskytnout informace, které potřebujete k zefektivnění vašich dotazů. Zde najdete metriky související s dotazem, jako je provádění za minutu, fyzické čtení za sekundu a počet duplikátů plánu provádění v mezipaměti. Pokud má jeden plán provádění vysoký počet duplikátů nebo poprav za minutu, můžete zvýšit výkon pomocí tipů pro dotazy, jak je popsáno v části 1.

Monitor aktivity poskytuje pohodlný přehled o vaší databázi na vysoké úrovni, ale má své limity. Pro jednoho, nemůžete upravit velikosti grafů nebo metriky, které ukazují. Nemůžete také změnit způsob, jakým Monitor aktivity agreguje své statistiky pro výkon dotazu, nebo zobrazit data za přednastaveným zobrazovacím oknem.

vizualizace dotazů

SSMS vám může pomoci optimalizovat výkon dotazu tím, že vám umožní vizualizovat, jak SQL Server provádí své plány dotazů, a ukazuje vám využití prostředků spojené s prováděním každého kroku plánu dotazu. Jak jsme diskutovali v části 1, SQL Server kompiluje dávky příkazů T-SQL pomocí automatického optimalizátoru k transformaci dávky do plánu provádění. Můžete si prohlédnout plán provádění v SSMS jako diagram výpočetních kroků a zjistit, jak přesně optimalizátor interpretoval Vaši dávku. Chcete-li vizualizovat dotaz, přejděte do podokna „nedávné drahé dotazy“ monitoru aktivity, klepněte pravým tlačítkem myši na jeden z dotazů a klikněte na „Zobrazit plán provádění.“Následující pohled bude vypadat takto:

pokud přejdete myší na uzel v diagramu, můžete vidět stručné vysvětlení kroku, který uzel představuje, stejně jako rychlé odečtení „odhadovaných nákladů operátora uzlu“.“Tato hodnota je vypočtena optimalizátorem serveru SQL při provádění dotazu. Vzhledem k tomu, že proces optimalizace je automatický, získáte způsob, jak zkontrolovat, zda se vaše šarže zkompilovaly podle plánu. A protože každý krok v plánu provádění je hodnocen podle nákladů, můžete vidět, na které kroky byste se měli zaměřit, pokud chcete zvýšit výkon.

v tomto příkladu vidíme, že Compute Scalar (převod řetězce na plovák) je minimální, s nulovými náklady v plánu provádění. Nejnákladnější operací je optimalizační technika, zařazování tabulek, která zkopíruje řádky do skryté dočasné tabulky.

zprávy

SSMS nabízí 20 standardních zpráv, které poskytují průzkum ve vysoké nadmořské výšce nasazení SQL Serveru, od využití zdrojů vaší databáze až po Historická data o změnách schématu a konzistenci databáze. Podrobný rozpis zpráv najdete zde.

přehledy jsou pevné v rozvržení a obsahu-zobrazují data dostupná v okamžiku vytvoření sestavy, spíše než aktualizace v reálném čase. Interaktivita ve standardních sestavách je omezená. Některé tabulky můžete třídit podle sloupců a rozbalit další, když jsou vnořené informace. Díky pevnému rozvržení je snadné vytvářet výtisky nebo dokumenty (PDF, Word a Excel).

v roce 2017 Společnost Microsoft přidala zprávu Performance Dashboard, která zobrazuje využití procesoru, aktuální počet uživatelských relací a další systémové informace pro instance serveru SQL.

Chcete-li vygenerovat sestavu, klepněte pravým tlačítkem myši na název databáze v Průzkumníku objektů, myší nad „sestavy“, poté nad „standardní sestavy“ a vyberte sestavu z nabídky.

pokud nemůžete najít požadovaný pohled ze standardních sestav SSMS, můžete vytvořit vlastní sestavu. Vlastní sestavy jsou psány v jazyce RDL (Report Definition Language), což je rozšíření XML. Poté, co jste zadali strukturu vlastní sestavy, můžete ji naplnit z nabídky „Sestavy“ kliknutím na “ vlastní sestavy.“Ty zůstávají oddělené od seznamu standardních zpráv.

je také třeba poznamenat, že společnost Microsoft vyvinula několik nástrojů pro vytváření grafických sestav, které přesahují funkčnost SSMS. Power BI může vizualizovat data z mnoha zdrojů, včetně SQL Server, a přichází s více plnohodnotnou sadou nástrojů pro vizuální úpravy. SQL Server Reporting Services (SSRS) je grafický reporting nástroj určený pro SQL Server, který může generovat stránkované, PDF-ready zprávy, stejně jako vizualizace dat pro mobilní zařízení a web.

Monitor výkonu

Monitor výkonu systému Windows vám pomůže vizualizovat využití prostředků na úrovni systému z vašich hostitelů systému Windows a umožňuje vám tyto metriky korelovat s čítači výkonu serveru SQL Server v grafech timeseries.

Monitor výkonu je zabudován do operačního systému Windows. Chcete-li jej použít, otevřete okno Spustit z nabídky Start a zadejte název programu perfmon. Graf v reálném čase se objeví v navigačním stromu pod “ monitorovací nástroje.“Poté můžete vybrat čítače výkonu serveru SQL Server a metriky systémových prostředků, které chcete vykreslit, a použít možnosti pro úpravu grafů.

bohatší nástroje pro monitorování serveru SQL Server v reálném čase

v tomto příspěvku jsme ukázali, jak používat nástroje pro monitorování serveru SQL a vestavěné funkce pro generování přehledů vašich databází v reálném čase a pro získání podrobných údajů na vyžádání o zdraví a výkonu serveru SQL.

v další části této série vám ukážeme, jak používat Datadog ke shromažďování, grafům a upozornění na metriky serveru SQL v reálném čase a historických SQL. Ukážeme vám také, jak nastavit dashboardy s vizualizacemi drag-and-drop a korelovat metriky serveru SQL s daty z celého zásobníku.