Edition features

Question

Post reply

Edition features

mtz676

SSCrazy Eights

Points: 9255
More actions
April 18, 2025 at 7:01 am

#4580819

SELECT * FROM sys.dm_db_persisted_sku_features
If the result set has no records is it conclusive to state that the enterprise features are not being used and we can downgrade to standard? This is across all sql versions...2008R2,2012,2014...2022
Are there more checks which need to be performed before we make an informed decision ?
Thanks

April 30, 2025 at 3:58 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80342 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

micherve Grasshopper Points: 15 More actions · Answer 2

I'd recommend also checking for things like online indexing, data compression, or transparent data encryption, especially if you're auditing older instances. A full review of what’s actually in use at the database and server level is probably the safest bet before downgrading.

WirkKarl Grasshopper Points: 20 More actions · Answer 3

mtz676 wrote:

SELECT * FROM sys.dm_db_persisted_sku_features
If the result set has no records is it conclusive to state that the enterprise features are not being used and we can downgrade to standard? This is across all sql versions...2008R2,2012,2014...2022
Are there more checks which need to be performed before we make an informed decision ?
Thanks

It's possible that some features may be used but not persist in that view, or that certain enterprise features are used in specific databases or applications but aren't universally applied across the system. You might also want to check for any advanced configurations, custom settings, or third-party applications that might be relying on enterprise-specific features. Additionally, reviewing any licensing documentation or consulting with your system administrators could provide more clarity.

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 4

Once you use enterprise and enterprise features are being used by the databases are you stuck with enterprise forever ?

and cannot downgrade to standard ?

And if the databases on enterprise are not using enterprise features then I suppose we can downgrade to standard?

Is there a checklist which can help determine that an enterprise can be successfully downgraded to a standard if enterrpsie features are not used by any of the databases ?

Thanks

Lowell SSC Guru Points: 323522 More actions · Answer 5

You can downgrade or upgrade Enterprise to Standard, and Vice Versa, if you have the key for the target edition.

I had started grabbing all the enterprise edition objects via a query and put them into a table. so many features, like Dynamic Data Masking, Partitioning, and i was not sure if they would appear in that sku table. here's the work i started and the comments are the queries form ChatGPT i was still fleshing out when i lost interest in the tangent project this turned into.

IF OBJECT_ID('tempdb..[#PartitionInfo]') IS NOT NULL 
DROP TABLE [#PartitionInfo] 
CREATE TABLE [#PartitionInfo] ( 
[DatabaseName]  NVARCHAR(128)                                                                           NULL,
[TableName]     SYSNAME                                                                             NOT NULL)

EXECUTE sp_MSforeachdb '    
INSERT INTO #PartitionInfo
SELECT ''?'' AS DatabaseName,
    t.name AS TableName
FROM [?].sys.tables t
JOIN [?].sys.partitions p ON t.object_id = p.object_id
WHERE p.partition_number > 1
GROUP BY t.name '


SELECT CONVERT(VARCHAR(128),'Enterprise SKU Features') AS Information,*  FROM sys.dm_db_persisted_sku_features
UNION ALL
SELECT 'Memory Limits>128GB' AS Information,
    CASE WHEN ((total_physical_memory_kb - available_physical_memory_kb) / 1024) / 1024 > 128 
         THEN 'Enterprise Edition Memory limits in Use :' + CONVERT(VARCHAR(30),(total_physical_memory_kb - available_physical_memory_kb) /(1024 * 1024)) + 'GB / ' + CONVERT(VARCHAR(30),(total_physical_memory_kb/ 1024) / 1024) + 'GB'
         ELSE 'Standard Edition Compatibile Limits in  Use:'  + CONVERT(VARCHAR(30),(total_physical_memory_kb - available_physical_memory_kb) /(1024 * 1024)) + 'GB / ' + CONVERT(VARCHAR(30),(total_physical_memory_kb/ 1024) / 1024) + 'GB'
         END AS [feature_name],0 AS [feature_id]
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 'Logical Core Count Limits > 24'AS Information,
    CASE WHEN cpu_count> 24 
         THEN 'Enterprise Edition Logical Core Count Required ' + CONVERT(VARCHAR(30),cpu_count) + 'cpus'
         ELSE 'Standard Edition CompatibleLogical Core Count ' + CONVERT(VARCHAR(30),cpu_count) + 'cpus'
         END AS [feature_name],0 AS [feature_id]
FROM sys.dm_os_sys_info
UNION ALL
SELECT 'Partitioning Tables' AS Information,'Enterprise Edition for Partitioning Tables In use for ' + CONVERT(VARCHAR(30),n.[DBCount]) + 'Databases on ' + CONVERT(VARCHAR(30),n.[TableCount]) + ' tables.',0 FROM (select COUNT (DISTINCT DatabaseName) AS DBCount,COUNT(TableName) AS TableCount  FROM [#PartitionInfo])n
UNION ALL
SELECT 'Partitioning Tables' AS Information,'Standard Edition Compatible No Partitioning Found ' ,0 WHERE NOT EXISTS(SELECT * FROM [#PartitionInfo])
UNION ALL
SELECT 'Availability Groups ' AS Information,
  CASE 
    WHEN n.IsBasicAG =0 THEN 'Enterprise Edition for Availability Groups ' 
    ELSE 'Standard Edition Compatible for  Availability Groups ' 
  END AS feature_name,0
FROM ( SELECT 
    ag.name AS AGName,
    ag.basic_features AS IsBasicAG,
    COUNT(DISTINCT drs.database_id) AS DatabaseCount,
    COUNT(DISTINCT ars.replica_id) AS ReplicaCount
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states ars ON ag.group_id = ars.group_id
JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id
GROUP BY ag.name, ag.basic_features) n
UNION ALL
SELECT 
  'Availability Groups ' AS Information,
  'Standard Edition Compatible No Availability Groups in use. ' ,0
  WHERE NOT EXISTS( SELECT 
    ag.name AS AGName,
    ag.basic_features AS IsBasicAG,
    COUNT(DISTINCT drs.database_id) AS DatabaseCount,
    COUNT(DISTINCT ars.replica_id) AS ReplicaCount
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states ars ON ag.group_id = ars.group_id
JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id
GROUP BY ag.name, ag.basic_features)


--6. Check for In-Memory OLTP
--In-Memory OLTP remains an Enterprise-only feature. Identify memory-optimized tables:
/*
--
Here is the updated diagnostic script tailored to identify features that require Enterprise Edition, excluding data compression as a criterion for SQL Server 2016 SP3 and later:

1. Check Persisted SKU Features
Detect Enterprise-only features explicitly enabled in the database:

sql
Copy code
SELECT * FROM sys.dm_db_persisted_sku_features;
2. Check Memory Utilization
Standard Edition supports up to 128GB of memory. Verify if memory usage exceeds this limit:

sql
Copy code
SELECT 
    physical_memory_in_use_kb / 1024 AS MemoryUsed_MB,
    (total_physical_memory_kb - available_physical_memory_kb) / 1024 AS MemoryAllocated_MB
FROM sys.dm_os_sys_memory;
3. Check CPU Core Usage
Standard Edition supports a maximum of 24 cores. Check if the server exceeds this:

sql
Copy code
SELECT 
    cpu_count AS TotalLogicalCPUs,
    hyperthread_ratio AS HT_Ratio,
    cpu_count / hyperthread_ratio AS PhysicalCPUs
FROM sys.dm_os_sys_info;
4. Check Always On Availability Groups
Standard Edition supports only Basic Availability Groups (BAG) with one database and a single replica. Check configurations:

sql
Copy code
SELECT 
    ag.name AS AGName,
    ag.basic_features AS IsBasicAG,
    COUNT(DISTINCT drs.database_id) AS DatabaseCount,
    COUNT(DISTINCT ars.replica_id) AS ReplicaCount
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states ars ON ag.group_id = ars.group_id
JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id
GROUP BY ag.name, ag.basic_features;
Interpretation:

IsBasicAG = 0: Indicates Enterprise-only availability groups.
DatabaseCount > 1: Exceeds Standard Edition limits.
ReplicaCount > 1: Exceeds Standard Edition limits.
5. Check for Partitioned Tables
Partitioning is still an Enterprise-only feature. Detect tables using partitioning:

sql
Copy code
SELECT 
    t.name AS TableName,
    p.partition_number
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.partition_number > 1;
6. Check for In-Memory OLTP
In-Memory OLTP remains an Enterprise-only feature. Identify memory-optimized tables:


SELECT 
    t.name AS TableName,
    t.is_memory_optimized
FROM sys.tables t
WHERE t.is_memory_optimized = 1;
--7. Check Columnstore Index Usage
--Nonclustered columnstore indexes are available in Standard Edition but with limitations. If clustered columnstore indexes are used, this may indicate Enterprise Edition:

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!