You may not immediately notice red flags in your SharePoint environment, but any performance degradation will be readily obvious to users who are waiting and waiting for queries to be returned or sites to open. Typically, such degradation is the result of deadlocks gumming up the database operations.
What Is a Database Deadlock?
“A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources,” explains The Microsoft TechNet Library.
One place these can occur is in Search databases, including the Crawl Store database. These result from “the concurrent and asynchronous nature of the Crawl processing.” Most of these occur when updating the MSSCrawlUrl table while also trying to delete the item from MSSCrawlQueue. When there are many deadlocks that occur in a given day across the database, this is one place to look.
Best Practices To Avoid Deadlocks in SharePoint
Here is an assortment of best practices you should follow in order to avoid deadlocks in SharePoint and increase its performance and your users’ productivity.
Reduce large lists. You should have no more than 2,000 items per list view. Try using filtered views. If this isn’t helpful at first, employ a 2,000 item limit. You may also wish to select a column to index. This should be the column your users are most often using for filters or searches. Be certain you are regularly maintaining these lists. This includes cleaning them up on a routine schedule or else regularly archiving theme.
By extension, you’ll also want to be certain any queries return only a maximum of 2,000 items. This should apply to all types of queries, including those executed by administrators.
Be certain there are no overlapping crawl times. Conducting two crawls at the same time on the same content source can lead to problems. Use “Continuous Crawls” for this purpose rather than “Incremental Crawl,” which is more efficient in keeping search results to the latest.
Allow the system to clean up any Workflow history lists.
Attend to Site Collection issues. Microsoft’s Chris We advises having Site Collections that are “as flat as is reasonable, this means that if a user needs a subsite that they should create it, but we should discourage the creation of multiple nested subsites.” This also includes deleting and archiving any sites and Site Collections data.
Check memory utilization. You want to be certain various SharePoint processes, such as Application Pools, are not using too much memory.
Review all scheduled interactions in SharePoint. Be certain these are occurring as intended rather than creating problems. This includes checking the configuration of SharePoint and SQL as well, since the performance of one can affect the other.
Microsoft offers some information for planning and performance, including the suggested database sizes and architecture.
Maintain your database on a regular basis. We cannot emphasize this enough. Regular maintenance can eliminate or resolve problems before they can morph into issues that can slow or bring your SharePoint environment to a full stop.
If you have followed these and other best practices and suggestions to determine the origins of your slow-running SharePoint and these have not been resolved, there may be other resources you need to explore.
We have identified the 10 leading issues that cause SharePoint to run slowly and suffer from performance problems in our white paper Top 10 Reasons SharePoint is Running Slowly, which you may download here. The information provided is designed to help you troubleshoot your slow system.
You may also wish to contact Datavail for a thorough evaluation of your SharePoint environment. We can diagnose and resolve performance issues as well as help your organization’s network run more effectively and efficiently.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.