SharePoint Search – a look behind the scene Part II

Part II: SharePoint Search and BCS

(...which data is stored in the SharePoint Search databases?)

Based on the first post “SharePoint Search – a look behind the scene Part I” we will know have a closer look to what happened in the SQL Server if a search request is generated (in this case against a BCS source).

In this example I used an already existing BCS source in my DEMO environment described here: LINK

In the SharePoint Search Center I used the search text “DVD”

With the SQL Server Profiler I can filter out the query SharePoint fires against the SQL Server:

exec sp_executesql

N'/* {00C1C222-BB31-408A-815F-C3A76E85C290} */

exec dbo.proc_MSS_GetMultipleResults @P1,@P2,@P3,@P4,@P5',

N'@P1 int,

@P2 nvarchar(4000),

@P3 nvarchar(4000),

@P4 nvarchar(4000),

@P5 varbinary(8000)',-2147483647,N'dvd',N'',N'SET NOCOUNT ON ;

DECLARE @joinRows INT ;

1.  SET @joinRows = DATALENGTH(@joinData) / 8 ;;

2.  WITH DocIds(DocId, Value) AS (

3.  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 )

    4. 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) ',0x00001592000000000000...700000031

So let’s see what exactly happens by disassemble that query call.

We can see that the Stored Procedure dbo.proc_MSS_GetMultipleResults is called. But we want to go just another step deeper and find out what’s behind this call. (The Stored Procedure dbo.proc_MSS_GetMultipleResults will be part of one of the next posts.)

First of all the DATALENGTH of the generated BLOB witch is stored in @joinData (details for the BLOB data can be found here: LINK) is used to set the variable @joinRows:

1.       SET @joinRows = DATALENGTH(@joinData) / 8

Let’s see what the result is using this query:

                SELECT DATALENGTH(0x000015920000…700000031) / 8

The result is “50”

In the next step a temporary result-set called “DocIds” is created using the “WITH” SQL statement.

2.       WITH DocIds(DocId, Value) AS (…

The call generates a result-set looking like this:

In the first column we see the DocIDs we will need in the next step. But let’s have a look on how this result is generated. The point in the query is this one

1.       SELECT TOP(@joinRows)

CAST(SUBSTRING(@joinData,((ord.n-1)*8) + 1, 4) AS INT),

CAST(SUBSTRING(@joinData, ((ord.n-1)*8) + 5, AS INT)

FROM dbo.MSSOrdinal AS ord WITH(NOLOCK) WHERE ord.n <= @joinRows

Using the CAST(SUBSTRING(@joinData,((ord.n-1)*8) + 1, 4) AS INT)on the BLOB data stored in the @joinData will fillter out a list of item identifiers and their rank contained in an Id value pair described also here LINK. This is the only magic creating the result-set showen in step 2.

Next is an easy join using the tempory result-set “DocIds” and the contend of the table “dbo.MSSDocResults”. The join is done with the part "WHERE P.DocId = T.DocId" showen in the query below:

2.       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)

The result is:

So we see that the results given back from SQL Server contains all the data needed even the data is coming from an extern BCS source. The given metadata like “name”, “description” etc. is stored in the PropertyBlog field explained in the first post.

If we now call the ProfilePage of the BCS source all data fields witch are configured are needed. This result in call against the data source defined in the BCS model. In my case this is also an SQL Server call because my External Data Source is a SQL Server:

Captured with SQL Profiler it looks like this:

exec sp_executesql N'SELECT [ProductKey] , [ProductName] , [ProductDescription] , [BrandName] , [ClassName] , [ColorName] , [UnitPrice] , [ProductSubcategoryName] , [ProductCategoryDescription] , [Expr1] FROM [dbo].[V_ContosoRetailDW_ProductSales] WHERE [ProductKey] = @ProductKey',N'@ProductKey int',@ProductKey=1642

and the result similar to what we can see at the ProfilePage:

See the complete post inc. the hands-on lab as webcaste here: