DBCC Usage on production system We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores).

Another VM (the application server) within the system accesses the db server quite intensively for both querying and data updates.

We have been experiencing two main exceptions in the application server at the time were a particular maintenance sub plan stored procedure is executed.

The two exceptions are:



  System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding (.......)







  System.Data.SqlClient.SqlException (0x80131904): The app domain with specified version id (1859) was unloaded due to memory pressure and could not be found. (.....)



The maintenance plan stored procedure executed at this time has the following statements:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
CHECKPOINT 
DBCC DROPCLEANBUFFERS 


This is obviously causing the issues after some testing. Also I have found out that:


Timeout exception is caused by this:

CHECKPOINT 
DBCC DROPCLEANBUFFERS 

Memory pressure exception is caused by this:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE



Unfortunately we don't know the reason for having this maintenance task happening. My first thought is remove this task, but I don't know if there would be more problems by not having these running.

Are these DBCC commands really necessary?

Thanks
First, find the person who put that job in place and have him/her tied to a chair listening to cats scratching a chalk board while you go for lunch. 

When done with that, compare your resource utilization and performance data for extended periods before and after the job runs. I suspect you'll see perf dips and a number of resources getting really busy after the job executes. You can temporarily suspend the job and monitor closely for a week to see what happens. I doubt you'll run into problems but as with all changes to a prod system, still keep a close eye for a while and make sure you capture perf, resource usage and waits data.

These sprocs are typically run in a test environment, especially for performance testing, to see how specific queries or the overall workload behaves on a cold-start equivalent. It can also be used to see how specific parts of the system handles unexpected load spikes especially storage. 

We have encountered a single support engineer whose standard perf tuning method involves doing these things on a production system; we couldn't get rid of him fast enough. Running all of them on a production system regularly is pretty close to rebooting your server daily to keep it healthy and doing it in the middle of a busy day for best results - really, really shouldn't.