January 8, 2013 at 10:53 am
Awesome.... You are right the problem with two (Not IN) If i remove both logic SP Works fine with No problem. The Problem with One Not IN i think that one is taking to long. Thank you all of you guys to help me to solve this issue.
Thank You....
January 8, 2013 at 10:55 am
rocky_498 (1/8/2013)
I am Refreshing this Database from Production to Test (Everything is same except One day old data)
So test has exactly the same load, exactly the same queries executing as production? I highly doubt that.
Deadlocks occur when two pieces of executing code want resources that the other one owns. If your stuff deadlocks on prod and runs fine on test, then that's because the queries that are executing on production are not executing on test.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2013 at 11:33 am
Good Question...
I am dam sure about this ALL SPS/INDEX/VIEW/TRIGG/ and so on are same (Prod/Test)
If i ran Same SP without any changes on Prod giving me Error and On test No Problem.
I change or comment out one Not Null Syntax on Production and its working fine....
To be honest i have no clue what's going on 🙂
January 8, 2013 at 12:00 pm
rocky_498 (1/8/2013)
I am dam sure about this ALL SPS/INDEX/VIEW/TRIGG/ and so on are same (Prod/Test)
I am not talking about the *definition* of the queries. To get a deadlock on two different servers, they have to have exactly the same queries *running* at the same time. That means that unless Test has as many connections as the production server does, running exactly the same queries, exactly the same data columns and activity, you won't see the deadlock on Test.
Deadlock require 2 or more queries running simultaneously, not just for the same pieces of code to be sitting in the database.
If i ran Same SP without any changes on Prod giving me Error and On test No Problem.
Because on Test, the procedures that it is deadlocking with are not executing at the same time (which they are on production)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2013 at 12:08 pm
GilaMonster is exactly right - removing the NOT INs is not a long term resolution as the deadlocks will still occur, just not as frequently. Expressing them as left joins though is a MUCH faster solution which will minimize the deadlocks. It won't get rid of them completely though.
January 8, 2013 at 12:13 pm
Just FYI
AND PM.[MId] NOT IN (SELECT MId FROM [tblContact] WHERE [Isdeleted] = 0
is equivalent to
--other joins not shown
LEFT JOIN [tblContact] tc ON tc.MId = PM.[pMId] and [Isdeleted] = 0
--this get added to WHERE clause
AND tc.MId IS NULL
January 8, 2013 at 12:28 pm
lnardozi 61862 (1/8/2013)
Expressing them as left joins though is a MUCH faster solution which will minimize the deadlocks. It won't get rid of them completely though.
Actually not. Left joins are slightly slower than NOT IN (assuming all columns involved are defined not null). If there are nulls or nullable columns involved, then NOT EXISTS is the recommended method, it's faster than joins, though not by that much.
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply