0

I am using MS SQL Server 2012. I am trying to see the optimization in the server so I can't just clear everything.

This link didn't work for me. (or this example)

Any suggestions how I can select the query and clear the cache afterwards?
(or prevent it from caching would be better)

EDIT:
Most desired outcome is testing the select query like it's running for the very first time.

MilkTea027
  • 301
  • 1
  • 5
  • 24

1 Answers1

0

You can query sys.dm_exec_connections to get a handle to last executed query for a given connection, and then evict that plan with DBCC FREEPROCCACHE. Make sure you do this on another connection.

Alternatively, use WITH RECOMPILE or OPTION(RECOMPILE) to not cache the plan at all:

http://blogs.msdn.com/b/sqlmeditation/archive/2013/01/01/is-it-really-quot-recompile-quot-or-a-quot-private-temporary-compile-quot.aspx

dean
  • 9,960
  • 2
  • 25
  • 26
  • I am trying this in my local machine for now. When I run the following: `CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO DBCC FREEPROCCACHE; GO DBCC FREESESSIONCACHE; GO` It first takes a minute for the query to finish, when I run the **DBCC FREEPROCCACHE** or the **RECOMPILE** stuffs, the second time queried takes less than a second.. Is there something else being saved other than cache that makes it so quick? (Less 1 sec on 2nd run) – MilkTea027 Apr 15 '14 at 10:41
  • Yes, the data is cached, repeat the CHECKPOINT / DROPCLEANBUFFERS routine every time. – dean Apr 15 '14 at 10:44
  • Locally that is okay, in the server however, I can't just clear all the cache.. Is there a way making that specific query run like it's queried for the first time? – MilkTea027 Apr 15 '14 at 10:46
  • No, you can't clear just selected pages from data buffer, only the selected plans. But you should pay attention to logical reads when you apply `SET STATISTICS IO ON` and also the CPU time from `SET STATISTICS TIME ON`. – dean Apr 15 '14 at 10:49