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
>
Showing posts with label rebuilding. Show all posts
Showing posts with label rebuilding. Show all posts
Monday, March 26, 2012
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
BrianBrian,
Fragmentation does not affect the general performance of a database server,
it is a performance problem for range scans operations only, that is, querie
s
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 mayb
e
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 appe
ar
> 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 Sta
ts
> 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
>
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
BrianBrian,
Fragmentation does not affect the general performance of a database server,
it is a performance problem for range scans operations only, that is, querie
s
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 mayb
e
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 appe
ar
> 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 Sta
ts
> 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
>
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
BrianBrian,
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
>sql
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
BrianBrian,
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
>sql
Index Rebuilding
Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am going to Rebuild the index on tables of production database.
> >
> > I will use 'dbcc dbreindex' command to do this index rebuilding.
> >
> > Now, I want to know what are the Prerequisites should i take to do
> > this job.
> >
> > And also what are the Precautions shuld i take.
> >
> > Please guide me in this regard.
> >
> >
> > Thanks & Regards,
> > Sajid.
> >
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
> > DBREINDEX rebuilds indexes completely and is resource intensive. This may
> > cause blocking and performance issues so you should run it during minimal
> > activity. Also, DBREINDEX is fully-logged regardless of your database
> > recovery model so make sure you have sufficient log space available.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <csajid@.gmail.com> wrote in message
> > news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I am going to Rebuild the index on tables of production database.
> > >
> > > I will use 'dbcc dbreindex' command to do this index rebuilding.
> > >
> > > Now, I want to know what are the Prerequisites should i take to do
> > > this job.
> > >
> > > And also what are the Precautions shuld i take.
> > >
> > > Please guide me in this regard.
> > >
> > >
> > > Thanks & Regards,
> > > Sajid.
> > >
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am going to Rebuild the index on tables of production database.
> >
> > I will use 'dbcc dbreindex' command to do this index rebuilding.
> >
> > Now, I want to know what are the Prerequisites should i take to do
> > this job.
> >
> > And also what are the Precautions shuld i take.
> >
> > Please guide me in this regard.
> >
> >
> > Thanks & Regards,
> > Sajid.
> >
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
> > DBREINDEX rebuilds indexes completely and is resource intensive. This may
> > cause blocking and performance issues so you should run it during minimal
> > activity. Also, DBREINDEX is fully-logged regardless of your database
> > recovery model so make sure you have sufficient log space available.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <csajid@.gmail.com> wrote in message
> > news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I am going to Rebuild the index on tables of production database.
> > >
> > > I will use 'dbcc dbreindex' command to do this index rebuilding.
> > >
> > > Now, I want to know what are the Prerequisites should i take to do
> > > this job.
> > >
> > > And also what are the Precautions shuld i take.
> > >
> > > Please guide me in this regard.
> > >
> > >
> > > Thanks & Regards,
> > > Sajid.
> > >
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.
Index Rebuilding
Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
[vbcol=seagreen]
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
>
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
[vbcol=seagreen]
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
>
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.
Wednesday, March 7, 2012
index defrag problem
As per BOL 2005 , i tried to defrag indexes by both these methods
1.reorganizing an index
2.rebuilding an index
but still some of my tables indexes have the same avg_fragmentation_in_percent that they were in before i did my defrag using the above 2 method.
i used sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG
Any pointers ? thankscan you display the results from the dm_db_index_physical_stats|||
database_id object_id indexe_id partition_number alloc_unit_type_desc index_depth index_level avg_frag_in_perfrg_cnt
10118511CLUSTERED INDEXIN_ROW_DATA2058.333333333333381.512NULLNULLNULLNULLNULLNULLNULLNULL
NOTE : i used the new alter index instead of the DBCC INDEXDEFRAG or DBCC DBREINDEX|||
I am having the same issue but only on certain tables in my database. Even when I create a new index on the problem tables, the index is 60%-80% fragmented. I tried rebuilding, defragging, and dropping and recreating the index and I am getting the same results. Here is one of the index properties...
DBCC SHOWCONTIG scanning 'DimTest' table... Table: 'DimTest' (59251366); index ID: 4, database ID: 12 LEAF level scan performed. - Pages Scanned................................: 5 - Extents Scanned..............................: 5 - Extent Switches..............................: 4 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 20.00% [1:5] - Logical Scan Fragmentation ..................: 80.00% - Extent Scan Fragmentation ...................: 80.00% - Avg. Bytes Free per Page.....................: 1608.2 - Avg. Page Density (full).....................: 80.13%|||Wanted to revisit this, as there hasn't been any resolutions or suggestions. This is happening at our company as well, but the clustered is fragmented even lower. Anyone have any ideas as to why this is happening on 2005?
Thanks...
|||I am having issues defraging indexes on several of my tables. I noticed in my test environment that one of the indexes (a primary key, clustered index, indexed on an identity column) was 87% fragmented. I rebuild the index and it was still 87% fragmented. Occasionally, after the rebuild it will drop to 82% fragmented. It does the same thing if I try a reorg or use DBCC DBReindex. Any ideas?
|||I'm having the same problem. I have run DBCC DBREINDEX, DBCC INDEXDEFRAG, dropped the index and recreated it. I've even changed my nonclustered index from a composit to a single index. Nothing will get my scan density over 50%. UGGGGGGG!!!!
index defrag problem
As per BOL 2005 , i tried to defrag indexes by both these methods
1.reorganizing an index
2.rebuilding an index
but still some of my tables indexes have the same
avg_fragmentation_in_percent that they were in before i did my
defrag using the above 2 method.
i used sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG
Any pointers ? thankscan you display the results from the dm_db_index_physical_stats|||
database_id object_id indexe_id partition_number alloc_unit_type_desc index_depth index_level avg_frag_in_perfrg_cnt
10118511CLUSTERED INDEXIN_ROW_DATA2058.333333333333381.512NULLNULLNULLNULLNULLNULLNULLNULL
NOTE : i used the new alter index instead of the DBCC INDEXDEFRAG or DBCC DBREINDEX|||
I am having the same issue but only on certain tables in my database. Even when I create a new index on the problem tables, the index is 60%-80% fragmented. I tried rebuilding, defragging, and dropping and recreating the index and I am getting the same results. Here is one of the index properties...
DBCC SHOWCONTIG scanning 'DimTest' table...
Table: 'DimTest' (59251366); index ID: 4, database ID: 12
LEAF level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 20.00% [1:5]
- Logical Scan Fragmentation ..................: 80.00%
- Extent Scan Fragmentation ...................: 80.00%
- Avg. Bytes Free per Page.....................: 1608.2
- Avg. Page Density (full).....................: 80.13%|||
Wanted to revisit this, as there hasn't been any resolutions or suggestions. This is happening at our company as well, but the clustered is fragmented even lower. Anyone have any ideas as to why this is happening on 2005?
Thanks...
|||I am having issues defraging indexes on several of my tables. I noticed in my test environment that one of the indexes (a primary key, clustered index, indexed on an identity column) was 87% fragmented. I rebuild the index and it was still 87% fragmented. Occasionally, after the rebuild it will drop to 82% fragmented. It does the same thing if I try a reorg or use DBCC DBReindex. Any ideas?
|||I'm having the same problem. I have run DBCC DBREINDEX, DBCC INDEXDEFRAG, dropped the index and recreated it. I've even changed my nonclustered index from a composit to a single index. Nothing will get my scan density over 50%. UGGGGGGG!!!!
index defrag problem
As per BOL 2005 , i tried to defrag indexes by both these methods
1.reorganizing an index
2.rebuilding an index
but still some of my tables indexes have the same
avg_fragmentation_in_percent that they were in before i did my
defrag using the above 2 method.
i used sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG
Any pointers ? thankscan you display the results from the dm_db_index_physical_stats|||
database_id object_id indexe_id partition_number alloc_unit_type_desc index_depth index_level avg_frag_in_perfrg_cnt
10118511CLUSTERED INDEXIN_ROW_DATA2058.333333333333381.512NULLNULLNULLNULLNULLNULLNULLNULL
NOTE : i used the new alter index instead of the DBCC INDEXDEFRAG or DBCC DBREINDEX|||
I am having the same issue but only on certain tables in my database. Even when I create a new index on the problem tables, the index is 60%-80% fragmented. I tried rebuilding, defragging, and dropping and recreating the index and I am getting the same results. Here is one of the index properties...
DBCC SHOWCONTIG scanning 'DimTest' table...
Table: 'DimTest' (59251366); index ID: 4, database ID: 12
LEAF level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 20.00% [1:5]
- Logical Scan Fragmentation ..................: 80.00%
- Extent Scan Fragmentation ...................: 80.00%
- Avg. Bytes Free per Page.....................: 1608.2
- Avg. Page Density (full).....................: 80.13%|||
Wanted to revisit this, as there hasn't been any resolutions or suggestions. This is happening at our company as well, but the clustered is fragmented even lower. Anyone have any ideas as to why this is happening on 2005?
Thanks...
|||I am having issues defraging indexes on several of my tables. I noticed in my test environment that one of the indexes (a primary key, clustered index, indexed on an identity column) was 87% fragmented. I rebuild the index and it was still 87% fragmented. Occasionally, after the rebuild it will drop to 82% fragmented. It does the same thing if I try a reorg or use DBCC DBReindex. Any ideas?
|||I'm having the same problem. I have run DBCC DBREINDEX, DBCC INDEXDEFRAG, dropped the index and recreated it. I've even changed my nonclustered index from a composit to a single index. Nothing will get my scan density over 50%. UGGGGGGG!!!!
index defrag problem
As per BOL 2005 , i tried to defrag indexes by both these methods
1.reorganizing an index
2.rebuilding an index
but still some of my tables indexes have the same avg_fragmentation_in_percent that they were in before i did my defrag using the above 2 method.
i used sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG
Any pointers ? thankscan you display the results from the dm_db_index_physical_stats|||
database_id object_id indexe_id partition_number alloc_unit_type_desc index_depth index_level avg_frag_in_perfrg_cnt
10118511CLUSTERED INDEXIN_ROW_DATA2058.333333333333381.512NULLNULLNULLNULLNULLNULLNULLNULL
NOTE : i used the new alter index instead of the DBCC INDEXDEFRAG or DBCC DBREINDEX|||
I am having the same issue but only on certain tables in my database. Even when I create a new index on the problem tables, the index is 60%-80% fragmented. I tried rebuilding, defragging, and dropping and recreating the index and I am getting the same results. Here is one of the index properties...
DBCC SHOWCONTIG scanning 'DimTest' table... Table: 'DimTest' (59251366); index ID: 4, database ID: 12 LEAF level scan performed. - Pages Scanned................................: 5 - Extents Scanned..............................: 5 - Extent Switches..............................: 4 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 20.00% [1:5] - Logical Scan Fragmentation ..................: 80.00% - Extent Scan Fragmentation ...................: 80.00% - Avg. Bytes Free per Page.....................: 1608.2 - Avg. Page Density (full).....................: 80.13%|||Wanted to revisit this, as there hasn't been any resolutions or suggestions. This is happening at our company as well, but the clustered is fragmented even lower. Anyone have any ideas as to why this is happening on 2005?
Thanks...
|||I am having issues defraging indexes on several of my tables. I noticed in my test environment that one of the indexes (a primary key, clustered index, indexed on an identity column) was 87% fragmented. I rebuild the index and it was still 87% fragmented. Occasionally, after the rebuild it will drop to 82% fragmented. It does the same thing if I try a reorg or use DBCC DBReindex. Any ideas?
|||I'm having the same problem. I have run DBCC DBREINDEX, DBCC INDEXDEFRAG, dropped the index and recreated it. I've even changed my nonclustered index from a composit to a single index. Nothing will get my scan density over 50%. UGGGGGGG!!!!
Subscribe to:
Posts (Atom)