Breaking the Law: Viewing Health Reports in SharePoint 2013

SharePoint 2013 provides the usage and health data collection services (this functionality is also present in SharePoint 2010). Using these services, you can combine a series of SQL queries and stored procedures to view, examine, and report on SharePoint metrics from within SQL.

Whoa – hold up for a minute! You’re not supposed to even open a SharePoint database in SQL Server Management Studio, right?

The answer is a definitive, “Yes, but.” All databases within SharePoint are off limits to SQL queries/manipulation except the SharePoint Server Logging database (the default name of this database is WSS_Logging if you are looking for it in SQL Server Management Studio).

This database excels at capturing events in a farm-wide fashion. Event Viewer, IIS Logs, even our beloved ULS Viewer – none of them can match this ability. Simply put, this database is a clearinghouse of information about your farm, soup-to-nuts.

(If you’d like to look at this further, by the way, check out this TechNet article).

From File System to Database

Once this service is activated, the system takes information from the log file location of each server in the farm (usually “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\LOGS\”) and sends this information to the Logging database for the farm, where it is processed and stored in a series of tables which are partitioned by category on a daily basis (dbo.AccessServicesMonitoring_Partition1, _Partition2, and so on).

Figure 1 – Partitioned Tables by Category

The View is GREAT from here

Contained within this database is a series of database views that are already assembled for us non-SQL SharePoint people to consume. Each of these views works with a particular category’s partitioned tables to generate a picture of a particular functionality within SharePoint 2013 (works in 2010 also, like I said before).

Here’s the rub – one of these bad boys isn’t working in SharePoint 2013 RTM (that I know of, haven’t tried them all).

Enabling Usage and Health Data Collection

Sounds good so far, let’s turn this on!

There’s really not much to making this work with the defaults:

1. Open Central Administration à Monitoring à Reporting à Configure usage and health data collection
2. Leave the “Enable usage data collection” checkbox selected
3. Look at all the Events that are logged to this database by default (already preselected)
4. Scroll down to the bottom of the page and click OK – Simple!

Figure 2 – Configure Usage and Health Data Collection

Let’s See Some Magic

Now that we have this all configured, we should be able to look at one of the built in reports.

1. Open Central Administration à Monitoring à Reporting à View health reports
2. When the “Slowest Pages” screen appears, select the server, web application, number of items and time range you’d like to view, then select the Go button.

Figure 3 – Selecting Items to View

Aww man – what does THIS message mean? “You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.”

Figure 4 – Something Went Wrong

So, How Do We Fix It?

Sounds like we’re gonna have to put our DBA hat on. Doing a little digging on the web, I found this article by Jason Lee that sounded like the solution (and it was).

His blog post indicates that the proc_GetSlowestPages stored procedure doesn’t work as expected (in SQL Server Management Studio, expand the database, then in the Programmability section, expand Stored Procedures to see dbo.proc_GetSlowestPages).

To modify the stored procedure, right click it and select Modify. Jason’s post calls for you to remark out the WITH (READPAST) section of the SELECT TOP(@maxrows) query, as shown below.

Figure 5 – The Culprit

Adding /* and */ characters before and after this selection remarks it out.

Figure 6 – Remarked Out

All Better Now

All that remains is to deploy the fix and try the report again.

To deploy the fix, simply execute it (this saves the modification to the stored procedure). When the “Command(s) completed successfully” prompt appears in the Messages window, you are ready to try out the fix.

Let’s run the Report again and see what the result was supposed to look like:

1. Open Central Administration à Monitoring à Reporting à View health reports
2. When the “Slowest Pages” screen appears, select the server, web application, number of items and time range you’d like to view, then select the Go button.
3. The Report appears, as shown below.

Again, for technical details about how this problem was captured, see Jason Lee’s blog – some amazing SQL sleuthing…