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 sp_executesql, this stored procedure is used to force a query to be parameterized.
The surprise: The execution was quite well on SSMS. 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 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 sends the query?
Ok, the plans shouldn’t be different, but let’s check anyway. We can recover the query plans from the cache:
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
cross apply sys.dm_exec_query_plan(qs.plan_handle)
order by total_elapsed_time desc
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.
How do I know it’s the same query? Couldn’t the text be different? Simple: The sql_handle was absolutely the same. It was the same query. Why different plans?
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.
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.
(select refcounts,usecounts,size_in_bytes, cacheobjtype,
objtype, attribute,value,plan_handle
from sys.dm_exec_cached_plans ecp
outer apply sys.dm_exec_plan_attributes(ecp.plan_handle) epa
where epa.attribute=‘user_id’)
select refcounts,usecounts,size_in_bytes,cacheobjtype,
objtype,attribute,value, objectid,[text],[dbid]
from qry
cross apply sys.dm_exec_sql_text(qry.plan_handle)
where dbid=db_id(<databaseName>)
and plan_handle in (<planhandle1>,<planhandle2>)
No solution: both plans with a -2 value for the user_id, so both can be used by any user. Oh, wait: user_id is a plan attribute, what about the other plan attributes? We just need to remove the where clause on this previous query to check:
(select refcounts,usecounts,size_in_bytes, cacheobjtype,
objtype, attribute,value,plan_handle
from sys.dm_exec_cached_plans ecp
outer apply sys.dm_exec_plan_attributes(ecp.plan_handle) epa)
select refcounts,usecounts,size_in_bytes,cacheobjtype,
objtype,attribute,value, objectid,[text],[dbid]
from qry
cross apply sys.dm_exec_sql_text(qry.plan_handle)
where dbid=db_id(<databaseName>)
and plan_handle in (<planhandle1>,<planhandle2>)
That’s it! Comparing all the attributes, one of them is different between both plans: set_options.
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
if ((1 & @set_options) = 1) print ‘ANSI_PADDING’
if ((4 & @set_options) = 4) print ‘FORCEPLAN’
if ((8 & @set_options) = 8) print ‘CONCAT_NULL_YIELDS_NULL’
if ((16 & @set_options) = 16) print ‘ANSI_WARNINGS’
if ((32 & @set_options) = 32) print ‘ANSI_NULLS’
if ((64 & @set_options) = 64) print ‘QUOTED_IDENTIFIER’
if ((128 & @set_options) = 128) print ‘ANSI_NULL_DFLT_ON’
if ((256 & @set_options) = 256) print ‘ANSI_NULL_DFLT_OFF’
if ((512 & @set_options) = 512) print ‘NoBrowseTable’
if ((4096 & @set_options) = 4096) print ‘ARITH_ABORT’
if ((8192 & @set_options) = 8192) print ‘NUMERIC_ROUNDABORT’
if ((16384 & @set_options) = 16384) print ‘DATEFIRST’
if ((32768 & @set_options) = 32768) print ‘DATEFORMAT’
if ((65536 & @set_options) = 65536) print ‘LanguageID’
Finally, the answer: SSMS had one single option more than the connections made by the application. ARITH_ABORT changes the SQL Server behaviour when a math error, such as overflow or division by zero, happens. Should SQL Server return null or raises an error?
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.
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.
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.
The solution for the parameterization problem is to use a query hint. There are two options:
Recompile: 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.
Optimize for Unknown: 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.
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 and get a copy of the library here.
Finally, problem solved after an interesting catch be found hiding the real problem.
The post A real parameterization problem with a plus appeared first on Simple Talk.
from Simple Talk http://bit.ly/2snYsV7
via
No comments:
Post a Comment