Transcript Incremental Index MaintenanceA Solution “That

Incremental Index Maintenance
A Solution “That Just Works”
A L NO E L
PR INCI PA L CO NSULTANT, MICR OSOFT
A L A N.NOE L@MICR O SOF T.COM , A L A NNO EL@ MSN. CO M
DMVMUG User Conference 2013 – Reston, VA
Agenda
• SQL Server Indexes
• A Solution that “Just Works”
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
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)
Fragmentation
• External – file level
• Internal – indexes in the database
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.
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
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 missioncritical systems), you want to both determine fragmentation levels
and defragment indexes in a non-impactful way.
• Can be small servers or very large
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”
Three Components:
• Table
• T-SQL stored procedure
• Scheduled job to run the stored procedure
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.
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)
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 0 results in the job processing all the
indexes.
• Setting it to 20, for example, results in 20 indexes being processed at
one time.
Index Maintenance
• Reorganizing or rebuilding indexes when appropriate
• You can configure how many indexes to possibly defragment at a
time with the @HowManyIndexToDefrag parameter.
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.
Doesn't Adversely Affect Database
Availability & Performance
• Configure a time limit that will override the
@HowManyIndexToDefrag and @HowManyIndexInfoToPull values
• You set the @DurationH and @DurationM parameters to indicate
how many hours and minutes, respectively, to limit the measurement
and defragment operations.
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
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
Please don’t forget your evaluations …
Email: [email protected]
Need more information on DMVMUG
Visit www.dmvmug.com
QUESTIONS?