What do you do if you have a very large table and you can’t
take an outage window to rebuild the clustered index offline?
·
Exclude the clustered index from your index
maintenance jobs
·
Configure it to only have REORGANIZE operations,
not REBUILDS
·
Rebuild Index Online
As Microsoft’s documentation
says:
We recommend performing online index operations for business
environments that operate 24 hours a day, seven days a week, in which the need
for concurrent user activity during index operations is vital.
How online index operations work
To allow for concurrent user activity during an index data
definition language (DDL) operation, the following structures are used during
the online index operation: source and preexisting indexes, target, and for
rebuilding a heap or dropping a clustered index online, a temporary mapping
index.
·
During an online index operation, such as
creating a clustered index on a nonindexed table (heap), the source and target
go through three phases: preparation, build, and final.
·
The source is the original table or clustered
index data.
·
An online index rebuild starts against an index
·
The preexisting indexes are available to
concurrent users for select, insert, update, and delete operations.
·
The target or target is the new index (or heap)
or a set of new indexes that is being created or rebuilt. Users insert, update,
and delete operations to the source are applied by the Database Engine to the
target during the index operation. The target index isn't used until the
index operation is committed. Internally, the index is marked as write-only.
·
A modification query runs before the index rebuilds
query. At the end of the index rebuild, it tries to get a schema
modification lock. It can’t, because the modification query is still running
·
The index rebuild’s super-high-level lock
request then causes a massive pile-up behind it– blocking even queries that
want to do dirty reads, even NOLOCK can be blocked
The following illustration shows the process for creating an
initial clustered index online. The source object (the heap) has no other
indexes. The source and target structure activities are shown for each phase;
concurrent user SELECT, INSERT, UPDATE, and DELETE operations are
also shown. The preparation, build, and final phases are indicated together
with the lock modes used in each phase.
Side-Effects of online Rebuild index
·
Blocking -. In order to complete Online
Rebuild Index operation, SQL server need
a very high level of lock: a schema modification lock (SCH-M) which
results in blocking scenarios.
·
Offline Rebuild Index -SQL Server Online
Index Rebuild sometimes happens offline without
warning and it blocks both read and write queries against the table for long
periods.
Workaround to avoid blocking
ALTER INDEX [INDEX_NAME]
ON [TABLENAME]
REBUILD WITH ( FILLFACTOR = 80,ONLINE = ON
(WAIT_AT_LOW_PRIORITY
(MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
- WAIT_AT_LOW_PRIORITY
– is the new setting
- MAX_DURATION
– is used to specify the maximum time in minutes to wait until an action
- ABORT_AFTER_WAIT
– is used to abort the operation if it exceeds the wait time
- NONE
– is to wait for the lock with regular priority
- SELF
– exits the online index operation
- BLOCKERS
– kills transactions blocking the index rebuild
There’s no pileup! “LOW_PRIORITY” really does mean low
priority – the “NOLOCK” query is able to get a shared schema lock and move
forward.
SQL Code:
CREATE TABLE [dbo].[IndexMaintenanceLog](
[IndexName]
[varchar](200) NULL,
[TableName]
[varchar](200) NULL,
[CommandText]
[varchar](1500) NULL,
[Status]
[varchar](200) NULL,
[ErrorMessage]
[varchar](1500) NULL,
[CreatedON]
[datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ IndexOptimize]
@UseResumable BIT
= 0,
@UseLowPriorityWait BIT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql
NVARCHAR(MAX), @IndexName SYSNAME, @TableName SYSNAME, @SchemaName SYSNAME,
@Frag FLOAT, @IndexTypeDesc NVARCHAR(60);
SELECT
s.name AS
SchemaName,
t.name AS
TableName,
i.name AS
IndexName,
ips.avg_fragmentation_in_percent AS Fragmentation,
i.type_desc AS
IndexTypeDesc
INTO #IndexStats
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
INNER JOIN
sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
INNER JOIN
sys.tables t ON i.object_id = t.object_id
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
AND i.name IS NOT
NULL
AND ips.page_count
> 100 -- Skip small indexes
AND
ips.index_level = 0 -- Only leaf level
ORDER BY
ips.avg_fragmentation_in_percent DESC;
DECLARE
index_cursor CURSOR FOR
SELECT SchemaName,
TableName, IndexName, Fragmentation, IndexTypeDesc FROM #IndexStats;
OPEN index_cursor;
FETCH NEXT FROM
index_cursor INTO @SchemaName, @TableName, @IndexName, @Frag, @IndexTypeDesc;
WHILE
@@FETCH_STATUS = 0
BEGIN
BEGIN TRY
IF @Frag
>= 30
BEGIN
SET
@sql = N'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' +
@TableName + '] REBUILD WITH ( FILLFACTOR = 80,ONLINE = ON ('
IF
@UseLowPriorityWait = 1
SET @sql += 'WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES,
ABORT_AFTER_WAIT = SELF)';
IF
@UseResumable = 1
SET @sql += ', RESUMABLE = ON, MAX_DURATION = 15 MINUTES';
SET
@sql += '));';
END
ELSE IF
@Frag >= 10
BEGIN
SET
@sql = N'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' +
@TableName + '] REORGANIZE;';
END
ELSE
BEGIN
FETCH
NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName, @Frag,
@IndexTypeDesc;
CONTINUE;
END
PRINT
@sql
EXEC
sp_executesql @sql;
INSERT
INTO IndexMaintenanceLog (IndexName, TableName, CommandText, Status,CreatedOn)
VALUES
(@IndexName, @TableName, @sql, 'Success',getdate());
END TRY
BEGIN CATCH
INSERT
INTO IndexMaintenanceLog (IndexName, TableName, CommandText, Status,
ErrorMessage,CreatedOn)
VALUES
(@IndexName, @TableName, @sql, 'Failure', ERROR_MESSAGE(),getdate());
END CATCH;
FETCH NEXT
FROM index_cursor INTO @SchemaName, @TableName, @IndexName, @Frag,
@IndexTypeDesc;
END
CLOSE
index_cursor;
DEALLOCATE
index_cursor;
SET NOCOUNT OFF;
END
No comments:
Post a Comment