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.