Index Rebuilding question

Hi,
Novice DBA here.
Is there any reason or bad side effect why I should NOT rebuild/reorganise
an index?
We have a table that has 500,000 leaf rows on 2500 pages that is 70%
fragmented. There are a couple others that are similar too.
We have never done a maintenance plan (other than backups) and things appear
to be running fine. Noone is complaining about speed. We have had this
DB/application for about 6 months now.
I was looking through the available reports and saw the Index Physical Stats
report and I'm wondering if things can get much better than just fine if I
do the operation recommended.
Thanks
Brian
Brian,
Fragmentation does not affect the general performance of a database server,
it is a performance problem for range scans operations only, that is, queries
that scan part or all of a table.
If you have fragmentation and have not seen any changes in the performance
of your application is maybe because your indexes are not big enough (See
number of pages scanned as shown on dbcc showcontig) or maybe your
application is not doing enough range scans.
According to BOL the recommendation to reindex depends on the value of
avg_fragmentation_in_percent value in sys.dm_db_index_physical_stats.
BOL recommends to reorganize if this value is between 5 and 30 % and to
rebuild if this value is greater than 30 %.
Anyway, even if indexing were not needed it will not hurt your system either
(except the resources used when you are running the index operation and maybe
some additional transaction log space).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Brian" wrote:

> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things appear
> to be running fine. Noone is complaining about speed. We have had this
> DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical Stats
> report and I'm wondering if things can get much better than just fine if I
> do the operation recommended.
> Thanks
> Brian
>
>
|||1) Make sure the transaction log is sized big enough to avoid growths while
the index is rebuilding.
2) Performance will suffer.
3) unless you have enterprise edition and have online rebuild on the index
will be unavailable.
4) Ensure you size the database sufficiently in advance so that there is
sufficient contiguous space to lay the pages down in order.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Brian" <s@.y> wrote in message
news:%23nkNAKeSIHA.4888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things
> appear to be running fine. Noone is complaining about speed. We have had
> this DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical
> Stats report and I'm wondering if things can get much better than just
> fine if I do the operation recommended.
> Thanks
> Brian
>
|||Thank you both for your replies.
Together your replies indicate it is better to do it than not but not a
great priority.
We are using the SQLExpress so I will reindex one day when noone is about.
Thanks
Brian
"Brian" <s@.y> wrote in message
news:%23nkNAKeSIHA.4888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Novice DBA here.
> Is there any reason or bad side effect why I should NOT rebuild/reorganise
> an index?
> We have a table that has 500,000 leaf rows on 2500 pages that is 70%
> fragmented. There are a couple others that are similar too.
> We have never done a maintenance plan (other than backups) and things
> appear to be running fine. Noone is complaining about speed. We have had
> this DB/application for about 6 months now.
> I was looking through the available reports and saw the Index Physical
> Stats report and I'm wondering if things can get much better than just
> fine if I do the operation recommended.
> Thanks
> Brian
>