SQL Server Plan Guides – Exclamation Point on Plan Guide

plan-guidessql serverssms

I created a plan guide using the following query:

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:

plan guide warning icon

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?

Best Answer

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:

enter image description here

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] ASC

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:

enter image description here

Related Question