SharePoint site are slow intermittently: SharePoint 2010.

Issue: We have been experiencing intermittent slowness on the SharePoint Farm.

Background: We are getting intermittent partial outage on the SharePoint Farm. We have assigned dedicated set of WFE’s for different set of web applications and all of sudden without any alert or alarming, user started reporting that SharePoint is taking long time to load or its keep on clocking. Lead time for the sites is min 3-5 min.

Troubleshooting:

We have checked if this issue is occurring for an individual user. It was happening for the large number of groups and people and not region specific.

We have checked on the specific server and sites are slow on the web servers as well.

We have checked on the SAAS, SCOM Alerts and Site Scope system from HP, however, no alert was triggered.

No blocking reported on the SQL server.

From server Event logs we only see an error message that “Cannot connect to SQL Server .”

Temporary Resolution: As a temporary resolution we had recycled the app pool on the webserver running that applications and it worked fine. However, I was not sure that there would be a huge service destruction coming in the next coming day.

You know what; this issue started occurring three to four times a day. And recycling app pool was not the correct choice always.

Permanent Resolution: So one day we have decided to involve the SQL Team as we see from the Event Logs that we get an Error message “Cannot connect to SQL ”.

Based on the MS analysis, we saw there were so many page latches on the TEMP DB by running the sys.dm_os_latch_stats and see what type of latches have increased contention and wait types, compared to previous base-line.

MS recommended splitting the 20 GB of Temp DB in smaller chunks. We divided Temp DB in 5 GB in size with 4 Databases on each SQL Server we have in the farm.

KB Article which support this resolution: http://support.microsoft.com/kb/307487.

Reference:

Diagnosing and Resolving Latch Contention on SQL Server: http://www.microsoft.com/en-in/download/details.aspx?id=26665

What is the temp Database: http://msdn.microsoft.com/en-us/library/ms190768.aspx

http://stackoverflow.com/questions/15131021/sql-server-2005-investigate-what-caused-tempdb-to-grow-huge.

If you have any queries/questions regarding the above mentioned information then please let me know.

I would be more than happy to help you as well as resolves your issues, thank you.

Applies to:  SharePoint Foundation Server 2010, SharePoint 2010, SQL Server 2005 and SQL Server 2008.