| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_recompile (Transact-SQL) |
Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they're run. |
markingmyname |
maghan |
wiassaf, randolphwest |
08/21/2024 |
sql |
system-objects |
reference |
|
|
|
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they're run. It does this by dropping the existing plan from the procedure cache, forcing a new plan to be created the next time that the procedure or trigger is run. In a [!INCLUDE ssSqlProfiler] collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_recompile [ @objname = ] N'object'
[ ; ]
The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. @objname is nvarchar(776), with no default.
-
If @objname is the name of a stored procedure, trigger, or user-defined function, the stored procedure, trigger, or function will be recompiled the next time that it's run.
-
If @objname is the name of a table or view, all the stored procedures, triggers, or user-defined functions that reference the table or view will be recompiled the next time that they're run.
0 (success) or a nonzero number (failure).
sp_recompile looks for an object in the current database only.
The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they're compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions might lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.
Proactive execution of this stored procedure is usually unnecessary. [!INCLUDE ssNoVersion] automatically recompiles stored procedures, triggers, and user-defined functions when advantageous. There are various reasons the database engine might choose to recompile objects. Most commonly, automatic recompilation follows changes to the underlying cardinality estimate because of automatic or manual statistics updates.
Recompiling a stored procedure with every execution is one of the less efficient ways to combat query plan issues caused by parameterization. The feature Parameter Sensitive Plan optimization introduced in [!INCLUDE sssql22-md] attempts to mitigate this problem automatically. In prior versions, instead of calling sp_recompile with each execution, consider:
- Append the WITH RECOMPILE option to the query, requiring a code change.
- Apply the
WITH RECOMPILEoption with a plan guide. - Apply the
WITH RECOMPILEoption with a Query Store hint without making code changes. - For more information, see Resolving queries with parameter sensitive plan problems.
Requires ALTER permission on the specified object.
The following example causes stored procedures, triggers, and user-defined functions that act on the Sales.Customer table to be recompiled the next time that they're run.
USE AdventureWorks2022;
GO
EXEC sp_recompile N'Sales.Customer';
GO