This was by far the most complex and vexing issue I have encountered in SharePoint. A little background on this first.
Everyday users would get the following errors BUT ONLY IN THE MORNING!!!:
This by itself seems quite strange to begin with, so i started down the familiar troubleshooting SharePoint path, starting with event viewer, then uls, then fiddler, then pulling out my hair, then calling Microsoft and then pulling out my hair some more.
I tried modifying the web config files for max upload time and ASP timeouts with no luck.
I looked at Load balancing settings and that yielded a dead end.
One promising direction i started going down was investigating the famed warm up script. This particular direction seemed fruitful. I wasn’t exactly sure that the warm up script was warming anything up. I asked one of my colleagues (sp dev, and a talented one) to help me create an automatic document upload script. Why an automatic upload script you ask? because after the first couple of users uploaded a document to SharePoint in the morning, the rest of the day it worked fine. So we created this band aid work around till we could figure out why it was happening.
So band aid in place we continued testing with fiddler and we found this little nugget of stress:
Tuesday, October 22, 2013 12:28:00 AM Start Upload http://prodservers/DatabaseWarm-upFile.docx
Tuesday, October 22, 2013 12:29:41 AM Upload Complete. Duration = 100.7616918
Interesting. All uploads at 12 am where taking over at 100 seconds. I looked for timer jobs that kicked off at midnight and i found quite a few, but they were default SharePoint timer jobs. I didn’t think messing with those was a road we should go down. However, looking at performance data we could NOT ignore this particular spike in IO. My dev colleague identified [dbo].[proc_UpdateStatistics as the culprit. This is the stored procedure that was causing the issue. There is an actual health analyzer rule that kicks off this timer job.
I had never heard about this before so i took to the net to see if anyone had this problem.
Thanks to Chun Liu’s blog we found this:
What? I say, what? How have i been working with SharePoint this long and never heard of this at all?
Needless to say, the fix that worked for us to was to set auto create statistics to false on all content databases.
Be warned that this should only be done in content databases and not search ones.
At midnight upload files went fro 100 seconds to 2 seconds.
This took me 3 months to diagnose and fix.
Enjoy this golden nugget! Learn from my fail.