K\u00e9t nem annyira rekl\u00e1mozott \u00faj szolg\u00e1ltat\u00e1st szeretn\u00e9k bemutatni az SQL Server 2005-b\u0151l, amelyeket a keddi SQL vizsg\u00e1ra k\u00e9sz\u00fclve fedeztem fel. (Tavaly decemberben sajnos m\u00e1r nem volt hely az egyik beta sql vizsg\u00e1ra, azt nyomom le most, mivel maradt p\u00e1r ingyenes kuponom, h\u00e1t kihaszn\u00e1lom \u0151ket).<\/p>\n
Adventureworks database, Contact t\u00e1bla.<\/p>\n
Pakoljunk r\u00e1 egy indexet, e ment\u00e9n szeretn\u00e9nk sz\u0171rni.<\/p>\n
CREATE NONCLUSTERED INDEX IDX_LastName ON [Person].[Contact]
\n(
\n[LastName] ASC
\n)<\/p>\n
Referencia lek\u00e9rdez\u00e9s\u00fcnk:<\/p>\n
select * from Person.Contact<\/p>\n
where LastName like ‘Ac%’<\/p>\n
Kimenet: 4 sor<\/p>\n
Execution Plan:<\/p>\n
select * from Person.Contact where LastName like ‘Ac%’<\/p>\n
|–Nested Loops(Inner Join, OUTER REFERENCES:([Person].[Contact].[ContactID]))<\/p>\n
|–Index Seek(OBJECT:([Person].[Contact].[IDX_LastName])<\/p>\n
|–Clustered Index Seek(OBJECT:([Person].[Contact].[PK_Contact_ContactID])<\/p>\n
K\u00f6lts\u00e9g: 8.2ms.<\/p>\n
Azaz haszn\u00e1lja az NC index\u00fcnket, mert kital\u00e1lja az ‘Ac%’ param\u00e9terb\u0151l \u00e9s a statisztik\u00e1kb\u00f3l, hogy v\u00e1rhat\u00f3an csak p\u00e1r sor j\u00f6n vissza, \u00edgy meg\u00e9ri az NC indexet haszn\u00e1lni, m\u00e9g akkor is, ha cser\u00e9be joinolni kell azt a clustered index-szel. Val\u00f3j\u00e1ban ugye ez nem igaz\u00e1n join, hanem el\u0151sz\u00f6r lem\u00e1szik az NC f\u00e1ban, azt\u00e1n a Clusteredben, de ez most sz\u00e1munkra mindegy.
\nA k\u00f6vetkez\u0151 eset viszont nem optim\u00e1lis tervet eredm\u00e9nyez:<\/p>\n
declare @a nvarchar(50)
\nset @a = ‘ac%’
\nselect * from
\nPerson.Contact
\nwhere LastName like @a –‘Ac%’<\/p>\n
EP:
\nselect * from Person.Contact where LastName like @a –‘Ac%’
\n|–Clustered Index Scan(OBJECT:([Person].[Contact].[PK_Contact_ContactID])<\/p>\n
K\u00f6lts\u00e9g: 438ms, 50x lassabb, mint a referenci\u00e1nk!<\/p>\n
Nem meri haszn\u00e1lni az NC indexet, mert fogalma sincs h\u00e1ny sor j\u00f6n vissza. Ha megkock\u00e1ztatja a LastName oszlop NC index\u00e9t, lehet, hogy bef\u00fcrdik vele, mert sok sor j\u00f6n vissza, \u00e9s akkor jobban meg\u00e9rte volna a Clustered indexet haszn\u00e1lni (table scan), mint az NC-t, \u00e9s azt\u00e1n m\u00e9g a Clustereden is v\u00e9gigmenni. (Ha van Clustered index is egy t\u00e1bl\u00e1n -szinte mindig- akkor az NC index nem \u00e9ri el k\u00f6zvetlen\u00fcl a sorokat, hanem m\u00e9g \u00e1t kell neki menni a Clustereden is. Ha valaki \u00e9rdekel, mi\u00e9rt, legk\u00f6zelebb le\u00edrom.)<\/p>\n
Na, de mi\u00e9rt nem tudja h\u00e1ny sor j\u00f6n vissza? Az\u00e9rt, mert a 2000 mindig, a 2005 is alapban az eg\u00e9sz batchet egyben leford\u00edtja, \u00e9s optimaliz\u00e1lja. Azaz \u00fagy pr\u00f3b\u00e1l v\u00e9grehajt\u00e1si tervet gener\u00e1lni, hogy nem futtatta m\u00e9g le a k\u00f3dot, \u00edgy nem ismeri @a \u00e9rt\u00e9k\u00e9t. Hogy lehet \u00edgy statisztik\u00e1t haszn\u00e1lni? Ilyenkor csak heurisztik\u00e1kat (okos bl\u00f6ff\u00f6ket) tud haszn\u00e1lni a szerver. Pl. ha where oszlop = @a, akkor legyen 10% a szelektivit\u00e1s, azaz tegy\u00fck fel, hogy a sorok 10%-a j\u00f6n vissza. A >, <, between 30 %-kal sz\u00e1moland\u00f3.\n\nT\u00e1bl\u00e1nk kb. 20e soros. Ennek 10%-a 2000 sor. A becs\u00fclt tervben 718 sor \u00edg\u00e9rnek, ez a t\u00e1bla 3.6%-a. Honnan j\u00f6n neki ez a sz\u00e1m?\n\nIlyenkor \u00e1ltal\u00e1ban a statiszk\u00e1ib\u00f3l pr\u00f3b\u00e1l kimazsol\u00e1zni valamit. Kicsit m\u00f6g\u00e9 n\u00e9zve:\n\ndbcc show_statistics ('Person.Contact', IDX_LastName)\n\nName Updated Rows Rows Sampled Steps Density Average key length String Index\n\nIDX_LastName Dec 17 2006 3:45PM 19972 19972 200 0.5642176 15.2126 YES\n\nAll density Average Length Columns\n\n0.0008291874 11.2126 LastName\n\n5.00701E-05 15.2126 LastName, ContactID\n\nEzzel nem jutottunk el\u0151bbre, r\u00e9gebben a 20000 x 0.0008291874 hozott volna valami k\u00f6zeli eredm\u00e9nyt a tervben l\u00e1that\u00f3hoz, de ez most nem j\u00f6n be, mert az 16 sort hozna be. Van viszont itt egy \u00e9rdekes dolog, az els\u0151 sorban. String Index, YES.\n\nEz egy \u00faj, 2005 f\u00edcs\u00f6r.\n\n\u201eSQL 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'.\u201d\n\nSz\u00f3val neh\u00e9z helyzetekben, ha m\u00e9g a sz\u0171r\u0151 kifejez\u00e9s elej\u00e9t se ismerj\u00fck (eset\u00fcnkben semmilyen r\u00e9sz\u00e9t), akkor is seg\u00edthet a string summary. Tal\u00e1n ez s\u00fag neki, onnan a 718 sor.\n\nNo, de l\u00e9pj\u00fcnk tov\u00e1bb, hogy l\u00e1tsz\u00f3djon, v\u00e9gre, mire akarok kilyukadni. A 2005 egyik, szerintem nagyon fontos \u00faj szolg\u00e1ltat\u00e1sa a statement level recompile. Azaz, ha az optimizer \u00fagy \u00e9rzi, akkor menet k\u00f6zben is nekil\u00e1that egyes parancsokat \u00fajraford\u00edtani, \u00faj tervet gener\u00e1lni hozz\u00e1. Ilyet a 2000 is csin\u00e1lt, csak a teljes batch-re, ami marha nagy k\u00f6lts\u00e9g\u0171 volt. \u0150 viszont most ak\u00e1r egyetlen parancsot is \u00fajra tud ford\u00edtani. Deh\u00e1t akkor az el\u0151bbi lek\u00e9rdez\u00e9st, a v\u00e1ltoz\u00f3st is \u00fajraford\u00edthatn\u00e1, \u00edgy a v\u00e1ltoz\u00f3 \u00e9rt\u00e9k\u00e9nek ismeret\u00e9ben m\u00e1r pontos becsl\u00e9se volna, h\u00e1ny sor j\u00f6n vissza, \u00edgy optim\u00e1lis tervet gener\u00e1lhatna.\n\n\u00c9s tudja, \u00e9s tudja!\n\ndeclare @a nvarchar(50)\nset @a = 'ac%'\nselect * from\nPerson.Contact\nwhere LastName like @a --'Ac%'\noption(recompile)\n\nEP:\n\nselect * from Person.Contact where LastName like @a --'Ac%' option(recompile)\n|--Nested Loops(Inner Join, OUTER REFERENCES:([Person].[Contact].[ContactID]))\n|--Nested Loops(Inner Join, OUTER REFERENCES:(LikeRangeStart([@a])...)\n| |--Compute Scalar(DEFINE:([ConstExpr1003]=LikeRangeStart([@a])\n| | |--Constant Scan\n| |--Index Seek(OBJECT:([Person].[Contact].[IDX_LastName])\n|--Clustered Index Seek(OBJECT:([Person].[Contact].[PK_Contact_ContactID])\n\nK\u00f6lts\u00e9g: 8.2ms!!!\n\nKicsit ugyan \u00e1gasbogasabb a v\u00e9grehajt\u00e1si terv, de ez csak a skal\u00e1r (@a) tervbev\u00e9tele miatt van.\nProfilerben n\u00e9zve az SQL:StmtRecompile esem\u00e9ny remek\u00fcl jelzi, hogy az utas\u00edt\u00e1st \u00fajraford\u00edtotta a szerver.\nIm\u00e1dom ezeket az apr\u00f3, de hihetetlen\u00fcl hasznos kis \u00faj\u00edt\u00e1sokat az SQL Serverben.\n<\/p>\n","protected":false},"excerpt":{"rendered":"
K\u00e9t nem annyira rekl\u00e1mozott \u00faj szolg\u00e1ltat\u00e1st szeretn\u00e9k bemutatni az SQL Server 2005-b\u0151l, amelyeket a keddi SQL vizsg\u00e1ra k\u00e9sz\u00fclve fedeztem fel. (Tavaly decemberben…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[],"class_list":["post-179","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=179"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/179\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}