patternsqlModerate
Exclamation point on plan guide
Viewed 0 times
planguideexclamationpoint
Problem
I created a plan guide using the following query:
It seems to work fine, but I noticed that there is a little warning icon on the plan in Object Explorer.
It looks like this:
I don't get any warnings when executing the query, and I can't find any information about it when hovering over it or checking the properties of the plan guide.
This is only applied in a test environment but why does it show up and should I be worried about it?
EXEC sp_create_plan_guide
@name = N'Entity_Property fix',
@stmt = N'SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM jirascheme.entity_property WHERE ENTITY_NAME=@P0 AND ENTITY_ID=@P1 AND PROPERTY_KEY=@P2',
@type = N'SQL',
@params = N'@P0 nvarchar(255), @P1 numeric(18, 0), @P2 nvarchar(255)',
@hints = N'OPTION (OPTIMIZE FOR UNKNOWN)';It seems to work fine, but I noticed that there is a little warning icon on the plan in Object Explorer.
It looks like this:
I don't get any warnings when executing the query, and I can't find any information about it when hovering over it or checking the properties of the plan guide.
This is only applied in a test environment but why does it show up and should I be worried about it?
Solution
This is an educated guess, so if anyone has something more authoritative I am happy to rescind it.
I've never used the SSMS UI to manage plan guides before, so I immediately checked a few servers where I know I have viable plan guides setup:
The red Xs indicate the plans are disabled, as one might expect.
When tracing SSMS as it populates this part of the UI, we can see it runs this query:
Which only includes basic data and the disabled flag - nothing else. My hunch is that the yellow exclamations are actually the default icons for plan guides.
This doesn't seem too far fetched since you're modifying execution plans and a similar icon is used whenever an execution plan might be impacted by something like a conversion or a plan guide:
I've never used the SSMS UI to manage plan guides before, so I immediately checked a few servers where I know I have viable plan guides setup:
The red Xs indicate the plans are disabled, as one might expect.
When tracing SSMS as it populates this part of the UI, we can see it runs this query:
SELECT pg.name AS [Name]
, 'Server[@Name=' + quotename(CAST(serverproperty(N'Servername')AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/PlanGuide[@Name=' + quotename(pg.name,'''') + ']' AS [Urn]
, pg.is_disabled AS [IsDisabled]
FROM sys.plan_guides AS pg
ORDER BY [Name] ASCWhich only includes basic data and the disabled flag - nothing else. My hunch is that the yellow exclamations are actually the default icons for plan guides.
This doesn't seem too far fetched since you're modifying execution plans and a similar icon is used whenever an execution plan might be impacted by something like a conversion or a plan guide:
Code Snippets
SELECT pg.name AS [Name]
, 'Server[@Name=' + quotename(CAST(serverproperty(N'Servername')AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/PlanGuide[@Name=' + quotename(pg.name,'''') + ']' AS [Urn]
, pg.is_disabled AS [IsDisabled]
FROM sys.plan_guides AS pg
ORDER BY [Name] ASCContext
StackExchange Database Administrators Q#227982, answer score: 11
Revisions (0)
No revisions yet.