SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Bring out your dead!
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/22/2006 :  05:07:03  Show Profile  Reply with Quote
quote:
A summary here would be good - I'm pretty swamped this week.

Hi, I ran 2 consecutive checkdb's during a low-activity period.

nothing running/quiet period (measured 15 min)
processor: 5.3%
disk queue: 3.6

checkdb physical_only, no_infomsgs (took 15 min)
processor: 9%
disk queue: 41

checkdb no_infomsgs (took 18:40 min)
processor: 14.5%
disk queue: 50

It seems to me that the IO system is the real bottleneck here.
If you have any comments or thoughts on this, i am all ears.

rockmoose
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 03/22/2006 :  12:23:42  Show Profile  Visit paulrandal's Homepage  Reply with Quote
Nothing much to add - your IO system is definitely a bottleneck for CHECKDBs. Thanks for posting the info.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/23/2006 :  04:37:37  Show Profile  Reply with Quote
quote:
Originally posted by paulrandal

Nothing much to add - your IO system is definitely a bottleneck for CHECKDBs. Thanks for posting the info.




We've been thinking of changing the SAN to a direct attached storage array with 2 RAID10 sets iof the SAN's (1?) RAID5. (It's about the same cost).

The san engineers say that there probably will not be any performance improvement for day-to-day operations due to the fact that the SAN has a "large" cache. More likely that configuration can be slower for day-to-day operations.
Of course this does not apply when one accesses / writes data not in the cache.

Well, it's when we do large IO operations that we experience that the SAN is not so fast.
We're having a discussion with them right now, and they are investigationg the IO capacity of the SAN.

Thanks for your feedback and time.

rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/30/2006 :  15:52:33  Show Profile  Reply with Quote
Installed Cumulative hotfix 2187, it did help locking issues when running indexdefrag.
good!

rockmoose
Go to Top of Page

dcrooks
Starting Member

4 Posts

Posted - 04/06/2006 :  15:44:42  Show Profile  Reply with Quote
Is there a way to analyze a SQL Server 2000 server with SQL Server 2005 tools? TIA!

David L. Crooks
Go to Top of Page

sureshot
Yak Posting Veteran

72 Posts

Posted - 05/10/2006 :  01:32:14  Show Profile  Reply with Quote
Running CHECKDB with physical_only
on a 170GB database in SQL 2005

8 dual core Xeon CPUs
CPU usage 2-3%

disk reads/sec = 800
disk writes/sec = 9
disk queue length = 65

8 disk RAID 1+0 array connected via fibre channel HBAs

I/Os per disk = (800 + (2 * 9)) / 8 = 102.25
disk queue length per disk = 8.125

It takes about 50 minutes. Seems to be I/O limited but with 8 1+0 disks I'm not sure what more can reasonably be done. Will throwing even more disks at it really help?

Logs are on a separate disk array but tempdb is on this same disk array. Will moving tempdb to its own disk array help CHECKDB? What about having multiple tempdb data files as per http://support.microsoft.com/kb/328551

?

Edited by - sureshot on 05/10/2006 01:33:39
Go to Top of Page

Andyyy
Starting Member

United Kingdom
2 Posts

Posted - 06/20/2008 :  04:17:16  Show Profile  Click to see Andyyy's MSN Messenger address  Reply with Quote
Hi,

Not sure if this is the right place for this post, however, i've tried to have a fish around to try and find it, however, i've been unable to find the whitepaper which Paul was going to write on DBCC CHECKDB for SQL 2005. Anyone got an idea where this could be found?

Thanks
Andy
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 06/20/2008 :  11:24:46  Show Profile  Reply with Quote
Not sure but Paul and his wife, Kimberly, have a website that you may want to check.

http://www.sqlskills.com


Terry
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 06/22/2008 :  13:42:13  Show Profile  Visit paulrandal's Homepage  Reply with Quote
The whitepaper never got written. Instead there's a bunch of stuff on my blog under the CHECKDB From Every Angle tag. Kimberly, Kalen Delanay and I are writing a book on SQL Server 2008 Internals that will have a long, detailed chapter on CHECKDB. Due date is Feb or so next year.

Thanks

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 06/22/2008 :  14:03:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please make a notice here at SQLTeam so we know when to buy it!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 06/23/2008 :  10:47:57  Show Profile  Reply with Quote
Sounds like a "must read" to me.

Terry
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000