The issue I’m experiencing is explained in a blog-post I created for this, (easy to refer to and to manage the responses / solutions in 1 location) which can be found here: http://schoennie.blogspot.com/2013/09/slow-statman-query-issue-with-sp2010.html
Short summary is that this single server install of SharePoint 2010 on top of SQL 2008R2 (same server) is experiencing a VERY slow response for every first upload during a certain interval (seems to be every morning). Analysing the SQL activities revealed to me that when you start an upload, this query get’s executed before the “insert part” of the upload:
SELECT StatMan([SC0], [LC0]) FROM (SELECT TOP 100 PERCENT CONVERT(VARBINARY, SUBSTRING ([Content], 1, 100) + +SUBSTRING([Content], CASE WHEN LEN([Content]) <= 200 THEN 101 ELSE LEN([Content]) - 99 END, 100)) AS [SC0], DATALENGTH([Content]) AS [LC0] FROM [dbo].[AllDocStreams] WITH (READUNCOMMITTED) ORDER BY [SC0]) AS _MS_UPDSTATS_TBL
I spent a couple of days now trying to get more info on this StatMan query and why it’s causing the disk i/o to go through the roof and The Disk Queue Length to grow above the 5 value (normally around 0.01)
Please share your thoughts on this or maybe point me in a certain direction / resource ? I’m involved as SharePoint Consultant & DBA in this software space for about 8 years now, but I have not yet seen something like this !
Thanks so much in advance, Jeroen