I am just not sure. I'm not seeing indications for why it's going wrong. Double check that the constraint has the WITH CHECK option in 2014. Other than that... I...

That doesn't mean it will lock the entire table (unless you're deleting a pretty substantial portion of it, but that's part of why you break it into smaller batches). It...

Breaking it into pieces and making sure that the delete statements are using indexes instead of scanning, those are really your choices if you can't partition the data.

If you don't have any sort of auditing set up on your server, you won't be able to see who deleted data. You can set up Audit within SQL Server....

Wish I could come over and play with this. The check constraint should allow the optimizer to eliminate servers from the choice. The fact that security might have changed the...

Alan.B (3/26/2015)

---

I suspect he meant...

Piling on.

Triggers are not bad. They just have a hidden behavior that, when they're written poorly, can lead to lots of issues and long troubleshooting sessions. Use appropriately, they're fine.

Now,...

Yep. 100% by design. Isn't the optimizer the coolest thing ever.

Drenlin (3/26/2015)

---

Phil Parkin (3/26/2015)That's exactly what the compatibility level is for, though it does come with downsides: all of the new features in 2014 are turned off for the databases...

Yet Another DBA (3/26/2015)

---

If the database fits in memory and the load is adequate, where locks and page splitting is not an issue, then GUIDs is not necessarily a practical...

I agree with everything that Gail said.

Well, except that you ought to be using extended events instead of trace.

But yeah, you may be hitting waits, CXPacket waits are not, all...

The index DMV, sys.dm_db_index_physical_stats, could cause blocking if you're asking for a detailed report. It's very intrusive.

I haven't seen or or heard about other DMVs causing blocking, yet.

I'd really focus on the execution plan. I suspect the MAX itself isn't causing issues but rather the GROUP BY.

One way around it would be to try getting a TOP...

I would try running the Upgrade Advisor against the database. I'm not 100% sure you can, but that will tell you specifically what won't work. You can also try to...

Assuming you have a logical or natural primary key, I'd look to the MERGE statement as an easy, although not necessarily fastest, way to get this done. That assumes you...