Finding Enterprise Only Features in Your SQL Server 2008 Database

Microsoft added a new DMV, sys.dm_db_persisted_sku_features to SQL Server 2008 that you can use to determine whether you have any Enterprise Edition only features in a particular database.

-- Detect Enterprise Edition Features
-- Glenn Berry 
-- May 2010
-- Twitter: GlennAlanBerry

    -- Look for Enterprise only features in the current database
    SELECT feature_name, feature_id 
    FROM sys.dm_db_persisted_sku_features
    ORDER BY feature_name;

The four possible results you can get in the feature_name column of the query are Compression, Partitioning, TransparentDataEncryption, ChangeCapture. Compression is for data compression, Partitioning is for table or index partitioning, TransparentDataCompression is for transparent data compression, while ChangeCapture is for change data capture.

If you get any rows back from the query, that means that you are using one of these Enterprise-only features in your database, and you will not be able to restore the database to a SQL Server instance that is running on a lower SKU (such as Standard Edition). To be more precise, the database will go through the full restore process, and then fail at the end (since it has no way of knowing that any of these features is being used until the restore is complete).

It would be nice if the DMV gave multiple rows of output, with an object_id for each occurrence of the feature for things like data compression and partitioning.  In the meantime, the query below will help you find the tables that are using data compression.

    -- Get Table names, row counts, 
    -- and compression status for clustered index (SQL 2008 Only)
    SELECT OBJECT_NAME(object_id) AS [Table Name], 
    SUM(Rows) AS [Row Count], data_compression_desc AS [Compression]
    FROM sys.partitions 
    WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
    AND OBJECT_NAME(object_id) NOT LIKE 'sys%'
    AND OBJECT_NAME(object_id) NOT LIKE 'queue_%' 
    AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%' 
    GROUP BY object_id, data_compression_desc

This entry was posted in SQL Server 2008. Bookmark the permalink.

1 Response to Finding Enterprise Only Features in Your SQL Server 2008 Database

  1. Hi Glenn,
    Is there anyway to check this out on Enterprise SQL server 2005?

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s