Re: Checkpoint causes need for better IO subsystem?
- From: "Geoff N. Hiten" <SQLCraftsman@xxxxxxxxx>
- Date: Tue, 7 Mar 2006 21:12:18 -0500
Checkpoints tend to be semi-random writes across the entire database file
footprint. Batch inserts can be sequential or not, depending on whether
your clustered index based on a monotonically increasing column. As such,
the random write capability of the drives comes into play, not the data
throughput limit. Besides, the theoretical limits stated by the
manufacturers are under very narrowly defined conditions. If you believe
manufacturer specs match up to SQL Server usage, I have a bridge I would
like to offer for sale. :)
Given that you are on a bus architecture disk subsystem, high write activity
can block read activity, this causing your slow response. Five to ten
seconds typically matches the duration of a normal checkpoint. A high-end
disk subsystem with one or more gigabytes of cache and a full-duplex
connection path can help. That translates to a Fibre-Channel connected SAN.
I would also check on the Page Life Expectency performance counter. If it
is low, you may benefit from more physical RAM in the server. This will
allow more data to stay in cache longer, thus eliminating the need to
constantly reload the data from the disks.
Finally, you can change the clustered indexes to use a monotonically
increasing key, thus making the data loads sequential and reducing the
number of page splits, random IO operations, and overall server load during
a data load.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:614A712D-3C95-44F7-9D6A-719788FAAC83@xxxxxxxxxxxxxxxx
Using Profiler and PerfMon, when there is a checkpoint, the durations of
INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
causes distress for clients and needs to be fixed.
Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
the 10 seconds that the checkpoint takes place..10% have a duration
increase.
It's during pereids of batch inserts that this happens which occurs many
times during the day at odd intervals.. I've written about this before and
someone suggested that the batch inserts take place off-peak. Can't be
done.
The nature of the business dictates otherwise.
It's also been suggested that a better IO subsystem be installed. We're
using a 168bit/sec controller card and using PerfMon and tracking data
transfered over all of the hard drives, that during these batch inserts,
the
total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
controller is able to handle the data.
To answer your other question..The MDF, LDF and C: drive are all on their
own physical separate disk drives and have been defragmented. These are
huge
130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
Any Help appreciated.
Don
SQL 2000 SP4
.
- Follow-Ups:
- Re: Checkpoint causes need for better IO subsystem?
- From: donsql22222
- Re: Checkpoint causes need for better IO subsystem?
- Prev by Date: Re: bcp accent problem
- Next by Date: RE: Sql Server 2005 How to ?
- Previous by thread: Re: bcp accent problem
- Next by thread: Re: Checkpoint causes need for better IO subsystem?
- Index(es):
Relevant Pages
|
