Pipboy level utility!
The next set of posts are more for my own sanity and documentation. Sometimes I slip up on my terminology, but most of the time my definitions are correct. This tells me that I need to start posting more as a learning reinforcement.
I’m going to start with index rebuild vs. reorganization. The concepts are fairly simple to understand. Over time operations such as inserts, updates and deletes cause index information to become scattered in the database or fragmented. This happens when the logical ordering of pages are different than the physical ordering on the inside the data file. When fragmentation becomes high you can bet that performance will degrade.
How do I know my current level of fragmentation? You will need to use the dmv function sys.dm_db_index_physical_stats
Below is a simple query that will call the function and return the fragmentation % for a specific table when the Fragmentation is above 5%. The reason I’m only looking for items with fragmentation above 5% is because Microsoft states that the benefit from removing fragmentation below 5% is almost always vastly outweighed by the cost of reorganizing or rebuilding. I have also added a couple commented out lines that can give you more information when joined on the sys.indexes table, but that is outside the scope of this post.
SELECT DISTINCT
DB_NAME(ps.database_id) AS [Database Name],
OBJECT_NAME(ps.object_id) AS [Table Name],
ix.name AS [Index Name],
ps.avg_fragmentation_in_percent AS [Frag %]
--page_count AS [Page Count],
--ix.is_disabled AS [Disabled],
--ix.is_hypothetical AS [Hypothetical],
--ix.fill_factor
FROM sys.dm_db_index_physical_stats(DB_ID('DATABASENAME'),OBJECT_ID('SchemaName.TableName'), NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes ix ON ps.object_id = ix.object_id
WHERE avg_fragmentation_in_percent > 5
ORDER BY avg_fragmentation_in_percent DESC
Depending on the size of your table this could take awhile, as always test this out on a staging server.
Now what do you do with anything you find? That depends on your environment, remember that when designing solutions, most of us work for a single company, in which case our solutions can be as closed box as possible. The only performance that matters is the performance that signs your paycheck. With that said lets go over some options.
First you could have your routine index maintenance using Michelle Ufford’s Index Maintenance Script. I use this weekly, its great, and it’s like clockwork, but what about in-between? “In-between…” you slowly mutter under your breath. HA. Yes, in-between.
Ask yourself this question, Do my indexes get fragmented a day after the maintenance, or 2 days, or 3 days, or…or…or…or you don’t know do you?
One thing you can do is automate the above script and save the data to one of your admin tables. Then analyze it. If some of your indexes are reaching > 5% a inbetween, could you defrag them individually? You can use DBCC INDEXDEFRAG (DatabaseName, “schema.tablename”, indexName) in order to defrag a single index. Perhaps a solution here is to write a job that looks at this nightly snapshot and defrags as needed?
According to microsoft DBCC INDEXDEFRAG is an online operation, therefore DBCC INDEXDEFRAG does not block running queries or updates. So this might not be a bad operations to run nightly and keep fragmentation low on your high volume tables.