Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

blocking LCK_M_SCH_S, LCK_M_SCH_M Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 2:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 23, Visits: 413
So How does it work, its the isolation level i need to change.
Post #1405014
Posted Wednesday, January 9, 2013 2:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1405015
Posted Wednesday, January 9, 2013 2:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 23, Visits: 413
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.
Post #1405016
Posted Wednesday, January 9, 2013 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1405023
Posted Thursday, January 10, 2013 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 23, Visits: 413
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.
Post #1405393
Posted Thursday, January 10, 2013 7:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1405415
Posted Thursday, January 10, 2013 7:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 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 #1405472
Posted Thursday, January 10, 2013 8:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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%


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1405495
Posted Thursday, January 10, 2013 1:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 23, Visits: 413
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.
Post #1405620
Posted Thursday, January 10, 2013 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1405621
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse