First published on TECHNET on Feb 06, 2014
Hello All,
Since I seem to be discussing this all the time, I thought I would put it in writing for everybody. When dealing with SharePoint and Deadlocks there is little we can do to directly change the behavior since we are very limited in what can be done to the databases, the Product Group does work to eliminate as many deadlock scenarios as possible but they expect to have deadlocks still due to the nature of how the databases are designed.
In this article I have focused on SharePoint 2010, but I would apply the same rules to any version of SharePoint.
So what can you do to eliminate as many deadlocks as possible? Here are a few things you can do:
Run database maintenance as often as possible, the healthier the database files are the better they will run. (See http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx )
Insure that your SQL servers have the resources they need, monitor the servers to insure they have RAM, CPU and disks all performing well. Again the healthier SQL is the better off SharePoint will be.
Reduce the number of jobs running against your SQL databases, this means minimize crawls, profile imports, even consider looking at timer jobs to see which ones can run less often.
Reduce number of Web Applications, this will reduce the number of timer jobs that have to run.
Do not perform bulk operations against your content databases, educate users to delete in small batches and upload in small batches. Educating users in general is a great idea.
Consider implementing Records Management, this means deleting old data you should not be keeping and archiving data that you need to keep but don’t use.
Follow best practices when it comes to list size, config of views, database size, number of databases, depth of sites and site collections. The size of queries due to views returning large number of results can cause deadlocks.
What symptoms should cause me to be concerned about Deadlocks? Look for the following to occur at the same time as the deadlock:
Opening a list takes a long time
Queries against a list take a long time
Opening a site takes a long time
As you can see the issues will surround around performance issues that your user base can notice.
Articles:
Database maintenance for SharePoint 2010 Products
http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx
Designing large lists and maximizing list performance (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx
Estimate performance and capacity requirements for large scale document repositories in SharePoint Server 2010
http://technet.microsoft.com/en-us/library/hh395916(v=office.14).aspx
Performance and capacity management (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc262971(v=office.14).aspx
Troubleshooting SQL Server deadlocks and performance when running SharePoint
http://blogs.msdn.com/b/grahamk/archive/2009/06/15/troubleshooting-sql-server-deadlocks-and-perform...
Best practices (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc850682(v=office.14).aspx
SharePoint lists V: Techniques for managing large lists (Online course)
http://office.microsoft.com/en-us/sharepoint-server-help/sharepoint-lists-v-techniques-for-managing...
