Abdul Rahman (12/14/2012)
But after interpreting the output and discussing with the developers, they want to start with adding with(Nolock) to all the queries, detected by -T1222.
Won't help. Look at the deadlock graph, no locks there, the waits are all WaitType=e_waitPortOpen.
Optimise the queries or stick a maxdop hint on the query.
Oh, and nolock can cause far more problems than just dirty reads. Are the devs OK with their queries potentially missing rows? Potentially reading rows twice? Those cause really fun problems when the reports created by queries running with nolock have unreproducible errors in them.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
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