Problems Viewing Health Reports in SharePoint 2013

I've been faced with an interesting problem over the last few days when working with the SharePoint 2013 RTM build. I'm using SharePoint 2013 RTM on Windows Server 2012 and SQL Server 2012 RTM on Windows Server 2012. I configure usage and health data collection in Central Admin using default settings. I click View health reports. I specify some criteria under Slowest Pages, and click Go. I then get presented with the following error message:

Sorry, something went wrong
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

This took quite a bit of troubleshooting. When you click Go on the Health Reports page, SharePoint calls a stored procedure named proc_GetSlowestPages in the WSS_Logging database. After spending some time messing around with SQL Server Profiler, we established beyond doubt that the call to the stored procedure is using the default READ COMMITTED transaction isolation level. The problem lies in a conflict between the proc_GetSlowestPages stored procedure and the database view that it selects data from.

The proc_GetSlowestPages stored procedure looks like this:

SET NOCOUNT ON
SELECT TOP(@MaxRows)
   ServerUrl +
   CASE  ISNULL(SiteUrl,'')+ ISNULL(WebUrl,'')
      WHEN '/' THEN '' ELSE ISNULL(SiteUrl,'')+ ISNULL(WebUrl,'')
   END
   +ISNULL(DocumentPath,'')
   +ISNULL(QueryString,'') AS Url,
   CONVERT(float,AVG(Duration))/1000 AS AverageDuration,
   CONVERT(float,MAX(Duration))/1000 AS MaximumDuration,
   CONVERT(float,MIN(Duration))/1000 AS MinimumDuration,
   AVG(QueryCount) AS AverageQueryCount,
   MAX(QueryCount) AS MaximumQueryCount,
   MIN(QueryCount) AS MinimumQueryCount,
   COUNT(*) AS TotalPageHits
FROM dbo.RequestUsage
WITH (READPAST)
WHERE PartitionId in (SELECT PartitionId from dbo.fn_PartitionIdRangeMonthly(@StartTime, @EndTime))
AND  LogTime BETWEEN @StartTime AND @EndTime
AND (@WebApplicationId IS NULL OR  WebApplicationId = @WebApplicationId)
AND (@MachineName IS NULL or MachineName = @MachineName)
GROUP BY ServerUrl,SiteUrl,WebUrl,DocumentPath,QueryString
ORDER BY AVG(duration) DESC

Notice that the SELECT statement queries dbo.RequestUsage, which is a database view. It uses the READPAST hint, which essentially tells the query engine to skip any locked rows.

The RequestUsage view looks like this:

SELECT * FROM [dbo].[RequestUsage_Partition0] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition1] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition2] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition3] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition4] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition5] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition6] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition7] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition8] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition9] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition10] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition11] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition12] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition13] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition14] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition15] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition16] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition17] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition18] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition19] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition20] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition21] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition22] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition23] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition24] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition25] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition26] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition27] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition28] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition29] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition30] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition31]

Notice that the view uses a whole bunch of NOLOCK hints. These essentially tell the query engine to ignore any locks. This is the source of the problem: you cannot use NOLOCK and READPAST in the same query as they basically contradict each other. Although the transaction isolation level is READ COMMITTED, the use of the NOLOCK hints means it behaves like a READ UNCOMMITTED isolation level.

As far as I can see, this is a bug in SharePoint 2013 RTM, which creates the usage database (named WSS_Logging by default) when you first configure usage and health data collection. I'd guess Microsoft will address it with a patch in the near future. I managed to work around it by altering the proc_GetSlowestPages stored procedure and commenting out the WITH (READPAST) line.

Edit, 24th June 2013: I edited the stored procedure in a development environment as a proof of concept. I am not suggesting that you do the same. Editing SharePoint databases in a production environment will leave your deployment in an unsupported state, could prevent patches and service packs from working properly, etc.

Edit, 8th November 2013: It turns that editing the schema of the Usage database is permitted - see the comments on this post, thanks Hossein.

Thanks to Geoff Allix and Graeme Malcolm for the SQL Server tips, it's been an education :-)

Comments

  1. It worked like a charm... Playing with the database side of sharepoint is really dangerous and Microsoft should put a fix to this issue as soon as possible..
    Thanks a lot for the tip, it helps a lot

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. In my case this also solved problem with "Site Web Analytics reports" in "site settings" (http://mariuszgorzoch.wordpress.com/2013/07/22/site-web-analytics-reports-freeze-on-use/)

    ReplyDelete
  4. Thanks for your article. Is there still no hotfix for that problem?

    ReplyDelete
  5. Hi Jason, the logging DB is the only DB that MS is OK with modifying. I cannot find the TechNet article right now. But it is out there :)

    ReplyDelete
  6. Here is the reference:

    "Microsoft does not support directly querying or modifying the databases that support SharePoint Server 2013. In SharePoint Server 2013 the Usage and Health Data Collection database does support schema modifications."

    http://technet.microsoft.com/en-us/library/ff945791.aspx#sec2

    ReplyDelete
    Replies
    1. Thanks Hossein, that's really good to know.

      Delete
  7. this solved my problem - thanks for sharing!

    ReplyDelete
  8. thanks for sharing this, this really helped me....

    ReplyDelete

Post a Comment

Popular posts from this blog

Server-side activities have been updated

The target principal name is incorrect. Cannot generate SSPI context.

Custom Workflow Activity for Creating a SharePoint Site