Incremental Index Maintenance A Solution “That Just Works”

1. Incremental Index MaintenanceA Solution “That Just Works” Al Noel Principal consultant, Microsoft Alan.noel@microsoft.com, alannoel@msn.com DMVMUG User Conference 2013 – Reston, VA

2. Agenda • SQL Server Indexes • A Solution that “Just Works”

3. SQL Server Indexes • Provide SQL Server additional ways to look up data and take shortcuts to that data’s location • Tend to be one of the most misunderstood objects in SQL Server • Often mismanaged

4. Index Structure • Balanced (B) Tree of 8KB pages • Clustered and non-clustered • Pages hold both non-leaf and leaf level pages of a non-clustered index, non-leaf level pages of a clustered index • Page splits • When a page becomes full it is split and approximately half the data is moved to the new page (with one exception – clustered index and next inserted row would be physically located as the last record in the table, a new page is created, and new row is added to the new page without relocating any of the existing data)

5. Fragmentation • External – file level • Internal – indexes in the database

6. Index Maintenance • Fundamental duty of production DBAs is to ensure that indexes are periodically maintained • Often there's a desire to • reorganize the index if a certain level of fragmentation is detected (e.g., between 10 percent and 20 percent) • and rebuild the index completely if the fragmentation is greater than a certain percentage (e.g., greater than 20 percent). • Often see the values 10 and 20 percent. Those originated with Paul Randall many years ago off the cuff. Values not based on analysis and design on part of the SQL Server product team.

7. Not Always a Big Deal • Measuring fragmentation and taking the appropriate action when necessary is pretty straightforward for databases with no resource constraints • All at once approach works with no issues • Create a maintenance plan using the Wizard in Management Studio

8. Resource Constrained Environment • You really don't want to do the fragmentation measurement and index defragmentation all at once for an entire database. • With the focus on availability and performance (especially in mission-critical systems), you want to both determine fragmentation levels and defragment indexes in a non-impactful way. • Can be small servers or very large

9. The Solution • First, the solution measures the fragmentation level of a specified number of indexes at a time, recording the information, until all the measurements are taken • Then, it reorganizes or rebuilds indexes as appropriate, again working on a specified number of indexes at a time. • After a complete cycle of measurement and defragmentation has completed, the whole process starts all over again. • Continuous process that discovers all tables each time it makes a complete loop. • “It just works”

10. Three Components: • Table • T-SQL stored procedure • Scheduled job to run the stored procedure

11. Table • Stores the table and index details (e.g., table and index names), the fragmentation information for the indexes (e.g., level of fragmentation), and when the last refresh occurred • If the table doesn't exist, it will be created by the second major component of the solution, a T-SQL stored procedure named usp_IndexDefrag.

12. Table Structure CREATE TABLE TRACE.[dbo].[IndexFrag]( [ID] [int] IDENTITY(1,1) NOT NULL, [DatabaseName] [sysname] NOT NULL, [SchemaName] [sysname] NOT NULL, [TableName] [nvarchar](128) NULL, [TableObjectID] [int] NULL, [IndexName] [sysname] NULL, [Index_ID] [int] NULL, [Index_type_desc] [nvarchar](60) NULL, [avg_fragmentation_in_percent] [float] NULL, [avg_fragment_size_in_pages] [float] NULL, [avg_page_space_used_in_percent] [float] NULL, [record_count] [bigint] NULL, [ghost_record_count] [bigint] NULL, [fragment_count] [bigint] NULL, [Index_Info_Refresh] [int] NOT NULL CONSTRAINT [DF_IndexInfoFrag] DEFAULT ((0)), [Index_Refresh] [int] NOT NULL CONSTRAINT [DF_IndexFrag] DEFAULT ((0)), [DeFrag_Time_Start] [datetime] NULL, [DeFrag_Time_End] [datetime] NULL ) ON [PRIMARY] Create Index idx_IndexFrag_TableName ON TRACE.dbo.IndexFrag(TableName)

13. usp_IndexDefrag • Measures and records the level of fragmentation. • You can configure how many indexes to process at a time with the @HowManyIndexInfoToPull parameter. • Setting this parameter to 0results in the job processing all the indexes. • Setting it to 20, for example, results in 20 indexes being processed at one time.

14. Index Maintenance • Reorganizing or rebuilding indexes when appropriate • You can configure how many indexes to possibly defragment at a time with the @HowManyIndexToDefrag parameter.

15. Index Maintenance • If the fragmentation level is below what is specified in the @avg_fragmentation_in_percent_limit_Reorg parameter, no defragmentation will occur • If the fragmentation is above the level specified in the @avg_fragmentation_in_percent_limit_Rebuild parameter, the index is rebuilt. • If the fragmentation is between the levels specified in these two parameters, the index is reorganized.

16. Doesn't Adversely Affect Database Availability & Performance • Configure a time limit that will override the @HowManyIndexToDefrag and @HowManyIndexInfoToPullvalues • You set the @DurationH and @DurationM parameters to indicate how many hours and minutes, respectively, to limit the measurement and defragment operations.

17. Example, suppose you set the parameters as follows • @HowManyIndexToDefrag=2 @HowManyIndexInfoToPull=20 @DurationH='01‘ @DurationM='30' • With these parameter values, • The measurement operation will stop after 90 minutes, even if all 20 indexes haven't been processed. • Similarly, the defragmentation operation will stop after 90 minutes, even if the two indexes haven't been processed. • If the time limit is reached in the middle of an operation, the solution will complete the operation for that index

18. Scheduled Job Calls the stored procedure using the various parameter values you set, for example: EXEC usp_IndexDefrag @DBNAME='TargetDBName1', @HowManyIndexToDefrag=10, @HowManyIndexInfoToPull=0, @DurationH='00', @DurationM='10', @avg_fragmentation_in_percent_limit_Reorg=10, @avg_fragmentation_in_percent_limit_Rebuild =20

19. Please don’t forget your evaluations … Email: alannoel@msn.com Need more information on DMVMUG Visit www.dmvmug.com Questions?