Sunday, June 30, 2019

SharePoint site slow

Using SharePoint 2016 on premise;

Issue: SharePoint sites does not load and stuck on loading.

Error in event viewer of DB server:
SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.


Troubleshooting:

  • Noticed that the CPU is stuck at 99% in SharePoint database server. 
  • Increased  the CPU in database server since the utilization was more.
  • The CPU utilization still fluctuates even after we increased the CPU. 
  • The CPU utilization decreases when we tried killing the SQL Services.
Root Cause:

  • On further analysis, noticed that the database server CPU usage was consumed by search service and crawling activity of a Portal site (1 TB).
  • The incremental search service that was scheduled on weekend did not complete because of the huge database size of  the portal. 
Fix:
  • Stopped the incremental crawl. Stopping the search reduced the CPU consumption is normal and the sites are back
  • The search performance level was set to maximum by default. To avoid maximum CPU consumption by Search service, the performance level value has been set to Reduced.