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
OPTIMIZE FOR UNKNOWN results in a slightly better plan, which is far from optimal. Executions are tracked in
RECOMPILE results in the best plan being chosen, however no execution counts and stats are tracked in
Is there another DMV we could use to track stats on queries with
Is this behavior by-design?
Is there another way we can for recompilation while keeping stats tracked in
Note: the framework will always execute parameterized queries using sp_executesql