#0246 – SQL Server 2012 – FileStream and FileTable related DMVs and Catalog Views


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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s