Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution context and Procedure Cache Expand / Collapse
Author
Message
Posted Wednesday, June 06, 2012 12:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 2:21 PM
Points: 55, Visits: 526
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



Post #1312130
Posted Wednesday, June 06, 2012 1:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1312139
Posted Thursday, June 07, 2012 5:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1312404
Posted Monday, August 20, 2012 10:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 06, 2014 1:09 AM
Points: 57, Visits: 272
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?
Post #1347342
Posted Monday, January 21, 2013 5:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 15, 2014 12:41 AM
Points: 75, Visits: 65
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.

http://technet.microsoft.com/library/Cc966425
Post #1409491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse