September 28, 2012 at 6:48 am
Note: this is more of a "request for opinions" post, not an actual problem that I have (no jokes here please :-D)
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:
SelectDB_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]
Fromsys.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
Wherestat.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
September 28, 2012 at 7:57 am
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!
September 29, 2012 at 5:28 am
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" 😉
September 29, 2012 at 2:09 pm
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?!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 30, 2012 at 10:23 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply