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):
However if the wrong plan is cached, it totally chokes when the date range is big: (notice the big fat lines)
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
