We are running database maintenace with indexing the system, indexing fails due to locking?

Question

We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be due to deadlocks

the indexing script we use is:

--------  REBUILDING THE INDEXES----------------------

CREATE TABLE #TableIDs (Tablename nvarchar(200),IndexName nvarchar(1000))
        --Database to be indexed--
        INSERT INTO #TableIDs (Tablename, IndexName) 
        SELECT a.object_id,name
        FROM sys.dm_db_index_physical_stats (DB_ID(N'RAL'), NULL, NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
        Where avg_fragmentation_in_percent > 20 and name is not null  and OBJECT_SCHEMA_NAME(a.object_id) = 'dbo' 
        And object_name(a.object_id) not in (SELECT
                                             OBJECT_NAME(p.object_id) BlockedObjectName
                                             FROM sys.dm_tran_locks AS tl
                                             INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                             INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                             INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                             INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                             INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                             CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                             CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                             Where db.name = 'RAL') 


declare c cursor for select object_name(Tablename) as Tablename ,IndexName from #TableIDs
declare @TableName varchar(1000)
declare @IndexName varchar(1000)
open c
fetch next from c into @TableName, @IndexName
        while @@FETCH_STATUS = 0
        BEGIN       



              if ((SELECT
                                         count(OBJECT_NAME(p.object_id)) BlockedObjectName
                                         FROM sys.dm_tran_locks AS tl
                                         INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                         INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                         INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                         INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                         INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                         CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                         CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                         Where db.name = 'RALMYT' and OBJECT_NAME(p.object_id) = @TableName) ) = 0      
                BEGIN
                    EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (FILLFACTOR=90,STATISTICS_NORECOMPUTE = OFF,ONLINE=OFF,MAXDOP=8)')
                    PRINT 'Reindexing ' + @IndexName + ' on  ' + @TableName + ' table'
                END
     fetch next from c into @TableName, @IndexName
End         
CLOSE c
DEALLOCATE c    


DROP TABLE #TableIDs

Please let me know..... how to avoid the errors or failures while indexing

Answer 1

If the requirement is to rebuild the indexes while letting users continue to work, you will find this extremely challenging, if not impossible, to meet if you can't do online index rebuilds, which are only available in Enterprise Edition.

There are really only two types of solutions:

• Use online index rebuilds.
• Do index REORGANIZE instead of REBUILD -- reorgs are always online operations, but may not do as good a job as a rebuild, and may run more slowly in comparison.

Regardless, instead of rolling your own scripts, I would highly recommend using an already-built-and-tested solution such as Ola Hallengren's maintenance scripts, which will intelligently maintain your indexes based on their current state of fragmentation. The indexes may not actually need to be rebuilt all the time.

Answer 2

Rather than re-index which will lock tables why not set up a defragment index solution instead?. I use one that combines showcontig with a defragment and does not seem to cause any issues. you can set the fragmentation level...

CREATE PROCEDURE  dbo.[zz_maint_Defragindexes]

AS 
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 25.0--can adjust this

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist 
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist