2

This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general.

I've got an adhoc sql statement that gets a different query plan solely because of OPTION(recompile). The batch statement is never (in practice) being reused as it is using non-parameterized dynamic sql generation where the statement changes with dates / other parameters. I've confirmed this is the case by checking the cached plan_handle, always a different ID as values in batch change.

When I do dbcc freeproccache I expect that the query plan I get for the statement would be exactly the same as one with "option (recompile)" query hint added. But its not, its quite different actually (much much faster).

Does anyone know why addition of a query hint would cause the engine to pick a different query plan?

The statement is something like this

dbcc freeproccache
go
sp_executesql ' 
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate 
and exists( 
select A
union all select B
union all select C
)'

all I have to do is change the statement to use recompile query hint and I get different (much better) query plan.

dbcc freeproccache
go
sp_executesql ' 
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate 
and exists( select A
union all select B
union all select C
) OPTION (RECOMPILE)'

As code is passing in the date values I can't use a plan guide to override the default behavior, looks like code change will be required. But I wish I knew where to look on why recompile would force a different plan to be used.

Improve this question
3
  • You have not included the plans, but theoretically I would say this comes down to cardinality estimates. Do you have up-to-date statistics? Is the latter re-sampling them on recompile?
    – Gaius
    Nov 17, 2011 at 10:27
  • I didn't include good versus bad plan yesterday because the query is actually a lot more complicated than I wrote above and therefore the query plan is quite large. I can post them if you think it would be helpful to understand issue. Statistics could be out of date, though we do resample full on the entire db each day (this database can undergo massive changes in a day - though it is not predictable). Performance without the recompile today (after statistcs rebuild last night) is still problematic.
    – JorgeSandoval
    Nov 17, 2011 at 19:03
  • 1
    I'm with @Gaius, when you choose option recompile then the QO can look at the local variables, whereas without it the QO must make a plan that works for all variables. This ends up coming down to cardinality estimation being better which is resulting in a better plan and operators for the option recompile query.
    – Sean Gallardy
    Oct 27, 2016 at 21:14

3 Answers 3

Reset to default
6

The query you posted contains variables.

SQL Server doesn't do variable sniffing so without OPTION (RECOMPILE) it will compile a general plan as it would for OPTIMIZE FOR UNKNOWN.

I don't really follow your question though. At one point you seem to be saying that the version without the hint is "much much faster" and then later you say the version with the hint is "much better". So which one is it?

Both are explicable however. If you find the version with the hint is better than this is because SQL Server can use statistics to estimate the number of rows that will be matched by the date predicate and choose an appropriate plan for that case.

If the version without the hint is better the statistics themselves may need updating. Perhaps when they were last updated there were few or no rows meeting that predicate and so SQL Server massively underestimates the number of rows that will be returned. See Statistics, row estimations and the ascending date column for more about this potential issue.

Improve this answer
6
  • Sorry if this was confusing. The sql had variables that were not parameterized, with the recompile query hint (not sp_executesql "with recompile") the plan operates much faster. Similarly, parameterizing those variables will improve the performance of the query plan, though I do not see exact same plan. I do not undestand why the same statement with variables can use statistics under option (recompile) while without it it optimizes for unknown. Does option (recompile) parameterize the query as a byproduct?
    – JorgeSandoval
    Nov 20, 2011 at 5:13
  • @JorgeSandoval - No. It is just that then when you use variables the batch as a whole gets compiled before the variables are assigned a value. SQL Server doesn't make any attempt to sniff variable values and take account of that in compilation. If you want that you can use the RECOMPILE hint. Or you can use OPTIMIZE FOR and tell it the specific values that the variables will have. Or you can simply not use variables at all and plug the literal values into the query as they don't seem to provide any benefit here.
    – Martin Smith
    Nov 20, 2011 at 9:32
  • So if I understand that right, the difference is essentially that because the recompile query hint has been added it discards the OPTIMIZE FOR UNKNOWN plan and generates the new plan with the values of the variables available to make decisions. That explains why we did not see the same behavior when specifying the "with recompile" to the sp_executesql call.
    – JorgeSandoval
    Nov 21, 2011 at 9:09
  • @JorgeSandoval - Yes, pretty much. Some people assign parameters to local variables specifically to take advantage of this behaviour as a technique to avoid parameter sniffing.
    – Martin Smith
    Nov 21, 2011 at 9:17
  • Yeah, I'm worried about that little bit. Some of the tables have a rather varied cardinality (think tree table with parentid, childid,level - where top of tree has ~300k rows with same parentid, but bottom node will have one record). Thanks for your help, its clear why the plan is bad at this point (FOR UNKNOWN leading to a lot of nested loops - super high logical reads...)
    – JorgeSandoval
    Nov 21, 2011 at 18:27
2

Short addendum to @MartinSmith's answer. There are two little known trace flags that can resolve the poor statistics with ascending dates and keys issue.

Quote from Ascending Keys and Auto Quick Corrected Statistics:

Trace flag 2389 and 2390, both new in SQL Server 2005 SP1, can help to address this problem. SQL Server 2005 SP1 begins to track the nature of columns via subsequent operations of updating statistics. When the statistics are seen to increase three times the column is branded ascending. If trace flag 2389 is set, and a column is branded ascending, and a covering index exists with the ascending column as the leading key, then the statistics will be updated automatically at query compile time. A statement is compiled to find the highest value and a new step is added at the end of the existing histogram to model the recently added data.

There was also a recent article on SimpleTalk discussing these flags, Statistics on Ascending Columns.

Improve this answer
1

This could happen if you have your database set up for forced parametrization. When the PARAMETERIZATION option is set to FORCED, any literal value is converted to a parameter. Except under a few circumstances; and one of these is when the query has OPTION RECOMPILE.

Check out: http://msdn.microsoft.com/en-us/library/ms175037.aspx

Improve this answer
1
  • In this case I've mentioned that we are getting different plans every occurrence due to the changed variables. This implies (though I didn't state it out right) that forced parameterization is not turned on for this db. However, you are on to something it seems. Parameterizing the query does seem to make it pick a different query plan than if not parameterized. And thanks to SQL engine's voodoo magic, one that runs quickly. We are in process of changing code to parameterize the query. If problem plan comes back we can consider plan guides.
    – JorgeSandoval
    Nov 19, 2011 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.