Prevent Caching in SQL Server

Question

Having looked around the net using Uncle Google, I cannot find an answer to this question:

What is the best way to monitor the performance and responsiveness of production servers running IIS and MS SQL Server 2005?

I'm currently using Pingdom and would like it to point to a URL which basically mimics a 'real world query' but for obvious reasons do not want the query to run from cache. The URL will be called every 5 minutes.

I cannot clear out the cache, buffers, etc since this would impact negatively on the production server. I have tried using a random generated number within the SELECT statement in order to generate unique queries, but the cached query is still used.

Is there any way to simulate the NO_CACHE in MySQL?

Regards

Answer 1

To clear the SQL buffer and plan cache:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

A little info about these commands from MSDN:

Use DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. (source)

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. (source)

Answer 2

SQL Server does not have a results cache like MySQL or Oracle, so I am a bit confused about your question. If you want the server to recompile the plan cache for a stored procedure, you can execute it WITH RECOMPILE. You can drop your buffer cache, but that would affect all queries as you know.

At my company, we test availability and performance separately. I would suggest you use this query just to make sure you your system is working together from front-end to database, then write other tests that check the individual components to judge performance. SQL Server comes with an amazing amount of ways to check if you are experiencing bottlenecks and where they are. I use PerfMon and DMVs extensively. Using PerfMon, I check CPU and page life expectancy, as well as seeing how long my disk queue is. Using DMVs, I can find out if my queries are taking too long (sys.dm_exec_query_stats) or if wait times are long (sys.dm_os_wait_stats).

The two biggest bottlenecks with IIS tend to be CPU and memory, and IIS comes with its own suite of PerfMon objects to query, but I am not as familiar with those.