[QUESTION] FinOps - Enterprise Edition with no Enterprise-only features detected - Detection review

[dphugo]

Which component is your question about?

Lite

Performance Monitor Version

2.5.0

Is your question about how it works, or the results?

Results / data interpretation

What's your question?

The questions are specific to the FinOps -> Recommendations on the Licensing Category, "Enterprise Edition with no Enterprise-only features detected".
I've submitted a PR #777 to not error because of non existing database_id and so now it does show the Licensing Recommendation:

1. Lite Edition - connects to master database, which will not have Enterprise features

We do use the Enterprise features: Compression, Partitioning, ColumnStoreIndex.
So I was surprised when the recommendation said that we don't use these features.
I initially thought this check scans all the databases and if one of them does not implement the Enterprise features it will show this recommendation.
But, while tracing the bug #777 I saw that it connects to the master database, which will not have Enterprise features.
1.1 I would think that if at least 1 database implements at least 1 Enterprise feature then this recommendation should not be shown?
1.2 Or it could be scaled? This is just a suggestion, for instance:
- 5% of databases implement Enterprise features = Severity: High, Detail: "Only 5% of databases ([List of DBs]) implement Enterprise features ([List of Enterprise Features]). Consider if this low level of implementation is worth the Enterprise costs, or whether Standard edition would be sufficient."
- 20% of databases implement Enterprise features = Severity: Medium, Detail: "Only 20% of databases ([List of DBs]) implement Enterprise features ([List of Enterprise Features]). Consider if this low level of implementation is worth the Enterprise costs, or whether Standard edition would be sufficient."

I just noticed that Check 8: Enterprise feature detail report — list what blocks a downgrade address 1.2.
Although, it would depend which database is used to check for features. If it is random and it looks at a DB not using the features on one refresh, and then does hit another which does implement the features, results would be random.
1.3 Would it be best to aggregate over all databases? Maybe then my suggestion in 1.2 above might be worth considering?

2. Full Edition issue not yet fixed

As noted in #777 I'm not able to test the Full Edition at this time, but the issue is there as well, with an additional issue:

 if (edition.Contains("Enterprise", StringComparison.OrdinalIgnoreCase))
 {
     var hasDatabaseId = false;
     using (var colCheck = new SqlCommand(
         "SELECT COL_LENGTH('sys.dm_db_persisted_sku_features', 'database_id')", connection))
     {
         colCheck.CommandTimeout = 10;
         hasDatabaseId = await colCheck.ExecuteScalarAsync() is not null and not DBNull;
     }

     var featSql = hasDatabaseId
         ? "SELECT DB_NAME(database_id) AS database_name, feature_name FROM sys.dm_db_persisted_sku_features"
         : "SELECT N'(unknown)' AS database_name, feature_name FROM sys.dm_db_persisted_sku_features";
     using var featCmd = new SqlCommand(featSql, connection);
     featCmd.CommandTimeout = 30;

2.1 Just reiterating that this connects to the master so will always show this recommendation, incorrectly.

2.2 There is an additional check on the Full edition, lines 1844 - 1850 that uses database_id to set

hasDatabaseId

2.2.1 This check would need to be changed as there is no database_id in sys.dm_db_persisted_sku_features.
Maybe a simple row count?

SELECT COUNT(*) AS FeatureCount FROM sys.dm_db_persisted_sku_features

2.3.1 Line 1853 also needs to be fixed as:

? "SELECT DB_NAME() AS database_name, feature_name FROM sys.dm_db_persisted_sku_features"

2.4 As mentioned in 1.3, this could maybe be expanded to look at all DB's and aggregate the results for more nuanced recommendation?

Additional Context

I'm hope my questions / suggestions were clear. Happy to discuss?

[erikdarlingdata]

Fixed in #782. The DMV is database-scoped on all versions (database_id was never added), so the query now iterates all online user databases. Also filtered to TDE only — Compression, Partitioning, and ColumnStoreIndex are available in Standard since 2016 SP1.