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 !
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 !
