Index on identity column

Hi everyone,
I am hoping that someone has come across the following situation and
can give me some insight on their findings. I have a table that logs a
lot of information that is then staged into a data warehouse. This
table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
database inserts get jammed up and bring my server almost to a halt.
The inserts keep coming in but the server just cannot keep up with
heavy load. I have had this same setup do 100 transactions a second
with no problem so the hardware us up to par. After the slow down has
started, I can try rebooting the server, stopping services etc but
will not the fix the problem. The only thing that I have found to work
is to rebuild or defrag the index on the identity column. I have used
both a clustered and non clustered index on this column and the
behaviour is similiar after the 20 - 30 day timeframe. I realize that
as pages get fragmented it greatly slows down insert performance but I
don't see how a clustered index can get that fragmented on an identity
column that it would cause a slowdown as a result of page splitting
which I beleive to be the cause. Taking the index off this column is
not an option because I need to quickly scan this table for data
warehouse population. This table is now at 130,000,000 rows and I plan
to truncate this down every few months as I don't need the
transactional data for very long. So, I guess my question is is there
any advice that you can give me on maybe managing these indexes to
avoid this problem or is a rebuild or defrag my only option? BTW I am
running SQL Server 2000. Thank you.
CoreyHi Corey
What makes you think page splits are the problem? Have you looked at the
Performance Monitor counter Page Splits/second when you are having your
slowdowns and compared it to the page splitting rate when not having
problems. Have you run dbcc showcontig to see what kind of fragmentation you
are getting? Do you ever actually rebuild the index or indexes?
Actually, 20-30 days is a long time to not do any maintenance on an index
used for heavy insert operations. You're lucky you don't have problems more
often. I would suggest rebuilding the index on a weekly basis, with a
fillfactor less than full. I couldn't suggest a particular value for the
fillfactor, that would need to be determined by testing, but you might start
with rebuilding the index with a fillfactor of 75 (or 75% of the current
value, which SQL Server attempts to observe during defragging) and see how
that works.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Corey" <kildenc@.hotmail.com> wrote in message
news:fbb80b9b.0401051445.3bc0c22d@.posting.google.com...
> Hi everyone,
> I am hoping that someone has come across the following situation and
> can give me some insight on their findings. I have a table that logs a
> lot of information that is then staged into a data warehouse. This
> table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
> database inserts get jammed up and bring my server almost to a halt.
> The inserts keep coming in but the server just cannot keep up with
> heavy load. I have had this same setup do 100 transactions a second
> with no problem so the hardware us up to par. After the slow down has
> started, I can try rebooting the server, stopping services etc but
> will not the fix the problem. The only thing that I have found to work
> is to rebuild or defrag the index on the identity column. I have used
> both a clustered and non clustered index on this column and the
> behaviour is similiar after the 20 - 30 day timeframe. I realize that
> as pages get fragmented it greatly slows down insert performance but I
> don't see how a clustered index can get that fragmented on an identity
> column that it would cause a slowdown as a result of page splitting
> which I beleive to be the cause. Taking the index off this column is
> not an option because I need to quickly scan this table for data
> warehouse population. This table is now at 130,000,000 rows and I plan
> to truncate this down every few months as I don't need the
> transactional data for very long. So, I guess my question is is there
> any advice that you can give me on maybe managing these indexes to
> avoid this problem or is a rebuild or defrag my only option? BTW I am
> running SQL Server 2000. Thank you.
> Corey|||What recovery mode are you in for the database? If it is a warehouse you
might be able to use simple recovery which will help you with the log
growing. Also, what other indexes are on the table? I would definitely
recommend keeping a clustered index on that table as heaps reclaim empty
space and if you are deleting and inserting that would cost you as well.
When you get to the point where you can not insert any records what is going
on with performance? As Kalen mentioned knowing if you are having page
splits or blocking/deadlocks will help you to remedy the situation.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Corey" <kildenc@.hotmail.com> wrote in message
news:fbb80b9b.0401051445.3bc0c22d@.posting.google.com...
> Hi everyone,
> I am hoping that someone has come across the following situation and
> can give me some insight on their findings. I have a table that logs a
> lot of information that is then staged into a data warehouse. This
> table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
> database inserts get jammed up and bring my server almost to a halt.
> The inserts keep coming in but the server just cannot keep up with
> heavy load. I have had this same setup do 100 transactions a second
> with no problem so the hardware us up to par. After the slow down has
> started, I can try rebooting the server, stopping services etc but
> will not the fix the problem. The only thing that I have found to work
> is to rebuild or defrag the index on the identity column. I have used
> both a clustered and non clustered index on this column and the
> behaviour is similiar after the 20 - 30 day timeframe. I realize that
> as pages get fragmented it greatly slows down insert performance but I
> don't see how a clustered index can get that fragmented on an identity
> column that it would cause a slowdown as a result of page splitting
> which I beleive to be the cause. Taking the index off this column is
> not an option because I need to quickly scan this table for data
> warehouse population. This table is now at 130,000,000 rows and I plan
> to truncate this down every few months as I don't need the
> transactional data for very long. So, I guess my question is is there
> any advice that you can give me on maybe managing these indexes to
> avoid this problem or is a rebuild or defrag my only option? BTW I am
> running SQL Server 2000. Thank you.
> Corey