July 7, 2009 at 1:30 pm
Following is the stored procedure which reindex the tables based on the fragmentation level. Recently, I added following command inside the cursor to set page locks on because I was getting error some thing like this " cannot re-organize the table because page lock is off"
My question is , is it possible that after adding this command will make re-index slow. ?
SET @SQLREINDEX = ''
SET @SQLREINDEX = 'ALTER INDEX ['+@INDEXNAME+'] ON DBO.['+@TABLENAME+']
SET (ALLOW_PAGE_LOCKS = ON)'
EXEC(@SQLREINDEX)
ALTER Procedure [dbo].[POST_DBREINDEX]
@TABLENAME SYSNAME
AS
BEGIN TRY
IF NOT EXISTS (SELECT NAME FROM SYS.SYSOBJECTS WHERE NAME = @TABLENAME)
BEGIN
PRINT 'TABLE NOT FOUND'
RETURN
END
DECLARE @SQLREINDEX VARCHAR(2000)
DECLARE @INDEXNAME VARCHAR(255)
DECLARE @AVG_FRAG SMALLINT
DECLARE @PAGE_COUNT INT
DECLARE @OBJECT_ID INT
SET @OBJECT_ID = OBJECT_ID(@TABLENAME)
-- declare cursor
DECLARE FRAG_CURSOR CURSOR FOR
SELECT --OBJECT_NAME(dt.Object_id) TableName,
si.Name IndexName, DT.Avg_Fragmentation_In_percent, dt.Page_Count
FROM
(
SELECT
Object_Id, Index_Id, Partition_Number, Avg_Fragmentation_In_percent,
Avg_Page_Space_Used_In_Percent, Page_Count
FROM
Sys.Dm_Db_Index_Physical_Stats (db_id(), @OBJECT_ID, NULL, NULL, NULL)
WHERE
Index_Id NOT IN (0) AND Index_Level = 0
) AS dt
INNER JOIN Sys.Indexes si ON si.Object_id = dt.Object_id AND si.Index_Id = dt.Index_Id
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO @INDEXNAME, @AVG_FRAG, @PAGE_COUNT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLREINDEX = ''
SET @SQLREINDEX = 'ALTER INDEX ['+@INDEXNAME+'] ON DBO.['+@TABLENAME+']
SET (ALLOW_PAGE_LOCKS = ON)'
EXEC(@SQLREINDEX)
-- PRINT @INDEXNAME + ' '+CONVERT(VARCHAR(40),@AVG_FRAG)
-- Check fragmentation level
IF @AVG_FRAG > 30 AND @PAGE_COUNT > 1000
BEGIN
PRINT 'REBUILD STARTED'+' '+@INDEXNAME
SET @SQLREINDEX = ''
-- DBCC DBREINDEX (@TABLENAME, @PKNAME, 0)
-- REINDEX PRIMARY AS WELL AS SECOUNDARY INDEXES WITH 95% FILL FACTOR FOR INCREMENTAL LOAD
SET @SQLREINDEX = 'ALTER INDEX ['+@INDEXNAME+'] ON DBO.['+@TABLENAME+']
REBUILD WITH ( MAXDOP = 8,
FILLFACTOR = 95,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON
)'
EXEC(@SQLREINDEX)
END
IF (@AVG_FRAG > 10 AND @AVG_FRAG <= 30) AND @PAGE_COUNT > 1000
BEGIN
PRINT 'REORGANIZE STARTED'+' '+@INDEXNAME
SET @SQLREINDEX = ''
SET @SQLREINDEX = 'ALTER INDEX ['+@INDEXNAME+'] ON DBO.['+@TABLENAME+']
REORGANIZE'
EXEC(@SQLREINDEX)
END
FETCH NEXT FROM FRAG_CURSOR INTO @INDEXNAME, @AVG_FRAG, @PAGE_COUNT
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
SELECT '' AS ErrorMessage
END TRY
BEGIN CATCH
SELECT CONVERT (TEXT, ERROR_MESSAGE()) AS ErrorMessage
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
END CATCH
July 10, 2009 at 3:33 am
In 2005 no as you can rebuild the index online .
A bit of performance hit will be there as page level locks will be taken at the time the reorganization is happening (you will not be allowed to change those pages) but the moment lots of row level locks are enabled lock escalation will take place . So , it should be fine .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply