Sfrgttk

Why didn't Lorentz conclude that no object can go faster than light?

Taking an academic pseudonym?

How to make my enemies feel real

How should I ship cards?

Why is opening a file faster than reading variable content?

MySQL: Is it a security risk to deactivate the setting "bind-address"?

How changes in personality/values of a person who turned into a vampire can be explained?

Hollowed circle with crossed line and arrow

"Cheaper by the dozen" phrase origin?

What are Holorydmachines?

Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash

Ramanujan's radical and how we define an infinite nested radical

Rational with finite decimals values for sine, cosine, and tangent

Where can I educate myself on DnD universe lore, specifically on vampires and supernatural monsters?

Found a major flaw in paper from home university – to which I would like to return

Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?

I hate taking lectures, can I still survive in academia?

Why is Bernie Sanders maximum accepted donation on actblue $5600?

Making a timetable based on user choices, in French

Have an different color before and after an div with CSS

Why isn't there any EEPROM in STM32F4 MCUs?

How can a kingdom keep the secret of a missing monarch from the public?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Discouraging missile alpha strikes

Exclamation point on plan guide

SQL Server Plan Guide vs Plan FreezingCreating plan guide for query called through sp_executesqlMulti-Statement Template Plan GuideWhy is my plan guide not being used?Plan guide validation with fn_validate_plan_guide gives false positivesSQL Guide Plan not being used?SQL Server Plan GuideTroubleshooting non-working Plan Guide in SQL Server 2008 R2Plan Guide for the LINQ-SQL QueryUnable to use plan guide with sp_execute

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:

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?

sql-server ssms plan-guides

share|improve this question

edited 7 mins ago

jadarnel27

5,32811836

asked Jan 24 at 14:32

FLeXFLeX

334

add a comment | 

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:

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?

sql-server ssms plan-guides

share|improve this question

edited 7 mins ago

jadarnel27

5,32811836

asked Jan 24 at 14:32

FLeXFLeX

334

add a comment | 

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:

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?

sql-server ssms plan-guides

share|improve this question

edited 7 mins ago

jadarnel27

5,32811836

asked Jan 24 at 14:32

FLeXFLeX

334

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)';

share|improve this question

edited 7 mins ago

jadarnel27

5,32811836

asked Jan 24 at 14:32

FLeXFLeX

334

share|improve this question

edited 7 mins ago

jadarnel27

5,32811836

asked Jan 24 at 14:32

FLeXFLeX

334

edited 7 mins ago

jadarnel27

5,32811836

edited 7 mins ago

jadarnel27

5,32811836

asked Jan 24 at 14:32

FLeXFLeX

334

asked Jan 24 at 14:32

FLeXFLeX

334

1 Answer
1

active

oldest

votes

10

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:

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:

share|improve this answer

answered Jan 24 at 15:10

LowlyDBALowlyDBA

7,05252542

• 
  
  
  

  
  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
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227982%2fexclamation-point-on-plan-guide%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

10

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:

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:

share|improve this answer

answered Jan 24 at 15:10

LowlyDBALowlyDBA

7,05252542

• 
  
  
  

  
  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
  

  
  
  
  

add a comment | 

10

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:

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:

share|improve this answer

answered Jan 24 at 15:10

LowlyDBALowlyDBA

7,05252542

• 
  
  
  

  
  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
  

  
  
  
  

add a comment | 

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:

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:

share|improve this answer

answered Jan 24 at 15:10

LowlyDBALowlyDBA

7,05252542

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

share|improve this answer

answered Jan 24 at 15:10

LowlyDBALowlyDBA

7,05252542

answered Jan 24 at 15:10

LowlyDBALowlyDBA

7,05252542

answered Jan 24 at 15:10

LowlyDBALowlyDBA

7,05252542