| Author |
 Topic  |
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
 Posted - 03/22/2006 : 05:07:03
|
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 |
 |
|
|
paulrandal
Yak with Vast SQL Skills
USA
899 Posts |
Posted - 03/22/2006 : 12:23:42
|
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.) |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 03/23/2006 : 04:37:37
|
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 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 03/30/2006 : 15:52:33
|
Installed Cumulative hotfix 2187, it did help locking issues when running indexdefrag.
good!
rockmoose |
|
|
|
dcrooks
Starting Member
4 Posts |
Posted - 04/06/2006 : 15:44:42
|
Is there a way to analyze a SQL Server 2000 server with SQL Server 2005 tools? TIA!
David L. Crooks |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 05/10/2006 : 01:32:14
|
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 |
|
|
|
Andyyy
Starting Member
United Kingdom
2 Posts |
Posted - 06/20/2008 : 04:17:16
|
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 |
|
|
|
tosscrosby
Aged Yak Warrior
USA
676 Posts |
Posted - 06/20/2008 : 11:24:46
|
Not sure but Paul and his wife, Kimberly, have a website that you may want to check.
http://www.sqlskills.com
Terry |
|
|
|
paulrandal
Yak with Vast SQL Skills
USA
899 Posts |
Posted - 06/22/2008 : 13:42:13
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 06/22/2008 : 14:03:34
|
Please make a notice here at SQLTeam so we know when to buy it!
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
tosscrosby
Aged Yak Warrior
USA
676 Posts |
Posted - 06/23/2008 : 10:47:57
|
Sounds like a "must read" to me.
Terry |
|
|
|
Topic |
|
Bring out your dead!
