June 6, 2012 at 12:56 pm
Not sure whether i m thinking the right way...I know Sql server stores execution context information in Execution plan but what if the parameter values change consistently in my application, then i don't find any advantage of storing Execution context in Execution Plan, I m simply wasting memory for storing all the parameter information in memory....
I find it more advantage to store just query plan in the Execution plan rather than storing Execution context...Is there any method in Sql Server to force it not to store Execution context information in Procedure Cache
June 6, 2012 at 1:12 pm
Execution contexts aren't reused, they're specific to a single query's execution. The memory space gets reused, not the contents.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2012 at 5:55 am
The compile parameters are stored with the plan because they help show how the plan was created. You also get the ANSI settings stored with the plan. Other than that, I'm not sure what you're calling "execution context." Certainly the execution parameter values are not stored with plans. You can see them when you capture an actual execution, but only because you're capturing that execution yourself. Those values are not stored.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2012 at 10:59 am
GilaMonster (6/6/2012)
Execution contexts aren't reused, they're specific to a single query's execution.
Hmmm... Guess it's not true. From here:
Suppose that a batch B contains an "if" statement. When B begins execution, an execution context for B is generated. Suppose that during this first execution, the "true" branch of the "if" is taken. Further, suppose that B was submitted again by another connection during the first execution. Because the only execution context existing at that moment was in use, a second execution context is generated and given to the second connection. Suppose that the second execution context takes the "false" branch of the "if". After both executions complete, B is submitted by a third connection. Supposing that the third execution of B chooses the "true" branch, the execution will complete slightly faster if SQL Server chose the first execution context of B for that connection rather than the second execution context.
If, as you wrote, execution contexts was "structure-for-one-execution-only" no "slightly faster" execution can be possible in principle - agree?
January 21, 2013 at 5:05 am
find this,
an execution context is derived. An execution context is what is "executed" to produce query results. Execution contexts are also cached and reused.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply