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.

The storage of large object data like images, videos and other such non-relational objects in a relational databases has always been challenging. The biggest of these challenges come in the form of controlling the size of the transactions, storage and also the performance. Storing these object types on the file system overcomes these issues, but fails to provide basic data integrity and other management features that databases provide. Microsoft SQL Server 2008 introduced a new concept called FileStream which allows the SQL Server to work with large object data directly from the file-system in a transparent fashion.

A complete list of the FileStream and FileTable related DMVs and Functions is available at the link below:

• FileStream and FileTable Catalog Views (Link)
  
• FileStream and FileTable Dynamic Management Views (Link)

Let us first take a look at the newly introduced FileStream and FileTable related catalog views first.

Catalog Views

sys.database_filestream_options

Introduced In: SQL Server 2012

Purpose: For each database hosted on the given SQL Server instance, this catalog view contains one record showing the level of non-transactional access to FILESTREAM data in FileTables that is enabled by the administrator.

SELECT database_id AS DatabaseId,

       DB_NAME(database_id) AS DatabaseName,

       non_transacted_access AS NonTransactedAccessEnum,

       non_transacted_access_desc AS NonTransactedAccessDescription

FROM sys.database_filestream_options

WHERE database_id <> 32767;

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

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

sys.filetables

Introduced In: SQL Server 2012

Purpose: Returns a record for each FileTable in SQL Server 2012.

SELECT object_id,

       is_enabled,

       directory_name,

       filename_collation_id,

       filename_collation_name

FROM sys.filetables;

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

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

sys.filetable_system_defined_objects

Introduced In: SQL Server 2012

Purpose: When file tables are created, objects such as constraints and indexes are also created. This catalog view provides a list of all the system defined objects that are related to FileTables.

SELECT object_id,

       parent_object_id

FROM sys.filetable_system_defined_objects;

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

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

Dynamic Management Views

sys.dm_filestream_non_transacted_handles

Introduced In: SQL Server 2012

Purpose: This DMV displays one row per currently open non-transactional file handle associated with FileTable data.

Permissions: No documentation available on BOL

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

Further Reading

• The Art of SQL Server FILESTREAM [Amazon.com, book by Jacob Sebastian & Sven Aelterman – Link]
  
• FILESTREAM Wait Types [Link]
  
• Working with FileTables in SQL Server 2012 – Part 01 [Link]
  
• Working with FileTables in SQL Server 2012 – Part 02 [Link]

Until we meet next time,

Be courteous. Drive responsibly.