Transcript Architektura SQL Serveru

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
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
611
1 - > 800
2 - > 1000
50 - > 801
Intermediate
100 - > 802
150 - > 803
800
Leaf
INSERT 1
801
1,…
1,…
1,…
2,…
3,…
4,…
50…
51…
51…
52 ...
~50%
100%
1000
~50%
803
802
100…
101…
105…
106…
150…
152…
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 pro 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.ProductNumber,P.ProductID, total_qty = SUM(I.Quantity)
FROM Production.Product P JOIN Production.ProductInventory AS I
ON I.ProductID = P.ProductID
WHERE P.ProductNumber LIKE N'T%'
GROUP BY P.ProductID, P.ProductNumber;
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
• CreateThread()
• _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
Call CreateThread()
connection 1
Task 1
task1
worker1
running
task2
worker2
runnable
pre-login/login
connection 2
Task 2
pre-login/login
Worker 2
Worker1
Thread1
Scheduler
0
Host OS
Thread1
Thread2
Scheduler
Scheduler
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
Processor
Waiter
List
Runnable
Queue
Runnable
Queue
Waiter
List
Runnable
Queue
Processor
Processor
Waiter
List
Runnable
Queue
Waiter
List
Runnable
Queue
Processor
Processor
Processor
Waiter
List
Runnable
Queue
Waiter
List
Runnable
Queue
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
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
• I/O, Network, Thread, Memory
• THREADPOOL, RESOURCE_SEMAPHORE
Synchronization
• 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
CXPACKET
Thread 0
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
CXPACKET
Thread 0
Thread 1
Thread 2
Thread 3
CXPACKET
CXPACKET
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
PAGELATCH_SH
1 - > 800
50 - > 801
1…
1…
2…
…
PAGELATCH_EX
100 - > 802
150 - > 803
50…
51…
51…
..
100…
101…
105…
…
X Lock
150…
152…
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 TempDB
• 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 TempDB
• 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. 64KB (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í
3
Buffer Cache
2
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 4ms 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í 4ms č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
•
–
Event Tracing for Windows (ETW)
•
•
Počítá výskyt definovaných událostí po celou dobu běhu Event Session
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/unLock) 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
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 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