3

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.

  • 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
2

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: http://cbailiss.wordpress.com/2014/10/01/free-tool-sql-workload-profiler-for-on-premise-and-azure-sql-db/

  • You can now do this via ALTER DATABASE SCOPED CONFIGURATION – Mitch Wheat Oct 15 '18 at 2:58
1

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.

1

As of SQL Server 2016 (including SQL Azure), ALTER DATABASE SCOPED CONFIGURATION lets you alter several database configuration settings at the individual database level, including clearing your procedure cache:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;  

The entire list of options:

  • Clear procedure cache.
  • Set the MAXDOP parameter to an arbitrary value (1,2, ...) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (such as for reporting queries).
  • Set the query optimizer cardinality estimation model independent of the database to compatibility level.
  • Enable or disable parameter sniffing at the database level.
  • Enable or disable query optimization hotfixes at the database level.
  • Enable or disable the identity cache at the database level.
  • Enable or disable a compiled plan stub to be stored in cache when a batch is compiled for the first time.
  • Enable or disable collection of execution statistics for natively compiled T-SQL modules.
  • Enable or disable online by default options for DDL statements that support the ONLINE= syntax.
  • Enable or disable resumable by default options for DDL statements that support the RESUMABLE= syntax
0

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

Your Answer

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