Sql-server – Extremely Slow StatMan query issue with #SP2010 farm

performancesharepointsql-server-2008-r2statistics

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

Best Answer

This is SQL Server updating the statistics on a table. It can be triggered automatically when about 20% of the data in your table changes, or on demand if you run jobs that update statistics.

You have a few ways to lessen the pain.

You can enable asynchronous statistics updates, which lets SQL Server update stats behind the scenes without blocking other queries. It'll still have a performance impact, though, as Kendra Little explains in The Secret IO Explosion.

You can manually update statistics proactively during maintenance windows. This doesn't stop the chances that 20% of your data will change, but it just reduces the likelihood.

You can disable automatic updating of statistics altogether. This is generally a pretty bad idea since your execution plans may be wildly incorrect, and it doesn't stop folks from manually updating the statistics either.

Related Question