-1

I have a request to drop all the store procedure in SQL Server 2019, to start with I have generated the dynamic script to drop all the procedures which are more than 11000 using SSMS however, when I executed the statement it was running very slow almost a minute for single procedure so I decided to drop the procedures in batch say 100 to 500 and it went smooth.

It looks like the bulk operation is taking hell lot of time to execute, The CPU was exhausted during execution, the memory consumption was at the lower side.

Can anyone confirm what changes I have to perform ?

Find below few resource details

Total CPU - 4
MaxDop - 2
RAM - 64GB
Disk - SSD

I tried changing the parameter value for MaxDop TO 1 AND 0 still same result

greybeard
  • 2,249
  • 8
  • 30
  • 66
Vinu
  • 1
  • 1
    You forgot to include your script. – Thom A Jun 13 '23 at 10:18
  • 1
    11000 is a *lot* of procedures. Dropping a procedure means SQL Server has to invalidate all referenced cached plans. Changes to *what* exactly? you have not included your existing process. – Stu Jun 13 '23 at 10:19
  • 1
    "2 RAM" what? 2 RAM sticks? What size are these stick? 64MB? 16GB? – Thom A Jun 13 '23 at 10:20
  • What is your question anyway? It seems you've found that if you drop them in batches of 100 to 500 instead of batches of 11000 things work better so what are you asking? With the 11K were you doing 11K separate statements or `DROP PROC P1, P2, P3` with 11K entries BTW? – Martin Smith Jun 13 '23 at 10:23
  • @stu , This was just an example however, all the bulk operation on this instance takes time like exporting only functions or only procedures through generate script also takes more time. – Vinu Jun 13 '23 at 10:34
  • @ThomA script which i have used --SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];' FROM sys.procedures p – Vinu Jun 13 '23 at 10:36
  • Use the [edit] feature please, @Vinu . – Thom A Jun 13 '23 at 10:38
  • 1
    Very long scripts need to be parsed and algebrized, so you'd do well to run it in batches. since you don't usually drop a lot of procedures, i don't think you need to change anything on your server to account for that – siggemannen Jun 13 '23 at 11:47

2 Answers2

1

You can try doing it in a transaction

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(
      CAST(
        'DROP PROCEDURE ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ';' 
        AS nvarchar(max)), N'
')
    FROM sys.procedures p
    JOIN sys.schemas s ON s.schema_id = p.schema_id
);

EXEC sp_executesql @sql;

COMMIT;
Charlieface
  • 52,284
  • 6
  • 19
  • 43
-2

Simplest :

DECLARE @SQL nvarchar(max) = N'';
SELECT @SQL = @SQL + 'DROP PROCEDURE [' + ROUTINE_SCHEMA +'].[' + ROUTINE_NAME +'];'
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_TYPE = 'PROCEDURE';
EXEC (@SQL);
SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • 2
    String aggregation using `@variable = @variable +` is a documented anti-pattern and can cause incorrect results. Also need to use `QUOTENAME` for correct escaping. And `INFORMATION_SCHEMA` is for compatibility only, and doesn't always give the correct data. How does this answer improve what OP is using already? – Charlieface Jun 13 '23 at 14:55
  • QUOTENAME is absolutly unecessary until only ISO standard SQL views are used. No it is not an anti pattern in this case... ! – SQLpro Jun 13 '23 at 14:57
  • 2
    See https://dbfiddle.uk/vn5hDO1D where `QUOTENAME` makes a difference. And see https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver16#c-antipattern-use-of-recursive-variable-assignment – Charlieface Jun 13 '23 at 15:12
  • @Charlieface you have already says that in another thread... I do not care of useless things... – SQLpro Jun 13 '23 at 15:23