Saturday, February 11, 2012

About db reindex

I just read an artice about database reindex published by microsoft
corporation, which states that the performance after doing db reindex is not
better than before defragment in OLTP system. In DSS system, it is faster
than before defragment. I got confused by this article. Do I need to do the
db reindex or defragment in OLTP system? Thanks.Iter wrote:
> I just read an artice about database reindex published by microsoft
> corporation, which states that the performance after doing db reindex is n
ot
> better than before defragment in OLTP system. In DSS system, it is faster
> than before defragment. I got confused by this article. Do I need to do t
he
> db reindex or defragment in OLTP system? Thanks.
Well, it is definitely a generalization. What they probably meant, is
that most access in an OLTP system are seeks for exact matches. In that
case fragmentation is not much of a problem, because the accessed index
pages and data pages aren't adjacent anyway.
If you have an OLAP type system, you are likely to request large
datasets with many consecutive pages. In that case these will be
adjacent after a reindex, which will speed up I/O (sequential I/O is
much faster than random I/O).
But the storey is not entirely fair, because a reindex will not only
remove fragmentation, but it will also remove unused space (depending on
the fill factor) and will rebalance the index tree. This can result in a
shallower index, and a shallower index will improve the performance of
all queries that use the index.
And of course, even in an OLTP system you will not always be doing just
exact match queries that will access only one (consecutive) data page.
So in general the statement is true, but most likely you can still
benefit from reindexing on your specific system.
HTH,
Gert-Jan|||To add on to Gert-Jan's response, defragmenting indexes can improve buffer
efficiency and reduce I/O increasing data density. I have seen noticeable
OLTP performance improvement by reorging clustered indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:6CD68521-EE02-4578-97C2-C2926B3D869F@.microsoft.com...
>I just read an artice about database reindex published by microsoft
> corporation, which states that the performance after doing db reindex is
> not
> better than before defragment in OLTP system. In DSS system, it is faster
> than before defragment. I got confused by this article. Do I need to do
> the
> db reindex or defragment in OLTP system? Thanks.
>

No comments:

Post a Comment