Obsah 1
Ladˇen´ı dotazu˚
1
2
SQL Server Profiler
2
2.1
Sledov´an´ı na stranˇe klienta . . . . . . . . . . . . . . . . . . . .
2
2.2
Sledov´an´ı na stranˇe serveru . . . . . . . . . . . . . . . . . . .
4
2.3
Analyza ´ trace souboru . . . . . . . . . . . . . . . . . . . . . .
5
2.4
Blokov´an´ı . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7
2.5
Profiler a Performance Monitor . . . . . . . . . . . . . . . . .
7
3
4
1
Data Collector
9
3.1
Konfigurace . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9
3.2
Reporty . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9
ˇ an´ı a fronty Cek´
9
Ladˇen´ı dotazu˚
˚ zeme vyuˇz´ıt SSMS. V Pro ladˇen´ı dotazu˚ a vypisov´an´ı query pl´anu˚ muˇ SSMS je moˇznost zapnout vypis query pl´anu pˇri prov´adˇen´ı dotazu. Je to ´ ˚ zeme v icona ’Include actual execution plan’. Po vykon´an´ı dotazu se muˇ z´aloˇzce execution plan pod´ıvat na jednotliv´e operace query pl´anu a jejich pod´ıl na sloˇzitosti dotazu (cost).
1
Obr´azek 1: Query plan dotazu
2 2.1
SQL Server Profiler Sledov´an´ı na stranˇe klienta
SQL Server Profiler (SSP) lze n´alezt v nab´ıdce Start ⇒ Programy ⇒ MS ˚ SQL Server ⇒ Performance tools. Bˇezˇ nym vyuˇzit´ı profileru ´ zpusobem ˚ Pro vytvoˇren´ı nov´eho sledov´an´ı provozu (trace) je je zjiˇstˇen´ı ceny dotazu. potˇreba kliknout na File ⇒ New Trace. Vybereme instanci SQL Serveru ˚ zeme v oknˇe Trace Properties definovat jenˇz se bude sledovat a po t´e muˇ detaily sledov´an´ı.
Obr´azek 2: Prvn´ı z´aloˇzka okna Trace Properties 2
Obr´azek 3: Druh´a z´aloˇzka okna Trace Properties Na obr´azku 2 je detail nastaven´ı sledov´an´ı v Trace Properties. Implicitnˇe je vystup vypisov´an pouze na obrazovku, takˇze toto zmˇen´ıme na ´ vystup do tabulky (prvn´ı z´aloˇzka). Ve druh´e z´aloˇzce (obr´azek 2) definu´ jeme ud´alosti, jenˇz se budou zachyt´avat. Definujeme ud´alosti SQL:BatchCompleted a RPC:Completed, aby se zachyt´avaly ukonˇcen´e T-SQL d´avky a uloˇzen´e ˚ zeme defiprocedury. Pokud klikneme na tlaˇc´ıtko Column Filters, pak muˇ novat filtr na zachyt´avan´e ud´alosti. Tedy napˇr´ıklad zachyt´av´an´ı jen ud´alost´ı urˇcit´eho uˇzivatele, podle toho jak dlouho bˇezˇ ´ı atd. ˚ zeme kliknou na tlaˇc´ıtko Run a spustit zachyt´av´an´ı Po nastaven´ı muˇ ud´alost´ı na stranˇe klienta. Po spuˇstˇen´ı nˇejakych ´ operac´ı na serveru se n´am tyto operace zaˇcnou zobrazovat i v profileru. Probl´emy profileru na stranˇe klienta: ˚ • D´a se vyuˇz´ıt sp´ısˇ e pro menˇs´ı mnoˇzstv´ı SQL pˇr´ıkazu. ˚ ze znaˇcnˇe zpomalovat jeich bˇeh. • Muˇ ˚ ze st´at, zˇ e nˇektery´ pˇr´ıkaz nen´ı zaznamen´an jelikoˇz SQL • Obˇcas se muˇ Server ’nest´ıh´a’. Sledov´an´ı prob´ıh´a na stranˇe klienta i kdyˇz SQL Server Profiler spouˇst´ıme na serveru na kter´em beˇz´ı sledovan´a instance. 3
2.2
Sledov´an´ı na stranˇe serveru
K SSP existuje jeˇstˇe druh´a varianta, kter´a netrp´ı zm´ınˇenymi probl´emy. ´ Jedn´a se o sledov´an´ı na stranˇe serveru (server-side trace), kter´e je definov´ana mnoˇzinou uloˇzenych ´ procedur. Toto je ovˇsem varianta pro zkuˇsen´eho administr´atora, ktery´ je schopen tyto procedury napsat. Naˇstˇest´ı v profileru existuje moˇznost vytvoˇrit skripty z pˇripraven´eho sledov´an´ı, kter´e mohou byt ´ vyuˇzity pro sledov´an´ı na stranˇe serveru. Po vytvoˇren´ı sledov´an´ı v profileru dejte export ⇒ Sript Trace Definition ⇒ For SQL Server 2005 – 2008. Takto vytvoˇr´ıte skript, ktery´ je moˇzn´e spustit na instanci, kterou chceme sledovat. Je pouze nutn´e ve skriptu pˇrepsat InsertFileNameHere na cestu k trace souboru, kde budeme ukl´adat ˚ zeme vyuˇz´ıt pˇr´ıkazy sp trace setstatus sledov´an´ı. Jakmile sledov´an´ı vytvoˇr´ıme muˇ a fn trace getinfo ke kontrole sledov´an´ı. Dalˇs´ı podrobnosti lze nal´ezt v books online 1 . • Spuˇstˇen´ı sledov´an´ı: execute sys.sp trace setstatus @traceid, 1; go • Zastaven´ı sledov´an´ı: execute sys.sp trace setstatus @traceid, 0; go execute sys.sp trace setstatus @traceid, 2; go • Zjiˇstˇen´ı @traceid dostupnych ´ sledov´an´ı: select * from sys.fn trace getinfo(0); go Vysledek sledov´an´ı se uloˇz´ı do specifikovan´eho trace souboru. Trace ´ ˚ soubor je d´al moˇzn´e vyuˇz´ıt dvˇe zpusoby: • Pouˇz´ıt jej jako zachycen´e vyt´ızˇ en´ı, kter´e se spust´ı znova. K tomuto je moˇzn´e vyuˇz´ıt sˇ ablonu TSQL Replay sˇ ablonu v oknˇe Trace Properties profileru 2 3 . • Soubor zanalyzovat a zobrazit uˇziteˇcn´e statistiky. (viz. kapitola 2.3) 1
http://msdn.microsoft.com/en-us/library/ms191006.aspx http://msdn.microsoft.com/en-us/library/ms189604.aspx 3 http://msdn.microsoft.com/en-us/library/ms187857.aspx 2
4
2.3
Analyza ´ trace souboru
Pro analyzu ´ trace souboru je moˇzn´e vyuˇz´ıt volnˇe dostupn´e n´astroje RML 4 . Tento bal´ık obsahuje tˇri z´akladn´ı n´astroje: • ReadTrace • Reporter • OStress Bal´ık je jiˇz na image SQL Server 1 instalov´an. Pokud vyuˇz´ıv´ate cˇ istou instalaci je potˇreba bal´ık nejprve instalovat. Pˇred analyzou trace souboru ´ je nutn´e sledov´an´ı nejprve zastavit. Po t´e v menu Start ⇒ Programy ⇒ RML Utilities for SQL Server spust’te RML Cmd Prompt a v nˇem spust’te pˇr´ıkaz: ReadTrace -I”CestaTraceSouboru”-o”VystupniAdresar”
Obr´azek 4: Hlavn´ı okno analyzy ´ v Reporter n´astroji 4
http://support.microsoft.com/kb/944837/en-us?fr=1
5
Obr´azek 5: Statistiky jednotlivych ´ SQL pˇr´ıkazu˚ ReadTrace provede analyzu ´ trace souboru, kter´a je uloˇzena v PerfAnalysis datab´azi a z´arovˇenˇ automaticky spust´ı Reporter, ktery´ tuto analyzu ´ ˚ zeme vidˇet pˇr´ıklad statistik z n´astroje otevˇre a zobraz´ı. Na obr´azku 4 a 5 muˇ ˚ Reporter. Tyto statistiky se zamˇerˇ uj´ı na z´akladn´ı parametry SQL dotazu, ˚ erny´ jako je poˇcet logickych ´ pˇr´ıstupu˚ (Reads), poˇcet z´apisu˚ (Write), prumˇ procesorovy´ cˇ as (Avg processor time), celkovy´ procesorovy´ cˇ as v mˇerˇ en´em intervalu (Total duration). Podrobnˇejˇs´ı informace je moˇzn´e z´ıskat z PerfAnalysis datab´aze pˇr´ımymi dotazy na uloˇzen´a data. ´ Pozn´amky: • Pro odeˇc´ıt´an´ı statistik v SQL Serveru mus´ıme prov´adˇet pˇreklad kaˇzd´eho pˇr´ıkazu zvl´asˇ t’. Nen´ı moˇzn´e pouze pˇr´ıkazy pˇredkompilovat a pak ˚ ymi jen spouˇstˇet s ruzn hodnotami jako v Oracle, jinak ve statistik´ach ´ ´ zachycenych nˇ jednotlivych ´ v trace souboru nebudeme moci j´ıt na urove ´ ˚ pˇr´ıkazu. 6
• Spouˇstˇen´ı ReadTrace obˇcas skonˇc´ı chybou. V z´asadˇe jsem narazil na ˚ dva duvody: Nen´ı ukonˇcen sbˇer dat, zkuste spustit skript Stop.sql. Selˇze pˇripojen´ı k SQL Serveru pˇres uˇzivatele user, po t´e se mi osvˇedˇcilo pouze ukonˇcen´ı SSMS.
2.4
Blokov´an´ı
ˇ ˚ zit´e ud´alosti v datab´azi. Jejich SSP n´am umoˇznuje sledovat i dalˇs´ı duleˇ nastaven´ı je moˇzn´e prov´est v druh´e z´aloˇzce Trace Properties (obr´azek 3). Pokud zaˇskrtneme moˇznost Show all events zobraz´ı se n´am dalˇs´ı ud´alosti jenˇz je moˇzn´e sledovat. Z tohoto seznamu vybereme poloˇzku Blocked process report, kter´e je v kategorii Errors and Warnings. Takto vytvoˇren´e sledov´an´ı jiˇz bude zachycovat i blokov´an´ı. D´ale je nutn´e nastavit parametr instance Blocked Process Treshold na nˇejakou hodnotu cˇ ek´an´ı pˇri jej´ımˇz pˇrekroˇcen´ı dojde k vyvol´an´ı varov´an´ı (ud´alosti). ˚ zete otestovat, pokud paralelnˇe spust´ıte ze SSMS tento kod: ´ Pak jiˇz muˇ begin transaction select * from aukce(xlock) where id = 15; waitfor delay ’00:00:30.0’ -- wait for 1 minute commit transaction Jelikoˇz SQL Server pouˇz´ıv´a pesimisticky´ pˇr´ıstup k zamyk´an´ı je dobr´e sledovat tak´e frekvenci blokov´an´ı jednotlivych ´ vl´aken, zda-li nedoch´az´ı k nepˇrimˇerˇ enˇe vysok´emu blokov´an´ı po nepˇrimˇerˇ enˇe dlouhou dobu.
2.5
Profiler a Performance Monitor
Do sledov´an´ı v SSP je moˇzn´e importovat tak´e data z performance moniˇ toru (PM), ktery´ je souˇca´ st´ı MS Windows. PM je n´astroj, ktery´ umoˇznuje sledovat statistiky OS a tyto data tak´e zaznamen´avat do logu. Zaznamen´av´an´ı dat do tzv. counter logu˚ je moˇzn´e v PM zah´ajit pokud v z´aloˇzce Performance Logs and Alerts vybereme poloˇzku Counter Logs a zde po kliknut´ı pravym ´ tlaˇc´ıtkem na System Overview vybereme Start. Z´aznamy jsou ukl´ad´any do
7
souboru specifikovan´em vlastnostmi toho logu. Implicitnˇe by to mˇel byt ´ adres´arˇ C:\ PerfLogs. Po zastaven´ı sledov´an´ı datab´aze, zastav´ıme tak´e ukl´ad´an´ı dat do PM logu. V SSP otevˇreme trace soubor s uloˇzenym ´ provozem. Po t´e klikneme na menu File ⇒ Import Performance Data a otevˇreme counter log vytvoˇreny´ v PM. V SSP se v´am otevˇre okno, kter´e bude vypadat podobnˇe jako na obr´azku 6 a kde budou zobrazeny statistiky z PM spolu s jednotlivymi ´ zachycenymi pˇr´ıkazy z SQL Serveru. ´
Obr´azek 6: Pˇr´ıkazy trace souboru zobrazen´e spolu s daty z Performance monitoru Po kliknut´ı na ud´alost v trace logu se n´am nastav´ı tak´e pozice cˇ erven´e cˇ a´ ry, ukazuj´ıc´ı koresponduj´ıc´ı statistiky z PM. To je moˇzn´e prov´est i naopak.
8
3
Data Collector
3.1
Konfigurace
Tento n´astroj je moˇzn´e konfigurovat v SSMS kdyˇz v z´aloˇzce Management ⇒ Data Collection klikneme pravym ´ tlaˇc´ıtkem a vybereme poloˇzku Configure Data Management Warehouse. Nejprve je potˇreba prov´est vytvoˇren´ı ˚ datab´aze do kter´e se budou statistiky ukl´adat. V pruvodci tedy vybereme moˇznost Create or upgrade a management data warehouse. V dalˇs´ım kroku vytvoˇr´ıme datab´azi DMW a pˇriˇrad´ıme aktu´aln´ımu uˇzivateli role nutn´e ´ esˇ nˇe vytvoˇrili Data management warepro sbˇer dat. Po dokonˇcen´ı jsme uspˇ house (DMW). Po t´e spust´ıme Configure Data Management Warehouse znovu a ten˚ tokr´at v pruvodci vybereme druhou moˇznost Set up data collection. V dalˇs´ım oknˇe vybereme datab´azi DMW, kterou jsme vytvoˇrili v pˇredchoz´ım kroku. Specifikujeme tak´e adres´arˇ , kde se budou ukl´adat data pˇred uloˇzen´ım do DMW datab´aze. Po potvrzen´ı zmˇen se automaticky zah´aj´ı sbˇer dat tˇr´ı implicitn´ıch collection set. Je moˇzn´e nastavit parametry jednotlivych ´ collection set, pokud na nˇe ˚ zeme klikneme pravym ´ tlaˇc´ıtkem a vybereme moˇznost Properties. Zde muˇ nastavit frekvenci nahr´av´an´ı (Non-cached vs. cached) a nebo dobu zachov´an´ı dat.
3.2
Reporty
Po uloˇzen´ı dat do datab´aze je moˇzn´e tyto data zobrazit pomoc´ı reportu˚ jenˇz jsou k dispozici pro jednotliv´e collection set.
ˇ an´ı a fronty 4 Cek´ podrobnˇejˇs´ı popis lze nal´ezt v dokumentu: Tom Davidson, Wait and queues 5 . 5
http://msdn.microsoft.com/en-us/library/cc966413.aspx
9
S touto kapitolou souvis´ı skripty uloˇzen´e v image v adres´arˇ i ’...\My Documents\scripts\WaitStat’. Pro diagnostiku cˇ ek´an´ı je moˇzn´e vyuˇz´ıt pohled sys.dm os wait stats (viz. skript PrintWaitStat.sql). Tento pohled obsahuje statistiky od spuˇstˇen´ı SQL Serveru. Pro vynulov´an´ı tˇechto statistik je nutn´e spustit pˇr´ıkaz: DBCC SQLPERF("sys.dm os wait stats",CLEAR);. ˚ ezˇ nˇe muˇ ˚ zeme vyuˇz´ıt Pokud budeme cht´ıt statistiky cˇ ek´an´ı sb´ırat prubˇ 6 skripty track waitstat 2005.sql a get waitstat 2005.sql 7 . Jako prvn´ı krok je potˇreba vytvoˇrit tabulku waitstat (skript create waitstat.sql) a po vytvoˇren´ı uloˇzenych ´ procedur je moˇzn´e sledov´an´ı wait statistik spustit skriptem StartWaitStat.sql. Ve vypisu, ktery´ n´am zobraz´ı procedura get waitstat 2005.sql ´ ˚ Ve druh´em dotazu jsou seˇrazeny jednotliv´e pˇr´ıcˇ iny vysledky tˇr´ı dotazu. ´ cˇ ek´an´ı. Jedna z pˇr´ıcˇ in cˇ ek´an´ı mohou byt ´ cˇ ast´e blokov´an´ı, pak se ve vypisu ´ ´ e izolace u jedobjevuj´ı poloˇzky LCK *. Je potˇreba zv´azˇ it nastaven´ı urovnˇ notlivych transakc´ ı a jejich d´ e lku. U SQL Serveru obecnˇ e plat´ ı, zˇ e transakce ´ by mˇely byt ´ co nejkratˇs´ı. Dalˇs´ı pˇr´ıcˇ inou mohou byt ´ cˇ ast´e pˇr´ıstupy na disk. V tom pˇr´ıpadˇe se v seznamu na pˇredn´ıch m´ıstech objevuje poloˇzka PAGEIOLATCH, ASYNC/IO COMPLETION, nebo WRITELOG. Posledn´ı poloˇzka souvis´ı se zapisov´an´ım do redo log souboru a v podstatˇe se d´a rˇ eˇsit jen um´ıstˇen´ım log soubor na rychlejˇs´ı disk, nebo alesponˇ na disk, ktery´ nen´ı zatˇezˇ ov´an dalˇs´ımi procesy. V posledn´ım dotazu je pomˇer mezi celkovym ´ ˚ cˇ ek´an´ım a cˇ ek´an´ım zpusoben ym ´ na CPU. Tato hodnota by nemˇela m´ıt vyˇssˇ´ı hodnotu neˇz 25% jinak nem´ame na dann´e vyt´ızˇ en´ı dostateˇcny´ pro˚ ze byt cesorovy´ vykon. Pochopitelnˇe tato hodnota muˇ ´ ´ zav´adˇej´ıc´ı, pokud ˚ ze doch´azet ke zbyteˇcnym nem´ate datab´azi dobˇre odladˇenu, muˇ ´ operac´ım, kter´e zamˇestn´avaj´ı procesor.
6
http://gallery.technet.microsoft.com/ScriptCenter/en-us/ 8c90bd2e-9def-4f44-bce4-e5dae4d86f71 7 http://gallery.technet.microsoft.com/ScriptCenter/en-us/ 86886b7f-8f75-4052-99d1-ef8ebd910a88
10
