That is an excellent point, Scott.

I'd written off commenting on the code when it seemed like it had been pretty heavily altered for presentation, but that was still worth mentioning.

I...

Thanks for that!

That is indeed interesting, as the priorities are exactly as you claimed, with the surviving spid at 0, and the victim at 10.

I have seen occasional indications...

Do you happen to have the deadlock XML to post?

Based solely on the information you've provided, my best guess is that the processes aren't running with the priorities you think...

To tie in with what Grant said earlier about the cost threshold for parallelism, typically your workload is far more important in determining an appropriate MAXDOP setting than your hardware....

You can use that CPU time, but you have to be careful both to compare it to the amount of time that process has been running and to check it...

That would make sense if it were bad parameter sniffing. All that takes is for the plan to be recompiled with atypical parameters passed in (e.g., the index gets rebuilt...

Sure, the main point is that it expected a lot more data when it cached the plan for the SSMS query.

Just based on this it seems like a normal...

Ah, those are very different plans, compiled for very different inputs.

The one through SSMS was compiled for a 5 year date range and a null CustomerNumber, while the one...

You'll also see multiple rows for the same table/index combination if the table has multiple partitions. You just need to include the columns for index level and partition number so...

I too failed to make the "hardened"-> no AdventureWorks2012 connection and chose 1 and 2.

The correct answers are fine, though. A conditional statement can be true even if its...

Well, it's not pristine, and I wouldn't recommend doing it, but in theory this is possible, yes. You could create 4 workload groups with their own resource pools using a...

Another important thing to note is that the max_cpu_percent very, very rarely actually limits anything to that percentage. In SQL Server 2012 they introduced the additional cap_cpu_percent, which is a...

Another way to approach this is to use the cached query stats to estimate it. This has a similar caveat to most suggestions on this topic, which is that it's...

When the log is growing, check the log_reuse_wait_desc for the msdb database in sys.databases. That should help narrow things down.

If it shows there's an active transaction, you should be...

Have you pulled the deadlock information (e.g., by enabling trace flag 1222)? That should clear up what is going on, as you'll be able to see exactly which locks each...