7

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

Improve this question
5
  • This looks like an auto-stats update, and it's scanning the content of all the documents you have in SharePoint. Not surprising this is expensive! A quick fix would be to turn on async stats updates, which would unblock the insert process, and do the stats update in the background. Looking at our own SharePoint database (I'm not an expert at that), I don't see any stats on this column, but we don't use the documents stuff extensively (< 2,000 rows in our table). Edit: we're using SP 2007.
    – Jon Seigel
    Commented Oct 10, 2013 at 17:01
  • Thanks for your thoughts Jon ! The issue with the stats on SharePoint (2010) is that there is a timer-job that is responsible for creating the stats. This timer-job is ran by SharePoint and therefore by default the SQL Create Auto Statistics is disabled. Microsoft recommends not to change this setting. There is however an issue with this setup, when there is another process running (e.g. a backup) the timer-job doesn't run, and there will be no fresh statistics. In my case, I ran the create statistics manually and the issue is still there.. I'll look into the async though! Thanks!
    – Jeroen Schoenmakers
    Commented Oct 11, 2013 at 4:33
  • In checking the settings and reading up into async stats updates I see that the content-database settings are changed to auto_create_statistics = True .. this is a not recommended setting and could cause the whole issue. I've changed it back and will monitor to see if this resolves the statman query issue (which used to take more than 18 minutes to finish !)
    – Jeroen Schoenmakers
    Commented Oct 11, 2013 at 5:09
  • I don't have a SP 2010 database to look at, but I really question why statistics are needed on this column at all, because it's a blob column. I suppose it wouldn't surprise me if SharePoint is doing something crazy like comparing that column to something within a range scan. Ugh.
    – Jon Seigel
    Commented Oct 11, 2013 at 13:25
  • Hi Jon, thanks again for your thoughts. I've tried again this morning and it seems to be resolved since I disabled the auto-stats configuration on the content database. There are not only BLOBS in the all_docs table, there is also a bunch of metadata etc. I'm really hoping it's resolved now, will monitor closely for the rest of the week ! Have a good one, Jeroen
    – Jeroen Schoenmakers
    Commented Oct 14, 2013 at 7:13

1 Answer 1

Reset to default
6

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.