Re: Checkpoint causes need for better IO subsystem?
- From: "Geoff N. Hiten" <SQLCraftsman@xxxxxxxxx>
- Date: Wed, 8 Mar 2006 16:29:34 -0500
850 is a bit on the low side. 4000-10000 or higher is considered good. As
it is, you are rewriting memory every 14 minutes. Not great.
Identity columns provide monotonically increasing keys. SQL creates a
clustered index out of your primary key by default, but that is not a
requirement. You can separate the two.
Narrow clustered indexes work better. Google the following string for some
excellent articles on clustered index selection and its impact on
performance:
clustered index sql kimberly tripp
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3F381A8B-84FB-4131-917E-24416EA5FE7E@xxxxxxxxxxxxxxxx
The Page Life Expectency performance counter hoovers around 850..not sure
if
that's good or bad.
There's still 700M of RAM available and SQL's set dynamically to use all
4GB
of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
taking.
Not sure about how to setup a monotonically increasing key.
Currently, the Clustered index is on multiple cols (2)... (Name, Date)
Would a monotonically increasing key include a new column with an
incrementing sequential value?
such as this?
(newvalue, Name, Date)
don
"Geoff N. Hiten" wrote:
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
.
- References:
- Re: Checkpoint causes need for better IO subsystem?
- From: Geoff N. Hiten
- Re: Checkpoint causes need for better IO subsystem?
- From: donsql22222
- Re: Checkpoint causes need for better IO subsystem?
- Prev by Date: Re: Checkpoint causes need for better IO subsystem?
- Next by Date: Re: Securables for Application Role in Management Studio do not stick
- Previous by thread: Re: Checkpoint causes need for better IO subsystem?
- Next by thread: Re: Checkpoint causes need for better IO subsystem?
- Index(es):
Relevant Pages
|
Loading
