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.

[erikdarlingdata]

This is kind of lame. The OPENJSON query doesn't work on pre-2017 compatibility levels. Just playing around a little this morning, I can pull out the recommended and regressed plans for queries like this:

SELECT
    query_id = 
        SUBSTRING
        (
            x.detail, 
            CHARINDEX('queryId: ', x.detail) + LEN('queryId: '), 
            CHARINDEX(',', x.detail, CHARINDEX('queryId: ', x.detail) + LEN(','))
            - LEN('queryId: ') - CHARINDEX('queryId: ', x.detail)
        ),
    regressed_plan_id = 
        SUBSTRING
        (
            x.detail, 
            CHARINDEX('regressedPlanId: ', x.detail) + LEN('regressedPlanId: '), 
            CHARINDEX(',', x.detail, CHARINDEX('regressedPlanId: ', x.detail) + LEN(','))
            - LEN('regressedPlanId: ') - CHARINDEX('regressedPlanId: ', x.detail)
        ),
    recommended_plan_id = 
        SUBSTRING
        (
            x.detail, 
            CHARINDEX('recommendedPlanId: ', x.detail) + LEN('recommendedPlanId: '), 
            CHARINDEX(',', x.detail, CHARINDEX('recommendedPlanId: ', x.detail) + LEN(','))
            - LEN('recommendedPlanId: ') - CHARINDEX('recommendedPlanId: ', x.detail)
        )
FROM
(
    SELECT
        detail = 
        (
            SELECT
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                    tr.details,
                '{', ''), '}', ''), '"', ''), ':', ': '), 'planForceDetails: ', ''), ',', ', ')
        )
    FROM sys.dm_db_tuning_recommendations AS tr
) AS x;

But I'm not really sure what to do with the details yet. I'm going to close this one out for now. Maybe I'll come back to it later if something hits me.