Snippet Name: SQL_Enterprise_Feature_Check
Tags:
Created Date: 2025-06-16 13:12:41
Last Modified Date: 2025-05-31 10:32:00
Dependencies:
DBA Notes:
Sample Output:
Title: SQL Enterprise Feature Check
Author: user
i am trying to determine if a SQL server is using Enterprise Edition features. i know i can query SELECT * FROM sys.dm_db_persisted_sku_features, but i feelt hat is insufficient, because ti does nto check to see if the memory in use is more than Standard Edition is allowed to use; i think the same Always availabiliy groups have a limitation, as well as max number of cores
Author: ChatGPT
To determine if a SQL Server instance is using features that require Enterprise Edition, you can combine several approaches to check for feature usage, memory, cores, and availability group configurations. Here's a comprehensive query and steps to identify Enterprise Edition dependencies:
---
### 1. **Check Persisted SKU Features**
This identifies Enterprise-only features explicitly enabled in a database:
```sql
SELECT * FROM sys.dm_db_persisted_sku_features;
```
---
### 2. **Check Memory Utilization**
Standard Edition has a memory limit of 128GB (for SQL Server 2019 and later). You can query the memory usage to see if it exceeds this limit:
```sql
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. Query the number of cores in use:
```sql
SELECT
cpu_count,
hyperthread_ratio,
cpu_count / hyperthread_ratio AS PhysicalCPUs
FROM sys.dm_os_sys_info;
```
---
### 4. **Check Always On Availability Groups**
Standard Edition supports Basic Availability Groups (BAG) with one database per group and a single secondary replica. Check the configuration of Availability Groups:
```sql
SELECT
ag.name AS AGName,
ag.basic_features AS IsBasic,
COUNT(drs.database_id) AS DatabaseCount,
COUNT(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;
```
---
### 5. **Cross-Check Enterprise-Only Features**
Query system views for Enterprise-only features that might not appear in `sys.dm_db_persisted_sku_features`:
#### Columnstore Indexes:
```sql
SELECT
o.name AS TableName,
i.name AS IndexName,
i.type_desc
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.type = 5 OR i.type = 6; -- Columnstore Indexes
```
#### Data Compression:
```sql
SELECT
o.name AS TableName,
p.data_compression_desc
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
WHERE p.data_compression > 0;
```
#### Partitioned Tables:
```sql
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. **Dynamic Management Views for Feature Insights**
You can also check for advanced features indirectly:
#### Query Hints:
Look for queries using `QUERYTRACEON`, `RESOURCE_SEMAPHORE`, or other hints available in Enterprise Edition.
---
### Automating the Checks
You can write a script or stored procedure that combines these checks and flags potential dependencies on Enterprise Edition.
Would you like a complete script that integrates these queries into a single diagnostic?
Author: user
i thought since SQL2016 SP3, compresison was included in Standard Edition?
Author: ChatGPT
You are absolutely correct! Starting with **SQL Server 2016 SP3**, several features previously exclusive to Enterprise Edition were made available in **Standard Edition**, including **data compression** and **backup compression**. This change means that data compression can no longer be used as a reliable indicator of Enterprise Edition usage for instances running SQL Server 2016 SP3 or later.
Here's a summary of features that were added to Standard Edition in SQL Server 2016 SP3:
1. **Data Compression**: Includes row and page compression.
2. **Backup Compression**: Previously available only in Enterprise Edition.
3. **Database Snapshot**: Allows creating snapshots of a database for reporting or other purposes.
4. **Columnstore Indexes**: Includes nonclustered columnstore indexes but still has limitations compared to Enterprise Edition.
---
### Adjusted Approach for Checking Enterprise Features
Since compression is now available in Standard Edition for SQL Server 2016 SP3 and later, you should focus on other Enterprise Edition features. Here's an updated plan:
#### Focus on:
1. **Memory and CPU Limits**: Validate if the instance exceeds the Standard Edition limits.
- **Memory**: Standard Edition supports a maximum of 128GB.
- **CPU**: Standard Edition supports up to 24 cores.
2. **Always On Availability Groups**: Check if the server uses features such as multiple replicas or non-basic availability groups, which require Enterprise Edition.
3. **Advanced Features**: Check for use of partitioning, in-memory OLTP, and other features exclusive to Enterprise.
---
If your instance is running SQL Server 2016 SP3 or later, you should omit checks for data compression as an Enterprise-only indicator. Would you like me to adjust the diagnostic queries accordingly?
Author: user
yes please
Author: ChatGPT
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
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
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
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
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
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:
```sql
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:
```sql
SELECT
o.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.type IN (5, 6); -- 5 = Nonclustered columnstore, 6 = Clustered columnstore
```
---
### **8. Check Query Hints and Advanced Options**
Look for queries using hints like `QUERYTRACEON` or features exclusive to Enterprise Edition, such as Resource Governor. These require manual review.
```sql
-- Check Query Plan hints or unusual trace flags.
SELECT *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle);
```
---
### Automating These Checks
Would you like help combining these into a single report or script to output the results in a unified format? Let me know, and I can help streamline the process further!
Author: user
that was helpful. looking at a microsoft source, i see these items are not supported by standard edition; would they appear in the SKu dmv, or do we need separate queries for these items? Contained availability groups
Distributed availability groups
Automatic read write connection rerouting
Online page and file restore
Online index create and rebuild
Resumable online index rebuilds
Resumable online ADD CONSTRAINT
Online schema change
Fast recovery
Author: ChatGPT
The features you listed are **Enterprise Edition-only features**, and while some of them might appear in the `sys.dm_db_persisted_sku_features` DMV, others require additional specific queries or manual configuration reviews. Here's an explanation and approach for each:
---
### **1. Contained Availability Groups**
**Description**: Allows databases in availability groups to remain independent of the instance.
- **Check in DMV**: Does not appear in `sys.dm_db_persisted_sku_features`.
- **Separate Query**: Query the contained status of availability groups:
```sql
SELECT
ag.name AS AGName,
ag.is_contained AS IsContained
FROM sys.availability_groups ag
WHERE ag.is_contained = 1;
```
---
### **2. Distributed Availability Groups**
**Description**: Combines multiple Always On availability groups across servers.
- **Check in DMV**: Does not appear in `sys.dm_db_persisted_sku_features`.
- **Separate Query**:
```sql
SELECT
dag.name AS DistributedAGName,
dag.group_id
FROM sys.dm_hadr_distributed_ag dag;
```
---
### **3. Automatic Read-Write Connection Rerouting**
**Description**: Enterprise feature for seamless failover of read-write workloads.
- **Check in DMV**: Does not appear in `sys.dm_db_persisted_sku_features`.
- **Separate Query**: Check routing URLs configured for read-write workloads:
```sql
SELECT
ars.replica_id,
ars.availability_mode_desc,
ars.routing_url
FROM sys.availability_replicas ars
WHERE ars.routing_url IS NOT NULL;
```
---
### **4. Online Page and File Restore**
**Description**: Allows restoring specific pages or files online without taking the database offline.
- **Check in DMV**: Does not appear in `sys.dm_db_persisted_sku_features`.
- **Separate Query**: There's no direct query to detect usage of online page/file restore. Manual review of backup/restore operations and configurations is required.
---
### **5. Online Index Create and Rebuild**
**Description**: Enterprise feature for creating and rebuilding indexes online.
- **Check in DMV**: May appear under `sys.dm_db_persisted_sku_features` as part of index operations.
- **Separate Query**:
```sql
SELECT
o.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
i.is_disabled,
i.allow_page_locks
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.is_disabled = 0 AND i.allow_page_locks = 0;
```
---
### **6. Resumable Online Index Rebuilds**
**Description**: Resumable rebuilds of indexes to avoid long downtime.
- **Check in DMV**: May appear indirectly but requires specific monitoring.
- **Separate Query**: Check resumable index rebuilds in progress:
```sql
SELECT
r.database_id,
r.object_id,
r.index_id,
r.operation,
r.state_desc
FROM sys.dm_exec_requests r
WHERE r.command = 'INDEX_RESUME';
```
---
### **7. Resumable Online ADD CONSTRAINT**
**Description**: Resumable online addition of constraints like primary keys.
- **Check in DMV**: Does not appear in `sys.dm_db_persisted_sku_features`.
- **Separate Query**: Detect resumable constraints:
```sql
SELECT
r.database_id,
r.command,
r.percent_complete,
r.status
FROM sys.dm_exec_requests r
WHERE r.command LIKE 'ADD%CONSTRAINT%';
```
---
### **8. Online Schema Change**
**Description**: Enterprise-only feature for online schema changes.
- **Check in DMV**: Does not appear in `sys.dm_db_persisted_sku_features`.
- **Separate Query**: Check schema changes that are allowed online via monitoring active sessions:
```sql
SELECT
r.database_id,
r.session_id,
r.command,
r.percent_complete
FROM sys.dm_exec_requests r
WHERE r.command LIKE '%ONLINE%';
```
---
### **9. Fast Recovery**
**Description**: Enables fast recovery during database startup or recovery.
- **Check in DMV**: Does not appear in `sys.dm_db_persisted_sku_features`.
- **Separate Query**: Fast recovery cannot be directly queried but is indicated by reduced recovery times during startup or failover. You can monitor the startup process logs for evidence.
---
### **Summary**
- Many features will not appear in `sys.dm_db_persisted_sku_features` because they are operational or configuration-based.
- For a comprehensive assessment, use both the DMV and specific queries like those provided above.
- **Would you like me to bundle these queries into a script or report?**