We use sys.dm_exec_query_stats to track down slow queries and queries that are IO offenders.

This works great, we get a lot of very insightful stats. It is clear this is not as accurate as running a profiler trace, as you have no idea when SQL Server will decide to chuck out a an execution plan.

We have quite a few queries where the wrong execution plan is cached. For example queries like the following:

FROM    Posts a
        JOIN Posts q ON q.Id = a.ParentId
        JOIN PostTags pt ON q.Id = pt.PostId
WHERE   a.PostTypeId = 2
        AND a.DeletionDate IS NULL
        AND a.CommunityOwnedDate IS NULL
        AND a.CreationDate > @date
        AND LEN(a.Body) > 300
        AND pt.Tag = @tag
        AND a.Score > 0

The problem is that the ideal plan really depends on the date selected (screenshot of ideal plan):

ideal plans

However if the wrong plan is cached, it totally chokes when the date range is big: (notice the big fat lines)

wrong plan

To overcome this we were recommended to use either OPTION (OPTIMIZE FOR UNKNOWN) or OPTION (RECOMPILE)

OPTIMIZE FOR UNKNOWN results in a slightly better plan, which is far from optimal. Executions are tracked in sys.dm_exec_query_stats.

RECOMPILE results in the best plan being chosen, however no execution counts and stats are tracked in sys.dm_exec_query_stats.

Is there another DMV we could use to track stats on queries with OPTION (RECOMPILE)? Is this behavior by-design? Is there another way we can for recompilation while keeping stats tracked in sys.dm_exec_query_stats?

Note: the framework will always execute parameterized queries using sp_executesql

  • Is this why serverfault.com/review/first-questions was barfing on me yesterday? – squillman Mar 10 '11 at 22:22
  • @squillman , this is actually the "hot answers" query, but this problem pattern runs quite deep in lots of our queries. First questions sure looks problamatic. – Sam Saffron Mar 10 '11 at 22:23
  • Yeah, no love today either :) – squillman Mar 10 '11 at 22:24
  • @squillman the issue with the /review page is resolved now, for gory details see: meta.stackexchange.com/questions/83069/… – Sam Saffron Mar 14 '11 at 3:14
  • How frequently is this query being run? The date may not be the only problem part of the query -- the tag could make a big difference, too. – Jon Seigel Mar 27 '11 at 15:44

Perhaps you should use a plan guide instead of option RECOMPILE. You already have a good plan, so just add it as a plan guide for your query and the optimizer will then produce this plan every time. See Optimizing Queries in Deployed Applications by Using Plan Guides and Specifying Query Plans with Plan Forcing.

In your case is really trivial, simply call sp_create_plan_guide_from_handle with the good query plan handle:

You can use this stored procedure to ensure the query optimizer always uses a specific query plan for a specified query.

  • I actually have 2 plans at play here, one is efficient for short date ranges, the other for longer ones ... at the moment my "fix" / workaround is to simply hard code the date (which only changes once a day) in a string. The trouble with telling it which plan to choose is that we need to scale this to 60 or so dbs. – Sam Saffron Mar 11 '11 at 7:08
  • Use different queries, one for the short ranges one for the long ranges. Guide each one with its appropriate plan. – Remus Rusanu Mar 11 '11 at 7:24
  • I fixed this particular issue by using .CreationDate > (GETUTCDATE() - 20) and the like, hardcoded into the sql string. This helps us in quite a few spots, but we are stuck with an uglier hack in some other spots. I guess there is no DMV that tracks option recompile queries, and I guess this is by design with no workaround – Sam Saffron Mar 14 '11 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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