Archiv pro měsíc: Září 2013

Dohledání kandidátů pro sparse column

Dohledejme si sloupce, které jsou nullable a vyhovují podmínkám pro řídké (sparse) sloupce.

SELECT s.name [schema], o.name [table], c.name [column], c.is_nullable, c.is_sparse, t.name dataType, c.max_length, 0 nullRows, 0 notNullRows, CAST(0 AS NUMERIC(18,2)) ratio
INTO #sparseCandidate
FROM sys.objects o 
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.columns c ON c.object_id = o.object_id
JOIN sys.types t ON c.system_type_id = t.user_type_id
WHERE o.type  = 'U' --user table
AND c.is_nullable = 1
AND t.name NOT IN ('geography', 'geometry', 'image', 'ntext', 'text', 'timestamp')
AND c.is_sparse = 0
AND c.is_nullable = 1
AND c.is_rowguidcol = 0
AND c.is_identity = 0
AND c.is_computed = 0
AND c.is_filestream = 0
AND c.default_object_id = 0
AND c.rule_object_id = 0
AND NOT EXISTS(--nemuze byt v unikatnim klici ani v clusterovanem indexu
	SELECT 1
	FROM sys.indexes i
	JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
	WHERE o.object_id = i.object_id AND c.object_id = ic.object_id AND c.column_id = ic.column_id
	AND (
		i.is_primary_key = 1
		OR i.type = 1 --clustered
	)
)

Dohledáme zaplnění sloupců hodnotami.

DECLARE @schema CHAR(100)
DECLARE @table CHAR(100)
DECLARE @column CHAR(100)
DECLARE @command NVARCHAR(MAX)

DECLARE crs CURSOR FOR SELECT [schema], [table], [COLUMN] FROM #sparseCandidate
OPEN crs
FETCH NEXT FROM crs INTO @schema, @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @command = '
		UPDATE #sparseCandidate 
		SET nullRows = (
			SELECT COUNT(*)
			FROM '+@schema+'.'+@table+'	WHERE '+@column+' IS NULL
		),
		notNullRows = (
			SELECT COUNT(*)
			FROM '+@schema+'.'+@table+'	WHERE '+@column+' IS NOT NULL
		)
		WHERE [schema] = '''+@schema+''' AND [table] = '''+@table+''' AND [column] = '''+@column+'''
	'
	PRINT @command
	EXEC (@command)
	FETCH NEXT FROM crs INTO @schema, @table, @column
END

CLOSE crs
DEALLOCATE crs

Dopočteme poměr.

UPDATE #sparseCandidate 
SET ratio = CAST( nullRows * 100 / (nullRows + notNullRows) AS NUMERIC(18,2)) 
WHERE nullRows <> 0 AND notNullRows <> 0

Řekněme, že nás zajímají sloupce s poměrem nad 70%. Tyto sloupce je vhodné nastavit jako sparse a případně doplnit index o filtr na NOT NULL.

SELECT * 
FROM #sparseCandidate
WHERE ratio > 70
ORDER BY ratio DESC

Clustered index – identity sloupec

Základním vzorem pro tvorbu clusterovaného indexu je identity sloupce. Clusterovaný index je postaven nad primárním klíčem, který je tvořen identity sloupcem.

Příkladem buď tabulka pro hlavičku faktury:

CREATE TABLE faktura(
	id_faktura INT IDENTITY(1,1),
	cislo_faktury CHAR(20),
	CONSTRAINT pk_faktura PRIMARY KEY CLUSTERED(id_faktura)
)

Clustered index – cizí klíč

Podívejme se na vzor pro clusterovaný index, který je možné s výhodou použít u tabulek, které mají vazbu 1:N (master/detail, hlavička/řádky). Vzor cizí klíč se zde použije pro závislou tabulku, tedy pro detaily, nebo řádky.

Mějme tabulku pro fakturu, která používá vzor identity sloupec.

CREATE TABLE faktura(
	id_faktura INT IDENTITY(1,1),
	cislo_faktury CHAR(20),
	CONSTRAINT pk_faktura PRIMARY KEY CLUSTERED(id_faktura)
)

A dále vytvořme tabulku pro řádky faktury, která využije vzor cizí klíč.

CREATE TABLE faktura_radek(
	id_faktura_radek INT IDENTITY(1,1),
	id_faktura INT NOT NULL,
	nazev_polozky NVARCHAR(500),
	castka DECIMAL(18,2),
	CONSTRAINT pk_faktura_radek PRIMARY KEY NONCLUSTERED(id_faktura_radek),
	CONSTRAINT clus_faktura_radek UNIQUE CLUSTERED(id_faktura, id_faktura_radek),
	CONSTRAINT fk_faktura_faktura_radek FOREIGN KEY (id_faktura) REFERENCES faktura(id_faktura)
)

U tohoto vzoru je na primární klič použit neclusterovaný index. Clustrovaný index je pak postavený nad kombinací sloupců pro cizí klíč a primární klíč. Umožňuje tak serveru rychle vyhledat položky faktury a všechny její sloupce při jediném průchodu clusterovaným indexem.

Non-clustered index – základní otázky

Při vytváření neclusterovaných indexů je potřeba si zodpovědět několik důležitých otázek.

Jaká je frekvence změn v kličových sloupcích neclusterovaného indexu?
Čím více změn, tím více je nutných přesunů položek v B-stromu indexu, což znamená režii navíc.

Které často prováděné dotazy má index zlepšit?

Jakou business logiku index řeší?
Index je důležitý i pokud je využit jednou měsíčně, ale řeší důležitou aplikační úlohu.

Jaké jsou náklady na údržbu vs. přínos indexu?
Je potřeba zvážit zvýšenou režii při udržování indexu, zabrané místo, čas pro defragmentaci indexu se skutečným přínosem indexu.

Clustered Index – základy

Clusterovaný index je B-strom, který tvoří samotnou databázovou tabulku. Data v B-stromu jsou uspořádána na základě klíčových sloupců. Správný návrh clusterovaného indexu je důležitý pro optimální přístup k datům.

Dobře definovaný clusterovaný index by měl být:

  • statický – data ve sloupcích indexů se nesmí s časem měnit
  • krátký – např. do 8B
  • unikátní
  • stále rostoucí řadou

Č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.