The goal is very simple. Build a query that could be scheduled to grab statistics that are helpful towards determining if an index needs to be defragged or reorganized. I would like to throw these results into a table so I could analyze them at a later date. I would also like to monitor the fill factor and padding to determine if I need to make changes and to analyze if the changes are really helpful.
To download the create table script for the table used click here
The following is an explanation of the columns. The following descriptions come from MSDN.
1: ALTER PROCEDURE [dbo].[GetStatsForIndexes]
2: @PageCount INT = 100
3:
4: AS
5: BEGIN
6: -- SET NOCOUNT ON added to prevent extra result sets from
7: -- interfering with SELECT statements.
8: SET NOCOUNT ON;
9:
10: -- Declare varables
11: DECLARE @dbID INT, @dbName VARCHAR(128), @SQL NVARCHAR(MAX)
12:
13: -- Create a temp table to store all active databases
14: CREATE TABLE #databaseList
15: (
16: databaseID INT
17: , databaseName VARCHAR(128)
18: );
19:
20: -- we only want non-system databases who are currenlty online
21: INSERT INTO #databaseList (databaseID, databaseName)
22: SELECT d.database_id, d.name FROM sys.databases d where d.[state] = 0 and d.database_id > 4
23:
24:
25: -- Loop through all databases
26: WHILE (SELECT COUNT(*) FROM #databaseList) > 0 BEGIN
27:
28: -- get a database id
29: SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
30: FROM #databaseList;
31:
32: SET @SQL = 'INSERT INTO DBA_Tools.dbo.IDX_FRAG (databaseName, ObjectName, indexName, partitionNumber, fragmentation, fill_factor, is_padded, type_desc, page_count, [date])
33: SELECT
34: db.name AS databaseName
35: , obj.name AS ObjectName
36: , idx.name AS indexName
37: , ps.partition_number AS partitionNumber
38: , ps.avg_fragmentation_in_percent AS fragmentation
39: ,idx.fill_factor
40: ,idx.is_padded
41: ,idx.type_desc
42: , ps.page_count
43: , GETDATE() as [date]
44: FROM sys.databases db
45: INNER JOIN sys.dm_db_index_physical_stats ('+CAST(@dbID AS VARCHAR(10))+', NULL, NULL , NULL, N''Limited'') ps
46: ON db.database_id = ps.database_id
47: INNER JOIN '+ @dbName+'.sys.objects obj ON obj.object_id = ps.object_id
48: INNER JOIN '+ @dbName+'.sys.indexes idx ON idx.index_id = ps.index_id AND idx.object_id = ps.object_id
49: WHERE ps.index_id > 0
50: AND ps.page_count > 100
51: ORDER BY page_count desc
52: OPTION (MaxDop 1);'
53:
54: EXECUTE sp_executesql @SQL
55: -- remove the database from the databases table
56: DELETE FROM #databaseList WHERE databaseID = @dbID
57:
58: -- get the next database in the databases table
59: SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
60: FROM #databaseList;
61:
62: END
63: -- temp table is no longer needed, so we will kill it.
64: DROP TABLE #databaseList;
65: END
