Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm doing a lot of work with SQL Azure at the moment, trying to improve performance of a web app. I am using the dm_exec_query_stats view to look for poor performing queries.

Does anyone know a way to clear the query stats in Azure? DBCC FREEPROCCACHE isn't supported. I can use sp_recompile 'procedure' to clear stored procedures/functions etc, but the site uses some LINQ-generated queries which can't be cleared in this manner.

share|improve this question

migrated from Dec 13 '11 at 20:14

This question came from our site for professional and enthusiast programmers.

DBCC FREEPROCCACHE is almost never the right answer. A bad band-aid. If you are looking to clear stored procedures/triggers, you can call sp_recompile 'yourTableName' to mark all procs/triggers for recompile that have yourTableName in the definition. – Thomas Stringer Apr 12 '12 at 1:00

You probably can't as the procedure cache is shared across all the databases which are hosed on the server that your database is hosted on. All you can do it wait for the old queries to be flushed from cache using normal aging out that the procedure cache uses.

Forcing a recompile of the table objects would probably do the trick as well.

share|improve this answer

Old question I know, but if anyone is still looking for one potential workaround...

I wrote a small utility (shared on my blog) that makes life easier when working with this DMV. This utility takes a baseline of the query stats to act as an effective zero.

The utility then queries the DMV repeatedly and works out the changes to show what has executed over the time since the last call into the DMV.

For more details, see:

share|improve this answer

Turning on/off forced parameterization seems to clear these stats.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.