blocking LCK_M_SCH_S, LCK_M_SCH_M

Question

blocking LCK_M_SCH_S, LCK_M_SCH_M

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

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 SSC Guru Points: 182276 More actions · Answer 3

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

rocks SSC Eights! Points: 988 More actions · Answer 4

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 SSC Guru Points: 182276 More actions · Answer 5

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

mauriciorpp Default port Points: 1472 More actions · Answer 6

opc.three (1/10/2013)
"Mostly Online" feels like ~60%. I'd submit "Near Online", it feels closer to 99% 😀

until today, all my "online" index rebuilds worked as expected. but just today I got a series of transactions running and SQL is not able to get the LCK_M_SCH_M and end the rebuild.

so, for me, today it feels as "not exactly online index rebuild". I would put it at 10%. :angry:

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

mauriciorpp (2/12/2014)
opc.three (1/10/2013)
"Mostly Online" feels like ~60%. I'd submit "Near Online", it feels closer to 99% 😀
until today, all my "online" index rebuilds worked as expected. but just today I got a series of transactions running and SQL is not able to get the LCK_M_SCH_M and end the rebuild.
so, for me, today it feels as "not exactly online index rebuild". I would put it at 10%. :angry:

If you had 1 failure out of 10 tries equating to 10% then by that accounting you can reasonably look forward to 90 error-free online rebuilds before your next failure...on average 😀

Seriously though, the 99% was not meant to be thought of as a success-rate. If you have a very busy system, even though the rebuild only asks for short-lived stability-type locks at the beginning and end of the rebuild, i.e. the rebuild displays properties of an online process during 99% of its operations and only displays offline properties for 1% of its operations, you can still run into problems during that 1% on a very regular basis on a busy system.

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

mauriciorpp Default port Points: 1472 More actions · Answer 8

you are correct, @opc.three. I just wanted to point out that even the unexpected should be expected at some point in time, given a large enough installed base.

I had to schedule this index rebuild, it took almost an hour to be able to hold a schema lock for 1 second (the table is small, around 25mb), but it worked after all!