{"id":70,"date":"2008-05-22T22:36:12","date_gmt":"2008-05-22T20:36:12","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=70"},"modified":"2012-10-15T16:20:30","modified_gmt":"2012-10-15T14:20:30","slug":"parameter-sniffing-pt-3","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/05\/22\/parameter-sniffing-pt-3\/","title":{"rendered":"Parameter sniffing, pt 3"},"content":{"rendered":"

Last, but not least, here’s one final look at parameter sniffing.<\/p>\n

In part 1<\/a> of this mini-series I wrote about data skew and the problems it can cause with parameters. In part 2<\/a>, I looked at what happens when the optimiser can’t sniff values. In this post, I’m going to look at a third thing that can cause parameter sniffing problems. It’s fairly obvious once you know about it, but the first time it was pointed out to me I was a little surprised.<\/p>\n

So, to recap. When the query optimiser gets a stored procedure to compile, it knows the values of any parameter passed to that procedure. It will then compile the procedure and optimise the queries based upon the value of those parameters. The optimiser cannot sniff the values of variables, because the values of the variables have not been set at the time that the procedure is optimised.<\/p>\n

I’m going to use the same sample code and data as in the first article, to keep things consistent.<\/p>\n

From the tests that were done before<\/a>, I know that the query<\/p>\n

select * from largetable where somestring = 'zzz'<\/pre>\n
executes optimally with an index seek and returns 9 rows. Likewise, I know that the query<\/p>\n
select * from largetable where somestring = 'abc'<\/pre>\n
executes optimally with a clustered index scan and returns 1667 rows.<\/p>\n
Now, let’s see if I can get the optimiser to make the wrong choice.<\/p>\n
<\/p>\n
CREATE PROCEDURE TestSniffing3 @StringVar VARCHAR(10) AS\r\nIF @StringVar not like 'a%'\r\nSET @StringVar = 'abc'\r\n\r\nSELECT *  from largetable where somestring = @StringVar\r\n\r\nGO<\/pre>\n
Looks simple enough. It the parameter isn’t within a certain range, set it to some default value. This kind of stored proc construction isn’t unusual. I often see it in search procs. Pass in a NULL if you want all rows to match. Let’s see what the optimiser makes of it.<\/p>\n
EXEC TestSniffing3 'zzz'<\/pre>\n
It returns 1667 rows, as expected. Now take a look at the execution plan. It’s doing an index seek. Not what was expected and not optimal. On my machine the key lookup’s in at 87% of the query.<\/p>\n
So, what went wrong?<\/p>\n
A look at the properties of the index seek give a clue. Estimated rows – 9 actual rows – 1667. The xml form of the execution plan give another clue.<\/p>\n
<ColumnReference Column="@StringVar"\r\nParameterCompiledValue="'zzz'" ParameterRuntimeValue="'abc'" \/><\/pre>\n
When this procedure was compiled, the value of the parameter was ‘zzz’. The optimiser compiled a plan optimal for that value. It didn’t and couldn’t know that the parameter was going to change within the stored procedure before the query was executed.<\/p>\n
This is something that can really bite badly. There often doesn’t appear to be a good reason for the plan to be wrong. Most people don’t go reading the raw XML of the execution plans, for good reason. It esspecially needs to be ketp in mind on procs that have multiple optional parameters, like the following<\/p>\n
create procedure DoSearch @var1 varchar(10) = NULL, @var2 varchar(10) = NULL\r\nAS\r\nIF @var1 is null\r\n@var1 = '%'\r\n\r\nIF @var2 is null\r\n@var2 = '%'\r\n\r\nSELECT * FROM SomeBigTable WHERE Col1 like @var1 AND Col2 LIKE @var2\r\nGO<\/pre>\n
If, when the proc is first compiled, either of the parameters is NULL, SQL will compile a plan optimal for no rows because a LIKE NULL will not match anything. If the query then goes on to match the entire table, the plan is not going to be very optimal at all.<\/p>\n
That I think concludes the short series on parameter sniffing. As always, I’m interested in hearing other people’s take on these problems and their favourite solutions.<\/p>\n","protected":false},"excerpt":{"rendered":"
undefined<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,15],"tags":[],"class_list":["post-70","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-18","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/70","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=70"}],"version-history":[{"count":2,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/70\/revisions"}],"predecessor-version":[{"id":1425,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/70\/revisions\/1425"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=70"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=70"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=70"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}