|
Hi, 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?
(comments are locked)
|
|
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. What will happen in below case - Q-1. SELECT * FROM SALES.CUSTOMER Q.2 SELECT * FROM CUSTOMER Q.3 SELECT * FROM SALES.CUSTOMER Does Q.2 and Q.3 both will use procedure cache or not?
Oct 03 '11 at 05:03 AM
Amardeep
1 & 3 will use the same plan. 2 will have a different plan. All three "use" procedure cache. See the comment below for a quick way to test this for yourself.
Oct 03 '11 at 06:00 AM
Grant Fritchey ♦♦
(comments are locked)
|
|
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. :) http://msdn.microsoft.com/en-us/library/ms181055.aspx 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: You run into other issues there too, assuming your tables are owned by DBO or by your default schema you can run the first query, but if they're not, you can only run the second query. But it will reuse the plan without the schema. Here: CREATE TABLE dbo.A (id INT IDENTITY, Val varchar(50)) SELECT * FROM A SELECT * FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text = 'SELECT * FROM A' If you run the SELECT statement multiple times, you can see the count on the plan go up. Then, if you run it as SELECT * FROM dbo.A and modify the other query: SELECT * FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text = 'SELECT * FROM A' OR dest.text = 'SELECT * FROM dbo.A' You get two rows.
Sep 30 '11 at 06:25 AM
Grant Fritchey ♦♦
Should we use fully qualified name while execute the stored procedure ? e.g. - schemaname.spname Does it will make any seance ?
Oct 03 '11 at 05:20 AM
Amardeep
I use fully qualified names for all objects.
Oct 03 '11 at 05:24 AM
Håkan Winther
I understand benefits of using fully qualified names of tables but is there any benefits associate with stored procedure,function and triggers of using fully qualified name in performance point of view?
Oct 03 '11 at 05:27 AM
Amardeep
(comments are locked)
|
