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
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
ips.avg_fragmentation_in_percent > 5
And ips.page_count > 1000
ips.avg_fragmentation_in_percent > 50
And ips.page_count < 1000
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
When ips.page_count > 1000 Then 1