Script to Recompile All SQL Server Database Objects
August 19th 2008 04:39
Script to Recompile All SQL Server Database Objects
Problem
In some of your tips you reference sp_recompile as a stored procedure that needs to be executed during some maintenance related processes. After doing some basic research it looks like I can execute sp_recompile against a particular stored procedure or against a table so that all objects, typically stored procedures, that reference that object can be recompiled. I know that sp_updatestats exists to update statistics against all objects in the database. Does a similar stored procedure exist for sp_recompile?
Solution
sp_recompile is a system stored procedure in both SQL Server 2000 and 2005 that will recompile an object the next time it runs. Recompiling an object is advantageous when 'indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries' (Source = sp_recompile). In essence the query plan changes and the SQL Server optimizer may not be getting the best query plan based on the recent changes. So recompiling the stored procedures should yield a performance gain.
In a different scenario, the parameter used to build the query plan may be good for some queries and bad for others. If this is the case, it is necessary to determine when the stored procedure should be recompiled or determine if the performance is bad enough to have separated stored procedures to support the two different query plans.
Nevertheless, in SQL Server 2000 and 2005, I am not aware of a system stored procedure that will recompile all of the objects in a similar manner as sp_updatestats. As such reference the script below as as simple means to recompile all of the objects in the database.
Sample Code
CREATE PROCEDURE dbo.spEXECsp_RECOMPILE AS
/*
----------------------------- ----------------------------- ------------------
-- Object Name: dbo.spEXECsp_RECOMPILE
-- Project: SQL Server Database Maintenance
-- Business Process: SQL Server Database Maintenance
-- Purpose: Execute sp_recompile for all tables in a database
-- Detailed Description: Execute sp_recompile for all tables in a database
-- Database: Admin
-- Dependent Objects: None
-- Called By: TBD
-- Upstream Systems: None
-- Downstream Systems: None
--
----------------------------- ----------------------------- ----------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
----------------------------- ----------------------------- ----------------------------
--
*/
SET NOCOUNT ON
-- 1a - Declaration statements for all variables
DECLARE @TableName varchar(128)
DECLARE @OwnerName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @TableListTable table
(UIDTableList int IDENTITY (1,1),
OwnerName varchar(128),
TableName varchar(128))
-- 2a - Outer loop for populating the database names
INSERT INTO @TableListTable(OwnerName, TableName)
SELECT TOP 10 u.[Name], o.[Name]
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'U'
ORDER BY o.[Name]
-- 2b - Determine the highest UIDDatabaseList to loop through the records
SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable
-- 2c - While condition for looping through the database records
WHILE @TableListLoop > 0
BEGIN
-- 2d - Set the @DatabaseName parameter
SELECT @TableName = TableName,
@OwnerName = OwnerName
FROM @TableListTable
WHERE UIDTableList = @TableListLoop
-- 3f - String together the final backup command
SELECT @CMD1 = 'EXEC sp_recompile ' '[' @OwnerName '.' @TableName ']' char(13)
-- 3g - Execute the final string to complete the backups
-- SELECT @CMD1
EXEC (@CMD1)
-- 2h - Descend through the database list
SELECT @TableListLoop = @TableListLoop - 1
END
SET NOCOUNT OFF
GO
Sample Output
Object 'dbo.sysdiagrams' was successfully marked for recompilation.
Object 'dbo.ShoppingCartContents' was successfully marked for recompilation.
Object 'dbo.ShoppingCart' was successfully marked for recompilation.
Object 'dbo.Customer' was successfully marked for recompilation.
Object 'dbo.CreditCard' was successfully marked for recompilation.
Object 'dbo.Address' was successfully marked for recompilation.
Word of caution
Make sure you have a legitimate reason to recompile all of the objects in your database, especially during core business hours. Although the process is typically quick, when all of the objects are recompiled, the first execution will be a little slower than normal. The remaining executions should perform at the normal execution time. This should be similar behavior as when you restart the SQL Server services or Windows (reboot).
Problem
In some of your tips you reference sp_recompile as a stored procedure that needs to be executed during some maintenance related processes. After doing some basic research it looks like I can execute sp_recompile against a particular stored procedure or against a table so that all objects, typically stored procedures, that reference that object can be recompiled. I know that sp_updatestats exists to update statistics against all objects in the database. Does a similar stored procedure exist for sp_recompile?
Solution
sp_recompile is a system stored procedure in both SQL Server 2000 and 2005 that will recompile an object the next time it runs. Recompiling an object is advantageous when 'indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries' (Source = sp_recompile). In essence the query plan changes and the SQL Server optimizer may not be getting the best query plan based on the recent changes. So recompiling the stored procedures should yield a performance gain.
In a different scenario, the parameter used to build the query plan may be good for some queries and bad for others. If this is the case, it is necessary to determine when the stored procedure should be recompiled or determine if the performance is bad enough to have separated stored procedures to support the two different query plans.
Nevertheless, in SQL Server 2000 and 2005, I am not aware of a system stored procedure that will recompile all of the objects in a similar manner as sp_updatestats. As such reference the script below as as simple means to recompile all of the objects in the database.
Sample Code
CREATE PROCEDURE dbo.spEXECsp_RECOMPILE AS
/*
----------------------------- ----------------------------- ------------------
-- Object Name: dbo.spEXECsp_RECOMPILE
-- Project: SQL Server Database Maintenance
-- Business Process: SQL Server Database Maintenance
-- Purpose: Execute sp_recompile for all tables in a database
-- Detailed Description: Execute sp_recompile for all tables in a database
-- Database: Admin
-- Dependent Objects: None
-- Called By: TBD
-- Upstream Systems: None
-- Downstream Systems: None
--
----------------------------- ----------------------------- ----------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
----------------------------- ----------------------------- ----------------------------
--
*/
SET NOCOUNT ON
-- 1a - Declaration statements for all variables
DECLARE @TableName varchar(128)
DECLARE @OwnerName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @TableListTable table
(UIDTableList int IDENTITY (1,1),
OwnerName varchar(128),
TableName varchar(128))
-- 2a - Outer loop for populating the database names
INSERT INTO @TableListTable(OwnerName, TableName)
SELECT TOP 10 u.[Name], o.[Name]
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'U'
ORDER BY o.[Name]
-- 2b - Determine the highest UIDDatabaseList to loop through the records
SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable
-- 2c - While condition for looping through the database records
WHILE @TableListLoop > 0
BEGIN
-- 2d - Set the @DatabaseName parameter
SELECT @TableName = TableName,
@OwnerName = OwnerName
FROM @TableListTable
WHERE UIDTableList = @TableListLoop
-- 3f - String together the final backup command
SELECT @CMD1 = 'EXEC sp_recompile ' '[' @OwnerName '.' @TableName ']' char(13)
-- 3g - Execute the final string to complete the backups
-- SELECT @CMD1
EXEC (@CMD1)
-- 2h - Descend through the database list
SELECT @TableListLoop = @TableListLoop - 1
END
SET NOCOUNT OFF
GO
Sample Output
Object 'dbo.sysdiagrams' was successfully marked for recompilation.
Object 'dbo.ShoppingCartContents' was successfully marked for recompilation.
Object 'dbo.ShoppingCart' was successfully marked for recompilation.
Object 'dbo.Customer' was successfully marked for recompilation.
Object 'dbo.CreditCard' was successfully marked for recompilation.
Object 'dbo.Address' was successfully marked for recompilation.
Word of caution
Make sure you have a legitimate reason to recompile all of the objects in your database, especially during core business hours. Although the process is typically quick, when all of the objects are recompiled, the first execution will be a little slower than normal. The remaining executions should perform at the normal execution time. This should be similar behavior as when you restart the SQL Server services or Windows (reboot).
| 72 |
| Vote |
Add To: 
Comment by Anonymous
EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''