One way that SQL Server optimizes stored procedure execution time is by caching the execution plan. This functionality is built-in to SQL Server and is done every time that you create a stored procedure.
What Is An Execution Plan?
An execution plan in SQL Server is simply the way that SQL Server will go about getting the data for the client. When a query is executed in SQL Server, it will generate an execution plan. This execution plan is a set of steps that is the most efficient way to get this data from the database for the user.
How Do You Know If You Need To Rebuild An Execution Plan?
Okay, this is not very scientific. Usually what I’ve found in the past is that when a stored procedure runs fast one day… and the next day it is running slow… one cause might be that the execution plan needs to be rebuilt.
How To Rebuild An Execution Plan?
If you think that a stored procedure in your database has an execution plan that is not the most efficient, here are a couple easy ways to rebuild the execution plan. Keep in mind that the following queries do not actually rebuild the execution plan. They merely force the stored procedure to rebuild the execution plan next time that it is executed.
Rebuild For One Specific Stored Procedure
In the example below, CustOrderHist is the name of the stored procedure we want to rebuild the execution plan for.
EXEC sp_recompile 'CustOrderHist'
Rebuild All Execution Plans For All Stored Procedures On Your Server
NOTE: The WITH NO_INFOMSGS option will suppress a generic message that comes up after executing the FREEPROCCACHE command.
DBCC FREEPROCCACHE WITH NO_INFOMSGS
Why Does This Happen?
As data changes in your database, the data in the index will change as well. As the statistics for an index change, what used to be the fastest way to pull data may no longer be the fastest way.
Automatic Rebuild Of The Execution Plan
Of course SQL Server does not require you to always rebuild the execution plans manually. There are triggers in the database that will cause an automatic rebuild of the execution plan.
- Alterations to the table, view, stored procedure, or indexes used by the stored procedure
- Updating statistics on the table or index that the stored procedure uses
- Dropping an index used by the stored procedure
- A lot of inserts or deletes from a table (causing a large change in the keys)
- Executing a stored procedure with the WITH RECOMPILE option
Reference: http://technet.microsoft.com/en-us/library/ms181647.aspx – sp_recompile (Transact-SQL)
Reference: http://technet.microsoft.com/en-us/library/ms174283(v=sql.105).aspx – DBCC FREEPROCCACHE (Transact-SQL)