Online index operation blocking inserts
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:
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
edited Apr 26 '17 at 7:51
Marco
3,68731524
asked Apr 26 '17 at 6:21
jesijesijesijesi
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.
add a comment |
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:
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
edited Apr 26 '17 at 7:51
Marco
3,68731524
asked Apr 26 '17 at 6:21
jesijesijesijesi
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.
add a comment |
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:
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
edited Apr 26 '17 at 7:51
Marco
3,68731524
asked Apr 26 '17 at 6:21
jesijesijesijesi
878419
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:
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
sql-server sql-server-2014 nonclustered-index
edited Apr 26 '17 at 7:51
Marco
3,68731524
asked Apr 26 '17 at 6:21
jesijesijesijesi
878419
edited Apr 26 '17 at 7:51
Marco
3,68731524
asked Apr 26 '17 at 6:21
jesijesijesijesi
878419
edited Apr 26 '17 at 7:51
Marco
3,68731524
edited Apr 26 '17 at 7:51
Marco
3,68731524
edited Apr 26 '17 at 7:51
Marco
3,68731524
3,68731524
asked Apr 26 '17 at 6:21
jesijesijesijesi
878419
asked Apr 26 '17 at 6:21
jesijesijesijesi
878419
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.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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:
answered Apr 26 '17 at 7:04
George KGeorge K
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
|
show 2 more comments
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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:
answered Apr 26 '17 at 7:04
George KGeorge K
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
|
show 2 more comments
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:
answered Apr 26 '17 at 7:04
George KGeorge K
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
|
show 2 more comments
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:
answered Apr 26 '17 at 7:04
George KGeorge K
1,224520
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:
answered Apr 26 '17 at 7:04
George KGeorge K
1,224520
answered Apr 26 '17 at 7:04
George KGeorge K
1,224520
answered Apr 26 '17 at 7:04
George KGeorge K
1,224520
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
|
show 2 more comments
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
|
show 2 more comments
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
