6

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?

11

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

  • 1
    I agree, I got the same results on my prod + dev environments, when testing with 'Object','SQL' or 'Template' types, with or without query hints, different hints, ... all of them gave that same warning in SSMS. The same was true for SSMS 2012, so it appears to have been in there for some time. – Randi Vertongen Jan 24 at 15:14
  • 1
    Thanks for looking into it. It's a pretty interesting icon choice. I feel like this could very well be it, but I'll give this some time before accepting your answer just in case. – FLeX Jan 24 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.