Each time a stored procedure is executed for the first time SQL Server creates an execution plan for the procedure. The execution plan, simply put, is a road map that instructs SQL as to the most efficient way to go about collecting the data that is being queried.
From time to time this map can become outdated. There are a number of reasons why an execution plan might need to be manually rebuilt but the most common factor is data growth within the tables that the procedure references. If a certain table only had 1,000 records when the initial execution plan was built but has grown significantly since, the existing execution plan may no longer be the most efficient means for collecting the data. This is generally a result of changes to the statistics of your indexes as a result of data growth. Though this post discusses how to manually recompile a stored procedure, SQL will also automatically recompile procedures whenever a table referenced by the stored procedure has undergone a physical design change. Restarting SQL Server will also result in all procedures being automatically recompiled.
Fortunately Microsoft has included a system procedure which can be used to clear the cached execution plan by recompiling the stored procedure.
The following script can be used to recompile a specific stored procedure which will clear the execution plan from the cache. In this example I am recompiling the stored procedure named
update_HR_profile in the
USE [HumanResourcesDB] --specify database
EXEC sp_recompile 'update_HR_profile' --specify stored proc name
Once the stored procedure has been recompiled a new execution plan will be created the next time the procedure is executed.
Once the procedure has successfully been recompiled, you should see the following message:
Object 'update_HR_profile' was successfully marked for recompilation.
It’s always a good idea to execute the stored procedure prior to running the recompile and view the actual execution plan as well as save it so you can compare the old execution plan with the new one once the recompile script has been run.
In order to view the execution plan of a stored procedure or query, click the Include Actual Execution Plan button on the menu in Management Studio as noted in the screenshot below.
You can also use the keyboard shortcut Ctrl+M.
The screenshot below displays an example of an execution plan. This is only a portion of the overall execution plan. Depending on the complexity of the procedure or query as well as the number of tables being referenced, the execution plan can be a bit lengthy.
Simply put, what you will be looking for when analyzing an execution plan are nodes that show a higher percentage of the overall cost of the query. Though this is only the first step to resolving potential performance issues, this should provide some insight as to where the resources are bottle-necking and give you a good idea of where to start.
With a little luck a quick recompile of the stored procedure will resolve the issue. However, if you are still seeing performance concerns, continue to review the execution plan and step through the stored procedure. In many cases, a poorly written query will cause significant performance hits. If a query is poorly written then the execution plan will also be inefficient as it is relying on the poorly written query. Tuning the query may allow SQL to find a better road map for retrieving the data.