Contact Us Today!

Minimize SQL Deadlocks for Microsoft Dynamics CRM Customized Solutions

Minimize SQL Deadlocks for Microsoft Dynamics CRM Customized Solutions

Often times an organization with a  customized CRM solution will have an ongoing data integration running, frequently querying against the CRM data.  Sometimes you may experience a sql deadlock, preventing additional queries from running.   When this happens frequently, the following are a few steps that we have taken to minimize sql deadlocks:

• Make sure proper indexes are in place
• Apply database optimization techniques such as regularly updating of statistics

If you have exhausted all resources and you are still running into deadlocks, consider placing NOLOCK’s against the tables within the queries.  A NOLOCK indicates that the query will read uncommitted data and that it will not place shared locks on the tables.  With this, there is a risk of reading dirty data, but if that risk is low for your organization where performance is preferred, this solution is often useful as it will allow other processes to write to the same rows of data when needed:

Select * from Account with (NOLOCK)

Source: http://stackoverflow.com/questions/686724/sql-server-when-should-you-use-with-nolock

With these simple changes, you can minimize the risk of future deadlocks. Contact Beringer today if you have questions or would like assistance in optimizing your sql queries.


theProfessor

theProfessor

Rob is the CTO of Beringer Technology Group, and focuses his efforts on software development, cloud engineering, team mentoring and strategic technical direction. Rob has worked with Beringer since 2005, and has influenced every department from Development, Security, Implementation, Support and Sales. Rob graduated with his MBA from Rowan University in 2012, earned his Bachelors of Computer Science in 1997, and is current with several Microsoft technical certifications. Rob is very active, and loves to mountain bike, weight train, cook and hike with his dog pack.

Leave a comment!

You must be logged in to post a comment.