SQL Server maintain procedure cache to store query execution plan and next time when same query execute then it check in procedure cache and if it available then it will not recompile the query and use existing query plan.
I have confusion that which algorithms use to match new SQL statement with existing?
Does it should be fully qualified? e.g. databasename.schemaname.tablename
Which SQL statement will be use existing query plan or both will be use exist query plan?
My sequence of statement is as given below:-
(1) Run first statement: query plan generated and stored in procedure cache.
(2) Secound SQL statement-
(3) Third SQL statement-
Which SQL query will use existing execution plan?
Please give some more information to get perfect benefits of SQL Server Procedure cache?
asked Sep 30 '11 at 04:55 AM in Default
Query 2 is likely to reuse the plan from Query 1. Query 3 is a different query because of the text, even though it's hitting the same table. It's probably that it will compile to it's own plan. That's one of the reasons why I recommend always including the Schema when referring to tables. I don't recommend using the database name unless you're doing cross database queries.
The best things you can to to get good use out of the procedure cache is to parameterize your queries. This means either using parameters to build and execute the queries from the code, or using stored procedures. Second, on SQL Server 2008, always enable Optimize for Ad Hoc Queries. This will make use of plan stubs which are saved instead of plans the first time a query is called. If it's called again and the plan is reused, then it's saved. That reduces memory use pretty radically in the plan cache.
answered Sep 30 '11 at 05:10 AM
Grant Fritchey ♦♦
I am not sure the first and the second query will reuse the plan either because the tables are not fully qualified, but I don't want to argue with grant who have written books about it. :)
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched: