Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 Oct 10 '13 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 Oct 11 '13 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 Oct 11 '13 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 Oct 11 '13 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 Oct 14 '13 at 7:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.