CODEWITHSUNDEEP
sql-server
Marian Siminescu
Marian Siminescu

Reputation: 27

MS SQL Server : avoid caching execution plan on a server where I'm not admin

I'm working on some Azure databases where I'm not admin, and I have this issue where, while trying to optimize some queries, at some point my queries are being cached, and I get false "great results".

How can I avoid my queries to be cached?

I would normally run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, but since I'm not an admin, I can't do that.

Thanks!

Upvotes: 0

Views: 659

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89016

How can I avoid my queries to be cached?

You can always send trivially-different queries. Any change in the query text, including in a comment will prevent the reuse of a cached plan.

But cached query plans and cached data pages are the normal state of a database. Cold caches are an abnormal condition.

But stepping back, you can optimize queries in either state. You should be looking at the query plans and the cost of the queries in CPU and Logical IO, which don't depend on whether the query plan or data pages are already cached.

Upvotes: 2

Related Questions