Share via


SharePoint server pages loading very slow and blocking in Database server , Tempdb full

I recently faced  an issue on my Public facing SharePoint 2010 website wherein due to an advertisement campaign , the number of click requests /page access requests increased dramatically and which led to blocking in SharePoint database on sql server and tempdb reaching its capacity. That slow down the response from database server and slowed down the page response of SharePoint to an extent that at times the page stopped loading and gave error.

This was happening because each page access request or the image/ video access request on SharePoint page was going to database for response .

There was no error message in IIS logs except the information on requested pages which were huge.

In SharePoint logs the only high priority event was the generic sql query for accessing the list was very slow in response but it was not referring to any particular list .

As a resolution we enabled the caching of the pages . The blob cache( in web.config)  and IIS caching for the website were  already enabled . The only thing which was missing was output caching was disabled in sitecollection root site settings .

I enabled the output caching for the site collection with default profile of anonymous users for internet facing sites  and checked the option for subsites to inherit the same profile.

This worked as charm .In couple of minutes ,all the site pages started responding at original /faster speed .

For Static content sites , output caching is must for performance of pages .