No
need to say that indexes on tables are important, as they directly affect the
performance of your queries and data applications.
Therefor
it is also needless to say that maintenance of indexes is as important as a
good indexing strategy. About indexing strategies, there are already written
thousands and thousands of pages, and it is for this reason that I want t spend
a few lines on index fragmentation and usage. So,what about my indexes, how
much are they fragmented, are they used at all?
First,
a script is shown below, which will give you a result set, showing the indexes
and their usage statistics:
SELECT
TableName =
t.name,
[RowCount] = si.rows ,
IndexName = ISNULL(i.name, i.type_desc),
IndexType = ISNULL(i.type_desc, ''),
IsFilteredIndex = CASE WHEN i.has_filter != 0 THEN 'True' ELSE 'False' END,
Filter = ISNULL(i.filter_definition , ''),
[FillFactor] = i.fill_factor,
[IndexDepth] = s.index_depth,
[FragmentationPercentage] = ISNULL(CAST(s.avg_fragmentation_in_percent
AS DECIMAL(10,2)),0),
[FragmentCount] = ISNULL(s.fragment_count,0),
[FragmentationSizeInPages] =
ISNULL(CAST(s.avg_fragment_size_in_pages AS
DECIMAL(10,2)),0),
[PageCount] = ISNULL(s.page_count,0),
UserSeeks = ISNULL(us.user_seeks,0),
UserScans = ISNULL(us.user_scans,0),
UserLookups = ISNULL(us.user_lookups,0),
UserUpdates = ISNULL(us.user_updates,0),
LastUserSeek = us.last_user_seek,
LastUserScan = us.last_user_scan,
LastUserLookup = us.last_user_lookup,
LastUserUpdate = us.last_user_update
FROM sys.tables t
INNER JOIN sysindexes si ON si.id = t.object_id
INNER JOIN
sys.indexes i ON i.object_id= t.object_id
LEFT JOIN
sys.dm_db_index_physical_stats(DB_ID(),0,-1,0, null) s
ON s.object_id = i.object_id
AND s.index_id = i.index_id
LEFT JOIN
sys.dm_db_index_usage_stats us
ON us.index_id = i.index_id
AND us.object_id = i.object_id
AND us.database_id = DB_ID()
WHERE t.name NOT LIKE '%_tracking'
AND t.name NOT IN ('anchor' ,'scope_config' , 'scope_info', 'schema_info')
AND si.name LIKE 'PK_%'
AND i.type_desc NOT LIKE 'HEAP'
ORDER BY
t.name ASC, i.name ASC
Secondly,
the following script will generate a result set, showing you the index
fragmentation:
SELECT DISTINCT
SchemaName = s.name,
TableName = t.name ,
IndexName = i.Name,
Fragmentation = ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),0,-1,0, null) ps
INNER JOIN sys.tables t ON t.object_id = ps.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON i.index_id = ps.index_id
AND i.object_id = t.object_id
WHERE ps.avg_fragmentation_in_percent
> 5
AND t.type = 'U' AND i.type <> 0
ORDER BY t.name;
Thirdly,
a script will generate T-SQL code, which will allow you to either rebuild or
reorganize, based on a 30% fragmentation ration to rebuild. This means that
every index which is fragmented more than 30% should be rebuild, all others
should be reorganized.
DECLARE @SQL
NVARCHAR(2000);
DECLARE @SchemaName NVARCHAR(10),
@TableName NVARCHAR(255),
@IndexAction NVARCHAR(20)
DECLARE IndexingCursor
CURSOR FOR
SELECT DISTINCT
SchemaName = s.name,
TableName = t.name ,
IndexAction = CASE WHEN ps.avg_fragmentation_in_percent
> 30 THEN 'REBUILD'
WHEN ps.avg_fragmentation_in_percent
<= 30 THEN 'REORGANIZE'
END
FROM
sys.dm_db_index_physical_stats(DB_ID(),0,-1,0, null) ps
INNER JOIN
sys.tables t ON t.object_id = ps.object_id
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i ON i.index_id = ps.index_id
AND i.object_id = t.object_id
WHERE ps.avg_fragmentation_in_percent >
5
AND t.type = 'U' AND i.type <> 0
ORDER BY
t.name;
OPEN IndexingCursor;
FETCH NEXT
FROM IndexingCursor
INTO @SchemaName, @TableName, @IndexAction;
WHILE @@FETCH_STATUS
= 0
BEGIN
PRINT '/* Index maintenance for [' +
DB_NAME() +'].[' + @SchemaName + '].[' + @TableName + '] */'
PRINT 'ALTER INDEX ALL ON ['
+ DB_NAME() +'].[' + @SchemaName + '].[' + @TableName + '] ' + @IndexAction + ';'
PRINT 'UPDATE STATISTICS ['
+ DB_NAME() +'].[' + @SchemaName + '].[' + @TableName + '];'
FETCH NEXT FROM IndexingCursor
INTO @SchemaName, @TableName, @IndexAction;
END
CLOSE IndexingCursor
DEALLOCATE IndexingCursor
Have
fun!
Jurgen
