September 30, 2013 at 2:06 am
hi all,
need your help on this.
i have a job which is reorganizing the index and with that i am having a issue of blocking when ever the job is running the blocking is coming up.AS per my knowledge reorganize of index is online and it should not create long holding blocking or blocking due to any update or select.
guide me if i am wrong and suggest for a solution and why the blocking happening ?
thanks
Ivan 🙂
September 30, 2013 at 5:08 am
Two points ...
1) are you running this maintenance job in peak hours... it should be run in nights (OFF - peak hours)
2) have you selectiong the advanced option in reindxing/rebuilding" like 'Sort in Tempdb ' and "Keep index online while .."
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 30, 2013 at 5:13 am
Rebuilding indexes will take locks; reorganising won't. Please will you post your code?
John
September 30, 2013 at 5:16 am
Reorganize of index is always online by-Default and we run it off hours less transaction.
September 30, 2013 at 5:23 am
below is the script that i use to reorganize the index
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
DECLARE @Statement NVARCHAR(300)
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN (''master'',''msdb'',''tempdb'',''model'')
AND DATABASEPROPERTYEX(name, ''Status'')=''ONLINE'' AND is_read_only<>1
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ''DECLARE TableCursor CURSOR FOR SELECT ''''['''' + table_catalog + ''''].['''' + table_schema + ''''].['''' +
table_name + '''']'''' as tableName FROM '' + @Database + ''.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''''BASE TABLE''''''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = ''ALTER INDEX ALL ON '' + @Table + '' REORGANIZE ''
EXEC (@cmd)
--PRINT (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,'' '',@fillfactor)
END
--PRINT N''UPDATING STATISTICS '' + @Table
SET @Statement = ''UPDATE STATISTICS '' + @Table + '' WITH FULLSCAN''
EXEC sp_executesql @Statement
--PRINT @Statement
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
September 30, 2013 at 5:37 am
I don't know why that would hold a lock for a long period of time. Have you tried to use sys.dm_os_waiting_tasks to see which session is doing the blocking?
John
September 30, 2013 at 5:40 am
yes that is what i am asking i am getting blocking email when ever this job is running so asked you all to help me to find a solution .
September 30, 2013 at 6:09 am
Do we need to update statistics AFTER index rebuild/reorgainze ??
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 30, 2013 at 6:29 am
Well that is not the issue over here plz read my question throughly and then reply.
September 30, 2013 at 6:41 am
What is a blocking email and under what circumstances do you get one? Have you managed to ascertain which particular statement is causing the blocking?
John
September 30, 2013 at 6:52 am
John Mitchell-245523 (9/30/2013)
Have you tried to use sys.dm_os_waiting_tasks to see which session is doing the blocking?
?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2013 at 6:53 am
john
yes i have automatic blocking alert also with the details which is causing the blocking with query and time and etc .
but here my question is when i reorganize the index why the blocking occurs which should not come as per the reorganize index criteria reorganize of index is online and lock does not occurs at that point of time as the index are available for use.
September 30, 2013 at 6:57 am
well gila i have the details of which all others query was running @ that point of time but alter index reorganize is the head of the blocking chain.
September 30, 2013 at 6:57 am
Yes, but how do you know it's the ALTER INDEX...REORGANIZE statement that's doing the blocking? The code you posted does more than that.
How long does the blocking have to last before the alert is sent?
John
September 30, 2013 at 6:59 am
Ivan Mohapatra (9/30/2013)
well gila i have the details of which all others query was running @ that point of time but alter index reorganize is the head of the blocking chain.
Ah, that answers one of my questions. What was the query that was being blocked?
John
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply