In the last parts of
the series “SharePoint Search – a look behind the scene” (Part I , Part II , Part III) we have seen how SharePoint interacts with the SQL
Server and which data is stored in which SQL Server Database belonging to the
Search Service.
In Part III I show
where custom managed properties belonging to an object are stored and how
SharePoint handles this correlation. As one of the missing parts now let´s have
a look at social Tags. The social Tags are stored in the UserProfile and not
within the content. So for me it was interesting to see, that in the Search
database the social Tags information is stored together with the content
information in one table. The Table is the MssDocResults table in the Search_Service_Application_PropertyStoreDB. Folowing link shows a description
of the table. http://msdn.microsoft.com/en-us/library/dd773971(v=office.12).aspx The column PopularSocialTags contains
the Tags given from the users.
Following example
shows a T-SQL Search for the tag “I like it”:
SELECT
* FROM [Search_Service_Application_PropertyStoreDB].[dbo].[MSSDocResults]
WHERE[PopularSocialTags] like
'%I like it%'
So what we also see here is that this table is just “de-normalized”. For
example the tag “SQL Server” and not only its ID / key is stored in the table MSSDocResults.
This is because of performance
issues. Search is read optimized and it’s faster to deliver the data / relation
from content and social Tags without doing joins before.
The keyword “Performance”
brings me to the next point. During this “look behind” series we see, and that’s
also the information SQL reports give us, that some tables are more frequently
used and have a bigger impact in the context of search performance.
There is a really good
article on which index is frequently used on MSDN.
Most frequently used tables by search queries are:
-
dbo.MSSDocProp
-
dbo.MSSDocResults
-
dbo.MSSOrdinal
in the Search_Service_Application_PropertyStoreDB.
So if
performance is a bottleneg it can be helpful placing this tables / index on a separate
filegroup on a fast disksubsystem.
Another
interesting part is the security. In case of a high sensitive environment we
have to see clear which data is placed in which database and how it is
protected. Communication between SharePoint Application Server and the SQL
Server SharePoint Search databases is not really critical. The search requests
are compiles in the BLOB Data as shown in Part I. But a maybe critical point
is, that the data coming from external systems via BCS are placed clear text in
the Search_Service_Application_PropertyStoreDB / table: dbo.MSSDocProps
this can be an issue. Details see Part III.
See the complete post
inc. the hands-on lab as webcaste here:
Keine Kommentare:
Kommentar veröffentlichen