One of our SharePoint Web Application is running extremely slow. It often takes about 2 to 3 minutes to load one single page. All other web applications are running as fast as they should be.
Called DBA and DBA reported there are blockings in the content database. It comes and goes.
Looked into SharePoint Logs and here is what we found:
System.Data.SqlClient.SqlException: Transaction (Process ID 212) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
PortalSiteMapProvider was unable to fetch children for node at URL: /subsite, message: An unexpected error occured while manipulating the navigational structure of this Web.
It looks like something wrong with this subsite’s navigations.
Went to navigation settings on the root level of the site: http://sharepoint_site/_layouts/AreaNavigationSettings.aspx and removed /subsite from the global navigation. Click OK button and the site is as fast as before.
Except the /subsite. Tried to open http://sharepoint_site/subsite/_layouts/AreaNavigationSettings.aspx but it is almost impossible to get the page load.
Opened the subsite using SharePoint designer, navigate into Pages library. Removed all recently modified publishing pages. Went back to the browser to test the site, and it was gooooooooooooood.
Restore the page one by one, surprisely the issue didn’t occur.
I believe somehow there is a stalled update in the navigations. It never finished and always tried to update the database. By resetting the navigation, it cleared the issue out.