#0240-SQL Server-Database & Index related DMVs-sys.dm_db_*

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 full list of documented database & index related DMVs is available here:

The database and index related DMVs that were introduced in SQL Server 2008 R2/2012 are:

sys.dm_db_uncontained_entities
sys.dm_db_log_space_usage
sys.dm_db_fts_index_physical_stats

sys.dm_db_uncontained_entities

Introduced In: SQL Server 2012

Purpose: Microsoft SQL Server 2012 introduces the concept of “Contained Databases” – A contained database has no external dependencies on server-based settings or metadata to define the database. Users defined in a contained database do not have a dependency on logins created on the server, and therefore contain all information necessary to authenticate a user within the database.

You can get a complete hands-on on Contained databases via my three part series based on the labs conducted by Jacob Sebastian (B|T) during TechEd 2011.

The sys.dm_db_uncontained_entities DMV provides a list of all uncontained entities within a database. In simple terms, the objects corresponding to the records returned by this DMV depend upon objects that are outside of the database boundary. For example, when executed against the AdventureWorks2012 sample database, the following output result set is seen:

USE AdventureWorks2012;

GO

SELECT * FROM sys.dm_db_uncontained_entities;

GO

A quick analysis of the output shows us that the class and class_desc columns describe the nature of the uncontained database object. To verify this, let’s look up the object with major_id = 343672272 (If the class = 1, the major_id would contain the object_id of the object – refer the MSDN/BOL link below).

USE AdventureWorks2012;

GO

SELECT OBJECT_NAME(343672272) AS ObjectName;

GO

If we look at the text for the stored procedure, we can see that on line #20, system function serverproperty is used. This is also shown in the output under the “feature_name” column and is used to assign a value to a variable (also indicated by the “statement_type” column in the output). Since the system built-in function serverproperty will be available on all SQL Server 2012 instances, we are okay in this being a non-contained reference.

The end objective of this output is to provide a list of all non-contained objects to the user so that it can be ensured that all possible objects can be contained within the database, thereby making administration and maintenance of the database easier.

Permissions: The DMV sys.dm_db_uncontained_entities returns information only about the objects to which a user has access to. A database may have uncontained entities within a user-defined schema, but if the user does not have permissions on the schema, these objects will not be reported. It is therefore recommended that this be executed under the context of a user who is a member of the sysadmin fixed server role or is a db_owner.

BOL Link: http://msdn.microsoft.com/en-in/library/ff929336.aspx

sys.dm_db_log_space_usage

Introduced In: SQL Server 2012

Purpose: To help identify the amount of transaction log space consumed for the current database. This view can also be treated as a replacement for the command DBCC SQLPERF(LOGSPACE) with the only difference being that DBCC only on all databases on a given SQL Server instance, whereas sys.dm_db_log_space_usage works only on the current database.

Here’s an example:

USE AdventureWorks2012;

GO

–Using DBCC

DBCC SQLPERF(LOGSPACE);

GO
–Using DMV

SELECT su.database_id AS DatabaseId,

       DB_NAME(su.database_id) AS DatabaseName,

       su.total_log_size_in_bytes AS TotalLogSizeInBytes,

       (su.total_log_size_in_bytes/1024)/1024.0 AS TotalLogSizeInMB,

       su.used_log_space_in_bytes AS UsedLogSpaceInBytes,

       (su.used_log_space_in_bytes/1024)/1024.0 AS UsedLogSpaceInMB,

       su.used_log_space_in_percent AS UsedLogSpacePercentage

FROM sys.dm_db_log_space_usage AS su;

GO

As can be seen from the example above, all columns from the output of the DBCC statement can be derived from sys.dm_db_log_space_usage.

Permissions: VIEW SERVER STATE permissions (not tested).

BOL Link: Undocumented

sys.dm_db_fts_index_physical_stats

Introduced In: SQL Server 2012

Purpose: Returns the logical size of each full-text or semantic index in every table that has an associated full-text or semantic index. Here’s an example for the AdventureWorks2012 sample database:

USE AdventureWorks2012;

GO

SELECT OBJECT_NAME(ftsi.object_id) AS ObjectName,

       ftsi.object_id,

       ftsi.fulltext_index_page_count,

       ftsi.keyphrase_index_page_count,

       ftsi.similarity_index_page_count

FROM sys.dm_db_fts_index_physical_stats as ftsi;

GO