As I said, I was connected to the 2014 instance for all of these tests.

Don't shrink the log. It's not going to help. All you're doing there is forcing the log to grow again.

What are the settings of the log backup in the maint...

Jacob Wilkins (7/18/2015)

---

So, just to double check, when you run the second INSERT after the TRUNCATE (all within the same transaction) in 2012 SP2, do you see the 6200ish page...

Jacob Wilkins (7/18/2015)

---

1) Both INSERTs in a single transaction, separated by a TRUNCATE TABLE x:

First INSERT escalates successfully, second INSERT triggers escalation but takes out page locks anyway. This...

Ah, so all 8 get called each time, in sequence, every time? That wasn't clear.

I suspect you may be falling into this trap a little: http://www.sqlservercentral.com/articles/Performance+Tuning/115825/

Are these queries of Stories...

Hehe, I think (at least for 2012 2014 and the insert after truncate case), the answer here is that SQL's a hell of a lot sneakier than we thought.

The second...

No. Primary keys are part of...

If you're indexing is correct, it shouldn't be scanning.

Copying to a table variable each time each procedure is run and then filtering the table var won't reduce the number of...

I've seen a bug 'closed as fixed' still present in...

2 and 2 on 2012 2014 RTM, with two lock escalation events fired.

First question is why are you trying to reduce the number of procedures? In SQL, generalising code often ends up degrading performance. What are you specifically trying to achieve here?

Jacob Wilkins (7/18/2015)

---

I didn't say it was blocked; I said lock escalation failed, because it seems the engine can't escalate to X from Sch-M (because Sch-M is a more protective...

Jacob Wilkins (7/17/2015)

---

Since this is all within a transaction, that Sch-M lock is held for the duration of the transaction, and when lock escalation is triggered by the second insert,...

Not necessarily. Updating the stats would have invalidated all plans, so if it was a bad plan, the stats...

Vaguely, yes. You really should take more direct metrics.