Last post Nov 28, 2016 09:08 AM by Chris Zhao
Nov 23, 2016 08:19 AM|sudip_inn|LINK
i heard that when we compile store proc in sql server then its execution plant is cached ?
does the plan cache when we compile SP or call SP first time ?
how long the SP exe plan get cached ?
SP cache depend on parameter user pass when they call SP from front end technology?
please let me know details. thanks
Nov 24, 2016 09:09 AM|Chris Zhao|LINK
When any SQL statement is executed in SQL Server, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server reuses any existing plan it finds, saving the overhead
of recompiling the SQL statement. If no existing execution plan exists, SQL Server generates a new execution plan for the query.
When a stored procedure is executed it is optimized and compiled and the query plan is placed in procedure cache.
Execution plans remain in the procedure cache as long as there is enough memory to store them. An execution plan is frequently referenced so that its cost never goes to zero. The plan remains in the procedure cache and is not removed unless
there is memory pressure and the current cost is zero.
When a stored procedure is compiled for the first time (or in fact any parameterized batch), the values of the parameters supplied with the execution call are used to optimize the statements within that stored procedure. This process is known as "parameter
sniffing." If these values are typical, then most calls to that stored procedure will benefit from an efficient query plan.
Nov 24, 2016 09:27 AM|sudip_inn|LINK
u said which is not very clear
When a stored procedure is compiled for the first time (or in fact any parameterized batch), the values of the parameters supplied with the execution call are used to optimize the statements within that stored procedure. This process is known as
"parameter sniffing." If these values are typical, then most calls to that stored procedure will benefit from an efficient query plan.
are u trying to say different different SP execution plan is cache in memory based on SP parameter value supplied ?
suppose a SP take ID and Name. when we call the SP with value 1 and "Test" then execution plan is generated and store in memory when again we call the same SP with different value then another execution plan is stored in memory. are trying to mean this ?
tell me in details with example that what is parameter sniffing
Nov 28, 2016 09:08 AM|Chris Zhao|LINK
An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL Server database engine compiles a stored procedure it looks at the parameter values being passed and creates an execution plan based on these
parameters. The process of looking at parameter values when compiling a stored procedure is commonly called "parameter sniffing". Furthermore, generated plans are cached and reused regardless of parameter values at time of reuse.