Automating defragmentation of indexes in MS SQL Server database
Foreword
On the Internet you can find a lot of information about defragmentation or rebuilding indexes. However, most recommendations are directed to databases that have a minimum load time (mostly at night).
And what about databases that are constantly used both to change data and to receive information 24 hours a day, 7 days a week?
In this article I will give the implemented mechanism for automating the defragmentation of indexes in the database to support the database in our enterprise. This mechanism allows you to defragment all the necessary indices all the time, and in the 24x7 system, index fragmentation occurs continuously. And often defragmentation even once a day is insufficient for indexes.
Decision
First, the general approach:
')
1) to create a representation for the required database, with the help of which it is possible to obtain which indices and how many percent are fragmented
2) create a table to save the results of defragmentation indexes
3) create a stored procedure that will analyze and defragment the selected index
4) create a view to view statistics on the results of index defragmentation
5) create a task in the Agent, which will run the implemented stored procedure in step 3.
And now the implementation:
1) to create a representation for the required database, with the help of which it is possible to obtain which indices and how many percent are fragmented:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vIndexDefrag] as with info as (SELECT [object_id], database_id, index_id, index_type_desc, index_level, fragment_count, avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count, record_count, ghost_record_count FROM sys.dm_db_index_physical_stats (DB_ID(N'__') , NULL, NULL, NULL , N'LIMITED') where index_level = 0 ) SELECT b.name as db, s.name as shema, t.name as tb, i.index_id as idx, i.database_id, idx.name as index_name, i.index_type_desc,i.index_level as [level], i.[object_id], i.fragment_count as frag_num, round(i.avg_fragmentation_in_percent,2) as frag, round(i.avg_fragment_size_in_pages,2) as frag_page, i.page_count as [page], i.record_count as rec, i.ghost_record_count as ghost, round(i.avg_fragmentation_in_percent*i.page_count,0) as func FROM Info as i inner join [sys].[databases] as b on i.database_id = b.database_id inner join [sys].[all_objects] as t on i.object_id = t.object_id inner join [sys].[schemas] as s on t.[schema_id] = s.[schema_id] inner join [sys].[indexes] as idx on t.object_id = idx.object_id and idx.index_id = i.index_id where i.avg_fragmentation_in_percent >= 30 and i.index_type_desc <> 'HEAP'; GO This view displays only those indices whose fragmentation percentage is at least 30. Those indexes that need to be defragmented. Only those indices that are not heaps are displayed, and the latter, when defragmented, can have a negative effect, expressed either by blocking such a heap, or by even greater fragmentation of the index.
The view uses the important system view sys.dm_db_index_physical_stats ( more ).
2) create a table to save the results of defragmentation of indexes:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[Defrag]( [ID] [bigint] IDENTITY(794,1) NOT NULL, [db] [nvarchar](100) NULL, [shema] [nvarchar](100) NULL, [table] [nvarchar](100) NULL, [IndexName] [nvarchar](100) NULL, [frag_num] [int] NULL, [frag] [decimal](6, 2) NULL, [page] [int] NULL, [rec] [int] NULL, [func] [int] NULL, [ts] [datetime] NULL, [tf] [datetime] NULL, [frag_after] [decimal](6, 2) NULL, [object_id] [int] NULL, [idx] [int] NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_Defrag] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; GO ALTER TABLE [srv].[Defrag] ADD CONSTRAINT [DF_Defrag_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]; GO The main thing in this table is not to forget to delete data (for example, which is more than a month or more often).
Fields in the table will be clear on the next item.
3) create a stored procedure that will analyze and defragment the selected index:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoDefragIndex] AS BEGIN SET NOCOUNT ON; -- declare @IndexName nvarchar(100) -- ,@db nvarchar(100) -- ,@Shema nvarchar(100) -- ,@Table nvarchar(100) -- ,@SQL_Str nvarchar (2000) -- ,@frag decimal(6,2) --% ,@frag_after decimal(6,2) --% -- IN_ROW_DATA ,@frag_num int ,@func int --round(i.avg_fragmentation_in_percent*i.page_count,0) ,@page int --- ,@rec int -- - ,@ts datetime -- ,@tf datetime -- -- , ,@object_id int ,@idx int; --ID -- set @ts = getdate(); -- -- . , -- , select top 1 @IndexName = index_name, @db=db, @Shema = shema, @Table = tb, @frag = frag, @frag_num = frag_num, @func=func, @page =[page], @rec = rec, @object_id = [object_id], @idx = idx from [srv].[vIndexDefrag] order by func*power((1.0- convert(float,(select count(*) from SRV.[srv].[Defrag] vid where vid.db=db and vid.shema = shema and vid.[table] = tb and vid.IndexName = index_name)) / convert(float, case when (exists (select top 1 1 from SRV.[srv].[Defrag] vid1 where vid1.db=db)) then (select count(*) from SRV.[srv].[Defrag] vid1 where vid1.db=db) else 1.0 end)) ,3) desc -- if(@db is not null) begin -- set @SQL_Str = 'alter index ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] Reorganize'; execute sp_executesql @SQL_Str; -- set @tf = getdate() -- SELECT @frag_after = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(@db), @object_id, @idx, NULL , N'DETAILED') where index_level = 0; -- insert into SRV.srv.Defrag( [db], [shema], [table], [IndexName], [frag_num], [frag], [page], [rec], ts, tf, frag_after, object_id, idx ) select @db, @shema, @table, @IndexName, @frag_num, @frag, @page, @rec, @ts, @tf, @frag_after, @object_id, @idx; -- set @SQL_Str = 'UPDATE STATISTICS ['+@Shema+'].['+@Table+'] ['+@IndexName+']'; execute sp_executesql @SQL_Str; end END 4) create a view to view statistics on the results of index defragmentation:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vStatisticDefrag] as SELECT top 1000 [db] ,[shema] ,[table] ,[IndexName] ,avg([frag]) as AvgFrag ,avg([frag_after]) as AvgFragAfter ,avg(page) as AvgPage FROM [srv].[Defrag] group by [db], [shema], [table], [IndexName] order by abs(avg([frag])-avg([frag_after])) desc; GO This view can be used to notify administrators daily of the work done on automating the defragmentation of indexes.
5) create a task in the Agent, which will run the implemented stored procedure in step 3:
Here you need to select the time experimentally. I got about 5 minutes, and about an hour.
This algorithm can be extended to several databases, but then you must also enter item 6:
Collect all statistics on automating defragmentation of indexes in databases in one place for later sending to administrators.
And now I would like to dwell in more detail about the already published recommendations on support for indices:
1) defragmentation of all indexes at once during the minimum load of the database - this is unacceptable for 24x7 systems, because the indexes are constantly fragmented and the database is not idle almost.
2) rebuilding the index - this operation locks the table or section (in the case of a partitioned index), which is not good for 24x7 systems. Further, rebuilding the index in real time is supported only in the Enterprise solution, and may also lead to data corruption.
This method is not optimal, but successfully copes with the fact that the indices are sufficiently defragmented (no higher than 30-40% of fragmentation) for use by the optimizer for building execution plans.
I would be very grateful if in the comments appear argued pros and cons of this approach, as well as proven alternative proposals.
Sources:
» Reorganization and rebuilding indexes
» Sys.dm_db_index_physical_stats
Source: https://habr.com/ru/post/314454/
All Articles