Rebuilding indexes nightly bad?

Hey all,
My company currently rebuilds the database indexes nightly.
We haven't really done an analysis on what fill factors might be good
for our indexes, and use a stock value of 95 when rebuilding.
Our application does majority of reads when using the database,
although there are also inserta and updates done here and there.
>From my understanding, an incorrect fill factor can contribute to page
splits, which is an I/O hit.
When the index is rebuilt, it adjusts the fill factor back to 95. So
thus, if it is not the best value, more page splits are likely and thus
I/O goes up. So over a period, say a week, would the amount of page
splitting decrease as the index creates new pages?
Would it be better to rebuild indexes say on a week? If not, is there a
way to determine when the ideal time frame to rebuild indexes is?
One last question - I assume a page split only occurs when inserting
into an index?
Thanks!Try this - the morning after you rebuild your indexes, cancel your
transaction log backups for a few hours and then run the following query
which will scan through your transaction logs and report on page splits -
select [Object Name], [Index Name], count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by [Object Name], [Index Name]
You can also run the following dbcc on Friday evenings (before the rebuild
indexes) to see how fragmented your tables and indexes are -
dbcc showcontig(tablename) with all_indexes
Be careful about running the dbcc as it may lock up user processes on the
server.
You shouldn't be rebuilding more than once a week - if you see excessive
fragmentation on Friday evening, change the fill factor with ALTER INDEX
Good luck!
"davconts@.gmail.com" wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
> >From my understanding, an incorrect fill factor can contribute to page
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>|||davconts@.gmail.com wrote:
> Hey all,
> My company currently rebuilds the database indexes nightly.
> We haven't really done an analysis on what fill factors might be good
> for our indexes, and use a stock value of 95 when rebuilding.
> Our application does majority of reads when using the database,
> although there are also inserta and updates done here and there.
>>From my understanding, an incorrect fill factor can contribute to page
> splits, which is an I/O hit.
> When the index is rebuilt, it adjusts the fill factor back to 95. So
> thus, if it is not the best value, more page splits are likely and thus
> I/O goes up. So over a period, say a week, would the amount of page
> splitting decrease as the index creates new pages?
> Would it be better to rebuild indexes say on a week? If not, is there a
> way to determine when the ideal time frame to rebuild indexes is?
> One last question - I assume a page split only occurs when inserting
> into an index?
> Thanks!
>
Consider rebuilding only those indexes that have become badly
fragmented. See
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Himanshu - I tried running that query but get:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Index Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Object Name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Index Name'.
Do I need to supply and particular details like the name of the
database/log?
I'm not that advanced with this type of SQL so sorry if this is a bit
of a stupid question :)
BTW I am using SQL 2005.
David
Himanshu wrote:
> Try this - the morning after you rebuild your indexes, cancel your
> transaction log backups for a few hours and then run the following query
> which will scan through your transaction logs and report on page splits -
> select [Object Name], [Index Name], count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by [Object Name], [Index Name]
> You can also run the following dbcc on Friday evenings (before the rebuild
> indexes) to see how fragmented your tables and indexes are -
> dbcc showcontig(tablename) with all_indexes
> Be careful about running the dbcc as it may lock up user processes on the
> server.
> You shouldn't be rebuilding more than once a week - if you see excessive
> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> Good luck!
>
>
> "davconts@.gmail.com" wrote:
> > Hey all,
> >
> > My company currently rebuilds the database indexes nightly.
> >
> > We haven't really done an analysis on what fill factors might be good
> > for our indexes, and use a stock value of 95 when rebuilding.
> >
> > Our application does majority of reads when using the database,
> > although there are also inserta and updates done here and there.
> >
> > >From my understanding, an incorrect fill factor can contribute to page
> > splits, which is an I/O hit.
> >
> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> > thus, if it is not the best value, more page splits are likely and thus
> > I/O goes up. So over a period, say a week, would the amount of page
> > splitting decrease as the index creates new pages?
> >
> > Would it be better to rebuild indexes say on a week? If not, is there a
> > way to determine when the ideal time frame to rebuild indexes is?
> >
> > One last question - I assume a page split only occurs when inserting
> > into an index?
> >
> > Thanks!
> >
> >|||Hi Tracy,
Thanks, I have tried your stored procedure.
Tried it in report mode, using a MaxFragmentationPercent parameter of
30 - it came back with about 50 indexes that had % frag of around 70 -
80%.
I then tried defragging/rebuilding options, but they still seem to have
the same frag %.
Seems like the defrag/rebuilding isn't really helping here...is there a
reason for this? (noob question, still learning here)
Also, do you have a suggested MaxFragmentationPercent from your
experience?
Thanks
David
Tracy McKibben wrote:
> davconts@.gmail.com wrote:
> > Hey all,
> >
> > My company currently rebuilds the database indexes nightly.
> >
> > We haven't really done an analysis on what fill factors might be good
> > for our indexes, and use a stock value of 95 when rebuilding.
> >
> > Our application does majority of reads when using the database,
> > although there are also inserta and updates done here and there.
> >
> >>From my understanding, an incorrect fill factor can contribute to page
> > splits, which is an I/O hit.
> >
> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> > thus, if it is not the best value, more page splits are likely and thus
> > I/O goes up. So over a period, say a week, would the amount of page
> > splitting decrease as the index creates new pages?
> >
> > Would it be better to rebuild indexes say on a week? If not, is there a
> > way to determine when the ideal time frame to rebuild indexes is?
> >
> > One last question - I assume a page split only occurs when inserting
> > into an index?
> >
> > Thanks!
> >
> Consider rebuilding only those indexes that have become badly
> fragmented. See
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||>My company currently rebuilds the database indexes nightly.
>although there are also inserta and updates done here and there.
The reason to rebuild indexes is that there has been significant
update activity. What you describe sounds like rather light update
activity. On that basis alone, daily rebuilding of indexes sounds
like absurd overkill.
If you are going beyond default fill factors, it only makes sense to
do it at the table and individual index level. In particular, the
clustered index on a table that is heavily updated needs careful
analysis. Cluster an invoice table on invoice_date and most inserts
will be at the end - new pages, but not page splits. Cluster a Orders
table on CusterID and inserts will tend to cluster on the most active
customers; free space and regular index rebuilds would be important.
Roy Harvey
Beacon Falls, CT|||David
Yes, that code will not work for SQL 2005. Metadata has been completely
reorganized.
Try this:
select AllocUnitName, count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by AllocUnitName
You need to be using the database that you are interested in.
--
HTH
Kalen Delaney, SQL Server MVP
<davconts@.gmail.com> wrote in message
news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> Thanks Himanshu - I tried running that query but get:
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 4
> Invalid column name 'Index Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Object Name'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Index Name'.
> Do I need to supply and particular details like the name of the
> database/log?
> I'm not that advanced with this type of SQL so sorry if this is a bit
> of a stupid question :)
> BTW I am using SQL 2005.
> David
> Himanshu wrote:
>> Try this - the morning after you rebuild your indexes, cancel your
>> transaction log backups for a few hours and then run the following query
>> which will scan through your transaction logs and report on page splits -
>> select [Object Name], [Index Name], count([Current LSN])
>> from ::fn_dblog(null, null)
>> where Operation = N'LOP_DELETE_SPLIT'
>> group by [Object Name], [Index Name]
>> You can also run the following dbcc on Friday evenings (before the
>> rebuild
>> indexes) to see how fragmented your tables and indexes are -
>> dbcc showcontig(tablename) with all_indexes
>> Be careful about running the dbcc as it may lock up user processes on the
>> server.
>> You shouldn't be rebuilding more than once a week - if you see excessive
>> fragmentation on Friday evening, change the fill factor with ALTER INDEX
>> Good luck!
>>
>>
>> "davconts@.gmail.com" wrote:
>> > Hey all,
>> >
>> > My company currently rebuilds the database indexes nightly.
>> >
>> > We haven't really done an analysis on what fill factors might be good
>> > for our indexes, and use a stock value of 95 when rebuilding.
>> >
>> > Our application does majority of reads when using the database,
>> > although there are also inserta and updates done here and there.
>> >
>> > >From my understanding, an incorrect fill factor can contribute to page
>> > splits, which is an I/O hit.
>> >
>> > When the index is rebuilt, it adjusts the fill factor back to 95. So
>> > thus, if it is not the best value, more page splits are likely and thus
>> > I/O goes up. So over a period, say a week, would the amount of page
>> > splitting decrease as the index creates new pages?
>> >
>> > Would it be better to rebuild indexes say on a week? If not, is there a
>> > way to determine when the ideal time frame to rebuild indexes is?
>> >
>> > One last question - I assume a page split only occurs when inserting
>> > into an index?
>> >
>> > Thanks!
>> >
>> >
>|||This is Microsoft definitive whitepaper on the subject:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
You need to make sure an index needs rebuilding before you rebuild it.
First, it has to be fragmented, and second, you have to verify that the
fragmentation is actually a problem. There are many cases where your
performance will not suffer just because indexes are fragmented. It's all in
the Whitepaper.
--
HTH
Kalen Delaney, SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQ5p$phpGHA.4188@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
>> Hey all,
>> My company currently rebuilds the database indexes nightly.
>> We haven't really done an analysis on what fill factors might be good
>> for our indexes, and use a stock value of 95 when rebuilding.
>> Our application does majority of reads when using the database,
>> although there are also inserta and updates done here and there.
>>From my understanding, an incorrect fill factor can contribute to page
>> splits, which is an I/O hit.
>> When the index is rebuilt, it adjusts the fill factor back to 95. So
>> thus, if it is not the best value, more page splits are likely and thus
>> I/O goes up. So over a period, say a week, would the amount of page
>> splitting decrease as the index creates new pages?
>> Would it be better to rebuild indexes say on a week? If not, is there a
>> way to determine when the ideal time frame to rebuild indexes is?
>> One last question - I assume a page split only occurs when inserting
>> into an index?
>> Thanks!
> Consider rebuilding only those indexes that have become badly fragmented.
> See http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Great that worked... so I assume that the count returned is the number
of page splits?
(and this should be as close to 0 as possible?)
Would a high number indicate that the fill factor might need to be
adjusted?
Thanks!
Kalen Delaney wrote:
> David
> Yes, that code will not work for SQL 2005. Metadata has been completely
> reorganized.
> Try this:
> select AllocUnitName, count([Current LSN])
> from ::fn_dblog(null, null)
> where Operation = N'LOP_DELETE_SPLIT'
> group by AllocUnitName
> You need to be using the database that you are interested in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <davconts@.gmail.com> wrote in message
> news:1152756009.634285.195120@.35g2000cwc.googlegroups.com...
> > Thanks Himanshu - I tried running that query but get:
> >
> > Msg 207, Level 16, State 1, Line 4
> > Invalid column name 'Object Name'.
> > Msg 207, Level 16, State 1, Line 4
> > Invalid column name 'Index Name'.
> > Msg 207, Level 16, State 1, Line 1
> > Invalid column name 'Object Name'.
> > Msg 207, Level 16, State 1, Line 1
> > Invalid column name 'Index Name'.
> >
> > Do I need to supply and particular details like the name of the
> > database/log?
> > I'm not that advanced with this type of SQL so sorry if this is a bit
> > of a stupid question :)
> >
> > BTW I am using SQL 2005.
> >
> > David
> >
> > Himanshu wrote:
> >> Try this - the morning after you rebuild your indexes, cancel your
> >> transaction log backups for a few hours and then run the following query
> >> which will scan through your transaction logs and report on page splits -
> >>
> >> select [Object Name], [Index Name], count([Current LSN])
> >> from ::fn_dblog(null, null)
> >> where Operation = N'LOP_DELETE_SPLIT'
> >> group by [Object Name], [Index Name]
> >>
> >> You can also run the following dbcc on Friday evenings (before the
> >> rebuild
> >> indexes) to see how fragmented your tables and indexes are -
> >>
> >> dbcc showcontig(tablename) with all_indexes
> >>
> >> Be careful about running the dbcc as it may lock up user processes on the
> >> server.
> >>
> >> You shouldn't be rebuilding more than once a week - if you see excessive
> >> fragmentation on Friday evening, change the fill factor with ALTER INDEX
> >>
> >> Good luck!
> >>
> >>
> >>
> >>
> >> "davconts@.gmail.com" wrote:
> >>
> >> > Hey all,
> >> >
> >> > My company currently rebuilds the database indexes nightly.
> >> >
> >> > We haven't really done an analysis on what fill factors might be good
> >> > for our indexes, and use a stock value of 95 when rebuilding.
> >> >
> >> > Our application does majority of reads when using the database,
> >> > although there are also inserta and updates done here and there.
> >> >
> >> > >From my understanding, an incorrect fill factor can contribute to page
> >> > splits, which is an I/O hit.
> >> >
> >> > When the index is rebuilt, it adjusts the fill factor back to 95. So
> >> > thus, if it is not the best value, more page splits are likely and thus
> >> > I/O goes up. So over a period, say a week, would the amount of page
> >> > splitting decrease as the index creates new pages?
> >> >
> >> > Would it be better to rebuild indexes say on a week? If not, is there a
> >> > way to determine when the ideal time frame to rebuild indexes is?
> >> >
> >> > One last question - I assume a page split only occurs when inserting
> >> > into an index?
> >> >
> >> > Thanks!
> >> >
> >> >
> >|||davconts@.gmail.com wrote:
> Hi Tracy,
> Thanks, I have tried your stored procedure.
> Tried it in report mode, using a MaxFragmentationPercent parameter of
> 30 - it came back with about 50 indexes that had % frag of around 70 -
> 80%.
> I then tried defragging/rebuilding options, but they still seem to have
> the same frag %.
> Seems like the defrag/rebuilding isn't really helping here...is there a
> reason for this? (noob question, still learning here)
They might be small indexes, in which case fragmentation really isn't
preventable, nor is it a problem. The rule of thumb is that any index
smaller than 1000 pages really doesn't suffer due to fragmentation. I
should make my routine smart enough to exclude those, but it's really a
non-issue either way.
> Also, do you have a suggested MaxFragmentationPercent from your
> experience?
I typically look for anything 30% or more.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I missed the start of this thread where I'm guessing the script got posted.
Is it looking at Extent Scan Fragmentation or is it based off my Example E
in DBCC SHOWCONTIG BOL? Usually when someone says that the frag% stayed the
same after a defrag/rebuild its because they're looking at Extent Scan
Fragmentation and they have multiple files - Extent Scan Fragmentation is
documented as not applicable when multiple files are invovled - I didn't
upgrade the algorithm when I rewrote DBCC SHOWCONTIG for SQL 2000.
Fragmentation % is a poor way to pick indexes to defrag, if that's all you
look at. Fragmentation is only going to affect range scan performance so you
need to identify those indexes that are used in queries that have range
scans. You should also correlate decreasing perf with increasing Logical
Scan Fragmentation before rebuilding indexes every night.
Have a look at the whitepaper below for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
And you probably want to start following my index fragmentation series on
the blog below.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23UeioknpGHA.3600@.TK2MSFTNGP04.phx.gbl...
> davconts@.gmail.com wrote:
>> Hi Tracy,
>> Thanks, I have tried your stored procedure.
>> Tried it in report mode, using a MaxFragmentationPercent parameter of
>> 30 - it came back with about 50 indexes that had % frag of around 70 -
>> 80%.
>> I then tried defragging/rebuilding options, but they still seem to have
>> the same frag %.
>> Seems like the defrag/rebuilding isn't really helping here...is there a
>> reason for this? (noob question, still learning here)
> They might be small indexes, in which case fragmentation really isn't
> preventable, nor is it a problem. The rule of thumb is that any index
> smaller than 1000 pages really doesn't suffer due to fragmentation. I
> should make my routine smart enough to exclude those, but it's really a
> non-issue either way.
>> Also, do you have a suggested MaxFragmentationPercent from your
>> experience?
> I typically look for anything 30% or more.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul S Randal [MS] wrote:
> I missed the start of this thread where I'm guessing the script got posted.
> Is it looking at Extent Scan Fragmentation or is it based off my Example E
> in DBCC SHOWCONTIG BOL? Usually when someone says that the frag% stayed the
> same after a defrag/rebuild its because they're looking at Extent Scan
> Fragmentation and they have multiple files - Extent Scan Fragmentation is
> documented as not applicable when multiple files are invovled - I didn't
> upgrade the algorithm when I rewrote DBCC SHOWCONTIG for SQL 2000.
> Fragmentation % is a poor way to pick indexes to defrag, if that's all you
> look at. Fragmentation is only going to affect range scan performance so you
> need to identify those indexes that are used in queries that have range
> scans. You should also correlate decreasing perf with increasing Logical
> Scan Fragmentation before rebuilding indexes every night.
> Have a look at the whitepaper below for more info:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> And you probably want to start following my index fragmentation series on
> the blog below.
> Thanks
>
Script is visible at
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded.
I'm looking at logical scan fragmentation. I'll read that whitepaper
and watch the blog, always room for improvement!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Cool - its based off my example (or is eerily close to it).
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O0jdO8FqGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Paul S Randal [MS] wrote:
>> I missed the start of this thread where I'm guessing the script got
>> posted. Is it looking at Extent Scan Fragmentation or is it based off my
>> Example E in DBCC SHOWCONTIG BOL? Usually when someone says that the
>> frag% stayed the same after a defrag/rebuild its because they're looking
>> at Extent Scan Fragmentation and they have multiple files - Extent Scan
>> Fragmentation is documented as not applicable when multiple files are
>> invovled - I didn't upgrade the algorithm when I rewrote DBCC SHOWCONTIG
>> for SQL 2000.
>> Fragmentation % is a poor way to pick indexes to defrag, if that's all
>> you look at. Fragmentation is only going to affect range scan performance
>> so you need to identify those indexes that are used in queries that have
>> range scans. You should also correlate decreasing perf with increasing
>> Logical Scan Fragmentation before rebuilding indexes every night.
>> Have a look at the whitepaper below for more info:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> And you probably want to start following my index fragmentation series on
>> the blog below.
>> Thanks
> Script is visible at
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded. I'm
> looking at logical scan fragmentation. I'll read that whitepaper and
> watch the blog, always room for improvement!
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul S Randal [MS] wrote:
> Cool - its based off my example (or is eerily close to it).
>
It might share some code here and there... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com