SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are not only useful to indicate the overall health of the SQL Server, but also of the databases hosted by the given SQL Server instance. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

A complete list of the full text and semantic search related DMVs and Functions is available at the link below:

• Full-Text Search and Semantic Search Dynamic Management Views and Functions (Link)
• Full-Text Search and Semantic Search Catalog Views (Link)

Let us looking at the Catalog Views related to the new feature introduced in Microsoft SQL Server 2012 – Semantic Search.

Catalog Views

sys.fulltext_semantic_language_statistics_database

Introduced In: SQL Server 2012

Purpose: The Semantic Language Statistics Database is a required component for the Statistical Semantic Search feature in Microsoft SQL Server 2012. This database can be downloaded and configured from http://www.microsoft.com/en-in/download/details.aspx?id=29069.

The catalog view sys.fulltext_semantic_language_statistics_database returns information about the statistical semantics database installed on the particular SQL Server instance. If the database is not installed, no records are returned.

USE AdventureWorks2012;
GO
SELECT * FROM sys.fulltext_semantic_language_statistics_database;
GO

The output looks similar to the screenshot shown below.

Permissions: Visibility is limited to securables that the user owns or has been granted permission to.

BOL Link: http://technet.microsoft.com/en-us/library/gg492069.aspx

sys.fulltext_semantic_languages

Introduced In: SQL Server 2012

Purpose: Returns a record for each language whose statistics model is registered with the given SQL Server instance through the statistical semantics database (see above).

USE AdventureWorks2012;
GO
SELECT * FROM sys.fulltext_semantic_languages;
GO

The output is quite simple (shown below):

 

Permissions: Visibility is limited to securables that the user owns or has been granted permission to.

BOL Link: http://technet.microsoft.com/en-us/library/gg492217.aspx

sys.registered_search_properties

Introduced In: SQL Server 2012

Purpose: Contains a row for each search property contained by any search property list on the current database. Property lists can be used to enable full-text searches to work with document properties like the Author name or title. For more information, please refer: http://technet.microsoft.com/en-us/library/ee677637.aspx

My environment has not yet been configured to use property lists and therefore no records were returned by the catalog view.

Permissions: Visibility is limited to securables that the user owns or has been granted permission to via a REFERENCE permission or otherwise.

BOL Link: http://technet.microsoft.com/en-us/library/ee677608.aspx

sys.registered_search_property_lists

Introduced In: SQL Server 2012

Purpose: Contains a row for each search property list in the current database. For more information, please refer: http://technet.microsoft.com/en-us/library/ee677637.aspx

My environment has not yet been configured to use property lists and therefore no records were returned by the catalog view.

Permissions: Visibility is limited to securables that the user owns or has been granted permission to via a REFERENCE permission or otherwise.

BOL Link: http://technet.microsoft.com/en-us/library/ee677629.aspx

Dynamic Management Views

sys.dm_fts_semantic_similarity_population

Introduced In: SQL Server 2012

Purpose: For all tables that have an associated semantic search index, this DMV returns the status information about the population of the semantic document similarity index.

As highlighted in http://msdn.microsoft.com/en-us/library/gg492085.aspx, the semantic indexing is essentially a two step process. The first step includes population of the full-text keyword index and the semantic key phrase index as well as extraction of document similarity data. The next phase uses this information to generate the semantic document similarity index.

On my test server, the generation was under the “starting” phase when the query was executed and screenshot captured.

USE AdventureWorks2012;
GO
SELECT * FROM sys.dm_fts_semantic_similarity_population;
GO

Permissions: VIEW SERVER STATE permission on the server.

BOL Link: http://msdn.microsoft.com/en-us/library/gg492070.aspx

Until we meet next time,

Be courteous. Drive responsibly.