I recently attended a Pittsburgh SQL Server user group meeting where Brent Ozar gave a presentation on the silent performance killer. This motivated me to create a stored procedure that could leverage the DMVs in SQL 2005/2008 to gather index fragmentation statistics for all databases on a given server.
Goal
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.
Download Scripts
The following script uses the following DMV’s sys.dm_db_index_physical_stats, sys.objects and sys.indexes and this script is provided as is.
To download the script click here
To download the create table script for the table used click here
Table Definition
The following is an explanation of the columns. The following descriptions come from MSDN.
|
Column Name |
Description |
| databaseName |
Name of database, unique within an instance of SQL Server. |
| objectName | Object name. |
| indexName | Name of the index. name is unique only within the object.
NULL = Heap |
| partitionNumber | 1-based partition number within the owning object; a table, view, or index.
1 = Nonpartitioned index or heap. |
| fragmentation | Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.
The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks. 0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED. |
| fill_factor | > 0 = FILLFACTOR percentage used when the index was created or rebuilt.
0 = Default value |
| is_padded | 1 = PADINDEX is ON.
0 = PADINDEX is OFF. |
| type_desc | Description of index type:
HEAP |
| page_count | Total number of index or data pages.
For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the total number of data pages in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit. |
| date | this is the current date GETDATE() |
Script
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
