x

SQL Server Procedure Cache

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.

SELECT * FROM Customer

(2) Secound SQL statement-

SELECT * FROM Customer

(3) Third SQL statement-

 SELECT * FROM SALES.SALES.Customer

Which SQL query will use existing execution plan?

Please give some more information to get perfect benefits of SQL Server Procedure cache?

more ▼

asked Sep 30, 2011 at 04:55 AM in Default

Amardeep gravatar image

Amardeep
1.3k 86 88 89

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Sep 30, 2011 at 05:10 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

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, 2011 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, 2011 at 06:00 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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:

SELECT * FROM Person;

SELECT * FROM Person.Person;
more ▼

answered Sep 30, 2011 at 05:57 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

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, 2011 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, 2011 at 05:20 AM Amardeep
I use fully qualified names for all objects.
Oct 03, 2011 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, 2011 at 05:27 AM Amardeep
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x40

asked: Sep 30, 2011 at 04:55 AM

Seen: 1746 times

Last Updated: Sep 30, 2011 at 04:55 AM