Why query cache plan kept dynamic queries but not sp?how to minimize the effect of updating statistics on the plan cache?Is execution plan cached “better” for stored procedures than for a non-dynamic query?Can a plan “age” reach zero?How to regenerate execution plans after clearing the plan cache?Tracking Down Plan Cache ClearingMissing execution plans for stored proceduresSQL Server cached an execution plan which is not optimal in some cases and uses it for all consequent queriesInstant plan cache cleanupPlan cache mysterySQL Buffer Pool and Plan cache
Was this a power play by Daenerys?
Is a vertical stabiliser needed for straight line flight in a glider?
How are Core iX names like Core i5, i7 related to Haswell, Ivy Bridge?
Is there a faster way to calculate Abs[z]^2 numerically?
Does Lawful Interception of 4G / the proposed 5G provide a back door for hackers as well?
Exception propagation: When to catch exceptions?
The lexical root of the perfect tense forms differs from the lexical root of the infinitive form
Why does TypeScript pack a Class in an IIFE?
How can a Lich look like a human without magic?
How could a Lich maintain the appearance of being alive without magic?
Why do unstable nuclei form?
Should these notes be played as a chord or one after another?
Guns in space with bullets that return?
Is there enough time to Planar Bind a creature conjured by a 1-hour-duration spell?
How to pronounce "r" after a "g"?
How did Thanos not realise this had happened at the end of Endgame?
Can the sorting of a list be verified without comparing neighbors?
Ubuntu won't let me edit or delete .vimrc file
Is a diamond sword feasible?
What does i386 mean on macOS Mojave?
Two researchers want to work on the same extension to my paper. Who to help?
How to deal with inappropriate comments during interviews for academic positions?
Why in a Ethernet LAN, a packet sniffer can obtain all packets sent over the LAN?
Can you book a one-way ticket to the UK on a visa?
Why query cache plan kept dynamic queries but not sp?
how to minimize the effect of updating statistics on the plan cache?Is execution plan cached “better” for stored procedures than for a non-dynamic query?Can a plan “age” reach zero?How to regenerate execution plans after clearing the plan cache?Tracking Down Plan Cache ClearingMissing execution plans for stored proceduresSQL Server cached an execution plan which is not optimal in some cases and uses it for all consequent queriesInstant plan cache cleanupPlan cache mysterySQL Buffer Pool and Plan cache
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our Stored Procedures frequently got performance spikes.
I looked into it and seems the plan cache periodically clears stored procedure plans, but keeps lots of dynamic SQL statements in the plan cache.
I am bit confused on why SQL Server (SQL Server 2017) is doing this. How does SQL Server decide which one to remove?
sql-server execution-plan plan-cache
edited May 1 at 12:24
Mark Sinkinson
8,11033149
asked May 1 at 12:23
daxudaxu
1303
add a comment |
My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our Stored Procedures frequently got performance spikes.
I looked into it and seems the plan cache periodically clears stored procedure plans, but keeps lots of dynamic SQL statements in the plan cache.
I am bit confused on why SQL Server (SQL Server 2017) is doing this. How does SQL Server decide which one to remove?
sql-server execution-plan plan-cache
edited May 1 at 12:24
Mark Sinkinson
8,11033149
asked May 1 at 12:23
daxudaxu
1303
add a comment |
My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our Stored Procedures frequently got performance spikes.
I looked into it and seems the plan cache periodically clears stored procedure plans, but keeps lots of dynamic SQL statements in the plan cache.
I am bit confused on why SQL Server (SQL Server 2017) is doing this. How does SQL Server decide which one to remove?
sql-server execution-plan plan-cache
edited May 1 at 12:24
Mark Sinkinson
8,11033149
asked May 1 at 12:23
daxudaxu
1303
My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our Stored Procedures frequently got performance spikes.
I looked into it and seems the plan cache periodically clears stored procedure plans, but keeps lots of dynamic SQL statements in the plan cache.
I am bit confused on why SQL Server (SQL Server 2017) is doing this. How does SQL Server decide which one to remove?
sql-server execution-plan plan-cache
sql-server execution-plan plan-cache
edited May 1 at 12:24
Mark Sinkinson
8,11033149
asked May 1 at 12:23
daxudaxu
1303
edited May 1 at 12:24
Mark Sinkinson
8,11033149
asked May 1 at 12:23
daxudaxu
1303
edited May 1 at 12:24
Mark Sinkinson
8,11033149
edited May 1 at 12:24
Mark Sinkinson
8,11033149
edited May 1 at 12:24
Mark Sinkinson
8,11033149
8,11033149
asked May 1 at 12:23
daxudaxu
1303
asked May 1 at 12:23
daxudaxu
1303
asked May 1 at 12:23
daxudaxu
1303
1303
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Cached Plans are typically only removed from the plan cache under memory pressure.
SQL Server primarily considers the cost of the plan when deciding which plans to remove. Lost-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.
When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%. For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused. If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted. For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost. You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.
Cache cost is measured in units known as "ticks", with a maximum of 31. Ticks are incremented according to this:
- each I/O adds 1 tick, up to a maximum of 19 ticks.
- each context switch adds one tick, up to a maximum of 8 ticks.
- one tick per 16 pages of memory, with a maximum of 4 ticks.
Ticks start to be decremented once the plan cache reaches 50% of its capacity. At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.
The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.
The details listed above are based on details found in SQL Server Internals by Kalen Delaney.
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
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%2f237117%2fwhy-query-cache-plan-kept-dynamic-queries-but-not-sp%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
Cached Plans are typically only removed from the plan cache under memory pressure.
SQL Server primarily considers the cost of the plan when deciding which plans to remove. Lost-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.
When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%. For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused. If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted. For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost. You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.
Cache cost is measured in units known as "ticks", with a maximum of 31. Ticks are incremented according to this:
- each I/O adds 1 tick, up to a maximum of 19 ticks.
- each context switch adds one tick, up to a maximum of 8 ticks.
- one tick per 16 pages of memory, with a maximum of 4 ticks.
Ticks start to be decremented once the plan cache reaches 50% of its capacity. At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.
The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.
The details listed above are based on details found in SQL Server Internals by Kalen Delaney.
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
add a comment |
Cached Plans are typically only removed from the plan cache under memory pressure.
SQL Server primarily considers the cost of the plan when deciding which plans to remove. Lost-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.
When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%. For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused. If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted. For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost. You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.
Cache cost is measured in units known as "ticks", with a maximum of 31. Ticks are incremented according to this:
- each I/O adds 1 tick, up to a maximum of 19 ticks.
- each context switch adds one tick, up to a maximum of 8 ticks.
- one tick per 16 pages of memory, with a maximum of 4 ticks.
Ticks start to be decremented once the plan cache reaches 50% of its capacity. At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.
The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.
The details listed above are based on details found in SQL Server Internals by Kalen Delaney.
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
add a comment |
Cached Plans are typically only removed from the plan cache under memory pressure.
SQL Server primarily considers the cost of the plan when deciding which plans to remove. Lost-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.
When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%. For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused. If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted. For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost. You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.
Cache cost is measured in units known as "ticks", with a maximum of 31. Ticks are incremented according to this:
- each I/O adds 1 tick, up to a maximum of 19 ticks.
- each context switch adds one tick, up to a maximum of 8 ticks.
- one tick per 16 pages of memory, with a maximum of 4 ticks.
Ticks start to be decremented once the plan cache reaches 50% of its capacity. At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.
The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.
The details listed above are based on details found in SQL Server Internals by Kalen Delaney.
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
Cached Plans are typically only removed from the plan cache under memory pressure.
SQL Server primarily considers the cost of the plan when deciding which plans to remove. Lost-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.
When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%. For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused. If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted. For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost. You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.
Cache cost is measured in units known as "ticks", with a maximum of 31. Ticks are incremented according to this:
- each I/O adds 1 tick, up to a maximum of 19 ticks.
- each context switch adds one tick, up to a maximum of 8 ticks.
- one tick per 16 pages of memory, with a maximum of 4 ticks.
Ticks start to be decremented once the plan cache reaches 50% of its capacity. At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.
The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.
The details listed above are based on details found in SQL Server Internals by Kalen Delaney.
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
answered May 1 at 13:46
Max VernonMax Vernon
53.2k13116234
53.2k13116234
add a comment |
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%2f237117%2fwhy-query-cache-plan-kept-dynamic-queries-but-not-sp%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
