Tuesday 12 January 2010

Rebuilding execution plans

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.