sys.dm_exec_query_stats interaction with recompilation

Question

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:

SELECT TOP 30
        a.Id
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
ORDER BY a.Score DESC

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 http://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
@squillman the issue with the /review page is resolved now, for gory details see: http://meta.stackexchange.com/questions/83069/first-question-is-not-working-oops/83072#83072 — Sam Saffron, Mar 14 '11 at 03: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
@Jon yeah lots of our tag/time/user based queries are problematic when it comes to plan generation. Parameterizing being good or bad heavily depends on table indexing and query complexity. — Sam Saffron, Mar 27 '11 at 21:32
Hmmm... what kind of performance does the ideal case have when run with the query plan generated from an expensive case? If you can get the expensive case plan into the cache and let *that* stick around, is that sufficient? Also, are you optimizing against CPU or I/O (or both) in the expensive case? There's so much information about the plan that we can't see from screenshots. — Jon Seigel, Mar 27 '11 at 22:45
@Jon, sure but this question is not really about this particular plan I sorted it out a while back. Its about the interaction of option recompile with dm_exec_query_stats. As it turns out, by design, queries with that hint bypass the DMV. — Sam Saffron, Mar 27 '11 at 22:50

Remus Rusanu · Answer 1 · 2011-03-11T07:22:55.177

1

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.

edited Mar 11 '11 at 07:22

answered Mar 11 '11 at 07:05

Remus Rusanu

8,283
1
21
23

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 07: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 07: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 03:27

sys.dm_exec_query_stats interaction with recompilation

1 Answers1