Defragmentation indices with the collection of statistics MS SQL 2008 R2

One of the first tasks that occurs before a DBA after a new database is deployed is setting up maintenance plans. Often, the task of defragmenting indexes is included in the maintenance plan. I like it when I know not only that the defragmentation was performed at night from Sunday to Monday, but also how it went, how much was performed, which indices were rebuilt and in what condition they remained after defragmentation.

To collect such statistics, I wrote a small scriptbook, which collects information about the work performed, and also gives the most detailed description
about the state of the indices before and after the procedure.

But let's start with a simple, create a table for storing these same data (I created a separate database where I put the tables, which I use when servicing the server databases):

Column	Type of	Comment
proc_id	int	The sequence number of the procedure for identification
start_time	datetime	Starting the ALTER INDEX query
end_time	datetime	Completion of the ALTER INDEX query
database_id	smallint	Database ID
object_id	Int	Table ID
table_name	varchar (50)	Table name
index_id	Int	Index ID
index_name	varchar (50)	Index name
avg_frag_percent_before	float	The percentage of index fragmentation before running ALTER INDEX
fragment_count_before	bigint	The number of fragments before defragmentation
pages_count_before	bigint	Number of index pages before defragmentation
fill_factor	tinyint	Index page fill level
partition_num	int	Section number
avg_frag_percent_after	float	The percentage of index fragmentation after ALTER INDEX
fragment_count_after	bigint	The number of fragments after defragmentation
pages_count_after	bigint	Number of index pages after defragmentation
action	varchar (10)	Action performed

The whole defragmentation procedure will take data from this table, which means you need to fill it in:

DECLARE @currentProcID INT –-    –-  ,     SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM dba_tasks.dbo.index_defrag_statistic --       INSERT INTO dba_tasks.dbo.index_defrag_statistic ( proc_id, database_id, [object_id], table_name, index_id, index_name, avg_frag_percent_before, fragment_count_before, pages_count_before, fill_factor, partition_num) SELECT @currentProcID, dm.database_id, dm.[object_id], tbl.name, dm.index_id, idx.name, dm.avg_fragmentation_in_percent, dm.fragment_count, dm.page_count, idx.fill_factor, dm.partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id WHERE page_count > 8 AND avg_fragmentation_in_percent > 10 AND dm.index_id > 0 

Selection conditions:
page_count> 8 - I think that rebuilding indexes with a small number of pages does not make sense, because it will not get better, and the time spent on the procedure is very valuable, especially if the base is working around the clock and is constantly under high load. (After the remark unfilled raised the bar to 8 pages)
avg_fragmentation_in_percent> 10 - Also a very subjective figure, almost all documentation suggests not to touch the index, if its fragmentation is 10 percent or less, with which I agree, if you are different, we change it.
dm.index_id> 0 - 0 is a bunch
')
After the table is full, we know what indexes need to be served.
Let's do business:

 --   DECLARE @partitioncount INT --  DECLARE @action VARCHAR(10) --,       DECLARE @start_time DATETIME --   ALTER INDEX DECLARE @end_time DATETIME --   ALTER INDEX --   DECLARE @object_id INT DECLARE @index_id INT DECLARE @tableName VARCHAR(250) DECLARE @indexName VARCHAR(250) DECLARE @defrag FLOAT DECLARE @partition_num INT DECLARE @fill_factor INT -- ,    ,   MAX,      ,      ,    . DECLARE @sql NVARCHAR(MAX) --   DECLARE defragCur CURSOR FOR SELECT [object_id], index_id, table_name, index_name, avg_frag_percent_before, fill_factor, partition_num FROM dba_tasks.dbo.index_defrag_statistic WHERE proc_id = @currentProcID ORDER BY [object_id], index_id DESC --    OPEN defragCur FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num WHILE @@FETCH_STATUS=0 BEGIN SET @sql = N'ALTER INDEX ' + @indexName + ' ON ' + @tableName SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @object_id AND index_id = @index_id; --  ,       , ,       ,           IF (@fill_factor != 80) BEGIN @sql = @sql + N' REBUILD WITH (FILLFACTOR = 80)' SET @action = 'rebuild80' END ELSE BEGIN --  ,    MS IF (@defrag > 30) --   30%,  REBUILD BEGIN SET @sql = @sql + N' REBUILD' SET @action = 'rebuild' END ELSE --   REORGINIZE BEGIN SET @sql = @sql + N' REORGANIZE' SET @action = 'reorginize' END END --    IF @partitioncount > 1 SET @sql = @sql + N' PARTITION=' + CAST(@partition_num AS nvarchar(5)) print @sql --   --   SET @start_time = GETDATE() EXEC sp_executesql @sql --   SET @end_time = GETDATE() --    UPDATE dba_tasks.dbo.index_defrag_statistic SET start_time = @start_time, end_time = @end_time, [action] = @action WHERE proc_id = @currentProcID AND [object_id] = @object_id AND index_id = @index_id FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num END CLOSE defragCur DEALLOCATE defragCur 

And lastly, we will collect information about the indexes after the defragmentation procedure:

 UPDATE dba SET dba.avg_frag_percent_after = dm.avg_fragmentation_in_percent, dba.fragment_count_after = dm.fragment_count, dba.pages_count_after = dm.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN dba_tasks.dbo.index_defrag_statistic dba ON dm.[object_id] = dba.[object_id] AND dm.index_id = dba.index_id WHERE dba.proc_id = @currentProcID AND dm.index_id > 0 

After executing such a script, you can get and count a lot of useful information. For example, the service time of all indices and each separately. Understand how this is related to the size of the index, see the effectiveness of this operation. By collecting such information for several times, you can slightly change the procedure, for sure some indexes are fragmented more and faster. In this case, their maintenance should be performed more often. How to use the information received, decide for yourself. As for me, after analyzing each such procedure, I change the service plans, if the situation requires. My bases work under high load around the clock. Therefore, I can constantly rebuild all indexes and for 1-2 hours I cannot reduce server performance. If your database also works around the clock, Resource Governor should be configured to perform such things.

I also want to note that there are detailed scripts in the internet, but be careful, many of them use outdated commands.

In more detail about system views used by me it is possible to read in msdn:

sys.sysindexes
sys.tables
sys.dm_db_index_physical_stats