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.
>
> 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.
>
> 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/vie...IndexesAsNeeded
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:[vbcol=seagreen]
> 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:
>|||>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|||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:
> Consider rebuilding only those indexes that have become badly
> fragmented. See
> http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||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:
>|||This is Microsoft definitive whitepaper on the subject:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/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:
> Consider rebuilding only those indexes that have become badly fragmented.
> See http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> 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:[vbcol=seagreen]
> 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...|||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
No comments:
Post a Comment