Hi Stephen
> Dynamic SQL execution plans are cached!
>
> http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
Yes and no. That cached value is a function of the true workload of the
sever over then next x min. So yes it is and it will sort of remain in
the cache, but as other work is getting requested it may have a very
short shelf life. It also depends on what volume of ram and your
settings for your procedure cache are.
That being said, workers who repeat the same functionality may have
their requests cached. But at what expense? We all know that you
can't get something for nothing. Oh that second round trip to the
server for each query will add up when your using client sided
statements. This is a bigger issue that the comments bring up. Below
goes into more details on this.
http://www.sql-server-performance.com/stored_procedures.asp
What makes you think the SP execution plans are kept any longer than
other execution plans?
This is from the BOL:
Stored Procedures and Execution Plans
In SQL Server version 6.5 and earlier, stored procedures were a way to
partially precompile an execution plan. At the time the stored
procedure was created, a partially compiled execution plan was stored
in a system table. Executing a stored procedure was more efficient
than executing an SQL statement because SQL Server did not have to
compile an execution plan completely, it only had to finish optimizing
the stored plan for the procedure. Also, the fully compiled execution
plan for the stored procedure was retained in the SQL Server procedure
cache, meaning that subsequent executions of the stored procedure
could use the precompiled execution plan.
SQL Server 2000 and SQL Server version 7.0 incorporate a number of
changes to statement processing that extend many of the performance
benefits of stored procedures to all SQL statements. SQL Server 2000
and SQL Server 7.0 do not save a partially compiled plan for stored
procedures when they are created. A stored procedure is compiled at
execution time, like any other Transact-SQL statement. SQL Server 2000
and SQL Server 7.0 retain execution plans for all SQL statements in
the procedure cache, not just stored procedure execution plans. The
database engine uses an efficient algorithm for comparing new
Transact-SQL statements with the Transact-SQL statements of existing
execution plans. If the database engine determines that a new
Transact-SQL statement matches the Transact-SQL statement of an
existing execution plan, it reuses the plan. This reduces the relative
performance benefit of precompiling stored procedures by extending
execution plan reuse to all SQL statements.
Mike
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
