Fixing SharePoint Search Performance with a SQL Server Plan Guide

We’ve been confronted with a performance issue in our SharePoint Server 2010 environment for quite some time and today, I was finally able to implement a workaround to get that resolved.

The problem was happening with a particular type of search that involved querying more than one managed properties in a customized search results web part in a search center. Every time that query ran, it took about 25-40 seconds, which was unacceptable. Other SharePoint search without those managed properties ran just fine. We could never really reproduce the issue in our QA environment so we were thinking it had to do with the volume of documents being indexed.

While looking at the performance of the SQL Server instance hosting SharePoint for another reason using my favorite SQL Server tool, SQL Sentry, I noticed that something was off with the following search query. I then recognized this to be our problematic query because of the parameters being passed.

WITH DocIds(DocId, Value) AS
( SELECT TOP(@joinRows) CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 1, 4) AS INT), CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 5, 4) AS INT)
FROM dbo.MSSOrdinal AS ord WITH(NOLOCK) WHERE ord.n <= @joinRows )
SELECT P.DocId, P.Pid, P.llVal, P.strVal, P.strVal2, CASE WHEN P.strVal2 IS NULL THEN 0 ELSE DATALENGTH(P.strVal2) END
FROM dbo.MSSDocProps AS P WITH(NOLOCK), DocIds AS T
WHERE P.DocId = T.DocId AND P.Pid IN ( 413, 414, 410, 411 )
ORDER BY T.Value ASC, P.Pid ASC OPTION (MAXDOP 1)

For some reason, the optimizer chose to do a Clustered Index Scan on MSSDocProps, a pretty large table of 5.3M in our production environment. This table is located in the PropertyStore database that is used by the SharePoint search engine. Here’s what the execution plan looked like in our production environment:

prod_plan

While digging a bit further in the execution plan, I noticed that one of the step was estimating 110M rows while the actual was much lower, in the 100s. I thought, maybe the statistics on the tables involved were out of date, so I decided to update all those. Tried the query again, still the same execution plan. I then took the query that ran in production and ran it against one of our QA database directly. Boom, the query completed in less than a second. While the QA environment was a bit smaller (~4M rows), it was much faster than production and this, on less hardware (2 vCPU vs 5 vCPU in prod).  I then compared the plan in QA with the plan from production. Here’s what the execution plan looks like:

qa_plan

In this plan the optimizer favored an index seek on MSSDocProps, which is what I would have expected for that query. I then started to play around a bit with the query to see how I could change the execution plan. I discovered that by introducing parallelism by changing the MAXDOP 1 hint to MAXDOP 2, the query started to use an index seek in the plan but the plan was still different between QA and production.

I then asked my SharePoint admin to restore the PropertyStore database from production on the QA server to see if the hardware difference could have been a difference. Running the query on the restored copy from production ran with the proper execution plan.

I then started to look into how I could force a seek on the MSSDocProps table and I remembered there was the FORCESEEK hint that could be used. I introduced the hint in the original query that had the MAXDOP 1 hint and the query was now using the proper plan in production. Unfortunately, that query could not be changed as it’s coming from SharePoint and I have no control on that code and/or the schema as it would leave me in an unsupported state with Microsoft.

I then started to investigate plan guides, which allow me to inject hints (or a whole plan) in a statement that is part of a batch, which was exactly my case. I had to fiddle around with sp_create_plan_guide to find how to properly specify the statement, parameters and the batch in order to match the incoming query from SharePoint. What I ended up doing is finding the existing plan in the plan cache and using sp_create_plan_guide_from_handle, I created a plan guide using that. I was then able to see how the statement, batch and parameters were specified in order to get the proper match, which would ensure that my plan guide will be used. Knowing this, I was able to create the plan guide as follow (and dropped the wrong plan guide afterwards):


EXEC sp_create_plan_guide
@name = N'SearchManagedPropertiesQuery',
@stmt = N'WITH DocIds(DocId, Value) AS ( SELECT TOP(@joinRows) CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 1, 4) AS INT), CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 5, 4) AS INT) FROM dbo.MSSOrdinal AS ord WITH(NOLOCK) WHERE ord.n <= @joinRows ) SELECT P.DocId, P.Pid, P.llVal, P.strVal, P.strVal2, CASE WHEN P.strVal2 IS NULL THEN 0 ELSE DATALENGTH(P.strVal2) END FROM dbo.MSSDocProps AS P WITH(NOLOCK), DocIds AS T WHERE P.DocId = T.DocId AND P.Pid IN ( 413, 414, 410, 411 ) ORDER BY T.Value ASC, P.Pid ASC OPTION (MAXDOP 1) ',
@type = N'SQL',
@module_or_batch = 'SET NOCOUNT ON ; DECLARE @joinRows INT ; SET @joinRows = DATALENGTH(@joinData) / 8 ;;WITH DocIds(DocId, Value) AS ( SELECT TOP(@joinRows) CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 1, 4) AS INT), CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 5, 4) AS INT) FROM dbo.MSSOrdinal AS ord WITH(NOLOCK) WHERE ord.n <= @joinRows ) SELECT P.DocId, P.SummaryBlobSize, P.Size, P.LastModified, P.IsDocument, P.IsPictureUrl, P.Author, P.Title, P.Url, P.PictureThumbnailUrl, P.ContentClass, P.FileExtension, P.Tags, P.PropertyBlob, CASE WHEN P.PropertyBlob IS NULL THEN 0 ELSE DATALENGTH(P.PropertyBlob) END, P.PopularSocialTags, P.SiteName, P.Description, P.ParentLink, P.NumberOfMembers, P.PictureHeightAndWidth, P.DisplayDate FROM dbo.MSSDocResults AS P WITH(NOLOCK), DocIds AS T WHERE P.DocId = T.DocId OPTION (MAXDOP 1)  ;WITH DocIds(DocId, Value) AS ( SELECT TOP(@joinRows) CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 1, 4) AS INT), CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 5, 4) AS INT) FROM dbo.MSSOrdinal AS ord WITH(NOLOCK) WHERE ord.n <= @joinRows ) SELECT P.DocId, P.Pid, P.llVal, P.strVal, P.strVal2, CASE WHEN P.strVal2 IS NULL THEN 0 ELSE DATALENGTH(P.strVal2) END FROM dbo.MSSDocProps AS P WITH(NOLOCK), DocIds AS T WHERE P.DocId = T.DocId AND P.Pid IN ( 413, 414, 410, 411 ) ORDER BY T.Value ASC, P.Pid ASC OPTION (MAXDOP 1) ',
@params = N'@joinData VARBINARY(MAX)',
@hints = N'OPTION (TABLE HINT( P, FORCESEEK,NOLOCK))';

GO

With that plan guide in place, the search query in SharePoint started to behave as expected. I’m still not sure why the query picked the suboptimal plan in the first place but at least my users should be happy for the time being while hopefully maintaining supportability from Microsoft (still pending a confirmation from Premier on this).

Let me know if you have questions or comments about this.

2015-12-10 – Update
One thing that I forgot to mention in the original post is that while researching the issue, I noticed that SharePoint 2013 has a property on SPWebApplication called ForceseekEnabled which probably means there are some cases/conditions where Microsoft thought it might be necessary to force this hint. You can find it in the doc here. It looks like that property is not there in SharePoint 2010 though.

2015-12-14 – Update
While exchanging a few tweets with @williambaer (Bill Baer), he brought up to our attention that this modification is not supported as per the KB 841057 which states that no changes whatsoever can be made to the SharePoint database schema (also a special mention to Trevor Seward @NaupliusTrevor). After discussing with both our SQL Server DBA and SharePoint administrator, we chose to live with an unsupported configuration for the time being.  Here are some of the points involved in the thought process:

  • The change can be easily rolled back by dropping the plan guide.
    • We will do this for upgrades and other support calls if needed
  • We feel comfortable with the change done
    • The explain plan that’s ultimately used through the plan guide is one that happens even without the plan guide
    • No change to the locking logic is done
    • Someone though that the FORCESEEK hint is useful in SharePoint in other cases, which means our solution is not that crazy to begin with
  • Our users are really happy
  • We are not seeing any adverse effect so far
  • If we open a support call that spans more than one technology, we usually end up with one engineer who is a specialist in one (i.e. SharePoint) and quite inexperienced in another (SQL Server) technology. Which means we have to “invest” quite a bit of time explaining how the other technology works to get our point across. We try not to generalize but this happened  on a few occasions:
    • DPM and SQL Server
    • DPM and Windows Server
    • Hyper-V and Storage Spaces
    • SharePoint and .NET Framework
  • We are nearing the end of our Premier contract term.
    • When an incident spans two contract terms and is considered a bug at the end, only the hours in the current contract are credited. Which means we lose quite a few precious Premier hours which could have been put to better and more proactive actions.
  • How much time an effort will that require on our part in order to prove to Microsoft that this change is required? Even though we get credited the hours on the call, we do not get those hours of our lives back! 😉 If we can skip that whole process and submit the RFC to the product group, we’ll gladly do so.

I know it might not be the most politically correct answer to give but  I try to be as transparent as possible. We’re all onboard in collaborating in getting this change in SharePoint through the official channel, it’s just the time (and money) commitment looks a bit too big from where we stand. We can definitely be convinced to go down the other/proper route if those hurdles are somewhat removed.

2015-12-15 – Update

Trevor kindly submitted an RFC to Microsoft on our behalf regarding this issue. Many thanks Trevor!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s