Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild/Reorg indexes script Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 6:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 8, 2014 7:12 AM
Points: 263, Visits: 443
Note: this is more of a "request for opinions" post, not an actual problem that I have (no jokes here please )

Hello all, I'm working on a rebuild/reorg indexes script (for fun, I know there are alot out there) and I'm curious about some "best practices".

So far, I've written the base query to select a few different items, average fragmentation in percent, user seeks, user scans, user lookups, and user updates. I've also added another column where I add up the total "touches" (seeks + scans + lookups + updates) and then calculate the reads percentage ((seeks + scans + lookups) / (seeks + scans + lookups + updates)). The purpose of the reads percentage is to try and determine the fill factor for a given index/table.

First question, I guess I'm curious if this sounds like a logical approach to get an estimate of what the fill factor should be.

Second question, a few of the scripts I've found online choose to just select indexes that have over 1000 pages (I believe for the index), and then only return indexes that also have greater than 5% fragmentation. I guess I'm curious how to handle indexes that have fewer than 1000 pages. Right now I'm returning them if they have greater than 50% fragmentation.

Lastly, I'd like to write the rebuild/reorg portion based on the percentage reads value that I'm calculating as well as the page count. Does any of this make sense? Has anyone else tried to build this much "smarts" into a rebuild/reorg script?

Here's the base query/select that I'm using:

Select	DB_NAME(ips.database_id) As [Database],
so.name As [ObjectName],
si.name As [IndexName],
ips.index_type_desc As [IndexType],
ips.page_count As [PageCount],
ips.avg_fragmentation_in_percent As [FragmentationPercentage],
stat.user_seeks As [Seeks],
stat.user_scans As [Scans],
stat.user_lookups As [Lookups],
stat.user_updates As [Updates],
--(stat.user_seeks + stat.user_scans + stat.user_lookups) As [Reads],
--(stat.user_seeks + stat.user_scans + stat.user_lookups + stat.user_updates) As [Touches],
Cast(Cast((stat.user_seeks + stat.user_scans + stat.user_lookups) As decimal(38,2)) / Cast((stat.user_seeks + stat.user_scans + stat.user_lookups + stat.user_updates) As decimal(38,2)) * 100 As int) As [PercentageReads]
From sys.dm_db_index_physical_stats(DB_ID(), 0, -1, 0, Null) ips
Inner Join sys.dm_db_index_usage_stats stat ON stat.object_id = ips.object_id And stat.index_id = ips.index_id
Inner Join sys.indexes si On si.object_id = ips.object_id And si.index_id = stat.index_id
Inner Join sys.objects so On so.object_id = si.object_id
Where stat.user_seeks + stat.user_scans + stat.user_lookups + stat.user_updates > 0
And
(
(
ips.avg_fragmentation_in_percent > 5
And ips.page_count > 1000
)
Or
(
ips.avg_fragmentation_in_percent > 50
And ips.page_count < 1000
)
)
Order By
Case
When ips.index_type_desc = 'HEAP' Then 1
When ips.index_type_desc = 'NONCLUSTERED INDEX' Then 2
When ips.index_type_desc = 'CLUSTERED INDEX' Then 3
Else 0
End Desc,
Case
When ips.page_count > 1000 Then 1
Else 0
End Desc,
ips.avg_fragmentation_in_percent Desc
--so.name

Post #1365838
Posted Friday, September 28, 2012 7:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
There is no "magic number" for fill factor, as it depends of your Index and table's activity. If the Index is static, I would recommend leaving default fill factor. If not, start with 80 or something like that and keep and eye on fragmentation every week. You will see how much your weekend's job is helping with the new FILL factor.

In terms of rebuilding Indexes with less than 1k pages, forcing a rebuild on those shows little or no effect in performance gain, so do not worry about those. Skipping those will have no side effects. If you Google it, you will find lot of Microsoft articles about it.

Last but not least, why reinvent the wheel? ... unless you are really bored, there are tons of awesome solutions online for Index Maintenance and jobs. My favorite is Ola's solution. Here's the link:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

It creates smart jobs. So it will skip those Indexes with low page count of low fragmentation. You can also put your own parameters or customize. It keep a history of the job's activity.

Have fun!
Post #1365883
Posted Saturday, September 29, 2012 5:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 6,191, Visits: 13,340
Hmm, this is a difficult one as there are already some very good scripts "out there".

I'm sure others will jump in here. 1000 pages is generally the perceived point at which fragmentation has more effect. However, the magical number at which an indexes disk layout changes is 8 pages (or 1 extent). To save and reuse space an index with less than 8 pages would be allocated from mixed extents and this means the pages are not necessarily in an ordered chain as the pages could be in different extents. 8 pages or greater and the index is allocated to uniform extents. Check this link for more info on this.

I have seen cases where constantly trying to rebuild an index does not reduce fragmentation. However, start messing with fill factors and the index will need to be rebuilt to achieve the specified free space in each page.

Base your fill factors on the number of page splits that the index incurs as this is what you are trying to negate.




-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1366180
Posted Saturday, September 29, 2012 2:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 1,861, Visits: 3,599
In addition to what has already been posted: include the partition_number column, output from the sys.dm_db_index_physical_stats DMV, in the list of columns you consider in your analysis.

If your database is partitioned, and you have fragmentation on partitions with partition_number > 1, then you need to be able to detect this, and rebuild or reorganize your index with that in mind.

The generic "ALTER INDEX ... REBUILD/REORGANIZE" commands do not touch non-default partitions; they only affect those partitions with partition_number = 1 (this includes all indexes that are not partitioned).

Use "ALTER INDEX ... REBUILD PARTITION = ..." and/or the corresponding reorg command to deal with non-default partitions, if they exist.
I have been bitten recently with this: non-default partitions were not being defragmented by my script, resulting in poor performance.

Who knew?!


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1366216
Posted Sunday, September 30, 2012 10:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
sql-lover (9/28/2012)
...
Last but not least, why reinvent the wheel? ... unless you are really bored, there are tons of awesome solutions online for Index Maintenance and jobs. My favorite is Ola's solution. Here's the link:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

It creates smart jobs. So it will skip those Indexes with low page count of low fragmentation. You can also put your own parameters or customize. It keep a history of the job's activity.

Have fun!


+1

Don't reinvent the wheel.

edit:
Also, don't worry about minimum page count or what the optimum magic number for rebuild vs reorg. A good script would have those values config'd out, so the same script can be used in different ways in different circumstances. The flexibility is what makes Ola's script good.
Post #1366314
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse