Online index operation blocking inserts












3















I am trying to create a non clustered index on a table which is around 500GB. The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=ON, however at the beginning of the index build it blocked other OLTP transactions.



When I checked what locks were being held I noticed exclusive table lock on the table and other transactions are getting blocked by this.



Screen shot:



enter image description here



I look at Guidelines for Online Index Operations for exclusions and its not applicable for this table.



Any idea why this is happening?










share|improve this question
















bumped to the homepage by Community 14 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.




















    3















    I am trying to create a non clustered index on a table which is around 500GB. The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=ON, however at the beginning of the index build it blocked other OLTP transactions.



    When I checked what locks were being held I noticed exclusive table lock on the table and other transactions are getting blocked by this.



    Screen shot:



    enter image description here



    I look at Guidelines for Online Index Operations for exclusions and its not applicable for this table.



    Any idea why this is happening?










    share|improve this question
















    bumped to the homepage by Community 14 mins ago


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      3












      3








      3


      1






      I am trying to create a non clustered index on a table which is around 500GB. The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=ON, however at the beginning of the index build it blocked other OLTP transactions.



      When I checked what locks were being held I noticed exclusive table lock on the table and other transactions are getting blocked by this.



      Screen shot:



      enter image description here



      I look at Guidelines for Online Index Operations for exclusions and its not applicable for this table.



      Any idea why this is happening?










      share|improve this question
















      I am trying to create a non clustered index on a table which is around 500GB. The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=ON, however at the beginning of the index build it blocked other OLTP transactions.



      When I checked what locks were being held I noticed exclusive table lock on the table and other transactions are getting blocked by this.



      Screen shot:



      enter image description here



      I look at Guidelines for Online Index Operations for exclusions and its not applicable for this table.



      Any idea why this is happening?







      sql-server sql-server-2014 nonclustered-index






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Apr 26 '17 at 7:51









      Marco

      3,68731524




      3,68731524










      asked Apr 26 '17 at 6:21









      jesijesijesijesi

      878419




      878419





      bumped to the homepage by Community 14 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community 14 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          1 Answer
          1






          active

          oldest

          votes


















          0














          Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.



          Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.



          And the article you provided has some info on this as well:
          enter image description here






          share|improve this answer
























          • Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.

            – jesijesi
            Apr 26 '17 at 7:06











          • In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure

            – George K
            Apr 26 '17 at 7:11











          • ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.

            – jesijesi
            Apr 26 '17 at 9:00











          • there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage

            – George K
            Apr 26 '17 at 10:57











          • @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?

            – RDFozz
            Apr 26 '17 at 15:45











          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%2f171999%2fonline-index-operation-blocking-inserts%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          0














          Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.



          Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.



          And the article you provided has some info on this as well:
          enter image description here






          share|improve this answer
























          • Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.

            – jesijesi
            Apr 26 '17 at 7:06











          • In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure

            – George K
            Apr 26 '17 at 7:11











          • ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.

            – jesijesi
            Apr 26 '17 at 9:00











          • there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage

            – George K
            Apr 26 '17 at 10:57











          • @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?

            – RDFozz
            Apr 26 '17 at 15:45
















          0














          Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.



          Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.



          And the article you provided has some info on this as well:
          enter image description here






          share|improve this answer
























          • Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.

            – jesijesi
            Apr 26 '17 at 7:06











          • In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure

            – George K
            Apr 26 '17 at 7:11











          • ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.

            – jesijesi
            Apr 26 '17 at 9:00











          • there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage

            – George K
            Apr 26 '17 at 10:57











          • @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?

            – RDFozz
            Apr 26 '17 at 15:45














          0












          0








          0







          Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.



          Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.



          And the article you provided has some info on this as well:
          enter image description here






          share|improve this answer













          Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.



          Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.



          And the article you provided has some info on this as well:
          enter image description here







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Apr 26 '17 at 7:04









          George KGeorge K

          1,224520




          1,224520













          • Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.

            – jesijesi
            Apr 26 '17 at 7:06











          • In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure

            – George K
            Apr 26 '17 at 7:11











          • ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.

            – jesijesi
            Apr 26 '17 at 9:00











          • there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage

            – George K
            Apr 26 '17 at 10:57











          • @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?

            – RDFozz
            Apr 26 '17 at 15:45



















          • Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.

            – jesijesi
            Apr 26 '17 at 7:06











          • In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure

            – George K
            Apr 26 '17 at 7:11











          • ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.

            – jesijesi
            Apr 26 '17 at 9:00











          • there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage

            – George K
            Apr 26 '17 at 10:57











          • @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?

            – RDFozz
            Apr 26 '17 at 15:45

















          Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.

          – jesijesi
          Apr 26 '17 at 7:06





          Thanks for the reply, I read that, but as we can see from the screenshot, its an exclusive lock and also the locking happened at the beginning of the index creation.

          – jesijesi
          Apr 26 '17 at 7:06













          In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure

          – George K
          Apr 26 '17 at 7:11





          In an online rebuild, the X locks you are seeing are on the new index structure being built, not the current index structure

          – George K
          Apr 26 '17 at 7:11













          ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.

          – jesijesi
          Apr 26 '17 at 9:00





          ok, but then it wouldn't explain the blocking which I am seeing at the starting the index creation.

          – jesijesi
          Apr 26 '17 at 9:00













          there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage

          – George K
          Apr 26 '17 at 10:57





          there is also a lock at the beginning of rebuild. So if you hace any data modification activity it is likely to see blicking at this stage

          – George K
          Apr 26 '17 at 10:57













          @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?

          – RDFozz
          Apr 26 '17 at 15:45





          @SmokingDBA - Note that, since this is the creation of a new index, there's no existing index; any chance that would result in different locking activity than rebuild. I know the rebuild is functionally creating a new index, but could this be part of the issue?

          – RDFozz
          Apr 26 '17 at 15:45


















          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%2f171999%2fonline-index-operation-blocking-inserts%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

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh