英文:
Dropping All SP's taking time - SQL server 2019 (SSMS)
问题
我有一个请求，要删除SQL Server 2019中的所有存储过程，首先我已经生成了一个动态脚本来删除所有超过11000个存储过程的存储过程，使用SSMS，但是，当我执行该语句时，它运行得非常慢，几乎需要一分钟来删除单个存储过程，所以我决定分批删除存储过程，比如每次删除100到500个，这样运行得很顺利。
似乎大容量操作需要花费很长时间来执行，执行期间CPU资源耗尽，内存消耗较低。
是否有人可以确认我需要进行哪些更改？
以下是一些资源详细信息：
总CPU - 4
MaxDop - 2
RAM - 64GB
硬盘 - SSD
我尝试将MaxDop参数值更改为1和0，结果仍然相同。
英文:
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
答案1
得分: 1
你可以尝试在事务中执行它
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;
英文:
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;
答案2
得分: -2
DECLARE @SQL nvarchar(max) = N'';
SELECT @SQL = @SQL + 'DROP PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + '];'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';
EXEC (@SQL);
英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论