Mixing
Exclamation point on plan guide
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
edited Feb 20 at 15:10
Josh Darnell
6,70522140
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
edited Feb 20 at 15:10
Josh Darnell
6,70522140
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
edited Feb 20 at 15:10
Josh Darnell
6,70522140
asked Jan 24 at 14:32
FLeXFLeX
334
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
sql-server ssms plan-guides
edited Feb 20 at 15:10
Josh Darnell
6,70522140
asked Jan 24 at 14:32
FLeXFLeX
334
edited Feb 20 at 15:10
Josh Darnell
6,70522140
asked Jan 24 at 14:32
FLeXFLeX
334
edited Feb 20 at 15:10
Josh Darnell
6,70522140
edited Feb 20 at 15:10
Josh Darnell
6,70522140
edited Feb 20 at 15:10
Josh Darnell
6,70522140
6,70522140
asked Jan 24 at 14:32
FLeXFLeX
334
asked Jan 24 at 14:32
FLeXFLeX
334
asked Jan 24 at 14:32
FLeXFLeX
334
334
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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:
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
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
});
}
});
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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:
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
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:
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
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:
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
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:
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
answered Jan 24 at 15:10
LowlyDBALowlyDBA
7,17752542
7,17752542
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 |
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
1
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
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
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
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 |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
