{"id":82923,"date":"2019-01-12T20:42:00","date_gmt":"2019-01-12T20:42:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=82923"},"modified":"2019-01-12T20:42:00","modified_gmt":"2019-01-12T20:42:00","slug":"a-real-parameterization-problem-with-a-plus","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/a-real-parameterization-problem-with-a-plus\/","title":{"rendered":"A real parameterization problem with a plus"},"content":{"rendered":"

A few weeks ago I faced this problem: One query on my application was (fortunately in the development environment) was facing a very bad execution time.<\/p>\n

Since the query was generated by entity framework, I used SQL Profiler<\/strong> to capture the query with all its parameters and execute in SSMS<\/strong>. The query was created using sp_executesql<\/strong>, this stored procedure is used to force a query to be parameterized.<\/p>\n

The surprise: The execution was quite well on SSMS<\/strong>. The query was the same, same server, same data, same parameters and still, the execution time was completely different. I cleared the cache many times, tried again the application, then SSMS<\/strong> and still, completely different execution times on both. The execution should be the same, why in hell the plan created when the application sends the query would be different than the plan created when SSMS<\/strong> sends the query?<\/p>\n

Ok, the plans shouldn’t be different, but let’s check anyway. We can recover the query plans from the cache:<\/p>\n

select<\/span>\u00a0*<\/span>\u00a0from<\/span>
\nsys<\/span>.<\/span>dm_exec_query_stats<\/span>\u00a0qs<\/span>
\ncross<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_sql_text<\/b><\/span>(<\/span>qs<\/span>.<\/span>sql_handle<\/span>)<\/span>
\ncross<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_query_plan<\/b><\/span>(<\/span>qs<\/span>.<\/span>plan_handle<\/span>)<\/span>
\norder<\/span>\u00a0by<\/span>\u00a0total_elapsed_time<\/span>\u00a0desc<\/span> <\/span><\/div>\n

 <\/p>\n

This query will retrieve all the plans from the cache. A few more filters over the query text and we can find the plans related to the query we are looking for. Surprise again: The same query has two different plans.<\/p>\n

How do I know it’s the same query? Couldn’t the text be different? Simple: The sql_handle<\/strong> was absolutely the same. It was the same query. Why different plans?<\/p>\n

My first thought was the user. When a table name is not fully qualified (<schema>.<table>), the query plan can be fixed for a specific user and this could be a problem.<\/p>\n

Using the query plan handles from both plans of the query and the query below we can identify if the plans are linked to a specific user or not.<\/p>\n

with<\/span>\u00a0qry<\/span>\u00a0as<\/span>
\n(<\/span>select<\/span>\u00a0refcounts<\/span>,<\/span>usecounts<\/span>,<\/span>size_in_bytes<\/span>,<\/span>\u00a0cacheobjtype<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0objtype<\/span>,<\/span>\u00a0attribute<\/span>,<\/span>value<\/span>,<\/span>plan_handle<\/span>
\n\u00a0\u00a0from<\/span>\u00a0sys<\/span>.<\/span>dm_exec_cached_plans<\/span>\u00a0ecp<\/span>
\n\u00a0\u00a0outer<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_plan_attributes<\/b><\/span>(<\/span>ecp<\/span>.<\/span>plan_handle<\/span>)<\/span>\u00a0epa<\/span>
\n\u00a0\u00a0where<\/span>\u00a0epa<\/span>.<\/span>attribute<\/span>=<\/span>‘user_id’<\/span>)<\/span>
\nselect<\/span>\u00a0refcounts<\/span>,<\/span>usecounts<\/span>,<\/span>size_in_bytes<\/span>,<\/span>cacheobjtype<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objtype<\/span>,<\/span>attribute<\/span>,<\/span>value<\/span>,<\/span>\u00a0objectid<\/span>,<\/span>[text]<\/span>,<\/span>[dbid]<\/span>
\nfrom<\/span>\u00a0qry<\/span>
\ncross<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_sql_text<\/b><\/span>(<\/span>qry<\/span>.<\/span>plan_handle<\/span>)<\/span>
\n\u00a0\u00a0\u00a0where<\/span>\u00a0dbid<\/span>=<\/span>db_id<\/span>(<\/span><<\/span>databaseName<\/span>><\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0and<\/span>\u00a0plan_handle<\/span>\u00a0in<\/span>\u00a0(<\/span><<\/span>planhandle1<\/span>><\/span>,<\/span><<\/span>planhandle2<\/span>><\/span>)<\/span> <\/span><\/div>\n

 <\/p>\n

No solution: both plans with a -2 value for the user_id<\/strong>, so both can be used by any user. Oh, wait: user_id<\/strong> is a plan attribute, what about the other plan attributes? We just need to remove the where clause on this previous query to check:<\/p>\n

with<\/span>\u00a0qry<\/span>\u00a0as<\/span>
\n(<\/span>select<\/span>\u00a0refcounts<\/span>,<\/span>usecounts<\/span>,<\/span>size_in_bytes<\/span>,<\/span>\u00a0cacheobjtype<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0objtype<\/span>,<\/span>\u00a0attribute<\/span>,<\/span>value<\/span>,<\/span>plan_handle<\/span>
\n\u00a0\u00a0from<\/span>\u00a0sys<\/span>.<\/span>dm_exec_cached_plans<\/span>\u00a0ecp<\/span>
\n\u00a0\u00a0outer<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_plan_attributes<\/b><\/span>(<\/span>ecp<\/span>.<\/span>plan_handle<\/span>)<\/span>\u00a0epa<\/span>)<\/span>
\nselect<\/span>\u00a0refcounts<\/span>,<\/span>usecounts<\/span>,<\/span>size_in_bytes<\/span>,<\/span>cacheobjtype<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objtype<\/span>,<\/span>attribute<\/span>,<\/span>value<\/span>,<\/span>\u00a0objectid<\/span>,<\/span>[text]<\/span>,<\/span>[dbid]<\/span>
\nfrom<\/span>\u00a0qry<\/span>
\ncross<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_sql_text<\/b><\/span>(<\/span>qry<\/span>.<\/span>plan_handle<\/span>)<\/span>
\n\u00a0\u00a0\u00a0where<\/span>\u00a0dbid<\/span>=<\/span>db_id<\/span>(<\/span><<\/span>databaseName<\/span>><\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0and<\/span>\u00a0plan_handle<\/span>\u00a0in<\/span>\u00a0(<\/span><<\/span>planhandle1<\/span>><\/span>,<\/span><<\/span>planhandle2<\/span>><\/span>)<\/span> <\/span><\/div>\n

 <\/p>\n

That’s it! Comparing all the attributes, one of them is different between both plans: set_options.<\/p>\n

This attribute, set_options, is expressed with a numeric value which needs some bitwise operations to be parsed. Here a script to parse the set_options value, you can read it in details here<\/a><\/p>\n

declare<\/span>\u00a0@set_options<\/span>\u00a0int<\/i><\/span>\u00a0=<\/span>\u00a0251
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>1<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a01<\/span>)<\/span>\u00a0print<\/span>\u00a0‘ANSI_PADDING’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>4<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a04<\/span>)<\/span>\u00a0print<\/span>\u00a0‘FORCEPLAN’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>8<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a08<\/span>)<\/span>\u00a0print<\/span>\u00a0‘CONCAT_NULL_YIELDS_NULL’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>16<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a016<\/span>)<\/span>\u00a0print<\/span>\u00a0‘ANSI_WARNINGS’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>32<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a032<\/span>)<\/span>\u00a0print<\/span>\u00a0‘ANSI_NULLS’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>64<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a064<\/span>)<\/span>\u00a0print<\/span>\u00a0‘QUOTED_IDENTIFIER’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>128<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a0128<\/span>)<\/span>\u00a0print<\/span>\u00a0‘ANSI_NULL_DFLT_ON’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>256<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a0256<\/span>)<\/span>\u00a0print<\/span>\u00a0‘ANSI_NULL_DFLT_OFF’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>512<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a0512<\/span>)<\/span>\u00a0print<\/span>\u00a0‘NoBrowseTable’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>4096<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a04096<\/span>)<\/span>\u00a0print<\/span>\u00a0‘ARITH_ABORT’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>8192<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a08192<\/span>)<\/span>\u00a0print<\/span>\u00a0‘NUMERIC_ROUNDABORT’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>16384<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a016384<\/span>)<\/span>\u00a0print<\/span>\u00a0‘DATEFIRST’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>32768<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a032768<\/span>)<\/span>\u00a0print<\/span>\u00a0‘DATEFORMAT’
\n<\/span>if<\/span>\u00a0(<\/span>(<\/span>65536<\/span>\u00a0&<\/span>\u00a0@set_options<\/span>)<\/span>\u00a0=<\/span>\u00a065536<\/span>)<\/span>\u00a0print<\/span>\u00a0‘LanguageID’<\/span> <\/span><\/div>\n

 <\/p>\n

Finally, the answer: SSMS<\/strong> had one single option more than the connections made by the application. ARITH_ABORT<\/strong> changes the SQL Server<\/strong> behaviour when a math error, such as overflow or division by zero, happens. Should SQL Server<\/strong> return null or raises an error?<\/p>\n

The option itself doesn’t affect the query in anything, however, since SSMS has a different set option enabled, SQL Server refuses to re-use the query plan created for the application and compile a new one. This totally hides the source of the problem.<\/p>\n

After discovering the logic behind the different query plans, identifying the problem was quite easy. The query was parameterized, it was using sp_executesql. The application is a web application hosted inside sharepoint. The first time the page loads, it loads a report with only a quarter for 2018, a small set of data. However, by doing this the query is sent to the server and a query plan is stored on the cache, compiled for this set of parameters and data. When the user changes the parameters, requesting a report from 2014 to 2018, the query returns a completely different amount of data and the plan works very badly for it.<\/p>\n

In summary, it’s a parameterization problem, when the query needs different plans for different parameter values. I wrote about the problem, how to identify and solve, you can read here. I was capturing the text and parameters of the query with problems and when executing in SSMS, due to the different set options, it was always recompiled and a new (and good) plan was created, hiding the parameterization problem.<\/p>\n

The solution for the parameterization problem is to use a query hint. There are two options:<\/p>\n

Recompile<\/strong>: This hint will ensure the query will always be recompiled, never using a plan from the cache and ensuring it always has a good plan for the current parameters.<\/p>\n

Optimize for Unknown<\/strong>: The parameterization problem happens because the query is optimized for the parameter values it’s using when it first arrives on the server and this optimization may not work for other parameter values. The Optimize for Unknow hint optimize the query for any parameter value, disregarding the values used on the first execution, so it’s more probably the resulting plan will work well enough for any parameter and the query will not need to be recompiled on each execution. On the other hand, some parameters that could have a better plan will also be using this “generic” plan.<\/p>\n

Finally, problem solved after an interesting catch be found hiding the real problem.<\/p>\n","protected":false},"excerpt":{"rendered":"

A few weeks ago I faced this problem: One query on my application was (fortunately in the development environment) was facing a very bad execution time. Since the query was generated by entity framework, I used SQL Profiler to capture the query with all its parameters and execute in SSMS. The query was created using……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[4170,5993],"coauthors":[6810],"class_list":["post-82923","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-database-administration","tag-query-plans"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82923"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82923\/revisions"}],"predecessor-version":[{"id":82925,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82923\/revisions\/82925"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82923"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

However, the query was generated by entity framework and entity framework doesn’t support query hints. How to solve this problem? Some time ago I wrote an article about how to create a library to support these query hints with entity framework. You can read the details here<\/a> and get a copy of the library here<\/a>.<\/p>\n