sp_QuickieStore: See if dm_db_tuning_recommendations would make a useful addition.

[erikdarlingdata]

Is your feature request related to a problem? Please describe. I'd like to see if this view has any generally useful information in it. It should be available in 2017+ whether the feature is enabled or not, but I need to figure out some stuff around Standard Edition existences/population.

Describe the solution you'd like There's a starter query from Microsoft. Maybe dump this into a temp table for expert mode and see how it goes.

SELECT
    tr.name,
    tr.type,
    tr.reason,
    tr.valid_since,
    tr.last_refresh,
    tr.state,
    tr.is_executable_action,
    tr.is_revertable_action,
    tr.execute_action_start_time,
    tr.execute_action_duration,
    tr.execute_action_initiated_by,
    tr.execute_action_initiated_time,
    tr.revert_action_start_time,
    tr.revert_action_duration,
    tr.revert_action_initiated_by,
    tr.revert_action_initiated_time,
    tr.score,
    script = 
        JSON_VALUE(tr.details, '$.implementationDetails.script'),
    planForceDetails.query_id,
    planForceDetails.regressedPlanId,
    planForceDetails.recommendedPlanId,
    planForceDetails.regressedPlanErrorCount,
    planForceDetails.recommendedPlanErrorCount,
    planForceDetails.regressedPlanExecutionCount,
    planForceDetails.regressedPlanCpuTimeAverage,
    planForceDetails.recommendedPlanExecutionCount,
    planForceDetails.recommendedPlanCpuTimeAverage,
    estimated_gain = 
        (
          planForceDetails.regressedPlanExecutionCount + 
          planForceDetails.recommendedPlanExecutionCount
        ) * 
        (
          planForceDetails.regressedPlanCpuTimeAverage - 
          planForceDetails.recommendedPlanCpuTimeAverage
        ) / 1000000,
    error_prone = 
        IIF
        (
            planForceDetails.regressedPlanErrorCount > planForceDetails.recommendedPlanErrorCount, 
            'YES', 
            'NO'
        )
FROM sys.dm_db_tuning_recommendations AS tr
CROSS APPLY OPENJSON(tr.details, '$.planForceDetails')
WITH
(
    query_id integer '$.queryId',
    regressedPlanId integer '$.regressedPlanId',
    recommendedPlanId integer '$.recommendedPlanId',
    regressedPlanErrorCount integer,
    recommendedPlanErrorCount integer,
    regressedPlanExecutionCount integer,
    regressedPlanCpuTimeAverage float,
    recommendedPlanExecutionCount integer,
    recommendedPlanCpuTimeAverage float
) AS planForceDetails;

Describe alternatives you've considered I haven't honestly thought about it much.

Are you ready to build the code for the feature? Don't you know who I am?

IMPORTANT: If you're going to contribute code, please read the contributing guide first. https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md

[ReeceGoding]

Does sys.dm_db_tuning_recommendations even work? Brent recently tried his best to get anything out of it. As I recall, he failed.

[erikdarlingdata]

@ReeceGoding yes it works. Brent might be able to get it to work too if he took my training 😃

[ReeceGoding]

@erikdarlingdata Do you actually have training on this? It sounds like my kind of thing.