DBCC Usage on production system

Question

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:

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

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

Answer 1

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.

Answer 2

Unfortunately the answer is going to be it depends. From what you have in the maintenance plan it seems whomever put them in place was trying to reset the server statistics, cache and running values at a specific time of the day to what it would be when SQL Server first starts up without actually restarting SQL Server.

CHECKPOINT is going to instruct the database to write all of the dirty pages in memory to disk. By default SQL Server will do automated checkpoints on the database to ensure that it can perform a recovery on the database within a specific amount of time. There is more info on it on Microsoft's TechNet. Running this manually could be a fairly intensive process if there are a lot of pages in memory that need to be written to disk which could put added pressure on your disk I/O subsystem. It might be a good idea to check the CHECKPOINT interval described in the article to see if it's been changed from it's default of 0.

DBCC DROPCLEANBUFFERS is going to clean out the memory of any 8KB pages that has already been loaded from disk. This is going to force SQL Server to reload data into memory from disk to fulfill incoming queries that don't have data residing in memory. MS's TechNet article on Buffer Management Running this command on a machine with a lot of load is going to put increased pressure on the disk I/O subsystem which in turn could be causing the timeouts.

DBCC FREESYSTEMCACHE ('ALL') - This will clear out all of the caches that SQL Server has built up since it was last restarted. The memory pressure exception you are getting is most likely do to SQL Server having to build back up all of it's caches including it's procedure cache, which also is cleared with the DBCC FREEPROCCACHE command. This means that for every query that comes in if there is not a execution plan already built it's going to put pressure on the CPU and Memory to generate the execution plan before it can actually fulfill the query.

@Kin's answer to the difference between FREESYSTEMCACHE and FREEPROCCACHE

TechNet's details on FREESYSTEMCACHE

TechNet's details on FREEPROCCACHE

DBCC FREESESSIONCACHE - Will remove the cache for distributed queries that use OPENROWSET and OPENDATASOURCE. Details on this command are a little sparse. SQL SERVER CENTRAL blog post

Ultimately the commands should really only be used in a development/test environment where you are tuning the database for performance. And while there could be legitimate reasons to use them in a production environment, unless you have a really good reason(s) to use them on occasion or a regular basis I would say they are probably causing more problems then they are resolving. But again without knowing the history of why they were put in place you are going to need to investigate the database schema/procedures and the application to try and identify if there is or was a reason for them.