<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
blocking LCK_M_SCH_S, LCK_M_SCH_M
20 posts, Page 1 of 2
1
2
»»
blocking LCK_M_SCH_S, LCK_M_SCH_M
Rate Topic
Display Mode
Topic Options
Author
Message
rocks
rocks
Posted Tuesday, January 08, 2013 9:32 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 7:08 AM
Points: 16,
Visits: 273
Hi Guru's,
I am trying to under stand about no lock hint with a select statement.
today when i was checking for locking / blocking history.
when i check alter index with online = on, which was type LCK_M_SCH_M, was getting blocked by a select statement with no lock hint.
and alter index was blocking select statement, which has type LCK_M_SCH_S.
how can i avoid this type of blocking / locking. attaching the file which show blocking hierarchy.
in attached screen shot process id 234 is blocking 85, 53, 55, 68 etc and 85 is blocking 51 and 92 etc.
How can i avoid blocking in this situation.
Any help will be appreciated.
Post Attachments
Blocking.PNG
(
6 views,
79.97 KB
)
Post #1404340
GilaMonster
GilaMonster
Posted Tuesday, January 08, 2013 9:51 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 38,118,
Visits: 30,402
You can't. Sch-M is a schema modification lock. Blocks everything and is blocked by absolutely anything, including the Sch-S locks that all queries have to take. Online index rebuilds only require short-lived Sch-M locks, so shouldn't be a major issue.
Oh, and you might want to ditch those nolock hints.... See -
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1404355
rocks
rocks
Posted Tuesday, January 08, 2013 10:18 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 7:08 AM
Points: 16,
Visits: 273
HI,
Thanks for quick reply, I have gone through the post. so what is the best way to avoid this locks. we are using nolock hint as we can have dirty read.
as this was on going problem in our production server. if i use offline indexes it will block even more.
GilaMonster (1/8/2013)
You can't. Sch-M is a schema modification lock. Blocks everything and is blocked by absolutely anything, including the Sch-S locks that all queries have to take. Online index rebuilds only require short-lived Sch-M locks, so shouldn't be a major issue.
Oh, and you might want to ditch those nolock hints.... See -
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Post #1404365
GilaMonster
GilaMonster
Posted Tuesday, January 08, 2013 10:58 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 38,118,
Visits: 30,402
rocks (1/8/2013)
HI,
Thanks for quick reply, I have gone through the post. so what is the best way to avoid this locks. we are using nolock hint as we can have dirty read.
You cannot avoid schema locks. All queries absolutely must in every case take schema stability locks. An index rebuild, even an online one, needs a schema mod lock (very short lived for an online rebuild) and a schema modification lock is blocked by any other lock that exists and will in turn block any other lock request.
Dirty reads are allowed, so the users are absolutely fine with their reports potentially missing chunks of data, potentially reading sets of rows twice or more? They fully understand and accept that any results they see are potentially incorrect?
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1404386
rocks
rocks
Posted Tuesday, January 08, 2013 11:27 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 7:08 AM
Points: 16,
Visits: 273
yes users understand these type of dirty read might happen.
Post #1404400
opc.three
opc.three
Posted Wednesday, January 09, 2013 1:20 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826,
Visits: 11,951
rocks (1/8/2013)
yes users understand these type of dirty read might happen.
I doubt they fully understand what dirty read really means otherwise they would not be alright with it. It means their reports are generally useless. If you care about the data you would be best to look into
enabling READ_COMMITTED_SNAPSHOT
in the database. The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads. The downside is that it can add some workload onto tempdb, but typically it is an easy tradeoff and unless your system is tempdb-constrained at the moment one that usually has no overall adverse effects.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1404972
GilaMonster
GilaMonster
Posted Wednesday, January 09, 2013 1:46 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 38,118,
Visits: 30,402
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.
Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1404993
opc.three
opc.three
Posted Wednesday, January 09, 2013 1:55 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826,
Visits: 11,951
GilaMonster (1/9/2013)
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.
Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.
That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1404997
GilaMonster
GilaMonster
Posted Wednesday, January 09, 2013 2:00 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 38,118,
Visits: 30,402
opc.three (1/9/2013)
GilaMonster (1/9/2013)
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.
Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.
That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.
Well, it's kinda honoured. Technically read committed and read committed snapshot aren't two different isolation levels, they're one isolation level with two possible methods of being enforced, and that method is chosen with an ALTER DATABASE statement
If you have read committed snapshot on and really want the old locking behaviour (and there's some good reasons to do so), then you can only do that with a hint: WITH (READCOMMITTEDLOCK)
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1404999
opc.three
opc.three
Posted Wednesday, January 09, 2013 2:03 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826,
Visits: 11,951
GilaMonster (1/9/2013)
opc.three (1/9/2013)
GilaMonster (1/9/2013)
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.
Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.
That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.
Well, it's kinda honoured. Technically read committed and read committed snapshot aren't two different isolation levels, they're one isolation level with two possible methods of being enforced, and that method is chosen with an ALTER DATABASE statement
Yep. Two "modes" of one isolation level is how I think about them. And SNAPSHOT is another animal completely, its own isolation level unto itself.
If you have read committed snapshot on and really want the old locking behaviour (and there's some good reasons to do so), then you can only do that with a hint: WITH (READCOMMITTEDLOCK)
That's a new one to me. I will have to look into it. Thanks.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1405002
« Prev Topic
|
Next Topic »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
