Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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
-- http://glennberrysqlperformance.spaces.live.com/
-- 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
    ORDER BY SUM(Rows) DESC;

Comments

Posted by Anonymous on 10 May 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Finding Enterprise Only Features in Your SQL Server 2008 Database - Glenn Berry's SQL Server Performance         [sqlservercentral.com]        on Topsy.com

Posted by Dukagjin Maloku on 11 May 2010

Thanks for the info and it is very nice DMV!

Posted by Anonymous on 11 May 2010

Pingback from  Mahalo  The Other Search Engine

Leave a Comment

Please register or log in to leave a comment.