andyfrench.info

To force SQL Server to rebuild its execution plans for a particular database run the following SQL:

DECLARE @intDBID INTEGER SET @intDBID = 
(SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'DatabaseNameHere')
DBCC FLUSHPROCINDB (@intDBID)

To rebuild execution plans for all databases run the following SQL:

DBCC FREEPROCCACHE

You can see the execution plans for a procedure by using the SET command:

USE [DatabaseNameHere]
GO

SET SHOWPLAN_ALL OFF 
GO

EXEC [WidgetQueries_RecentlyAddedContent] 0
GO

You can also force a recompilation for a specific procedure or trigger using sp_recompile stored procedure:

sp_recompile [ @objname = ] 'object'

There are many other options. Check the documentation for details.

• http://msdn.microsoft.com/en-us/library/ms174283%28SQL.90%29.aspx – FREEPROCCACHE
• http://msdn.microsoft.com/en-us/library/ms181055%28SQL.90%29.aspx - Execution Plan Caching and Reuse
• http://msdn.microsoft.com/en-us/library/ms187735.aspx - SET SHOWPLAN_ALL
• http://msdn.microsoft.com/en-us/library/ms188722%28SQL.90%29.aspx - Query Tuning Recommendations
• http://msdn.microsoft.com/en-us/library/ms180765%28SQL.90%29.aspx - Displaying Execution Plans by Using the Showplan SET Options
• http://msdn.microsoft.com/en-us/library/ms181647%28SQL.90%29.aspx - sp_recompile