Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adhoc queries and deadlocks. Expand / Collapse
Author
Message
Posted Thursday, July 17, 2008 1:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
Will converting adhoc queris to stored procedures reduce deadlocks?
Post #535721
Posted Thursday, July 17, 2008 1:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 39,973, Visits: 36,330
Just converting them to stored procs, probably not.

To fix deadlocks, you need to identify the cause of that deadlock and fix that. It's probably bad code, bad indexes or both



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #535724
Posted Thursday, July 17, 2008 2:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:26 AM
Points: 485, Visits: 11,019
I would agree, straight conversion from Query to Stored Proc would not stop dead lock.

Rather get the queries working right then convert them to a stored procedure.
Post #535744
Posted Thursday, July 17, 2008 2:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:36 PM
Points: 568, Visits: 517
yes this will not stop deadlocks on its own there are a number of things you must do, look at the table and how they are set-up the code you can commit trans before starting a new one this will also help. there are many ways to reduce dead locks one of the ways is to use row level locking, however, as they will always be a part of life the best way is to identify the underling in application causes and make the appropriate changes to that.
Post #535746
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse