CODEWITHSUNDEEP
sql-server
T McKeown
T McKeown

Reputation: 12857

When are multiple Execution Plans created?

Imagine a procedure named dbo.TheBestCodedProcedure, if it is executed in the following ways:

EXEC dbo.TheBestCodedProcedure

And 

EXEC TheBestCodedProcedure

Both will execute successfully, however how many execution plans are created? It is my understanding that the execution plan is based off the name of the object that is called and parameters provided. I have seen examples of multiple execution plans even when the same arguments are provided, the only difference being the dbo. prefix, is this true or not? What are the rules of WHEN an additional execution plan is created?

Upvotes: 1

Views: 853

Answers (2)

M.Ali
M.Ali

Reputation: 69564

The only difference in two statements you have shown in your question is that in one statement you have used TWO PART NAME i.e [Schema].[Object] and in other statement you have used only the object name to call that procedure.

This has no effect on stored procedure's execution plans but its does make a difference on how sql server finds this stored procedure.

It is always best to use two part name as it eliminates the need to resolve schema and sql server has less work to do in order to execute this procedure.

Schema Name

On the other hand if you do not use the two part name for a stored procedure when you are calling it sql server has to go through a few steps to find the stored procedure which are as follows.

  1. It looks for that procedure in current database's sys schema.
  2. If it cant find it in sys schema than it looks in Caller's Default Schema.
  3. If it is not there than it look in the schema of the outer calling procedure if this procedure is being called from inside of another procedure.
  4. If it cant find it there than finally it looks for the procedure in dbo schema.

As you can see by just adding the schema name when calling a procedure can save some work for sql server therefore it is always best practice to use schema name when calling a procedure in sql server.

Execution Plan

When we execute a Sql Query in Sql server, Sql Server goes through 4 steps in order to successfully execute the query.

  1. Parses syntax.
  2. Translate to Query tree.
  3. Creates an Execution plan.
  4. Execute.

In a stored procedure's case, 1st two steps only take place when the stored procedure is created.
3rd step takes place when the stored procedure is executed for the very first time.
and finally 4th step takes place for every subsequent execution.

Now the Creation of execution plan can take place for a number of reasons. Some of them are

  • Stored Procedure has not been executed for a while and the cached plan has been flushed from the cache memory.
  • An index has been dropped or added to the underlying table.
  • Statistics has been updated.
  • Executed stored procedure with recompile option.

Upvotes: 2

dean
dean

Reputation: 10098

To answer your original question, try this:

create procedure dbo.proc_test
@i int
as
select @i
go

exec proc_test 1
exec dbo.proc_test 2
go

select usecounts, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where text like '%proc_test%'
and text not like '%dm_exec_cached_plans%'

There is only one plan in the cache, as you can see.

For the complete discussion of plan cachability and reusability, pls see here:

http://technet.microsoft.com/en-us/library/ee343986%28v=sql.100%29.aspx

IMO it's too broad topic to discuss it at length here.

Upvotes: 3

Related Questions