December 4, 2009 at 2:04 pm
I have a strange scenario. When I use the "Check Database Integrity" as part of a Maintenance Plan, that portion of the SSIS package typically runs in under 30 seconds. When I "View SQL", and copy and paste DBCC CHECKDB portions to SSMS and run it, it takes 3-4 minutes to complete.
So, the question is, why is it taking so long using straight SQL code vs. the "Check Database Integrity" in the SSIS package?
Confused... any help would be greatly appreciated. 🙂
December 4, 2009 at 2:21 pm
Got any options checked in the maintenance plan?
What's the server (and IO) load at the two times?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2009 at 2:36 pm
I actually just decided to isolate the largest single database and create a plan for it to do quick compares. So, for 1 DB I am only using the "Check Database Integrity" with "Include Indexes". The "View SQL" shows:
USE [MyDB]
GO
DBCC CHECKDB WITH NO_INFOMSGS
When I run this from the pan it takes 30 seconds. When I paste the code into SSMS it takes 3-4 minutes. Activity on the box is only myself as it is a test server. No other people and / or processes are running.
December 4, 2009 at 5:39 pm
you might try tracing the task and see if something else is being called
December 5, 2009 at 3:17 am
I've run extensive profiler traces and there is no additional / unusual activity going on. In additional to this, the SSIS package always runs in 30 seconds while the SSMS codealways takes 3-4 minutes. Just wondering if there's something else going on in the SSIS package to make things run faster... but there's nothing that I can see in the trace results.
December 7, 2009 at 1:17 pm
Is the maintenance plan always running during non-peak times, or are you running it around the same time frame that you are running checkdb manually? I am just wondering what the pre-existing load is on the server is when the two tasks are ran.
Joie Andrew
"Since 1982"
April 2, 2010 at 6:59 am
Finally, the answer. There is indeed a bug when using the GUI:
Quote:
Another SP2 Maintenance Plan bug - Database Integrity Checks
** Update: A hotfix is now available to address this issue here for SQL versions 3042 through 3053**
** Update: A hotfix is now available to address this issue here for SQL versions 3150 through 3158**
Tony has picked up on another issue with SP2 in regard to maintenance plans in that integrity checks are only ever checking the master database. I've done some checking on an RTM and SP1 server and this bug wasn't present so it's definately a regression. Note that this could lead to undetected database corruption if you rely on Maintenance Plans and SSIS Check Database Integrity Tasks. This is not fixed in the SP2 Hotfix I blogged about earlier. The workaround is to uncheck the Include Indexes checkbox in the Check Database Integrity Task however that of course means that you won't get the same level of checks for nonclustered indexes on user tables. My advice - check your database integrity yourself using a simple TSQL job - don't rely on your maintenance plans if you're on SP2!
Published 07 March 2007 15:02 by sqldbatips
Filed under: Management Studio, SQL Tools, Service Pack
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply