SQL Server – Index Rebuild – Blocked / Blocking

Background

Got a call about a hung database job.

 

Check Current Sessions

sp_whoIsActive

Code


exec sp_whoIsActive

Output

Explanation

  1. Session ID :- 161
    • Alter Index Session
    • Being blocked by Session ID :148
  2. Session ID :- 148
    • Select Statement

 

TroubleShooting

Why Is Select Blocking?

sp_block

Let us issue sp_block against the blocker and blocked sessions.

Code


-- exec sp_help sp_lock
declare @spidIndexOptimize int
declare @spidBlocker int

set @spidIndexOptimize = 161
set @spidBlocker = 148

exec sp_lock
		  @spid1 = @spidIndexOptimize 
		
exec sp_lock
		  @spid1 = @spidBlocker

 

Output

 

Explanation

  • Session ID :- 161
    • Schema Stability
    • Schema Modification
  • Session ID :- 148
    • Schema Stability

 

Why Is Index Reorg / Rebuilt?

sysindexes

Let us see how many records we have and how many records have changed.

Code


select 
		  [table] = 
					  object_schema_name(tblSI.id)
					+ '.'
					+ object_name(tblSI.id)
		, tblSI.indid
		, tblSI.[name]
		, tblSI.[rowcnt]
		, tblSI.[rowmodctr]
from   sysindexes tblSI
where  tblSI.indid In ( 0, 1)
and    tblSI.id = object_id('dbo.event_log_bkp')

;

 

sys.dm_db_index_physical_stats

Fragmentation %

Code


use [rbpivr1]
go

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'rbpivr1');  
SET @object_id = OBJECT_ID('dbo.event_log_bkp');  

IF @db_id IS NULL  
BEGIN;  
    PRINT N'Invalid database';  
END;  
ELSE IF @object_id IS NULL  
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN

	SELECT
			  tblSI.[name]
			--, tblSI.type_desc
			, tblSI.[index_id]
			, tblDIPS.index_type_desc
			, tblDIPS.page_count
			--, tblDIPS.record_count
			--, tblDIPS.*
			, tblDIPS.avg_fragmentation_in_percent 

	FROM   sys.dm_db_index_physical_stats
			(
				  @db_id
				, @object_id
				, NULL
				, NULL 
				, 'LIMITED'
			) tblDIPS

	inner join sys.indexes tblSI
		on   tblDIPS.[object_id] = tblSI.[object_id]
		and  tblDIPS.[index_id] = tblSI.[index_id]
			;  
END;

Output

Explanation

It appears that a couple of indexes are eligible for re-org and others for a rebuild.

Remediation

Handle Blocking

Documentation

Found out that Olla Hallengren’s code has a built-in mechanism for handling blocking.

The documentation is here.

Image

Explanation

  1. WaitAtLowPriorityMaxDuration
    • How long to wait in minutes
  2. WaitAtLowPriorityAbortAfterWait
    • Options
      • NONE
        • Continue waiting for Locks
        • Default Option
      • SELF
        • Abort the online index rebuild operation.
        • Terminate the Index Rebuild Option
      • BLOCKERS
        • Kill user transactions that block the online index rebuild operation.
        • Terminate the Blocker

 

Choice

Our choice, when blocked, will be to wait 10 minutes and terminate the Index Rebuild Option

Code


EXECUTE [AdminDB].dbo.IndexOptimize
	  @Databases = 'USER_DATABASES'
	, @FragmentationLow = NULL
	, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationLevel1 = 5
	, @FragmentationLevel2 = 30
	, @UpdateStatistics = 'ALL'
	, @OnlyModifiedStatistics = 'Y'
	, @LogToTable = 'Y'
	--20170813 9:17 PM dadenji
	, @WaitAtLowPriorityMaxDuration=10
	, @WaitAtLowPriorityAbortAfterWait='SELF'

Generated Code

Here is the code that is generated when we make the change outlined above:

 

References

  1. Michael J Swart ( Database Whisper )
    • The Sch-M Lock is Evil
      Link

One thought on “SQL Server – Index Rebuild – Blocked / Blocking

Leave a Reply

Please log in using one of these methods to post your comment:

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