sp_QuickieStore: See if dm_db_tuning_recommendations would make a useful addition. #483
Description
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