主页 / user-137486

cilerler's questions

Martin Hope
cilerler
Asked: 2021-01-20 03:32:07 +0800 CST
  • 1

我从 切换sqlcmd到Invoke-SqlCmd，突然我的 RAISERROR 消息消失了。当我用 运行它时sqlcmd，它们会出现。有什么办法让它们可见Invoke-SqlCmd吗？

RAISERROR (N'Hello World',0,1) WITH NOWAIT;

Invoke-SqlCmd -Server $env:MSSQL_SERVERNAME -Username $env:MSSQL_USERNAME -Password $env:MSSQL_PASSWORD -Database "mydatabase" -Query "EXEC dbo.myprocedure" -OutputSqlErrors $true;

/opt/mssql-tools/bin/sqlcmd -S $env:MSSQL_SERVERNAME -U $env:MSSQL_USERNAME -P $env:MSSQL_PASSWORD -d mydatabase -Q "EXEC dbo.myprocedure";
sql-server powershell
Martin Hope
cilerler
Asked: 2017-10-29 12:46:38 +0800 CST
  • 10

我必须从 221+ 百万行表中删除 16+ 百万条记录，而且速度非常慢。

如果您分享使以下代码更快的建议，我将不胜感激：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @BATCHSIZE INT,
        @ITERATION INT,
        @TOTALROWS INT,
        @MSG VARCHAR(500);
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;

BEGIN TRY
    BEGIN TRANSACTION;

    WHILE @BATCHSIZE > 0
        BEGIN
            DELETE TOP (@BATCHSIZE) FROM MySourceTable
            OUTPUT DELETED.*
            INTO MyBackupTable
            WHERE NOT EXISTS (
                                 SELECT NULL AS Empty
                                 FROM   dbo.vendor AS v
                                 WHERE  VendorId = v.Id
                             );

            SET @BATCHSIZE = @@ROWCOUNT;
            SET @ITERATION = @ITERATION + 1;
            SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
            SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);             
            PRINT @MSG;
            COMMIT TRANSACTION;
            CHECKPOINT;
        END;
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
       AND @@TRANCOUNT > 0
        BEGIN
            PRINT 'There is an error occured.  The database update failed.';
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO

执行计划（仅限 2 次迭代）

在此处输入图像描述

VendorId是PK和non-clustered，这里脚本不使用聚集索引。还有 5 个其他非唯一、非聚集索引。

任务是“删除另一个表中不存在的供应商”并将它们备份到另一个表中。我有 3 张桌子，vendors, SpecialVendors, SpecialVendorBackups. 尝试删除表SpecialVendors中不存在的Vendors记录，并备份已删除的记录，以防我做错了，我必须在一两周内将它们放回去。

sql-server performance