June 8, 2017 at 7:18 pm
Hi All,
We have a SELECT query which runs for almost 9 hours queing up CXPACKET waittype and it is blocking other DML and SELECT queries.
After getting the lock info for SELECT session_id/SPID, I see SCH_S lock is getting acquired on one of the object/table being referred in
the SELECT statement. Just to mention, the rows fetched by the table is more 5 million rows.
There is another query (truncate table <tname>) which is getting blocked and saw it was requesting for SCH-M lock and since both are
mutually exclusive, I see a long term blocking
Two things I want to know here.
1) Why are SELECT is trying to acquire a SCH-S lock? why not Shared lock?
As per my knowledge SCH-S is a kind of lock that is taken on an object when a process doesn’t want that object to change its definition.
2) Is there any scenarios where 'Shared' lock gets converted into SCh-S lock?
Any demo script illustrating the same will be a great help.
Thanks,
Sam
June 9, 2017 at 3:52 am
vsamantha35 - Thursday, June 8, 2017 7:18 PM1) Why are SELECT is trying to acquire a SCH-S lock? why not Shared lock?
As per my knowledge SCH-S is a kind of lock that is taken on an object when a process doesn’t want that object to change its definition.
Because having the table definition changing while queries are running against that table is going to cause problems. As such, all queries will take a schema stability lock (Sch-S) to prevent the table's definition from changing while they're running
2) Is there any scenarios where 'Shared' lock gets converted into SCh-S lock?
Converted? No. Shared and schema locks are two different things
All queries against a table take an Sch-S lock while they're running, in addition to any shared or exclusive locks they may need
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 4:41 am
vsamantha35 - Thursday, June 8, 2017 7:18 PMHi All,We have a SELECT query which runs for almost 9 hours queing up CXPACKET waittype and it is blocking other DML and SELECT queries.
After getting the lock info for SELECT session_id/SPID, I see SCH_S lock is getting acquired on one of the object/table being referred inthe SELECT statement. Just to mention, the rows fetched by the table is more 5 million rows.
There is another query (truncate table <tname>) which is getting blocked and saw it was requesting for SCH-M lock and since both are
mutually exclusive, I see a long term blocking
Two things I want to know here.
1) Why are SELECT is trying to acquire a SCH-S lock? why not Shared lock?
As per my knowledge SCH-S is a kind of lock that is taken on an object when a process doesn’t want that object to change its definition.
2) Is there any scenarios where 'Shared' lock gets converted into SCh-S lock?Any demo script illustrating the same will be a great help.
Thanks,
Sam
on top of what Gail has said, have you thought about changing your isolation level?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 9, 2017 at 5:52 am
Perry Whittle - Friday, June 9, 2017 4:41 AMon top of what Gail has said, have you thought about changing your isolation level?
Won't help in this case as no matter what the isolation level is, queries will take Sch-S locks and truncate table always needs Sch-M, as it's a schema modification
One thing that might help is using DELETE instead of TRUNCATE, as that just requires X locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 7:59 am
GilaMonster - Friday, June 9, 2017 5:52 AMPerry Whittle - Friday, June 9, 2017 4:41 AMon top of what Gail has said, have you thought about changing your isolation level?Won't help in this case as no matter what the isolation level is, queries will take Sch-S locks and truncate table always needs Sch-M, as it's a schema modification
One thing that might help is using DELETE instead of TRUNCATE, as that just requires X locks.
yes of course, i was thinking more of snapshot isolation for the select queries
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 9, 2017 at 8:02 am
Perry Whittle - Friday, June 9, 2017 7:59 AMGilaMonster - Friday, June 9, 2017 5:52 AMPerry Whittle - Friday, June 9, 2017 4:41 AMon top of what Gail has said, have you thought about changing your isolation level?Won't help in this case as no matter what the isolation level is, queries will take Sch-S locks and truncate table always needs Sch-M, as it's a schema modification
One thing that might help is using DELETE instead of TRUNCATE, as that just requires X locks.
yes of course, i was thinking more of snapshot isolation for the select queries
They're still going to take Sch-S and block the truncate though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 8:04 am
RDMS locking can seem like a game of "Rock, Paper, Scissors". Data Readers (which can include DML operations in addition to SELECT statements) take out both shared locks and schema stability locks. Blocking is perfectly normal and generally not a problem so long as SQL operations complete quickly, but it seems your SELECT is getting stuck for an extended of time in CXPACKET wait state. This specific wait state is caused when a process using parallelism temporarily halt to exchange or merge packets of data between threads. When this takes a long time to complete it can sometimes mean unequal distribution of data between threads, and the root cause of that can perhaps be stale statistics. Try updating table and index stats to see if that improves performance of this problematic SELECT.
Another issue is that, from a workflow perspective, it doesn't sound right for one process to jump in and attempt to TRUNCATE a table while other processes are currently running SELECT queries against it. If this is an ETL process that periodically truncates and re-loads, then consider scheduling off hours, or alternately implement a MERGE or some other form of incremental insert / update processing that does not require a schema modification lock or writing to every page in the table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 9, 2017 at 8:14 am
vsamantha35 - Thursday, June 8, 2017 7:18 PMHi All,We have a SELECT query which runs for almost 9 hours queing up CXPACKET waittype and it is blocking other DML and SELECT queries.
After getting the lock info for SELECT session_id/SPID, I see SCH_S lock is getting acquired on one of the object/table being referred inthe SELECT statement. Just to mention, the rows fetched by the table is more 5 million rows.
There is another query (truncate table <tname>) which is getting blocked and saw it was requesting for SCH-M lock and since both are
mutually exclusive, I see a long term blocking
Two things I want to know here.
1) Why are SELECT is trying to acquire a SCH-S lock? why not Shared lock?
As per my knowledge SCH-S is a kind of lock that is taken on an object when a process doesn’t want that object to change its definition.
2) Is there any scenarios where 'Shared' lock gets converted into SCh-S lock?Any demo script illustrating the same will be a great help.
Thanks,
Sam
You have a SELECT query running for 9 hours and returning 5 million rows? That's unusually slow. Why not post up an estimated execution plan - folks here will almost certainly have some tuning advice for you.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 9, 2017 at 2:33 pm
GilaMonster - Friday, June 9, 2017 3:52 AMvsamantha35 - Thursday, June 8, 2017 7:18 PM1) Why are SELECT is trying to acquire a SCH-S lock? why not Shared lock?
As per my knowledge SCH-S is a kind of lock that is taken on an object when a process doesn’t want that object to change its definition.Because having the table definition changing while queries are running against that table is going to cause problems. As such, all queries will take a schema stability lock (Sch-S) to prevent the table's definition from changing while they're running
2) Is there any scenarios where 'Shared' lock gets converted into SCh-S lock?
Converted? No. Shared and schema locks are two different things
All queries against a table take an Sch-S lock while they're running, in addition to any shared or exclusive locks they may need
Thanks Gail for pitching in.
One thing, still I was not convinced of how the Sch-S locks are being acquired for a SELECT statement in our production env. Attaching headblocker.xml and victim.xml files which contains lock info from production data. PFA prod.zip.
Again, coming back, As you said, if any alterations being done while queries are running against that table, then SELECT should acquire Sch-S locks but it was not happening. correct me if my statement is wrong.
I did tried to repro with a dummy table but I am not seeing Sch-S lock at all for the SELECT. Tried executing SPIDS in below sequence spid 60, next spid 55, next spid 53, finally spid 56.
Below are the repro steps :
use master
go
--drop database demo
--go
create database demo
go
use demo
go
drop table t1
go
create schema prod;
go
create table prod.t1
(c1 int identity,
c2 char (8000) default 'a', --- inserting 8k page
c3 varchar(100) default 'manu'
);
insert into prod.t1 default values
go 131072 -- 1 GB
insert into prod.t1 default values
go 131072 -- 1 GB
---------------
--spid 60
-- opened a new session in SSMS and started monitoting lock info using sp_whoisactive
while ( 1 = 1 )
begin
WAITFOR DELAY '00:00:01';
EXEC master.dbo.sp_WhoIsActive
@show_sleeping_spids= 1,
@get_transaction_info =1,
@get_plans =1,
@get_outer_command =1,
@get_additional_info=1,
@get_task_info=1, ---gets u waitinfo
@get_locks=1,
@find_block_leaders=1,
@sort_order = '[blocked_session_count] DESC'
end
--------------------------------------
--now opened 3 new SSMS windows and executed below queries.
--spid 55
use demo
go
select * from prod.t1
go
--spid 52
use demo
go
alter table prod.t1 add c4 varchar(10) default 'a';
go
--spid 56
use demo
go
truncate table prod.t1
go
--------
June 9, 2017 at 3:07 pm
.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 3:55 pm
Just to clarify, if you're trying to create a demo so you can observe a Sch-S lock being used, queries will not always take one.
They're taken when a query plan is compiled (and during execution when queries don't need anything more restrictive), but during query execution a different lock might held at the object level, and if a plan doesn't have to be compiled for a query (it's already in cache), then a Sch-S lock might not be taken at all.
Any other lock includes the guarantees of a Sch-S lock and then some, so even if executing the query requires only an IS lock at the object level, there's no point to keeping the Sch-S, and SQL Server doesn't.
Most queries will require at least an IS at the object level to execute (SELECTs under optimistic concurrency or NOLOCK/READ UNCOMMITTED are the exceptions, and they'll just take a Sch-S lock for the duration of the read).
This does mean that you can't just run any random query and see a Sch-S lock taken; it is not, however, particularly relevant to your initial problem.
Sch-S is the least restrictive lock mode, only conflicting with Sch-M.
If your SELECT query were taking an IS or S lock at the object level, the query would still conflict with a TRUNCATE taking a Sch-M lock. The Sch-M lock conflicts with every other lock type, so with respect to the TRUNCATE it doesn't really matter what the SELECT takes. They will always conflict.
Cheers!
June 9, 2017 at 4:03 pm
ChrisM@Work - Friday, June 9, 2017 8:14 AMYou have a SELECT query running for 9 hours and returning 5 million rows? That's unusually slow. Why not post up an estimated execution plan - folks here will almost certainly have some tuning advice for you.
Let me second what Chris said. If you tune the query to run faster, you can narrow your window. Please post the DDL, all index definitions, sample data (anonymized) and your query. Any actual execution plans will also probably help.
I have to ask why you're firing a truncate against the table while users are running queries against the data. Is this a staging table? Or does the data really need to be rebuilt frequently? Well, every 9 hours anyway. 😉
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply