With help of Profiler, I can confirm that ALTER INDEX REBUILD indeed runs such a statement.
The statement may seem silly, but keep in mind that this is actually what SQL Server is doing: it copies the table (or the index) from one place to another.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi,
We have SQL Sentry installed for our SQL server 2014.
Today I saw a query in Blocking tab , that was block another query for short time:
"insert into dbo.test select * from dbo.test option (maxdop 1)" executed by sqlserveragent --This query was block some other
I could not get it as we do not run such queries manually or in any of the job.
We have a loging database, in which we log all running queries after a specifice given period in whole day. I looked into those logs & found that at same time index rebuild was in process on table dbo.test.
Does index rebuild executes such queries in backend working.
Regards,
Zahid
With help of Profiler, I can confirm that ALTER INDEX REBUILD indeed runs such a statement.
The statement may seem silly, but keep in mind that this is actually what SQL Server is doing: it copies the table (or the index) from one place to another.
Hi @Zahid Butt ,
An offline index rebuild usually takes less time than an online rebuild, but it holds object-level locks for the duration of the rebuild operation, blocking queries from accessing the table or view.
An online index rebuild does not require object-level locks until the end of the operation, when a lock must be held for a short duration to complete the rebuild.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".