Microsoft SQL Server Monitorovn David Hlavek MCP MCTS

Microsoft SQL Server: Monitorování David Hlaváček MCP, MCTS, MCITP, MCSA, MCSE, MCT Microsoft SQL Server www. hlavacek. pro

Na co se můžete těšit ? • • • Performance Killers Architektura Wait types Dynamic Management Objects Extended Events Data Collector

Jakou monitorovací techniku jste v minulosti použili ? • • • Performance Monitor SQL Profiler Dynamic Management Objects Data Collector Extended Events SQLDiag

Performance Killers

SQL Server performance killers • Indexy a statistiky – Špatná strategie indexů – Nepřesné statistiky – Nadměrná fragmentace • Konfigurace serveru a databází – – • Použití default konfigurace SQL Serveru (v mnoha případech nevhodné) Neoptimální konfigurace systému Windows Uložení a konfigurace systémových databází (především tempdb) Neoptimální konfigurace uživatelských databází Exekuční plány – Nízká míra opětovného použití exekučního plánu – Neoptimální exekuční plány – Vysoká frekvence rekompilací plánů • T-SQL a objekty – – Nevhodný návrh dotazu Non-set-based operace (kurzory apod. ) Nadměrné uzamykání a deadlock Špatný návrh databáze (normalizace vs. denormalizace)

Špatná strategie indexů • Indexy umožňují SQL Serveru pracovat pouze daty, která jsou nutná pro zpracování výsledku • Samotná existence indexu nezaručuje jeho využití při procesování dotazu • Při absenci vhodného indexu musí být zpracováno nadměrné množství dat což vede k problémům s: o o Physical IO (Disk) Paměť CPU Uzamykání = Délka procesování dotazu

Nepřesné statistiky • SQL Server provádí optimalizaci dotazů na základě ceny (cost-based optimalization) • Statistiky poskytují velice důležité informace o distribuci dat v datovém objektu • Informace ze statistik jsou použity při generování exekučního plánu • Informace získané ze statistik ovlivní : o Použití indexu při procesování dotazu o Logické a fyzické operace při procesování dotazu

Nadměrná fragmentace • Interní fragmentace – Poměr volného místa a řádků v datových stránkách není optimální „Datové stránky jsou poloprázdné“ – Existuje více datových stránek než je nutné pro uložení stejného množství dat • Externí fragmentace – Související datové stránky nejsou uloženy souvisle

Index Page Split INSERT VALUE (1) 1 - >610 Root 100 - > 611 610 Intermediate 611 1 - > 800 100 - > 802 2 - > 1000 50 - > 801 150 - > 803 800 Leaf INSERT 1 801 1, … 2, … 3, … 4, … 50… 51… 52. . . ~50% 1000 ~50% 803 802 100… 101… 105… 106… 150… 152… 153…

Nevhodná konfigurace SQL Serveru • CPU – Počet CPU vs. množství zpracovávaných operací – Paralelně zpracovávané operace • Memory – Způsob a množství alokace paměti – Datové objekty a exekuční plány – Nadměrné používání storage • Storage – Výkon Physical I/O operací • Database Read/Write • Backup

Lock, Block a Deadlock • SQL Server automaticky spravuje a řeší konkurenční přístup k objektům (čtení vs. změna) • Pro řešení konkurenčního přístupu používá zámky • Při vysokém konkurenčním přístupu může dojít k vzájemnému blokování • Nevhodné řízení konkurenčního přístupu má za následek : o Čekání na přístup k objektu o Množství použité paměti (zámky jsou paměťové objekty) o Deadlock

Nízká míra opětovného použití plánu • Aby mohl být dotaz procesován, musí být nalezena optimální postup (exekuční plán) • Exekuční plán je uložen do paměti, aby mohl být opětovně použit • Nízká míra opětovného použití plánu může mít za následek : o Méně efektivní využití paměti o Vyšší zátěž CPU

Neoptimální exekuční plán • Exekuční plán je generován na základě cost-based optimalizace (použitím statistik) • Po uložení do paměti je opětovně využíván, aby nemuselo docházet k dalšímu generování • Použití plánu procesování dotazu nemusí být optimální – pokud plán pracuje např. s jinou hodnotou parametru a tím i s jiným množstvím dat • Parameter sniffing

Nevhodná konfigurace databází • Mezi důležité konfigurační vlastnosti databáze řadíme : o Počet, uložení a vlastnosti souborů databáze o Datové soubory o Filegroup o Trasakční log o FILESTREAM o Distribuce volného místa a datových objektů v souborech o Práce se statistikami o Způsob generování exekučních plánů

Architektura

SQL Server Database Engine Metadata, System Types Query Optimization • Plan Generation, Statistics, Costing Query Execution • Query Operators, Memory Grants, Parallelism Storage Engine • Access Methods, Database Page Cache, Locking, Transactions, … SQLOS • Schedulers, Buffer Pool, Memory Management, … Utilities • DBCC, Backup/Restore, BCP, … Language Processing • Parse/Bind, Statement/Batch Execution

SELECT P. Product. Number, P. Product. ID, total_qty = SUM(I. Quantity) FROM Production. Product P JOIN Production. Product. Inventory AS I ON I. Product. ID = P. Product. ID WHERE P. Product. Number LIKE N'T%' GROUP BY P. Product. ID, P. Product. Number;

Základní pojmy • Scheduler – Objekt řídící plánování činnosti vláken v SQL Serveru – Mapován na každý logický CPU – Určuje, které vlákno bude vystaveno CPU sys. dm_os_schedulers • Task – Představuje jednotku práce, která má být vykonána • Query request • Pre-login request, logout request • Bulk load request, distribued transaction request sys. dm_os_tasks

Základní pojmy • Thread – OS Thread vytvořený voláním • Create. Thread() • _beginthreadex() – Jednotka programových instrukcí sys. dm_os_threads • Worker – – Logická reprezentace Thread v SQL Serveru Worker nese instrukce Tasku Worker a Thread jsou mapovány 1: 1 Lze konfigurovat maximální počet • (CPU - 4) * 16 + 512 sys. dm_os_workers

Základní pojmy • Connection – Představuje fyzické připojení navázané na úrovni protokolu – Existuje pouze pro „externí“ komunikaci sys. dm_exec_connections • Session – Logická reprezentace connection – Zjednodušeně session =~ connection – Při navázání připojení client/server obě strany vytvoří Session pro výměnu informací – Identifikace pomocí session_id • Pro uživatelská připojení nemusí platit session_id > 50 sys. dm_exec_sessions

Sqlservr. exe process task 1 Call Create. Thread() connection 1 prelogin/login connection 2 pre- task 2 Task 1 worker 1 running worker 2 runnable Task 2 login/login Worker 2 Worker 1 Thread 1 Schedul er 0 Host OS Thread 2 1 Scheduler

Thread Scheduling • Plánování činnosti je rozděleno do 3 částí Processor Runnable Queue Waiter List

8 x Scheduler • Scheduler pro každý jeden logický nebo virtuální CPU Processor Waiter Runnable Queue List Runnable Queue Waiter Runnable Queue Processor List Waiter List Runnable Queue Waiter Runnable Queue Processor List Processor Waiter List Runnable Queue Waiter Runnable Queue List Processor Waiter List Runnable Queue Waiter List

Session state Session Query Run Wait Idle Compile Executing Lock I/O Memory CPU

Execution model – Thread state • Running – Vlákno je vykonáváno procesorem • Runnable – Vlákno čeká na vykonání procesorem • Suspended – Vlákno čeká na dostupnost zdroje Runnable • Signal wait Suspended

Execution model

Quantum Exhaustion • Specifický případ přerušení vykonávání vlákna procesorem • Quantum je časový limit souvislého RUNNIG stavu vlákna – Pevně nastaven na 4 ms (nelze změnit) • Pokud RUNNIG stav vlákna není přerušen nutností čekat na dostupnost zdroje (SUSPENDED) a dosáhne limitu Quanta, dojde k dobrovolnému přerušení – „Vyčerpání Quanta“ – Stav vlákna se mění z RUNNING na RUNNABLE

Wait Time Definition • Resource Wait time – Čas vlákna ve stavu SUSPENDED Running • Signal Wait time – Čas vlákna ve stavu RUNNABLE Runnable • Signal wait • Wait time – Celkový čas čekání při zpracování požadavku – Resource Wait time + Signal Wait time Suspended

Nástroje pro zjišťování Wait statistik • • SQL Server Performance Dashboars Reports SQL Server Data Collector Dynamic Management Objects Extended Events • Third Party Tools – Who Is Active - script

Wait types

Wait types Resource Synchronization • I/O, Network, Thread, Memory • THREADPOOL, RESOURCE_SEMAPHORE • Lock, Latch, Spinlock ect. • PAGEIOLATCH_SH, LATCH_EX, CXPACKET Forced • Yield, Sleep • SOS_SCHEDULER_YIELD, WAITFOR External • Preemtive • PREEPTIVE_OS_AUTHENTICATION Queue • Background tasks • RESOURCE_QUEUE, LAZYWRITER_SLEEP

SQL Scheduling DMV • sys. dm_os_waiting_tasks − Zobrazuje všechna vlákna, která jsou ve stavu SUSPENDED waiting_tas_address Adresa paměti pro task session_id Číslo připojení daného tasku exec_context_id Číslo vlákna daného tasku (>0 znamená paralelní operaci) wait_duration_ms Délka čekání v ms (včetně signal_wait) wait_type Důvod čekání (typ čekání) blocking_session_id Identifikace připojení, které daný task blokuje

SQL Server Wait statistics • sys. dm_os_wait_stats o Obsahuje agregované informace o čekání na zdroje o Informace jsou agregovány od startu SQL Serveru • Možnost resetu bez nutnosti restartu o DBCC SQLPERF('sys. dm_os_wait_stats', CLEAR) wait_type Identifikuje resource, na který operace čekaly waiting_task_count Udává celkový počet případů, kdy se na zdroj čekalo wait_time_ms Celkový čas čekání všech požadavků max_wait_time_ms Nejdelší doba čekání jediného požadavku signal_wait_time_ms Udává dobu čekání vlákna ve stavu RUNNABLE

Jaké Wait typy jsou relevantní ? • Existence čekání nemusí znamenat problém • Je nutné identifikovat nejčastější Wait typy • Celá řada Wait typů nesignalizuje výkonnostní problém • Příklad : – Identifikujeme 1000 wait LCK_M_S, je to problém ? • V jakém časovém intervalu toto čekání vzniklo ? • Jaké bylo průměrné čekání jediného výskytu ?

Worldwide Wait Types • Průzkum 1800 produkčních instancí

CXPACKET • Ukazuje na paralelně vykonávané operace – Toto nemusí být výkonnostní problém • V případě četného výskytu v krátkém čase – Špatná distribuce práce mezi jednotlivá vlákna – Jedno z paralelních vlákej je blokováno • Korelace s dalšími Wait typy – PAGEIOLATCH_SH – LATCH_XX (ACCESS_METHODS_DATASET_PARENT, ACCES_METHODS_SCAN_RANGE_RENERATOR) • Velké scan operace datových zdrojů • Identifikace paralelních dotazů a prozkoumání plánů • Zjištění příčiny čekání jednoho vlákna (non CXPACKET)

CXPACKET Table Scan Thread 0 CXPACKET Thread 1 Thread 2 Thread 3 Thread 4 • CXPACKET koordinačního vlákna není problém • CXPACKET se při paralelních operacích vyskytuje naprosto běžně

CXPACKET Table Scan Thread 0 Thread 1 Thread 2 Thread 3 CXPACKET Thread 4 • CXPACKET koordinačního vlákna není problém • CXPACKET se při paralelních operacích vyskytuje naprosto běžně

CXPACKET příčiny a řešení • Příčiny : – Probíhají paralelně zpracovávané operace (není problémem) – Velké Scan operace – chybějící indexy (nelze provádět Seek) – Zastaralé statistiky vedoucí k nerovnoměrné distribuci • Řešení – – – – UPDATE Statistik a ověření existence indexů MAXDOP pro daný dotaz MAXDOP dle počtu CPU v NUMA nodu MAXDOP na úrovni instance (pozor na mix workload) RESOURCE GOVERNOR s použitím MAX_DOP Cost threshold for parallelism Běžná chyba je MAXDOP 1 na úrovni instance

Latch • Synchronizační mechanizmus mezi vlákny • Chrání přístup k datovým strukturám • Když mají být data čtena nebo zapisována, Thread nejprve musí vytvořit latch nad příslušnou datovou strukturou – Latch existuje pouze po dobu samotné operace • Latch není možné ovládat • Podobně jako zámky existují i různé módy – EX při změně dat, SH při čtení dat, KP brání odstranění objektu – DT při odstranění objektu, UP Update Latch • Existují 3 základní typy : – PAGEIOLATCH_XX • Používán při práci s datovou stránkou na disku (čtení stránky do paměti) – PAGELATCH_XX • Používán při přístupu vlákna do datové stánky v paměti – LATCH_XX • Používán při přístupu k ostatním datovým strukturám • Není použit při přístupu k datovým stránkám tabulek/indexů

Latch - Příklad • Vložení řádku do struktury indexu Virtual Root LATCH_SH (ACCESS_METHODS_ HOBT_VIRTUAL_ROOT) 1 - >610 100 - > 611 PAGELATCH_SH 1 - > 800 50 - > 801 1… 1… 2… … PAGELATCH_EX 100 - > 802 150 - > 803 50… 51…. . 100… 101… 105… … X Lock 150… 152… …

Latch Contention • Blokování nemusí být způsobenou pouze zámky • Příklad : – Vlákno vytvoří PAGELATCH_EX nad stránkou – Ostatní vlákna jsou blokována při přístupu do dané stránky • Protože je Latch vytvořen na dobu samotné operace, nemusí znamenat výkonnostní problém – Dokud mnoho vláken nepožaduje přístup k jediné stránce • Latch Contention se obvykle vyskytuje : – – – Server s 16+CPU Časté vkládání řádků do tabulky s IDENTITY a indexem Častá tvorba a odstraňování objektů U tabulek s velkým množstvím řádků na jedné datové stránce Nad systémovými stránkami PFS, GAM/SGAM

Latch nad tabulkou • Doporučeno používat Hash Partitioning

Latch a Temp. DB • Při tvorbě dočasného objektu : – Čtení SGAM page (2: 1: 3) pro nalezení mixed extend s volným místem • SQL Server vytvoří exclusive latch nad SGAM – Čtení PFS page (2: 1: 1) pro nalezení volné stránky v extentu • SQL Server vytvoří exclusive latch nad PFS – Ostatní procesy, které v danou dobu chtějí přistoupit k těmto stránkám jsou přesunuti na waiting list s typem čekání PAGELATCH • Doporučená řešení : – Vhodná konfigurace Temp. DB • Množství datových souborů odpovídajících ¼ - ½ CPU (max 8) • Vytvářené objekty budou používat round-robin napříč data files – Trace Flag 1118 • Zakáže Mixed Extend allocation – Každá tabulka mude mít min. 64 KB (ve všech databázích) – SQL Server 2008 není použití nutné z důvodu cache temp objektů

Latch Info DMV • sys. dm_os_latch_stats – Obsahuje informace o použití Latch při práci serveru – Informace agregovány od startu SQL Serveru

PAGEIOLATCH_XX • Vlákno čeká na získání datové stránky z disku – SH poukazuje na čtení stránky z disku do paměti – EX poukazuje na změnu stránky na disku • Běžná chyba : – Nemusí nutně poukazovat na problém IO subsystému • Zaměřit se na : – Které tabulky/indexy jsou čteny z disku – Analýza latence IO subsystému • sys. dm_io_virtual_file_stats • Avg Disk sec/Read Performance Couters – Korelace s CXPACKET – poukazuje na Parallel Scany – Kontrola správnosti exekučních plánů (např. konverze dat) – Memory management a Page Life Expectancy

PAGEIOLATCH_XX • Řešení – Tvorba indexů a redukce Scan operací – UPDATE Statistik – Optimalizace IO subsystému • Rozložení databáze na více datových souborů • RAID konfigurace – Memory optimalizace • Více RAM • Změna konfigurace ovlivňující memory management

ASYNC_NETWORK_IO • SQL Server čeká na potvrzení převzetí dat od klienta • Obvykle není problém sítě • Zaměřit se na : – Typ klientské aplikace a způsob použití dat – Kontrola latence a vytížení sítě • Příčina a řešení : – RBAR aplikace (Row-By-Agonizing-Row) – Klient získává velké datové množiny – Problémy na úrovni sítě a konfigurace • HW, Duplex nastavení, TCP chimney offload

WRITELOG • Čekání na zápis log block buffer do souboru na disk • Nemusí nutně znamenat problém v oblasti IO – Nikdy neřešte přidáním dalšího souboru transakčního logu • Zaměřit se na – Korelace sys. dm_io_virtual_file_stats a Perfmon IO counters • Dalším možným ukazatelem je výskyt LOGBUFFER wait – Average Disk Queue Lenght • SQL Server má interní limit na 32 zápisů do logu jedné DB – Kontrola množství změn v transakci • Minimální velikost zápisu do logu je 512 B • Časté zápisy minimální velikosti mohou způsobit problém – Kontrola Page Split indexů

Transaction log slouží pro zaznamenání změn transakcí, aby byla zajištěna Atomicita a Durabilita operací 1 Změna dat zaslaná aplikací Buffer Cache 2 3 Změna je zaznamenána do logu na disk Nalezení datové stránky v paměti nebo její umístění z disku do paměti 4 Později proces Checkpoint zapíše změněné stránky na disk

WRITELOG • Řešení – Oddělit soubor transakčního logu a umístit na rychlý IO – Zvýšit počet změn v transakci • Zabráníme velkému množství minimálních zápisů – Odstranění nepotřebných indexů • Snížíme počet Page Split a zkrátíme dobu údržby DB – Změna FILLFACTOR indexů – Rozdělení zátěže mezí více databází nebo serverů

PAGELATCH_XX • Vlákno čekán a přístup do datové stránky v paměti – Nezaměňovat s PAGEIOLATCH_XX – Nepoukazuje na problém nedostatku paměti nebo IO • Zaměřit se na – Identifikaci stránek/objektů na které vláka čekají • Uživatelská databáze vs. tempdb – Identifikace dotazů, které čekají na tento typ • Jaké operace dané dotazy provádějí (např. INSERT) – Analýza tabulky a indexů • Struktura, datové typy, fragmentace, Page Splits

PAGELATCH_XX • Řešení – Tempdb PAGELATCH (již probíráno) – Doporučit zvýšit opětovné používání temp tabulek (aplikace) – Omezit Page Split (např. FILLFACTOR) • Pozor na náhodné generování dat do indexu • Zvětšování řádků plněním variable dat (vložit DUMMY data) – Omezit nutnost přístupu do stejné stránky • Viz řešení Latch nad tabulkou

LCK_M_XX • Vlákno čeká na zámek z důvodu existence nekompatibilního zámku – Lock nemusí být příčinou daného problému • Zaměřit se na – Blocking chain v sys. dm_os_waiting_tasks – Blocked proces report • Řešení – – Vyřešit Lock Escalation Změnit strategii indexů (rychlejší seek namísto scan) Snapshot Isolation Level nebo lock hint Odstranit čekání blokujícího

SOS_SCHEDULER_YIELD • Vlákno vyčerpalo 4 ms quantum • Spinlock (Spin a backoff) • Není zobrazován v sys. dm_os_waiting_tasks – Nejedná se o čekání (Running - Runnable) • Zaměřit se na – Exekuční plány kde hledáme velké scan operace • Pokud je zároveň minimum PAGEIOLATCH, jedná se o velké scany objektů v paměti – Signal Wait a Runnable Queues • Poukazují na problém výkonu CPU

SOS_SCHEDULER_YIELD • Příčiny – Spuštěná operace není přerušena čekáním • Má všechny zdroje proto nenastane stav Suspended • Vyčerpání 4 ms času na procesoru • Řešení – V případě vysokého Signal Wait a Runnable fronty • Více CPU, zapnout Hyperthreading, výkonnější CPU – Pokud je příčinou Spilnock • Volejte podporu nebo odborníka • Nutné analyzovat Call Stack

OLEDB • Při práci s SQL Serverem je použit mechanismus OLEDB • Nemusí nutně znamenat použití Linked Servers • Zaměřit se na – Jaké dotazy čekají na OLEDB – Pokud jsou použity Linked Servers, jaké operace způsobují čekání na OLEDB • Příčiny – DBCC CHECKDB – DMV – Linked Servers

PREEMPTIVE_OS_XX • Thread vykonává činnost v OS • Thread musí být přepnuto do preemptive módu – Je použit RUNNING stav namísto SUSPENDED – SQL Server nemá info o stavu vykonání na úrovni OS • Zaměřit se na – Existuje cca 194 PREEMPTIVE_OS_XX waits – Velmi špatně dokumentovány – XX je obvykle název Windows API (MSDN nápověda) • Příčiny – Záleží na wait typu – Např. CREATEFILE nastává při použití FILESTREAM

PREEMPTIVE_OS_XX • PREEMPTIVE_OS_CREATEFILE – Tvorba nového souboru – Použití FILESTREAM (doporučena optimalizace) • PREEMPTIVE_OS_WRITEFILEGATHER – Zápis do souboru – Např. Restore, tvorba databáze, zvětšování souboru – Propsání 0 při inicializaci souboru (data vs. log) • PREEMPTIVE_OS_WAITFORSINGLEOBJECT – – Thread čeká změnu stavu synchronizace objektu Obvykle z důvodu ASYNC_ / NETWORK_IO Shared Memory Provider – SSMS na serveru NETWORK_IO obvykle z důvodu použití replikace

BACKUPXX • BACKUPBUFFER • BACKUPIO • BACKUPTHREAD • Význam – Čekání na data nebo na buffer pro data – Čtení z datových souborů databáze – Při paralelní operaci může jít o čekání na jiné vlákno • Např. nulování datového souboru nebo logu při Restore operaci • Příčiny – Zálohování na pásku nebo síť (IO vzdáleného serveru) – Pomalé čtení z datových souborů při zálohování

DBMIRRORXX • • DBMIRROR_EVENTS_QUEUE DBMIRROR_SEND DBMIRROR_CMD DBMIRROR_DBM_MUTEX • Příčiny – Analýza průměrné hodnoty DBMIRROR_DBM_MUTEX • Při vyšších hodnotách značí velké množství zrcadlených databází nebo velké množství zrcadlených transakcí – Vysoké průměrné hodnoty pro všechny wait typy poukazuje na problém s výkonem celého systému • Kontrola Mirror Serveru (IO, Memory, thread)

HADR_XX • HADR_SYNC_COMMIT – Doba čekání primární repliky na zápis do logu sekundárních replik při synchronním módu – Problém na úrovni sítě nebo sekundární repliky • HADR_SYNCHRONIZING_THROTTLE – Doba čekání na synchronní stav sekundární repliky – Sekundární replika ztrácí synchronní stav s hrozící ztrátou dat • atd. • Význam – Všechny tyto typy se vztahují na Availability Groups

TRACEWRITE a SQLTRACE_XX • Význam – Použití mechanizmu SQL Trace • Příčiny – – Nevhodně nastavené trasování Velké množství trasovaných událostí Nedostatečný IO výkon při zápisu trasování na disk File nebo Rowset (Profiler) provieder nezpracovávají data dostatečně rychle – Nástroje třetích stran

Méně běžné Wait typy • EXECSYNC – Výměna informací mezi vlákny při paralelním zpracování • ASYNC_IO_COMPLETION – Non-data-file IO • Nulování transakčního logu • Zápis do backup media • IO_COMPLETION – Non-data-file IO • Čtení z transakčního logu pro ROLLBACK • Čtení z transakčního logu při replikaci • WRITE_COMPLETION – Non-buffer IO (zápis do souboru) • Tvorba alokačních bitmap stránek (např. PFS) • THREADPOOL – Čekání na dostupnost vlákna – Např. při nadměrném paralelním zpracování

Méně běžné Wait typy • RESOURCE_SEMAPHORE – Dotaz čeká na memory grant (sort a hash operace) • MSQL_XP – Čekání na dokončení extended stored procedure • LOGBUFFER – Čekání na dostatek paměti pro zápis do transakčního logu – Velké množství malých zápisů do logu

Microsoft SQL Server: Nástroje a metody monitorování

Dynamic Management Objects • Metadatové objekty, které umožňují zjistit stav a chování SQL Serveru • Jedná se o systémové pohledy a funkce • Prvně uvedeny v MS SQL 2005 • Obsahují informace o aktuálním chování nebo historické informace o událostech od restartu • Jsou rozděleny do skupin dle informací, které obsahují • sys. dm_*

Dynamic Management Objects DMV Info sys. dm_exec_* Obsahují informace o uživatelských dotazech, vlastnostech připojení, procesech, exekučních plánech sys. dm_os_* Obsahují systémové informace o distribuci paměti, scheduler, vláknech, zdrojích SQL Serveru sys. dm_tran_* Zobrazují informace o aktuálních transakcích a jejich vlastnostech, aktuálně existujících zámcích sys. dm_io_* Informace o I/O aktivitě na úrovni sítě a disků sys. dm_db_* Obsahují detaily o databázích a jejich objektech, alokaci objektů, vlastnosti datových struktur, fragmentace indexů, použití objektů

Extended Events • • • Event-handling system Ladění a diagnostika Minimální zátěž pro server Doplňují a nahrazují SQL Trace K dispozici od MS SQL 2008 Grafická podpora SSMS 2012

Extended Events • Events – Trasované události – Každý event obsahuje základní informace s popisem události – Dělení : • Kategorie – database, error, execution, io, lock, memory, latch, server… • Kanály – Admin, Analytic, Debug, Operational

Extended Events • Predicates – Princip podobný filtrům SQL Trace nebo WHERE klauzuli – K filtrování dochází před zasláním události do Target • SQL Trace filtruje až v cílovém umístění – Filtrování je prováděno synchronně • Pozor na příliš složité podmínky • Actions – Poskytují další informace o trasované události

Extended Events Targets • „Konzumují“ události dle svého typu • Synchronní – Event counter • Počítá výskyt definovaných událostí po celou dobu běhu Event Session – Event Tracing for Windows (ETW) • • Používá se pro korelaci SQL Events s Windows system nebo application event data Asynchronní – Event File • Zápis události do souboru na disk – Event Pairing • Dochází k párování událostí (Lock/un. Lock) a po spárování jsou vymazány, uložení v paměti – Histogram • Počítá výskyt specifické události na základě definované vlastnosti. – Ring Buffer • Informace o události jsou uloženy do paměti

Extended Events Targets Advanced • Event retention mode – Single event loss • – Multiple event loss • – Čas, po který bude event uložen v paměti před jeho uložením na definovaný asynchronní Target Max memory size – • Nedochází ke ztrátě eventů. Všechny operace pro které existuje event musí počkat, dokud není event zapsán do paměti (i v případě nedostatku paměti) Maximum dispatch latency – • Při nedostatku paměti může dojít ke ztrátě skupiny událostí (nejmenší zátěž na server) No event loss • • Při nedostatku paměti může dojít ke ztrátě jednotlivých událostí (střední zátěž na server) Určuje maximální množství paměti pro uložení eventů před odesláním na Target Memory partition mode – None • – Per node • – Paměť pro eventy bude uložena v jediném bloku pro celou instanci SQL Serveru Paměť bude rozložena napříč NUMA nody instance Per CPU • Paměť bude vytvořena pro každý CPU instance

Extended Events – system_healt • Default monitorování po instalaci SQL Server – SQL 2008 – ring_buffer – SQL 2012 – ring_buffer, event_file • Events : – Všechny operace • • • s chybou serverity > = 20 s memory error čekající na latch > 15 sec čekající na lock > 30 sec Čekající na external nebo pre-emptive zdroj > 5 sec – Deadlock

Data Collector je komponenta SQL Serveru používající se pro kapacitní plánování a výkonnostní diagnostiku. – – – Nižší nároky na monitorování Trvalé uložení monitorovaných událostí Automatické čištění historie Vestavěné reporty Rozšiřitelnost Centrální úložiště pro více monitorovaných serverů

Data Collector • Pro monitorování jsou použity SSIS package a SQL Agent • Management Studio je použito pro zobrazení reportů Data Collector Performance Counter SQL Trace T-SQL Data Repository Centralized Data Storage Reporting

Konfigurace Data Collector • Wizard v SSMS • Dva kroky : – Tvorba centrálního úložiště – Konfigurace Data Collector na monitorovaných instancích • Doporučeno pečlivé plánování dostatečné kapacity centrálního úložiště • Každý Data Collection Set obsahuje informace : – Co má být monitorováno – Frekvence monitorování – Doba, po kterou budou výsledky monitorování uloženy • Lze vytvořit vlastní monitorování

Sledování Data Collector • Konfigurace a Log jsou zapsány do msdb – Implementace přes uložené procedury a SSIS logování • Doba uložení je dána konfigurací • Log může být zobrazen přes SSMS nebo T-SQL • Zabezpečení přes role v MDW

Disk Usage Report – Report zobrazuje velikost, distribuci dat a trend databází • T-SQL dotazy • Spouštěn každých 6 hodin • Výsledky monitorování jsou uloženy 730 dní

Server Activity Report • Zobrazuje analýzu zátěže serveru – DMV a čítače Performance monitoru – Spouštěn každých 60 sec a centrálně uložen každých 15 min – Výsledky monitorování uloženy po dobu 14 dnů

Query Statistics Report • Obsahuje výkonnostní charakteristiku dotazů – Spouštěn každých 10 sec a centrálně uložen každých 15 min – Výsledky uloženy 14 dní • Jsou uloženy výkonnostní informace, text i exekuční plán