8

In a SQL Server database sp_recompile can be run on a stored procedure to update the execution plan. I would like to run this on all stored procedures in a database. Also, I would like to run its equivalent on all table-valued functions but I do not know which sys procedure to run.

Is it possible to do this without manually typing out a sp_recompile line for all of the stored procedure names in SQL Server Management Studio? Likewise for the table valued functions?

I believe I need to do this because the VM SQL Server has had its memory significantly increased but I am only seeing a marginal increase in execution time. The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.

1
  • Regarding clustered index seek performance, you are probably right performance cannot be improved more if it's a trivial query that selects a single row by unique key, However, with a non-unique key, performance also depends how many rows are actually touched during the scan, – Dan Guzman Sep 29 '17 at 14:56
9

You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help:

DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF'));
DECLARE @name SYSNAME;
OPEN C;
FETCH NEXT FROM C INTO @name;
WHILE @@FETCH_STATUS=0 BEGIN
    EXEC sp_recompile @name;
    FETCH NEXT FROM C INTO @name;
END;
CLOSE C;
DEALLOCATE C;

or you could produce ad-hoc SQL and run that via EXEC, takes less code which might be marginally more efficient:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_recompile '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF');
EXEC (@sql);

(though I find this form sometimes throws people due to looking set-based but building the string up iteratively, and not being a standard SQL pattern)

Another set of objects that might be a similar concern here is views. You can similarly mark them as needing to be reassessed to make sure stored plans and other meta-data is not stale with sp_refreshview, by small modifications to either the cursor or ad-hoc SQL methods shown above:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_refreshview '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('V');
EXEC (@sql);

The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.

There is sometimes more to optimisation than preferring seeks over scans and so forth, sometimes an index scan is more efficient than many executions of seek operations, and the cost estimates upon which the percent figures you are looking at are calculated are that (estimates) at best (a useful guide but sometimes far from at all accurate).

While "throw more memory at it" can help some database performance issues, at least temporarily, if your bottlenecks are very CPU bound rather than memory and/or IO bound then adding more memory will have very little effect.

5
  • This is what I was looking for. (+1) while we wait to see if anything else turns up. Thanks for the performance comments. – Edmund Sep 29 '17 at 14:24
  • Execution time reduced to 2 minutes from 3 minutes. – Edmund Sep 29 '17 at 14:51
  • That execution time reduction is almost certainly not due to recompiling. Much more likely, you have blocking affecting throughput. – Hannah Vernon Sep 29 '17 at 17:01
  • 3
    Unfortunately. this solution does not work with schemas, and does not work if a procedure has special characters in its name. I added another solution below. – SQL Police Apr 16 '20 at 8:45
  • Down vote due to answer not including schema – AntonK Dec 10 '20 at 3:00
4

If you add memory (even if it's a Hot Added to a VM), and increase Max Server Memory to match, your plan cache will clear out.

That is effectively 'recompiling' all of those things you mentioned, because they won't have a stored plan in cache to re-use. SQL Server will have to build a new one.

You may not have ever set Max Server Memory though. If you're not sure about that, you can run DBCC FREEPROCCACHE to clear out the plan cache.

You do this at your own risk in production. I can't guarantee the new plan will be better.

Memory doesn't solve every performance problem in SQL Server, and a Seek isn't necessarily the finish line of performance tuning.

If you need help with a specific query, you should ask a separate question.

1
  • It looks as if the DB admins did increase the max memory if view the server properties in SSMS. Many thanks for your performance comments. (+1) – Edmund Sep 29 '17 at 14:22
4

This solution is based upon the other answer here, but takes schemas into account and considers special characters in the name. It recompiles all procedures, functions and inline functions in all schemas.

create procedure dbo.RecompileAllProcedures
as
begin
    declare cur cursor for 
    (
        select quotename(s.name) + '.' + quotename(o.name) as procname
        from 
           sys.objects o
           inner join sys.schemas s on o.schema_id = s.schema_id
        where  o.[type] in ('P', 'FN', 'IF')
    );

    declare @procname sysname;

    open cur;
    fetch next from cur into @procname;
    while @@fetch_status=0 
    begin
        exec sp_recompile @procname;
        fetch next from cur into @procname;
    end;
    close cur;
    deallocate cur;
end;

It can then be called as follows:

exec dbo.RecompileAllProcedures

PS: I'm a big fan of Allman coding style ;)

EDIT: Improved the procedure by using quotename which is safer in case the name contains a square bracket.

0
0

I know its an old question, but there's another documented approach you can follow.

The Query Optimizer checks statistics on tables before deciding to either use the current Query Execution Plan or compile a new QEP.

The QO already "does" the statistics work to decide over the QEP, but you can update statistics on your own, which automatically signals QO to recompile on the next run.

That way you can avoid running a stored procedure just to recompile it, because it will be recompiled on the next run. You can update statistics table by table, or you can update for the whole database with

EXEC sp_updatestats;

sources:

https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver15

-2

Building on David Spillett's answer, if running SQL Server 2017 or newer you can use the String_Agg function and further simplify:

declare @sql nvarchar(max) = (select String_Agg('exec sp_recompile '''+[Name]+'''', Char(10)) from sys.Objects where [Type] in ('P', 'FN', 'IF'));
exec (@sql);
1
  • Why are people down-voting this? – N8allan May 20 '20 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.