Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


blocking LCK_M_SCH_S, LCK_M_SCH_M


blocking LCK_M_SCH_S, LCK_M_SCH_M

Author
Message
rocks
rocks
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 461
So How does it work, its the isolation level i need to change.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
You would enable READ_COMMITTED_SNAPSHOT on the database and remove all NOLOCK hints from your queries. See the article I linked to in my initial post on how to enable the option.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
rocks
rocks
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 461
So i need to change all my SP's and Views to avoid nolock hint.
so in this snapshot isolation level no locking happens at all for reporting scripts ( for read only scripts ) how about SP's which runs under transaction.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
rocks (1/9/2013)
So i need to change all my SP's and Views to avoid nolock hint.

By all means, absolutely. If the use of NOLOCK is that pervasive then consider this your opportunity to start living right :-)

so in this snapshot isolation level no locking happens at all for reporting scripts ( for read only scripts ) how about SP's which runs under transaction.

Locking still occurs such that writers block writers, but in this mode readers are not blocked by writers which is the problem the developers were likely trying to solve by adding all the NOLOCK hints.

Have a read through that article and if you have more questions post back.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
rocks
rocks
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 461
so In Snapshot , even i SP runs under transactions to read write, reporting SP's which are select script only will not get affected. how about Indexes, if i rebuild indexes online will it block as well or not.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
rocks (1/10/2013)
so In Snapshot , even i SP runs under transactions to read write, reporting SP's which are select script only will not get affected.

Correct. Writers do not block readers under read committed snapshot.

how about Indexes, if i rebuild indexes online will it block as well or not.

Different animal. Online index rebuilds do not block anything regardless of the isolation level. Nothing would change for you here if you enabled read committed snapshot.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47193 Visits: 44361
opc.three (1/10/2013)
. Online index rebuilds do not block anything regardless of the isolation level.


Kinda...

Online index rebuilds are mostly online. They take a short-lived IX lock at the beginning (which blocks writers) and a short-lived Sch-M lock at the end (which blocks everything)

I guess "Mostly Online Index Rebuilds" wasn't acceptable to marketting


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
GilaMonster (1/10/2013)
opc.three (1/10/2013)
. Online index rebuilds do not block anything regardless of the isolation level.


Kinda...

Online index rebuilds are mostly online. They take a short-lived IX lock at the beginning (which blocks writers) and a short-lived Sch-M lock at the end (which blocks everything)

I guess "Mostly Online Index Rebuilds" wasn't acceptable to marketting

I know about those locks but took the question to mean "blocking during the rebuild." The locks are very short lived and are an even trade between isolation levels. They effectively pause necessary operations on the table to ensure stability at the initiation of the index rebuild and at the end when the new index is swapped into the old indexes place.

"Mostly Online" feels like ~60%. I'd submit "Near Online", it feels closer to 99% :-D

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
rocks
rocks
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 461
i was going through the document you asked me to go through and i found below.

"In systems that have a high percentage of data changing, enabling the use of the new snapshot isolation level may have a negative affect on overall performance, because the overhead of creating and managing the previous versions of a row can slow down transactions, particularly when tempdb or the disk subsystem is already close to being a system bottleneck. In this situation, the performance cost of enabling the new infrastructure may not be worth the value of reporting against the real-time data, especially because any reporting will likely add even more load to an already busy system."

so how do i determine what is best for me. as mine is heavy data changing application in terms of insert , delete and update.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
rocks (1/10/2013)
so how do i determine what is best for me. as mine is heavy data changing application in terms of insert , delete and update.

Test on representative hardware with a representative workload.

As I mentioned before, if your tempdb subsystem is close a tipping point you could have issues but in general SQL Server handles the additional activity quite well and with minor performance impact, usually unnoticeable.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search