What does index rebuild does in backend

Zahid Butt 956 Reputation points
2022-10-11T19:32:35.957+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,481 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 117.1K Reputation points MVP
    2022-10-11T21:20:08.067+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. CathyJi-MSFT 22,346 Reputation points Microsoft Vendor
    2022-10-18T08:11:50.957+00:00

    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".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.