Archiv autora: Jakub Rychlý

Čtení informací z default trace

Z default trace je možné vyčíst spoustu zajímavých událostí.

Database Data File Autogrow
Database Data File AutoShrink
Database Database Mirroring State Change
Database Log File Autogrow
Database Log File AutoShrink
Errors and Warnings ErrorLog
Errors and Warnings Hash Warning
Errors and Warnings Missing Column Statistics
Errors and Warnings Missing Join Predicate
Errors and Warnings Sort Warnings
Full text FT:Crawl Aborted
Full text FT:Crawl Started
Full text FT:Crawl Stopped
Objects Object:Altered
Objects Object:Created
Objects Object:Deleted
Performance Plan Guide Unsuccessful
Security Audit Audit Add DB User Event
Security Audit Audit Add Login to Server Role Event
Security Audit Audit Add Member to DB Role Event
Security Audit Audit Add Role Event
Security Audit Audit Addlogin Event
Security Audit Audit Backup/Restore Event
Security Audit Audit Change Audit Event
Security Audit Audit Change Database Owner
Security Audit Audit Database Scope GDR Event
Security Audit Audit DBCC Event
Security Audit Audit Login Change Property Event
Security Audit Audit Login Failed
Security Audit Audit Login GDR Event
Security Audit Audit Schema Object GDR Event
Security Audit Audit Schema Object Take Ownership Event
Security Audit Audit Server Alter Trace Event
Security Audit Audit Server Starts And Stops
Server Server Memory Change 
SELECT 
	d.StartTime,
	d.LoginName,
	d.HostName,
	d.ApplicationName,
	d.DatabaseName,
	d.ObjectName,
	d.textData,
	te.name
FROM sys.traces t
CROSS APPLY
fn_trace_gettable(t.path,default) d
LEFT JOIN sys.trace_events te ON d.EventClass = te.trace_event_id
WHERE is_default = 1
ORDER BY d.StartTime

Odhady počtu řádků a proměnné

Pojďme se podívat, co dělá query optimizer, když mu zabráníme používat statistiky.

Nejdříve si připravme tabulku pro testy a naplňme jí daty.

CREATE TABLE A(a INT, b CHAR(500))
GO

SET NOCOUNT ON
DECLARE @o INT = 1
DECLARE @p INT = 1

WHILE @o <= 50
BEGIN

	WHILE @p <= 200
	BEGIN
		INSERT INTO A(a,b) VALUES(@p,'x')
		SET @p += 1
	END
	
	SET @p = 1
	SET @o += 1
	
END
GO

Vytvoříme index, přičemž se nám vytvoří také statistika na základě všech hodnot ve sloupci a.

CREATE INDEX i_A ON A(a)
GO

Nyní vyzkoušíme jak SQL server pracuje, když může statistiky využít. U následujících dotazů na základě statistik server přesně odhadne počet vrácených řádků, což si můžeme ověřit v exekučním plánu.

--odhaduje 50 řádků, vrací 50 řádků
SELECT * FROM A WHERE a = 50

--odhaduje 500 řádků, vrací 500 řádků
SELECT * FROM A WHERE a <= 10

--odhaduje 1000 řádků, vrací 1000 řádků
SELECT * FROM A WHERE a <= 20

--odhaduje 3000 řádků, vrací 3000 řádků
SELECT * FROM A WHERE a <= 60

--odhaduje 150 řádků, vrací 150 řádků
SELECT * FROM A WHERE a BETWEEN 1 AND 3

sestatistikou

Pokud však místo konstanty v podmínce výběru použijeme proměnné, pak server v době kompilace dotazu nezná jejích hodnotu a nemůže podle statistiky tedy odvodit počet vrácených řádků. Server v tomto případě počty vrácených řádků odhaduje na základě použitého operátoru v podmínce a to nezávisle na hodnotě proměnné.

--odhaduje 50 řádků, vrací 50 řádků
DECLARE @a INT = 50
SELECT * FROM A WHERE a = @a
GO

--odhaduje 50 řádků, vrací 0 řádků
DECLARE @a INT = 500
SELECT * FROM A WHERE a = @a
GO

--odhaduje 3000 řádků, vrací 500 řádků
DECLARE @a INT = 10
SELECT * FROM A WHERE a <= @a
GO

--odhaduje 3000 řádků, vrací 1000 řádků
DECLARE @a INT = 20
SELECT * FROM A WHERE a <= @a
GO

--odhaduje 3000 řádků, vrací 3000 řádků
DECLARE @a INT = 60
SELECT * FROM A WHERE a <= @a
GO

--odhaduje 3000 řádků, vrací 0 řádků
DECLARE @a INT = 200
SELECT * FROM A WHERE a > @a
GO

--odhaduje 900 řádků, vrací 150 řádků
DECLARE @a INT = 1
DECLARE @b INT = 3
SELECT * FROM A WHERE a BETWEEN @a AND @b

Pojďme si vysvětlit jak k takovým odhadům server dojde. Sloupec a obsahuje 10 000 hodnot a 200 jedinečných hodnot. Statistika i_A obsahuje hodnotu All density, která se vypočte jako 1 / počet jedinečných hodnot, tedy v našem případě 1 / 200 = 0.005.

statistika

Pro operátor rovnosti server odhaduje počet vrácených řádků jako All density * počet řádků. V našem případě 0.005 * 10 000 = 50.
Pro operátory >, <, >=, <= odhaduje počet vrácených řádků jako 30% z celkového počtu řádků, tedy 0.3 * 10 000 = 3000.
Pro operátor BETWEEN odhaduje server počet vrácených řádků jako 9% z celkového počtu řádků, tedy 0.09 * 10 000 = 900.

Přesně takto kvalitní odhady server používá, pokud vnější proměnné z procedury předáme do vnitřních proměnných a snažíme se tak zamezit parametr sniffingu.

Nalezení problémových dotazů 

SELECT 
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (CASE WHEN qs.statement_end_offset = -1 
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                       ELSE qs.statement_end_offset end -
                            qs.statement_start_offset
                 )/2
             ) AS query_text
, (total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms] 
, max_elapsed_time/1000 AS [MaxExecTime in ms]
, min_elapsed_time/1000 AS [MinExecTime in ms]
, (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
, max_logical_reads AS MaxLogicalReads
, min_logical_reads AS MinLogicalReads
, max_logical_writes AS MaxLogicalWrites
, min_logical_writes AS MinLogicalWrites
, qs.execution_count AS NumberOfExecs
, qs.total_rows
, qs.last_rows
, qs.min_rows 
, qs.max_rows
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE qt.dbid = db_id()
ORDER BY (total_elapsed_time/execution_count) DESC;

Statistiky v MSSQL serveru

Podrobnosti o existující statistice je možné zjistit v

  • SSMS – Object Explorer – statistics – properties – Details.
  • systémových pohledech sys.stats a sys.stats_columns
  • DBCC SHOW_STATISTICS(<table_name>, <stats_name>) [WITH HISTOGRAM]

Co statistika obsahuje?

All Density – 1 / (počet jedinečných hodnot)

Statistika má maximálně 200 řádků.

Řádek statistiky obsahuje:

  • RANGE_HI_KEY – Horní mez intervalu kroku histogramu.
  • RANGE_ROWS – počet řádků v intervalu, mimo horní mez
  • EQ_ROWS – počet řádků odpovídající horní mezi intervalu
  • DISTINCT_RANGE_ROWS – počet jedinečných hodnot v intervalu, mimo horní mez
  • AVG_RANGE_ROWS – průměrná hodnota počtu jedinečných hodnot, mimo horní mez. Tedy RANGE_ROWS / DISTINCT_RANGE_ROWS

Pokud v exekučním plánu vidíme velmi rozdílnou hodnotu v údaji Estimated Number of Rows a Actual Number of Rows, pak máme problém se statistikami.

Pokud je na úrovni SQL serveru zapnuto Auto Create Statistics a Auto Update Statistics (a tyto hodnoty by měly být zapnuty), pak pokud server při vytváření exekučního plánu zjistí chybějící statistiky respektive zastaralé statistiky, tak dojde k vytvoření potřebné statistiky respektive k občerstvení zastaralé statistiky. To může zdržovat při vykonávání dotazu. Odchytit update statistik při vykonání dotazu je možné Profilerem – událost Performance/Auto Stat.

Při rebuildu indexu dojde k aktualizaci statistik s ním spojených. V tomto případě se provede fullscan, tedy statistiky se vypočtou ze všech řádků a dostaneme tak nejpřesnější možné statistiky. Po tomto není vhodné aktualizovat statistiky s implicitním nastavením, výsledek by byl horší než při použití fullscan.

Server automaticky (pokud je zapnuto Auto Create Statistics) vyrábí statistiky, které potřebuje pro vytvoření plánu vykonávání dotazu. Statistiky vyrábí jen pro jednotlivé sloupce. Automaticky není schopen vyrobit statistiky pro kombinací sloupců. V určitých případech může být vhodné takovéto statistiky vytvořit manuálně, nebo za použití Database Tuning Advisor.

Filtrované statistiky

S vytvořením filtrovaného indexu se vytvoří i filtrovaná statistika.
Filtrované statistiky je možné vytvářet i manuálně.
Výhodou je možnost obejít limit 200 řádků statistiky tak, že vytvoříme dvě statistiky v různých intervalech hodnot. Například jednu pro 90% historických dat a jednu podrobnější pro 10% aktuálních a často používaných dat.
Nevýhodou je, že pokud používáme filtrované statistiky nad sloupcem a pokud server zjistí, že chybí nefiltrovaná statistika, pak nefiltrovanou statistiku automaticky vytvoří. S tím souvisí režie s udržováním dvojích statistik. Doporučuje se ponechat zapnuté automatické tvoření statistik a u nefiltrované statistiky vypnout automatický přepočet (nastavit NORECOMPUTE) a nechat ji zestárnout, server pak bude používat aktuální filtrované statistiky.

Automatický update statistik

Řídí se nastavením serveru:
Auto Update Statistics – zapíná automatickou aktualizaci
Auto Update Statistics Asynchronously – zapíná asynchronní aktualizaci statistik, implicitně vypnuto

Synchronní aktualizace – Pokud se při sestavování plánu zjistí zastaralá statistika, pak se okamžitě provede její aktualizace. Výhodou je plán na základě vždy aktuálních statistik. Nevýhodou je, že aktualizace statistik zdržuje sestavení plánu. Toto nastavení se doporučuje.
Asynchronní aktualizace – Plán se sestaví na základě dostupných statistik. Pokud se zjistí zastaralá statistika, pak je asynchronně odpálena její aktualizace. Výhodou je, že sestavení plánu není zdržováno výpočtem statistik. Nevýhodou je, že plán může být postaven na základě neaktuálních statistik.

Statistiky jsou zastaralé když

  • tabule má více než 500 řádků a je modifikováno více než 20% řádků + další 500 řádků
  • tabule má méně nebo rovno 500 řádků a je modifikováno 500 řádků
  • pokud se počet řádků tabule změní z 0 na nenulový počet
  • u temporarních tabulí po každých změněných 6 záznamech

Filtrované statistky se vyhodnocují bohužel podle počtu modifikací všech řádků a ne jen těch řádků zahrnutých do filtru. Z toho vyplývá nutnost manuální aktualizace filtrovaných statistik.

Zda jsou statistiky zastaralé se vyhodnocuje při sestavování plánu vykonání dotazu.

Chybějící statistiky

Je možné odhalit profilerem zachycením události Errors and Warnings/Missing Column Statistics
Příčinou může být vypnutá volba serveru Auto Create Statistics.
Druhou příčinou může být používání tabulkových proměnných pro které server statistiky nevyrábí (odhadovaný počet řádků je vždy 1 respektive 0 při nesplnitelné podmínce). Mělo by být pravidlem, že tabulkové proměnné se používají jen pro tabulky do 100 řádků. Pro větší tabulky je lépe využívat temporární tabuli (s ‚#‘ na začátku názvu).

Nemožnost použít statistiky

Při použití proměnných v podmínkách výběru nemůže server vyžít statistiky, protože nezná hodnotu proměnné v době sestavování plánu. Pro odhadovaný počet řádků tak server použije průměrnou hodnotu z celé tabulky, která se může diametrálně lišit od hodnoty odpovídající hodnotě proměnné, můžeme tak dostat chybný plán vykonání.

Příliš hrubé statistiky

Pro 4 000 000 záznamů dostaneme 200 řádků statistiky po 20 000 záznamech. Z toho může být 90% historických dat, pro které se statistiky nemění. Řešením je udělat dvě statistiky. Jednu pro historická data, tu není potřeba často udržovat. A jednu pro aktuální živá data. Statistika pro 10% živých dat tak bude jemněji dělena a bude muset být manuálně častěji udržována aktuální.

Nedostatečné automatické aktualizace

20% modifikovaných řádků je málo pro odpálení automatické aktualizace u tabulí s velkým počtem řádků. Nejhorší je to u primárních klíčů, které přibývají na konci řady, tedy mimo statistku a server je tak nemůže vyhodnotit. Zde je nutná častější manuální aktualizace statistiky.

Statistiky a paměť

Statistiky jsou používány pro odhad velikosti potřebné paměti pro některé druhy joinu. Pokud je odhad podhodnocen, pak je místo chybějící paměti využívána tempdb a dochází k cca 10 násobnému zpomalení. Tento problém je možné zachytit profilerem jako události Errors and Warnings/Sort Warnings a Errors and Warnings/Hash Warnings

Zdroje

SQL Server Statistics

Parametr sniffing – experimenty

Jako parametr sniffing se označuje chování SQL serveru, kdy při prvním spuštění procedury server očichá hodnoty vstupních parametrů a na jejich základě postaví exekuční plán, který pak používá při každém dalším spuštění procedury. Ve většině případů je to v pořádku. Ušetří se čas na sestavování exekučního plánu. Pokud však máme proceduru, která na základě různých vstupních parametrů vrací tak rozdílné sady výsledků, že by zasloužily i rozdílné exekuční plány, pak máme problém. Prozkoumal jsem pár možností jak přesvědčit server sestavit odpovídající exekuční plán.

Nejdříve si vytvoříme podkladovou tabulku s daty.

CREATE TABLE tabule(
	id INT IDENTITY(1,1) CONSTRAINT pk_tabule PRIMARY KEY,
	data NVARCHAR(max) NULL,
	a INT
)


--vygeneruji 200 000 záznamů, 
--sloupec [a] naplním náhodným číslem s maximální hodnotou 10 000
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 200000
BEGIN
	PRINT @counter
	INSERT INTO tabule (a) SELECT CAST(RAND() * 10000 AS INT)
	SET @counter+=1
END

--naplním tabulku daty, aby bylo bolestivé číst celý clusterovaný index
UPDATE tabule SET data = N'
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'

--vytvořím index na sloupci [a]
CREATE INDEX ix_tabule_a ON tabule(a)

Na jednoduchých dotazech si ověřím, který přistup je nejoptimálnější.

SELECT * FROM tabule WHERE 1 < a AND a < 10
--Table 'tabule'. Scan count 1, logical reads 452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

V prvním případě jsem se snažil načíst pouze malou část tabulky. Server optimálně použil index seek nad indexem ix_tabule_a pro vyhledání záznamů vyhovujících podmínce a následně key lookup pro načtení zbylých dat tabulky. Server k tomu potřeboval 452 logických čtení.
selectSNIFF1

SELECT * FROM tabule WHERE 1 < a AND a < 10000
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ve druhém případě chceme vrátit velké množství dat. Server optimálně zvolil index scan nad indexem primárního klíče ze kterého načetl všechna data. Server k tomu potřeboval 99845 logických čtení.
selectSNIFF2

SELECT * FROM tabule WITH (INDEX (ix_tabule_a)) WHERE 1 < a AND a < 10000
--Table 'tabule'. Scan count 1, logical reads 612740, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ve třetím pokusu se serveru snažím vnutit index seek nad indexem ix_tabule_a pro načtení všech dat. Z počtu logických čtení (612740) opravdu vidíme, že tento přístup je několikanásobně horší než přístup předchozí.
selectSNIFF3

Konečně vytvoříme několik procedur, kde každá bude používat jiný přístup k problému.

IF  OBJECT_ID('p_sniff1') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff1]
GO
--klasicky parametr sniffing
CREATE PROCEDURE [dbo].[p_sniff1]
	@a INT,
	@b INT
AS
BEGIN
	SELECT * FROM tabule WHERE a > @a  AND a < @b
END

GO


IF  OBJECT_ID('p_sniff2') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff2]
GO

--vnitrni promenne
CREATE PROCEDURE [dbo].[p_sniff2]
	@a VARCHAR(30),
	@b VARCHAR(30)
AS
BEGIN
	DECLARE @_a VARCHAR(30)
	DECLARE @_b VARCHAR(30)
	SET @_a = @a
	SET @_b = @b
	SELECT * FROM tabule WHERE a > @_a  AND a < @_b
END

GO

IF  OBJECT_ID('p_sniff3') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff3]
GO

--skladany dotaz
CREATE PROCEDURE [dbo].[p_sniff3]
	@a INT,
	@b INT
AS
BEGIN
	DECLARE @prikaz NVARCHAR(max)
	SET @prikaz = 'SELECT * FROM tabule WHERE a > '+CAST(@a AS VARCHAR(30))+' AND a < '+CAST(@b AS VARCHAR(30))
	PRINT @prikaz
	EXEC(@prikaz)
END

GO

IF  OBJECT_ID('p_sniff4') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff4]
GO

--WITH RECOMPILE
CREATE PROCEDURE [dbo].[p_sniff4]
	@a INT,
	@b INT
WITH RECOMPILE
AS
BEGIN
	SELECT * FROM tabule WHERE a > @a  AND a < @b
END

GO

Pak procedury spustíme s následujícími parametry.

EXEC p_sniff1 @a = 1, @b = 10000--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff1 @a = 1, @b = 10--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff2 @a = 1, @b = 10000--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 612740, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff2 @a = 1, @b = 10--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff3 @a = 1, @b = 10000--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff3 @a = 1, @b = 10--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff4 @a = 1, @b = 10000--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff4 @a = 1, @b = 10--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Procedura p_sniff1 používá klasický parametr sniffing. Při prvním spuštění byl sestaven exekuční plán využívající index scan nad pk_tabule, což je správně. Při druhém spuštění se tohoto plánu stále drží a to je problém, protože při výběru pár hodnot by byl výhodnější index seek nad ix_tabule_a.

Na internetu je možné najít, že pro řešení problému s parametr sniffingem se dá použít metoda s deklarací vnitřních proměnných v proceduře. Toto zkouším v proceduře p_sniff2. Už ale první spuštění pro celý rozsah sloupce a skončilo s exekučním plánem využívajícím index seek nad indexem ix_tabule_a, což je blbost. Server přečetl index a navíc ještě celou tabuli. Pokud se podíváme na exekuční plán a na tooltip nad ikonou index seek, pak se dozvíme, že Estimated Number of Rows je 18 000 v obou případech a Actual Number of Rows je 199 960 v prvním případě (@a = 1, @b = 10000) a 147 ve druhém případě (@a = 1, @b = 10). Příčinou je, že server nemůže kvůli použití vnitřních proměnných vhodně využít statistiky a učiní tak odhad na základě průměrného počtu jedinečných řádků v celé tabulce. Tento odhad je pro oba případy 18 000 řádků. Na základě tohoto odhadu vybral server index seek. V prvním případě byl tento odhad velmi vzdálen 199 9600 vráceným řádkům a využití index seek bylo naprosto nevhodné. Ve druhém případě i navzdory tomu, že se odhad velmi lišil od hodnoty 147 vybraných řádků bylo použití index seek vhodné. Použití vnitřních proměnných považuji za absolutně nevhodné, protože server nemůže optimálně vyžít statistiky. Více o příčinách v článku Odhady počtu řádků a proměnné. Více o statistikách v článku Statistiky v MSSQL serveru.

Procedura p_sniff3 používá skládaný dotaz. Exekuční plán dostaneme správný pro obě volání. Ve skutečnosti server cachuje plán pro každou kombinaci vstupních parametrů. Je potřeba zvážit, zda je vhodné takto plnit cache.

Procedura p_sniff4 používá hint WITH RECOMPILE, čímž donutí server poskládat správný exekuční plán při každém spuštění procedury. Ztratíme výhodu nacachovaného exekučního plánu, ale zato vždy dostaneme plán správný. Pokud potřebujeme pro různé vstupní parametry procedury různé exekuční plány, přijde mi toto řešení jako ideální varianta.

Pokrývací index

Nutno dodat, že všechny problémy by se zde daly vyřešit pokrývacím indexem, který by byl schopen vrátit všechny požadované sloupce a kde jedinou správnou variantou je index seek nad tímto indexem. Pak odpadá problém, že pro různé vstupy je potřeba různý exekuční plán.

CREATE INDEX ix_tabule_covering ON tabule(a) INCLUDE(data)

EXEC p_sniff1 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
EXEC p_sniff2 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
EXEC p_sniff3 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
EXEC p_sniff4 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Statistiky spuštění reportů

V databázi report serveru je užitečný pohled ExecutionLog3. Umožňuje získat informace o spouštění reportů, o tom, kteří uživatelé reporty spouští a s jakými parametry, jak dlouho trvalo načtení dat, jestli report nezhavaroval atd.

USE ReportServer

SELECT * FROM ExecutionLog3

--nejcasteji spoustene reporty
SELECT ItemPath, COUNT(*) pocetSpusteni 
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
GROUP BY ItemPath 
ORDER BY pocetSpusteni DESC

--nejaktivnejsi uzivatele
SELECT UserName, COUNT(*) pocetSpusteni 
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
GROUP BY UserName
ORDER BY pocetSpusteni DESC

--nejaktivnejsi uzivatele podle reportu
SELECT ItemPath, UserName, COUNT(*) pocetSpusteni 
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
GROUP BY ItemPath, UserName
ORDER BY ItemPath, pocetSpusteni DESC

--reporty s chybou
SELECT ItemPath, UserName, TimeStart
FROM ExecutionLog3 
WHERE Status <> 'rsSuccess'
ORDER BY TimeStart DESC

--reporty s dlouhym nacitanim dat
SELECT ItemPath, UserName, TimeStart,TimeDataRetrieval
FROM ExecutionLog3 
WHERE ItemAction = 'Render'
ORDER BY TimeDataRetrieval DESC

--parametry spusteni
;WITH parametry(ItemPath, UserName, TimeStart, parametry) AS (
	SELECT ItemPath, UserName, TimeStart, CAST('<root><parametr><nazev>'+REPLACE(REPLACE(CAST([Parameters] AS VARCHAR(MAX)),'=','</nazev><hodnota>'),'&','</hodnota></parametr><parametr><nazev>')+'</hodnota></parametr></root>' AS XML) parametry
	FROM ExecutionLog3 
	WHERE ItemAction = 'Render' AND ISNULL(CAST([Parameters] AS NVARCHAR(MAX)),'') <> '' 
)
SELECT ItemPath, 
	UserName, 
	TimeStart, 
	T.p.value('nazev[1]','VARCHAR(50)') parametr, 
	T.p.value('hodnota[1]','VARCHAR(50)') hodnota
FROM parametry
CROSS APPLY parametry.nodes('/root/parametr') AS T(p)

Indexed view

Mějme špatně navrženou tabulku

CREATE TABLE testpohledu (id INT IDENTITY(1,1),data NVARCHAR(50))
GO

--naplníme daty
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 100000
BEGIN
	INSERT INTO testpohledu(data)
	SELECT CHAR(ROUND(RAND()*25,2)+65)+cast(round(RAND()*10000,0) as varchar(30))
	SET @counter += 1
END

A pak mějme proceduru, které prohledává testpohledu.data a hledá číslo, nehledíc na první znak.

SELECT * FROM testpohledu WHERE SUBSTRING(data,2,5) = '9601'

Jenže tohle hledání je pomalé. I kdybychom na sloupci data měli index, tak by nám byl k ničemu, protože index je seřazený od prvního znaku. Co teď? Zákazník si stěžuje na pomalost, přidat vypočtený a oindexovaný sloupec nemůžeme, protože kdoví kde všude programátoři narvali * a co by se tím všechno rozbilo…

Zbývá indexovaný pohled

CREATE VIEW v_testpohledu WITH SCHEMABINDING
AS
SELECT id,data,CAST(SUBSTRING(data,2,5) AS VARCHAR(30)) subst FROM dbo.testpohledu
GO

CREATE UNIQUE CLUSTERED INDEX ix_v_testpohledu ON v_testpohledu(subst,data,id)
GO

Podle teorií by tohle samo o sobě mělo stačit, ale nestačí…
EDIT: Takže podle teorií tohle funguje u verze 2008 jen v edici Enterprise .

SELECT * FROM testpohledu WHERE SUBSTRING(data,2,5) = '9601'
--logical reads 348

SELECT id, data FROM v_testpohledu WHERE subst = '9601'
--logical reads 348

SELECT id, data  FROM v_testpohledu WITH (NOEXPAND) WHERE subst = '9601'
--logical reads 3

Pomohlo až přepsání procky pro pro použití pohledu a hint NOEXPAND, který přinutil server použít index seek na indexu ix_v_testpohledu a nečíst zdrojovou tabulku.

Jak ovlivnit zámky na úrovni definice tabulky nebo indexu

LOCK_ESCALATION

SQL server využívá eskalaci zámků na vyšší úroveň aby ulevil režii se zámky. Například, pokud server drží příliš mnoho zámků na řádcích, pak dojde k eskalaci zámků na stránku a zámky na řádcích jsou uvolněny.
Eskalace zámků může způsobit blokování zdrojů, protože takto může být zamčeno více řádků než je nutné.

Na úrovni tabulky můžeme ovlivnit, jestli server bude eskalaci zámků používat.

--vypne eskalaci zámků pro většinu případů
ALTER TABLE tabule SET (LOCK_ESCALATION = DISABLE)

--eskalace na úroveň partition (pokud existuje) nebo tabulky
ALTER TABLE tabule SET (LOCK_ESCALATION = AUTO)

--eskalace na úroveň tabulky
ALTER TABLE tabule SET (LOCK_ESCALATION = TABLE)

ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS

Na úrovni indexu (a to i clusterovaného) můžeme ovlivnit, zda server může používat zámky stránek a zámky řádků.

--povolí zámky stránek
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_PAGE_LOCKS  = ON )

--povolí zámky řádků
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_ROW_LOCKS  = ON )

--zakáže zámky stránek
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_PAGE_LOCKS  = OFF )

--zakáže zámky řádků
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_ROW_LOCKS  = OFF )

Rychlokurz XML v T-SQL

Funkce nad xml

  • query() – dotaz nad XML, výsledkem je opět XML
  • value() – dotaz nad XML, vrátí hodnotu definovaného datového typu
  • exists() – test, zda dotaz vrátí neprázdnou hodnotu
  • modify() – modifikace XML
  • nodes() – dotaz nad XML, výsledkem je tabulka s jedním sloupcem typu XML

Do XQuery dotazu můžeme dostat SQL proměnnou díky funkci sql:variable(„@nazevPromenne“) nebo hodnotu z tabulky díky funkci sql:column(„tabulka.sloupec“) .

DECLARE @xml XML
SET @xml = '
<zamestnanci xmlns="nejaky-namespace">
	<oddeleni typ="vyvoj">
		<zamestnanec id="1">
			<jmeno>Jarmil</jmeno>
			<prijmeni>Novák</prijmeni>
			<plat premie="ne" xmlns="dalsi-namespace">20000</plat>
		</zamestnanec>
		<zamestnanec id="2">
			<jmeno>Vlastimil</jmeno>
			<prijmeni>Hujer</prijmeni>
			<plat premie="ano" xmlns="dalsi-namespace">40000</plat>
		</zamestnanec>
	</oddeleni>
	<oddeleni typ="implementace">
		<zamestnanec id="3">
			<jmeno>Petr</jmeno>
			<prijmeni>Zeman</prijmeni>
			<plat premie="ne" xmlns="dalsi-namespace">50000</plat>
		</zamestnanec>
	</oddeleni>
</zamestnanci>
'

Vypořádání se s namespace

Přečtení jedné konkrétní hodnoty, deklarace defaultního namespace

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace' 
)
SELECT @xml.value('(/zamestnanci/oddeleni[@typ="vyvoj"]/zamestnanec[@id="2"]/prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, deklarace defaultního namespace

SELECT @xml.value('
declare default element namespace "nejaky-namespace";
(/zamestnanci/oddeleni[@typ="vyvoj"]/zamestnanec[@id="2"]/prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, jakýkoliv namespace

SELECT @xml.value('(/*:zamestnanci/*:oddeleni[@typ="vyvoj"]/*:zamestnanec[@id="2"]/*:plat)[1]','VARCHAR(50)') AS plat
/*
plat
--------------------------------------------------
40000
*/

Přečtení jedné konkrétní hodnoty, deklarace namespace

SELECT @xml.value('
	declare namespace N="nejaky-namespace";
(/N:zamestnanci/N:oddeleni[@typ="vyvoj"]/N:zamestnanec[@id="2"]/N:prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, deklarace dvou namespace

SELECT @xml.value('
	declare namespace N="nejaky-namespace";
	declare namespace P="dalsi-namespace";
(/N:zamestnanci/N:oddeleni[@typ="vyvoj"]/N:zamestnanec[@id="2"]/P:plat)[1]','VARCHAR(50)') AS plat
/*
plat
--------------------------------------------------
40000
*/

Čtění z XML

Vypsání všech hodnot uzlu na konkrétní úrovni, absolutní adresa

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat, --používám "dalsi-namespace"
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie --používám "dalsi-namespace"
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x) --dohledání uzlu, ke kterému se bude vztahovat funkce value
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Vlastimil                                          Hujer                                              40000       ano
Petr                                               Zeman                                              50000       ne
*/

vypsání všech hodnot uzlu na konkrétní úrovni, relativní adresa

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat, 
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie 
FROM @xml.nodes('//zamestnanec') x(x)--relativní adresa
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Vlastimil                                          Hujer                                              40000       ano
Petr                                               Zeman                                              50000       ne
*/

podmínka na hodnotu atributu

;WITH XMLNAMESPACES(
    'dalsi-namespace' AS P,
    DEFAULT 'nejaky-namespace'
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec[@id="1"]') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
*/

podmínka na hodnotu atributu ve větší úrovni zanoření

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec/P:plat[@premie="ne"]/..') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Petr                                               Zeman                                              50000       ne
*/

podmínka ve where

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x)
WHERE x.value('P:plat[1]/@premie','VARCHAR(3)') = 'ano'
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Vlastimil                                          Hujer                                              40000       ano
*/

čtění na základě proměnné

DECLARE @promenna INT
SET @promenna = 50000

--čtění na základě proměnné, jednoduše ve where klauzuli
;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x)
WHERE x.value('P:plat[1]','INT') = @promenna
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Petr                                               Zeman                                              50000       ne
*/

čtění na základě proměnné, sql proměnná protažená do XQuery výrazu

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec/P:plat1/..') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Petr                                               Zeman                                              50000       ne
*/

mazání

smazání uzlu

DECLARE @smaz XML
SET @smaz = @xml
SET @smaz.modify('delete /*:zamestnanci/*:oddeleni[@typ="vyvoj"]') --jakykoliv namespace
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
	<oddeleni typ="implementace">
		<zamestnanec id="3">
			<jmeno>Petr</jmeno>
			<prijmeni>Zeman</prijmeni>
			<plat xmlns="dalsi-namespace" premie="ne">50000</plat>
		</zamestnanec>
	</oddeleni>
</zamestnanci>
*/

smazání uzlu s deklarací dafaultniho namespace

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	delete /zamestnanci/oddeleni[@typ="vyvoj"]'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

smazání atributu

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	declare namespace P = "dalsi-namespace";
	delete /zamestnanci/oddeleni/zamestnanec/P:plat/@premie
	'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="vyvoj">
    <zamestnanec id="1">
      <jmeno>Jarmil</jmeno>
      <prijmeni>Novák</prijmeni>
      <plat xmlns="dalsi-namespace">20000</plat>
    </zamestnanec>
    <zamestnanec id="2">
      <jmeno>Vlastimil</jmeno>
      <prijmeni>Hujer</prijmeni>
      <plat xmlns="dalsi-namespace">40000</plat>
    </zamestnanec>
  </oddeleni>
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

modifikace

bez cyklu lze modifikovat jen jednu hodnotu

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	declare namespace P = "dalsi-namespace";
	replace value of (/zamestnanci/oddeleni/zamestnanec[@id=2]/P:plat/text())[1]
	with "10000"
	'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="vyvoj">
    <zamestnanec id="1">
      <jmeno>Jarmil</jmeno>
      <prijmeni>Novák</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">20000</plat>
    </zamestnanec>
    <zamestnanec id="2">
      <jmeno>Vlastimil</jmeno>
      <prijmeni>Hujer</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ano">10000</plat>
    </zamestnanec>
  </oddeleni>
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

Další zdroje
http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx
http://www.w3schools.com/xpath/default.asp
http://www.w3schools.com/xquery/default.asp

Tabulka použitá v reportech

Často se stane, že změníme strukturu databázové tabulky a nevíme, které reporty tím rozbijeme. Následujícím dotazem nad databází report serveru můžeme dohledat reporty, které v příkazu obsahují hledaný text, třeba název tabulky, kterou jsme změnili.

DECLARE @text varchar(250)
SET @text = 'hledany text'

;WITH xmlCatalog AS
(
	SELECT
		 Name
		,Path
		,CONVERT(xml,CONVERT(varbinary(max),content)) AS Content
	FROM ReportServer.dbo.Catalog
	WHERE Type  = 2
)
SELECT 
	name,
	path,
	Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM xmlCatalog
CROSS APPLY xmlCatalog.Content.nodes('//*:Query') Queries(Query)
WHERE Query.value('(./*:CommandText/text())[1]','nvarchar(max)') like '%'+@text+'%'