I got several times the same questions around index and fragmentation on SQL2008. So I decided to add one more item on Internet to discuss about the reconstruction and reorganization of the index and how detect index fragmentation.
StarterAs fragmentation can have a negative impact on the efficiency of data access; one of the main tasks of a DBA is to maintain database indexes.In the life cycle of a database, fragmentation is expected behavior and natural. If the database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.
Main courseSince SQL Server 2005 the sys.dm_db_index_physical_stats Dynamic Management Function returns the size and fragmentation information for the data and indexes of the specified table or view.
1. There are 3 mode in the syntax of sys.dm_db_index_physical_stats:
2. There are 2 kind of fragmentation on indexes.
In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing and rebuilding depends on defragmentation values. The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. The value should be as close to zero as possible. A value between 5-30% indicates moderate fragmentation, while any value over 30% indicates high fragmentation.
The avg_page_space_used_in_percent is another value that it is worth to look closely. This value represents the amount of spaced used in the indexes. A value below 75% is usually associated to internal fragmentation (more blank pages on our book than recommended).
DessertThe script will work for both SQL 2005 and higher versions. The aim is to reduce index fragmentation by recreating, reorganizing, or rebuilding the index:
DOWNLOAD THE SCRIPT SAMPLE HERE
To run the stored procedure without execute the rebuild : EXECUTE handdleFragmentationIndexes @debugMode = 1
To run the stored procedure and defragement indexes : EXECUTE handdleFragmentationIndexesTo run the stored procedure and defragement indexes on a specific database : EXECUTE handdleFragmentationIndexes @databaseName = 'myDatabaseName'
This script sample is extract in part from the following BOL : sys.dm_db_index_physical_stats (Transact-SQL)
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |
When you have Change Data Capture(CDC) enabled on some of your tables, when you want to do a maintenance on regular tables you need to disable CDC on table and then do the maintenance and enabled CDC back again.
I like your script just want to let you know about it.
Just for information about Arbi's comment. This issue is fixed. See the article KB below :
FIX: Error message when you run an ALTER INDEX statement on a table that has CDC enabled in SQL Server 2008: "Msg 22983: The unique index <Index Name> on source table <Table Name> is used by Change Data Capture"
Hi all, Following my discussion with Gregory. I updated the script to avoid the error message below :
Msg 8152, Level 16, State 2, Procedure handdleFragmentationIndexes, Line 48
String or binary data would be truncated.
The fix was to change VARCHAR(50) by VARCHAR(150)
thank you for providing this script! It saved me much time"
In Step 2 of your stored proc it seems the "database prefix" is missing when joining the ".sys.schemas". For most of my tables, I had no object names and schema names without that prefix, as these are lying in "user created" schemas.
I've added the prefix and it seems to work.:
' SELECT @objectname = o.name, @schemaname = s.name '
+ ' FROM ['+@db_name+'].sys.objects AS o '
+ ' JOIN ['+@db_name+'].sys.schemas as s ON s.schema_id = o.schema_id '
+ ' WHERE o.object_id = ' + CAST(@objectid as varchar(50)
Thank you again