Why does an index rebuild requires a Sch-M lock?
Clash Royale CLAN TAG#URR8PPP
I don't see why an index rebuild would require a Sch-M lock on the given index.
sql-server database-internals index-maintenance
add a comment |
I don't see why an index rebuild would require a Sch-M lock on the given index.
sql-server database-internals index-maintenance
add a comment |
I don't see why an index rebuild would require a Sch-M lock on the given index.
sql-server database-internals index-maintenance
I don't see why an index rebuild would require a Sch-M lock on the given index.
sql-server database-internals index-maintenance
sql-server database-internals index-maintenance
edited Jan 31 at 14:21
Paul White♦
52.2k14278451
52.2k14278451
asked Jan 31 at 13:45
KimoKimo
254
254
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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
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
add a comment |
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
add a comment |
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%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
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
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
add a comment |
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
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
add a comment |
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
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
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
add a comment |
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
add a comment |
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
add a comment |
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
add a comment |
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
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
edited Jan 31 at 15:00
answered Jan 31 at 14:19
Paul White♦Paul White
52.2k14278451
52.2k14278451
add a comment |
add a comment |
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%2f228561%2fwhy-does-an-index-rebuild-requires-a-sch-m-lock%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