Why does an index rebuild requires a Sch-M lock?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP












4















I don't see why an index rebuild would require a Sch-M lock on the given index.










share|improve this question




























    4















    I don't see why an index rebuild would require a Sch-M lock on the given index.










    share|improve this question


























      4












      4








      4


      1






      I don't see why an index rebuild would require a Sch-M lock on the given index.










      share|improve this question
















      I don't see why an index rebuild would require a Sch-M lock on the given index.







      sql-server database-internals index-maintenance






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 31 at 14:21









      Paul White

      52.2k14278451




      52.2k14278451










      asked Jan 31 at 13:45









      KimoKimo

      254




      254




















          2 Answers
          2






          active

          oldest

          votes


















          4














          Schema modification (Sch-M) locks are acquired when any metadata changes occur.



          In case of index rebuild (online or offline) a new copy of an index is built, and the old index is dropped. Before dropping an old index, table metadata need to be updated.



          So at the final stage of index rebuild Sch-M lock is acuired to replace an index reference in the metadata.



          UPDATE



          Answer to comment




          Adding (not rebuilding!) a new nonclustered index online does not
          require Sch-M. – Paul White




          Ok.
          Here is my new repro:



          use tempdb;
          go

          create table dbo.t (id int);
          insert into dbo.t values (1);
          go

          begin tran;
          create index IX_t on dbo.t (id) with (online = on);

          select l.request_mode,
          l.resource_type,
          l.resource_subtype,
          l.resource_associated_entity_id,
          object_name(p.object_id),
          p.index_id
          from sys.dm_tran_locks l
          left join sys.partitions p
          on p.hobt_id = l.resource_associated_entity_id
          where l.request_session_id = @@spid and l.request_mode = N'Sch-M';

          commit;
          go


          enter image description here






          share|improve this answer




















          • 1





            Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock?

            – Kimo
            Jan 31 at 14:31






          • 1





            The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one

            – sepupic
            Jan 31 at 14:35


















          10














          The documentation for How Online Index Operations Work says:




          SCH-M (Schema Modification) if any source structure (index or table) is dropped.*



          * The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.




          That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.



          Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).



          Also related from the documentation:




          ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...




          From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.



          In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:




          When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.




          Related links:




          • The Sch-M lock is Evil by Michael J Swart





          share|improve this answer
























            Your Answer








            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "182"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228561%2fwhy-does-an-index-rebuild-requires-a-sch-m-lock%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            4














            Schema modification (Sch-M) locks are acquired when any metadata changes occur.



            In case of index rebuild (online or offline) a new copy of an index is built, and the old index is dropped. Before dropping an old index, table metadata need to be updated.



            So at the final stage of index rebuild Sch-M lock is acuired to replace an index reference in the metadata.



            UPDATE



            Answer to comment




            Adding (not rebuilding!) a new nonclustered index online does not
            require Sch-M. – Paul White




            Ok.
            Here is my new repro:



            use tempdb;
            go

            create table dbo.t (id int);
            insert into dbo.t values (1);
            go

            begin tran;
            create index IX_t on dbo.t (id) with (online = on);

            select l.request_mode,
            l.resource_type,
            l.resource_subtype,
            l.resource_associated_entity_id,
            object_name(p.object_id),
            p.index_id
            from sys.dm_tran_locks l
            left join sys.partitions p
            on p.hobt_id = l.resource_associated_entity_id
            where l.request_session_id = @@spid and l.request_mode = N'Sch-M';

            commit;
            go


            enter image description here






            share|improve this answer




















            • 1





              Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock?

              – Kimo
              Jan 31 at 14:31






            • 1





              The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one

              – sepupic
              Jan 31 at 14:35















            4














            Schema modification (Sch-M) locks are acquired when any metadata changes occur.



            In case of index rebuild (online or offline) a new copy of an index is built, and the old index is dropped. Before dropping an old index, table metadata need to be updated.



            So at the final stage of index rebuild Sch-M lock is acuired to replace an index reference in the metadata.



            UPDATE



            Answer to comment




            Adding (not rebuilding!) a new nonclustered index online does not
            require Sch-M. – Paul White




            Ok.
            Here is my new repro:



            use tempdb;
            go

            create table dbo.t (id int);
            insert into dbo.t values (1);
            go

            begin tran;
            create index IX_t on dbo.t (id) with (online = on);

            select l.request_mode,
            l.resource_type,
            l.resource_subtype,
            l.resource_associated_entity_id,
            object_name(p.object_id),
            p.index_id
            from sys.dm_tran_locks l
            left join sys.partitions p
            on p.hobt_id = l.resource_associated_entity_id
            where l.request_session_id = @@spid and l.request_mode = N'Sch-M';

            commit;
            go


            enter image description here






            share|improve this answer




















            • 1





              Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock?

              – Kimo
              Jan 31 at 14:31






            • 1





              The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one

              – sepupic
              Jan 31 at 14:35













            4












            4








            4







            Schema modification (Sch-M) locks are acquired when any metadata changes occur.



            In case of index rebuild (online or offline) a new copy of an index is built, and the old index is dropped. Before dropping an old index, table metadata need to be updated.



            So at the final stage of index rebuild Sch-M lock is acuired to replace an index reference in the metadata.



            UPDATE



            Answer to comment




            Adding (not rebuilding!) a new nonclustered index online does not
            require Sch-M. – Paul White




            Ok.
            Here is my new repro:



            use tempdb;
            go

            create table dbo.t (id int);
            insert into dbo.t values (1);
            go

            begin tran;
            create index IX_t on dbo.t (id) with (online = on);

            select l.request_mode,
            l.resource_type,
            l.resource_subtype,
            l.resource_associated_entity_id,
            object_name(p.object_id),
            p.index_id
            from sys.dm_tran_locks l
            left join sys.partitions p
            on p.hobt_id = l.resource_associated_entity_id
            where l.request_session_id = @@spid and l.request_mode = N'Sch-M';

            commit;
            go


            enter image description here






            share|improve this answer















            Schema modification (Sch-M) locks are acquired when any metadata changes occur.



            In case of index rebuild (online or offline) a new copy of an index is built, and the old index is dropped. Before dropping an old index, table metadata need to be updated.



            So at the final stage of index rebuild Sch-M lock is acuired to replace an index reference in the metadata.



            UPDATE



            Answer to comment




            Adding (not rebuilding!) a new nonclustered index online does not
            require Sch-M. – Paul White




            Ok.
            Here is my new repro:



            use tempdb;
            go

            create table dbo.t (id int);
            insert into dbo.t values (1);
            go

            begin tran;
            create index IX_t on dbo.t (id) with (online = on);

            select l.request_mode,
            l.resource_type,
            l.resource_subtype,
            l.resource_associated_entity_id,
            object_name(p.object_id),
            p.index_id
            from sys.dm_tran_locks l
            left join sys.partitions p
            on p.hobt_id = l.resource_associated_entity_id
            where l.request_session_id = @@spid and l.request_mode = N'Sch-M';

            commit;
            go


            enter image description here







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Jan 31 at 20:07

























            answered Jan 31 at 14:23









            sepupicsepupic

            7,328818




            7,328818







            • 1





              Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock?

              – Kimo
              Jan 31 at 14:31






            • 1





              The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one

              – sepupic
              Jan 31 at 14:35












            • 1





              Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock?

              – Kimo
              Jan 31 at 14:31






            • 1





              The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one

              – sepupic
              Jan 31 at 14:35







            1




            1





            Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock?

            – Kimo
            Jan 31 at 14:31





            Thank you. But in case we are using a clustered index, there is no index reference needed since the index is the data itself right? So why does it still acquire a Sch-M lock?

            – Kimo
            Jan 31 at 14:31




            1




            1





            The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one

            – sepupic
            Jan 31 at 14:35





            The metadata of the table itself still need to be udated, it's the table itself that now is dropped and substituted with the new one

            – sepupic
            Jan 31 at 14:35













            10














            The documentation for How Online Index Operations Work says:




            SCH-M (Schema Modification) if any source structure (index or table) is dropped.*



            * The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.




            That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.



            Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).



            Also related from the documentation:




            ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...




            From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.



            In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:




            When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.




            Related links:




            • The Sch-M lock is Evil by Michael J Swart





            share|improve this answer





























              10














              The documentation for How Online Index Operations Work says:




              SCH-M (Schema Modification) if any source structure (index or table) is dropped.*



              * The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.




              That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.



              Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).



              Also related from the documentation:




              ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...




              From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.



              In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:




              When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.




              Related links:




              • The Sch-M lock is Evil by Michael J Swart





              share|improve this answer



























                10












                10








                10







                The documentation for How Online Index Operations Work says:




                SCH-M (Schema Modification) if any source structure (index or table) is dropped.*



                * The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.




                That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.



                Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).



                Also related from the documentation:




                ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...




                From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.



                In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:




                When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.




                Related links:




                • The Sch-M lock is Evil by Michael J Swart





                share|improve this answer















                The documentation for How Online Index Operations Work says:




                SCH-M (Schema Modification) if any source structure (index or table) is dropped.*



                * The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.




                That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.



                Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).



                Also related from the documentation:




                ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...




                From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.



                In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:




                When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.




                Related links:




                • The Sch-M lock is Evil by Michael J Swart






                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Jan 31 at 15:00

























                answered Jan 31 at 14:19









                Paul WhitePaul White

                52.2k14278451




                52.2k14278451



























                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228561%2fwhy-does-an-index-rebuild-requires-a-sch-m-lock%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown






                    Popular posts from this blog

                    How to check contact read email or not when send email to Individual?

                    Displaying single band from multi-band raster using QGIS

                    How many registers does an x86_64 CPU actually have?