Hi opc.three,

Thanks for the feedback so far. Really appreciate your time.

Actual execution plan of a currently running query is what I'm looking to get, I'll take a look at...

Thanks, will check it out.

Bit of a breakthrough on this I thought I'd share....

There is an undocumented flag, -T1229 which turns off Lock Partitioning on SQL Servers with >= 16 CPU.

Once I set...

Ok, could the different be number of CPUs? DOP is set the same on both environments, but one environment has 24 CPUs and the other only has 8 CPU.

Having...

nevermind... got my answer:

http://aboutsqlserver.com/2012/04/05/locking-in-microsoft-sql-server-part-13-schema-locks/

schema is exactly the same, database settings are the same definitely for IL (default is READ_COMMITTED), and we're trying to weed through all other possible settings to see anything that...

Thanks Gail,

Appreciate all the insights provided so far.

I'm still trying to understand what the variable could be to explain the difference in behavior in the 2 environments we have....

Yes, that's the one.

The SQL is application-generated, but it's from a reporting tool that does not do any DML, only reads from star-schema data models.

Still, I'll try and dig deeper...

Thanks!!!

Here's the trace. Check out the last deadlock between SPID 113 and 20

Ooops... just realized that you can't uploaded images directly from laptop to this forum.. I don't have the images on a public server, but basically graphs are described in the...

Hi,

ALZDBA: Thanks for your great suggestions and being so willing to help and even offering to look at log files.. much appreciated

Jason: That is one bizarre story wrt the cluster...

Hi Jason,

Thanks for the additional suggestions.

I can rule out the first one, as the swap files are a fixed size (initial size and maximum size are set equal).

The...

Hi Alzdba,

The question of why we have the memory configured this way is a valid one, which I do not know the answer to.

All I can say is that it...

Grant:

Thanks for your feedback. I still have a feeling we are hitting some bug, and I'm tempted to raise an SR with microsoft. Even with the poor execution plan, I...

Ok, thanks everyone for the excellent advice and suggestions.

I have finally found the variable between the 2 environments that triggers this problem. However, it's counter-intuitive to me, so if anyone...