Thursday, September 19, 2013

Slow StatMan query issue with #SP2010 farm

2 days ago I've emailed help@brentozar.com the mail below, unfortunately I haven't heard anything and I haven't managed to resolve this issue yet. That's why I decided to blog about it, so I can also keep you posted on the progress !

Hi Help team,

I'm Jeroen (Dutch) all the way from South Africa, running into a Microsoft SQL Server 2008 R2 (SP2) - 10.50.4279.0 (X64)   Mar 26 2013 17:33:13   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) challenge.

This server is also running SharePoint 2010 14.0.6117.5002 (Feb 2012 CU); The problem is that 'suddenly' (used to work without this problem), when the first person uploads a new document to a SharePoint document library, this upload takes forever.
Thanks to your free 'Triage' tool, I managed to pinpoint the query that is taking forever to finish:

EXEC master.dbo.sp_whoisactive
@get_plans=1,@get_transaction_info =1

results in:
SELECT StatMan([SC0], [LC0]) FROM (SELECT TOP 100 PERCENT CONVERT([varbinary](200), 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

This seems to be related with the statistics.. Probably the insert query for the document triggers the query optimizer to create statistics.. which causes a lot of disk i/o and takes more than 20 minutes to complete...
The auto_create_statistics & auto_update_statics settings are FALSE / off on the content-database.

I have manually created the statistics (not trusting the SharePoint timer job that supposed to do that but often fails, as explained here:

I used this query to create the statistics:
EXECUTE sp_msforeachdb
'USE [?];
USE WSS_Content_DiscoverPortal 
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
     begin
          print ''updating statistics in database  ==> '' + db_name()
          if exists (select 1 from sys.objects where name = ''proc_updatestatistics'')
             begin
                  print ''updating statistics via proc_updatestatistics''
                  exec proc_updatestatistics
             end
         else
             begin
                  print ''updating statistics via sp_updatestats''
                  exec sp_updatestats
             end
    end'


But unfortunately, that doesn't help.. It still hangs on the same "SELECT StatMan…." query

I have also looked at the indexes in the database / AllDocStreams table (by the way, the AllDocStreams table seems to hold all BLOB data for the documents in a SharePoint 2010 document library).
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.AllDocStreams'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

ALTER INDEX AllDocStreams_CI ON dbo.AllDocStreams
REBUILD;
GO
--1%

ALTER INDEX AllDocStreams_RbsId ON dbo.AllDocStreams
REBUILD;
GO
--3.5%

To rebuild the indexes, resulting in 0% fragmentation.

I found this blogpost that seems to describe the same type of issue.. With no solution described..

And also these posts..
SELECT query is timing out due to SELECT STATMAN process
-->this is exactly it, but no answer..

BINGO: STATSMAN QUERY – KNOWN ISSUE WITH UPDATE STATISTICS
--> no answer..

Does the BrentO help-team have any other pointers for me to look at ??
It's massively appreciated and I think maybe even challenging enough to take a look at ?

Thanks very much in advance,

Jeroen

Update: 14 October 2013
After posting this issue here: http://dba.stackexchange.com/questions/51303/extremely-slow-statman-query-issue-with-sp2010-farm?noredirect=1#comment90988_51303

I got some nice feedback and started re-checking the settings on the SharePoint content database.  Somehow (probably during early testing and trying to resolve this issue) the Database Setting "Auto Create Statistics) was turned on... This is not best practice for the SharePoint content database (SharePoint's timer job is taking care of creating statistics). After turning off this setting (resetting it to default setting), the issue seems to be resolved. Will keep a close monitor on this for the rest of this week and let you know next week if this resolved this painful issue !