sg05121983
asked on
Index Rebuild causing blocking issue
Hi,
The attached script is causing blocking issue on our prod servers. Please let me know how to avoid the blocking issue i.e., is there any modifications required in that attached script.
Thanks in advance.
Index-Rebild.txt
The attached script is causing blocking issue on our prod servers. Please let me know how to avoid the blocking issue i.e., is there any modifications required in that attached script.
Thanks in advance.
Index-Rebild.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thnaks Mr. JestersGrind.
I will try your suggestion and let your know the results.
Any other suggestions on my script?
I will try your suggestion and let your know the results.
Any other suggestions on my script?
The rest of the script looks fine.
Greg
Greg
Just remember in your scripts you use the dm_db_index_physical_stats and that is a function that will go out and take some locks. Not always will there be extreme blocking, but it is not a lightweight operation. That will block things the way you are doing it, but it will always be the case and should be done at a time that there is less activity on the database.
You might also want to try to do that during "off hours" . . . doing it during "business hours" is not a Best Practice.
JestersGrind:
ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)
online index rebuild runs only in the enterprise editon ?
ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)
online index rebuild runs only in the enterprise editon ?
@sqlservr
That's a good point. Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions in SQL 2008.
Greg
That's a good point. Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions in SQL 2008.
Greg
ASKER
--
I take it the author has the Enterprise version of SQL Server. ;-)
Greg