Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm helping a client with a vertical market web application written in ASP.NET. It uses SQL Server 2005 for the back end. The client is having performance issues with the application and the vendor support has not been very helpful to resolve the issue. So the client brought me to have a fresh set of eyes look at the problem. While doing some basic review of the system and hardware I noticed that a "Maintenance" job was scheduled in SQL Server to run every 15 minutes that performs a DBCC CHECKDB and then a Transaction Log Backup. This job was taking approximately 6 minutes to perform during business hours.

When I questioned the vendor's support staff about was this necessary and could we reschedule the DBCC CHECKDB to once per night, the response was "that was proprietary information and don't touch anything".

What I am basically looking to understand is. Is there a reason why DBCC CHECKDB should be ran every 15 minutes? Besides, the obvious affect while the job is running, how else could running this command affect system performance? For example, does it cause SQL Server to loose what it has in memory?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

DBCC CHECKDB can indeed affect system performance - CPU, disk I/O and memory can all be affected by it. Here are some documents from Microsoft that outline how DBCC CHECKDB can affect the performance of your system:

http://msdn.microsoft.com/en-us/library/aa937548(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms175515(SQL.90).aspx

Microsoft's general recommendation is that it should be run during low usage times - so once overnight or something like that - not every 15 minutes every day.

share|improve this answer
    
Thanks for the best practice links. I had previously read the BOL on the DBCC CHECKDB command (msdn.microsoft.com/en-us/library/ms176064.aspx), but these should help me convince the vendor to change the Maintenance plan. –  Frank Perez Mar 4 '10 at 17:33
add comment

DBCC CHECKDB is to show any corruption issues in your database. Unless there are underlying hardware or other issues that give one to believe that this will happen under normal application operation I'm not sure of why that might be.

Is the performance issue response or through-put? What does the underlying data model look like? Do you have any feel yet for the types of queries that are common? Proper indexes in place (columns in WHERE clause on which there is no index)?

share|improve this answer
    
I do have a feel for which queries are common and most expensive in terms of CPU, Read, and Write costs. And yes there are columns in the JOIN and WHERE clauses with no indexes. Unfortunately the Vendor is not interested in addressing those issues and is trying to convince the client to purchase new hardware. –  Frank Perez Mar 4 '10 at 17:38
    
I guess that explains the "proprietary information". –  jl. Mar 4 '10 at 18:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.