Looking for the cmd to Recompile ALL procs across ALL DBs on a single SQL Server

Question

Looking for the cmd to Recompile ALL procs across ALL DBs on a single SQL Server

Express12

SSCoach

Points: 18514
More actions
May 13, 2020 at 1:36 pm

#3751149

This command will update ALL stats on ALL DBs on a single SQL Server:
exec sp_MSForEachDB 'use ?; if DB_ID(''?'') > 4 exec sp_updatestats;'
I'm looking for a similar, powerful command to RECOMPILE ALL procs across ALL DB's on a single SQL Server. I know this cmd recompiles ALL procs for 1 DB:
USE [myDatabase];
GO
EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";
GO
any suggestions are greatly appreciated!
BT

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

DBCC FREEPROCCACHE;

Understand that there could be a period of some pretty high activity and some slowness until all the procs being used are recompiled on their first runs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Express12 SSCoach Points: 18514 More actions · Answer 2

thx for the suggestin Jeff. I'd really prefer NOT to free the cache.. Looking for the command to mark the procs for RECOMPILE -- across all DB's..

BT

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 3

Express12 wrote:

thx for the suggestin Jeff. I'd really prefer NOT to free the cache.. Looking for the command to mark the procs for RECOMPILE -- across all DB's..

Whether you free the cache - or mark all objects for recompile - it is doing the same thing. Upon the next execution of the code - it will be recompiled and placed in the procedure cache. The existing cached item will be overwritten/removed...

If you really want to affect only stored procedures, then you need to write the code to build the list of stored procedures in each database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

SQL Server has the object code. The procedure code is stored in sys.sql_modules (https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql?view=sql-server-ver15), but there is no facility to retrieve this and recompile it. The nature of that platform doesn't make this easy. You could retrieve items from here, but that would be cumbersome.

You can use sp_recompile (https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-recompile-transact-sql?view=sql-server-ver15) and pass in an object name. This might be the best way to do this for certain objects. You can write code that will loop through a list of objects and call sp_recompile for each. You likely need some code like:

DECLARE @s VARCHAR(100) = 'dbo.GetPopularTags';
DECLARE @cmd VARCHAR(500);

SET @cmd = 'sp_recompile ''' + @s + '''';
EXEC (@cmd);

The dbo.GetPopularTags is a proc I have. I could wrap this in some loop that includes all procs in a database if I need to do this. You can also clear the cache for a database only and all procs will get recompiled.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Express12 wrote:

thx for the suggestin Jeff. I'd really prefer NOT to free the cache.. Looking for the command to mark the procs for RECOMPILE -- across all DB's..

Like Jeffrey Williams states, it doesn't clear buffer memory (data in memory). It only causes code to recompile and basically works the same as if you marked all the code objects individually, as what you're trying to do. And, what you're trying to do will effectively clear Proc Cache, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Express12 SSCoach Points: 18514 More actions · Answer 6

this is valuable feedback. thanks a million, very much appreciated!

BT

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Thanks for your feedback. Much appreciated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)