Két nem annyira reklámozott új szolgáltatást szeretnék bemutatni az SQL Server 2005-ből, amelyeket a keddi SQL vizsgára készülve fedeztem fel. (Tavaly decemberben sajnos már nem volt hely az egyik beta sql vizsgára, azt nyomom le most, mivel maradt pár ingyenes kuponom, hát kihasználom őket).

Adventureworks database, Contact tábla.

Pakoljunk rá egy indexet, e mentén szeretnénk szűrni.

CREATE NONCLUSTERED INDEX IDX_LastName ON [Person].[Contact]
(
[LastName] ASC
)

Referencia lekérdezésünk:

select * from Person.Contact

where LastName like ‘Ac%’

Kimenet: 4 sor

Execution Plan:

select * from Person.Contact where LastName like ‘Ac%’

|–Nested Loops(Inner Join, OUTER REFERENCES:([Person].[Contact].[ContactID]))

|–Index Seek(OBJECT:([Person].[Contact].[IDX_LastName])

|–Clustered Index Seek(OBJECT:([Person].[Contact].[PK_Contact_ContactID])

Költség: 8.2ms.

Azaz használja az NC indexünket, mert kitalálja az ‘Ac%’ paraméterből és a statisztikákból, hogy várhatóan csak pár sor jön vissza, így megéri az NC indexet használni, még akkor is, ha cserébe joinolni kell azt a clustered index-szel. Valójában ugye ez nem igazán join, hanem először lemászik az NC fában, aztán a Clusteredben, de ez most számunkra mindegy.
A következő eset viszont nem optimális tervet eredményez:

declare @a nvarchar(50)
set @a = ‘ac%’
select * from
Person.Contact
where LastName like @a –‘Ac%’

EP:
select * from Person.Contact where LastName like @a –‘Ac%’
|–Clustered Index Scan(OBJECT:([Person].[Contact].[PK_Contact_ContactID])

Költség: 438ms, 50x lassabb, mint a referenciánk!

Nem meri használni az NC indexet, mert fogalma sincs hány sor jön vissza. Ha megkockáztatja a LastName oszlop NC indexét, lehet, hogy befürdik vele, mert sok sor jön vissza, és akkor jobban megérte volna a Clustered indexet használni (table scan), mint az NC-t, és aztán még a Clustereden is végigmenni. (Ha van Clustered index is egy táblán -szinte mindig- akkor az NC index nem éri el közvetlenül a sorokat, hanem még át kell neki menni a Clustereden is. Ha valaki érdekel, miért, legközelebb leírom.)

Na, de miért nem tudja hány sor jön vissza? Azért, mert a 2000 mindig, a 2005 is alapban az egész batchet egyben lefordítja, és optimalizálja. Azaz úgy próbál végrehajtási tervet generálni, hogy nem futtatta még le a kódot, így nem ismeri @a értékét. Hogy lehet így statisztikát használni? Ilyenkor csak heurisztikákat (okos blöfföket) tud használni a szerver. Pl. ha where oszlop = @a, akkor legyen 10% a szelektivitás, azaz tegyük fel, hogy a sorok 10%-a jön vissza. A >, <, between 30 %-kal számolandó. Táblánk kb. 20e soros. Ennek 10%-a 2000 sor. A becsült tervben 718 sor ígérnek, ez a tábla 3.6%-a. Honnan jön neki ez a szám? Ilyenkor általában a statiszkáiból próbál kimazsolázni valamit. Kicsit mögé nézve: dbcc show_statistics ('Person.Contact', IDX_LastName) Name Updated Rows Rows Sampled Steps Density Average key length String Index IDX_LastName Dec 17 2006 3:45PM 19972 19972 200 0.5642176 15.2126 YES All density Average Length Columns 0.0008291874 11.2126 LastName 5.00701E-05 15.2126 LastName, ContactID Ezzel nem jutottunk előbbre, régebben a 20000 x 0.0008291874 hozott volna valami közeli eredményt a tervben láthatóhoz, de ez most nem jön be, mert az 16 sort hozna be. Van viszont itt egy érdekes dolog, az első sorban. String Index, YES. Ez egy új, 2005 fícsör. „SQL Server 2005 introduces additional information collected by statistics created on char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text, and ntext columns. This information, called a string summary, helps the query optimizer estimate the selectivity of query predicates on string patterns. String summaries lead to more accurate estimates of result set sizes and frequently better query plans when LIKE conditions are present in a query. This includes conditions such as WHERE ProductName LIKE '%Bike' and WHERE Name LIKE '[CS]heryl'.” Szóval nehéz helyzetekben, ha még a szűrő kifejezés elejét se ismerjük (esetünkben semmilyen részét), akkor is segíthet a string summary. Talán ez súg neki, onnan a 718 sor. No, de lépjünk tovább, hogy látszódjon, végre, mire akarok kilyukadni. A 2005 egyik, szerintem nagyon fontos új szolgáltatása a statement level recompile. Azaz, ha az optimizer úgy érzi, akkor menet közben is nekiláthat egyes parancsokat újrafordítani, új tervet generálni hozzá. Ilyet a 2000 is csinált, csak a teljes batch-re, ami marha nagy költségű volt. Ő viszont most akár egyetlen parancsot is újra tud fordítani. Dehát akkor az előbbi lekérdezést, a változóst is újrafordíthatná, így a változó értékének ismeretében már pontos becslése volna, hány sor jön vissza, így optimális tervet generálhatna. És tudja, és tudja! declare @a nvarchar(50) set @a = 'ac%' select * from Person.Contact where LastName like @a --'Ac%' option(recompile) EP: select * from Person.Contact where LastName like @a --'Ac%' option(recompile) |--Nested Loops(Inner Join, OUTER REFERENCES:([Person].[Contact].[ContactID])) |--Nested Loops(Inner Join, OUTER REFERENCES:(LikeRangeStart([@a])...) | |--Compute Scalar(DEFINE:([ConstExpr1003]=LikeRangeStart([@a]) | | |--Constant Scan | |--Index Seek(OBJECT:([Person].[Contact].[IDX_LastName]) |--Clustered Index Seek(OBJECT:([Person].[Contact].[PK_Contact_ContactID]) Költség: 8.2ms!!! Kicsit ugyan ágasbogasabb a végrehajtási terv, de ez csak a skalár (@a) tervbevétele miatt van. Profilerben nézve az SQL:StmtRecompile esemény remekül jelzi, hogy az utasítást újrafordította a szerver. Imádom ezeket az apró, de hihetetlenül hasznos kis újításokat az SQL Serverben.

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.