Index defragmentation with (MAXDOP = 4)

SQL Server database indexes are created mainly to optimize performance. Different types of indexes such as clustered index, non-clustered index are put in place to make query process run faster. However over time, DML statements (insert, update, delete) cause information scattered across database hence resulting table or index fragmentation. Fragmentation happens when the data logical order does not match with physical ordering inside the data file. These fragmentation need to be addressed according as it could significantly affect SQL Server performance.

There are two main ways to address fragmentation, REORGANIZE or REBUILD the index. In general, reorganize an index uses lower resources than rebuild by reordering the leaf-level pages to match the logical order. Rebuild an index is to drop the existing index and recreate the index (To be more accurate, it creates a new copy of the index before dropping the old one). During index creation, the index row is order in contiguous pages.

Here are some of the differences between reorganize and rebuild:

REORGANIZE is a single thread process. It is fully logged but does not prevent transaction log from clearing. REORGANIZE is an ONLINE operations which only incur minimal lock, not the holding the lock for entire operation. REORGANIZE does not update statistics. It requires less space.

REBUILD can utilize multiple CPUs for its operation (Running in parallel, limited to MAXDOP setting). It can use minimal logging in bulk-logged and simple recovery model to reduce log growth, but fully logged in full recovery model. REBUILD may incur long term lock with OFFLINE operations. Short term lock still incur at the start and the end of the operation.

Since REBUILD recreate index which require a full scan, it automatically update the statistics (similar with UPDATE STATISTICS .. WITH FULL SCAN mode). It requires larger space since a new copy of index has to be created before the old one is discarded.

The general guideline provided from Microsoft is to use REORGANIZE if fragmentation is > 10% and <= 30%; and use REBUILD operation for > 30% fragmentation.
In both cases, the index should have at least 1000 pages to consider for defragmentation. Keep in mind that this is just the general guidelines

To detect fragmentation and prepare script for index defragmentation, you can use the following stored procedure:

USE [DBA]   –Your personal service database name
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPU_IndexFragmentationInfo]

AS

BEGIN

–Create work table
IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘IndexesInfo’)
DROP TABLE DBA.dbo.IndexesInfo

CREATE TABLE DBA.dbo.IndexesInfo
(
[DatabaseName]  VARCHAR(75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TableName]         VARCHAR(275) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IndexFragmentation] VARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_Fragmentation]  DECIMAL (10,2),
[Date]                                  DATETIME
)

–Prepare scripts for index defragmentation
DECLARE @dbname    VARCHAR(150),
@sqltext      VARCHAR(4000),
@dbid           INT

DECLARE x CURSOR FOR
select sd.name, sd.dbid
from sys.sysdatabases sd JOIN sys.databases d ON sd.dbid = d.database_id
WHERE sd.name IN (‘database names’)                            –Change it to database names you want to be defragmented
AND d.collation_name = ‘SQL_Latin1_General_CP1_CI_AS’
OPEN x
FETCH NEXT FROM x INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN

–When the script for REBUILDIN/REORGANIZING indexes is being generated we use option MAXDOP=4

SELECT @sqltext = ‘

USE [‘+ @dbname +’]

INSERT INTO DBA.dbo.IndexesInfo ([DatabaseName], [TableName], [IndexFragmentation], [AVG_Fragmentation], [Date])
SELECT DB_NAME(), t.name, ”ALTER INDEX [” + ix.name + ”] ON [” + s.name + ”].[” + t.name + ”] ” +
CASE WHEN ps.avg_fragmentation_in_percent > 30 THEN ”REBUILD WITH (ONLINE = ON, MAXDOP = 4)” ELSE ”REORGANIZE” END +
CASE WHEN pc.partition_count > 1 THEN ” PARTITION = ” + cast(ps.partition_number as nvarchar(4000)) ELSE ”” END, ps.avg_fragmentation_in_percent, GETDATE()
FROM   sys.indexes (NOLOCK) AS ix INNER JOIN sys.tables (NOLOCK) t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas (NOLOCK) s
ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (‘+CAST(@dbid AS VARCHAR)+’, NULL, NULL, NULL, NULL)) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions (NOLOCK)
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL

–PRINT @sqltext
EXEC (@sqltext)

FETCH NEXT FROM x INTO @dbname, @dbid
END

CLOSE x
DEALLOCATE x

END

After running this stored procedure you’ll get something like this:

preparescriptsresult

 

SQL Server (Enterprise edition) allows use of multiple processors to perform REBUILD operation by using MAXDOP option. This MAXDOP option set number of dedicated CPU for index operation, and it overrides max degree of parallelism set at server configuration.

SQL Server 2008 introduces ONLINE option for REBUILD. It reduces the duration of locks by holding lock very shortly at the beginning and the end of the REBUILD operation.
ONLINE operation is fully logged regardless of recovery model. OFFLINE option applies lock throughout the index operation and prevent user to access the table.
However, OFFLINE is faster.

As a database administrator, our goal is to optimize SQL Server for the day to day workload. This may require configuring “maximum degree of parallelism” (MAXDOP)
to a value different than the default.  In some circumstances the MAXDOP value can be less than 8, even though modern servers may have more than 8 processors.
We should take advantage of increasing the “maximum degree of parallelism” (MAXDOP) during maintenance for rebuilding indexes.

I’m going to demonstrate one approach how to defragment indexes with MAXDOP=4. You can try with any parallel treads you want. It depends on how many CPUs there are on a server.

First, we need to split all tables (containing indexes for defragmentation) in as many groups as we going to use CPUs for defragmentation. In our case is 4. Below you can find a stored procedure for that:

USE [DBA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GenerateIndexRebuildDistribution]
@totalTaskCount INT

AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘IndexesRebuildTableDistro’)
DROP TABLE DBA.dbo.IndexesRebuildTableDistro

CREATE TABLE DBA.dbo.IndexesRebuildTableDistro
(
[TaskId]     INT NOT NULL,
[TableName]  VARCHAR(275) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IndexCount] INT,
[Date]       DATETIME
)
— I included IndexCount so we can check the quality of the distribution using this query
— select taskid, count(tableName) tables, sum(indexcount) indexes from IndexesRebuildTableDistro group by taskId

DECLARE @totalIndexCount INT
select @totalIndexCount = count(*) from DBA.dbo.IndexesInfo
DECLARE @indexCountTarget INT
SET @indexCountTarget = @totalIndexCount/@totalTaskCount

PRINT @indexCountTarget

DECLARE @currentTaskId INT = 1
DECLARE @taskIndexCount INT = 0

DECLARE @tableName VARCHAR(275)
DECLARE @indexCount INT
DECLARE x CURSOR FOR
select count([TableName]) indexCount, [TableName] from DBA.dbo.IndexesInfo group by [TableName]

OPEN x
FETCH NEXT FROM x INTO @indexCount, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN

— Assign this table to the current task
INSERT INTO DBA.dbo.IndexesRebuildTableDistro (TaskId, TableName, IndexCount, Date)
values (@currentTaskId, @tableName, @indexCount, GETDATE())

— Update the current task’s index count.
SET @taskIndexCount = @taskIndexCount + @indexCount

— If the task’s current index count exceeds the target we’ll move on to the next task
IF @taskIndexCount > @indexCountTarget
BEGIN
SET @currentTaskId = @currentTaskId + 1
SET @taskIndexCount = 0
END

FETCH NEXT FROM x INTO @indexCount, @tableName
END

CLOSE x
DEALLOCATE x

END

The @totalTaskCount  = 4 in our case.

The result looks like:

TaskId TableName IndexCount Date
1 actcod 1 1/11/17 3:30 AM
1 alloc_stats 1 1/11/17 3:30 AM
1 alt_prtmst 1 1/11/17 3:30 AM
1 appt 3 1/11/17 3:30 AM
1 appt_id 1 1/11/17 3:30 AM
1 archdr 1 1/11/17 3:30 AM
2 invsum 5 1/11/17 3:30 AM
2 job_definition_exec 2 1/11/17 3:30 AM
2 jobcode 1 1/11/17 3:30 AM
2 jobcode_kvi 1 1/11/17 3:30 AM
2 kvi_summary 3 1/11/17 3:30 AM
3 pmsn_ctl_assoc 1 1/11/17 3:30 AM
3 profile_def 1 1/11/17 3:30 AM
3 prtftp 2 1/11/17 3:30 AM
3 prtlot 1 1/11/17 3:30 AM
3 prtmst_sup_wh 1 1/11/17 3:30 AM
3 rcvinv 2 1/11/17 3:30 AM
4 sl_ifd_seg 2 1/11/17 3:30 AM
4 sl_msg_log 2 1/11/17 3:30 AM
4 sl_o_ifd_sys_map 4 1/11/17 3:30 AM
4 sl_retr_mthd_impl_def 2 1/11/17 3:30 AM
4 sl_sys_comm_val 2 1/11/17 3:30 AM
4 sl_sys_comm_val_bnd_data 3 1/11/17 3:30 AM
4 sl_sys_def 1 1/11/17 3:30 AM
4 sl_sys_ifd_comm_val_bnd_data 2 1/11/17 3:30 AM

Now we need to execute command for index REBUILD or REORGANIZE for particular TaskID for running these commands in parallel. For that you can find another stored procedure:

USE [DBA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RebuildIndexes]
— Database to execute against
@database NVARCHAR(100),
— Task ID.  Should be an intenter ranging from 1 to @numberOfTasks.  Used to multithread work
@taskId INT

AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(4000)
DECLARE @command NVARCHAR(4000)
DECLARE x CURSOR FOR
SELECT ii.IndexFragmentation FROM DBA..IndexesInfo ii
JOIN DBA..IndexesRebuildTableDistro id
ON ii.tableName = id.tableName
WHERE id.taskId = @taskId

OPEN x
FETCH NEXT FROM x INTO @command
WHILE @@FETCH_STATUS = 0
BEGIN
— Create a SQL command that will execute the index ALTER statement against the specified database
SET @SQL = ‘USE ‘+@database+CHAR(32)+@command
EXEC (@SQL)
–PRINT @SQL

FETCH NEXT FROM x INTO @command
END

CLOSE x
DEALLOCATE x

END

Now we’re ready for create Maintenance plan:

maintenanceplan_indexdefragment_maxdop4

In “Get Index fragmentation Info” control we execute stored procedure:  [dbo].[SPU_IndexFragmentationInfo]

In “GenerateIndexRebuildDistribution” control we execute stored procedure: [dbo].[GenerateIndexRebuildDistribution] 4

In “Index Rebuilder 1” we execute stored procedure [dbo].[RebuildIndexes] <Your db name>,1

In “Index Rebuilder 2” we execute stored procedure [dbo].[RebuildIndexes] <Your db name>,2 and so on.

I think this approach will help you to handle index defragmentation on your servers much faster.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s