Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rspinell
Starting Member
36 Posts |
 Posted - 2007-07-24 : 01:41:59
|
| We're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's. We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring. Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me. Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-24 : 03:15:00
|
| Has this only started happening recently?Perhaps physical Defrag of the database files would help (see CONTIG.EXE from Sysinternals)Kristen |
 |
|
|
rspinell
Starting Member
36 Posts |
Posted - 2007-07-24 : 06:56:48
|
| Actually, I believe it has always been happening, I just could never figure out what it was. Since im a SQL newbie, this behavior is not supposed to occur, or does avg disk queue length always shoot up for anyone who runs SQL whever a checkpoint occurs ?Thank you |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-24 : 08:34:09
|
| Well, I suppose the disk queue is bound to increase at a checkpoint if there is "real work" to be done, but a permanently slow response sounds like some sort of bottleneck on the way to the disk storage ??Kristen |
|
|
|
rspinell
Starting Member
36 Posts |
Posted - 2007-07-24 : 09:19:35
|
| Kristen, would you happen to know in your experience when one of you large DB's does a checkpoint, does your avg disk queue length spike to high levels like that ? All other times the disk queue length is never more then 2-3 and we have 6 drives on that array. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-24 : 09:55:25
|
Its not really the sort off thing I'm close to, but I would be pretty sure the answer is "no". Hopefully someone more in tune with this will be along in a moment.It feels to me to be more likely that there is a problem [either physical or a configuration issue] with the Controller (or the connection to the disks if they are "remote" to the Server), or some caching problem; or the disks continually retrying  What type of Array is it? RAID10 hopefully?Are the databases on their own disk channels? (Preferably Logs on one and Data on another; O/S should be on a separate channel, and ideally additional channels for Backups and TEMPDB). The other Apps on that server may be contributing to inefficiency on the disks, or robbing RAM or somesuch. They should be using a separate channel if they have lots of disk activity of their own.145GB is a big database, how much RAM have you got? How much is SQL Server actually able to use? What have you got in BOOT.INI [/3GB and/or /AWE ??] What is the running value for SQLServer:Memory Manager\Total Server Memory and does it exceed the physical memory available to SQL Server?Is the O/S "enterprise" or just the DB?Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-24 : 11:15:48
|
| You might consider running a disk benchmark on each disk array with Microsoft's SQLIO utility to verify that your disk arrays are getting acceptable performance. You do not need SQL Server to run SQLIO, so you can run it on any system.I had a problem on a server a couple of weeks ago, and used SQLIO to discover that one array was getting only about 3% of expected IO performance. Once the vendor replaced a bad drive, performance returned to normal.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Server freezes while doing checkpoint ?!