Dropping All SP's taking time - SQL server 2019 (SSMS)

Question

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

Answer 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;

Answer 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);