Sql-Server

啟用 XP_CMDShell 甚至禁用後 SQL Server 工作緩慢，直到重新啟動服務

  • December 22, 2019

2 天前，我創建了一個需要INSERTand的函式，我通過執行腳本UPDATE啟用並完成了它。XP_CMDShell

之後，SQL Server 對SELECT命令的執行速度非常慢。甚至通過單獨的命令SELECT執行的非常簡單的語句。INSERT

我在其他數據庫上測試了這種行為，幾分鐘後執行結果相同SELECT。

此外，我在其他 2 台使用 SQL Server 2014 的主機上對其進行了測試，結果是相同的。

我為在另一個 select 語句中獲取它的值而創建的函式：

ALTER FUNCTION [Prg].[intCheckDelayedProcessProgram] 
(
    @SalesOrderProductID INT,
    @MainProductTreeID INT,
    @ProductTreeID INT,
    @ProcessId INT,
    @additionalDays INT = 2,
    @currentDateReverceString VARCHAR(10) = NULL
)
RETURNS INT
AS
BEGIN
   --declare @SalesOrderProductID INT = 40957,
   --  @MainProductTreeID INT = 93758,
   --  @ProductTreeID INT = 93758,
   --  @ProcessId INT = 4472,
   --  @additionalDays INT = 2,
   --  @currentDateReverceString VARCHAR(10) = null -- '30/09/1398'

DECLARE @ProduceDailyProgramProductTree_Id INT, @ProgramQuantity INT, @startDate JalaliDate,
       @CurrentDate JalaliDate, @lastDate JalaliDate, @additionalDate JalaliDate, @holiDaysCount INT;
IF(@currentDateReverceString IS NULL OR @currentDateReverceString = '')
   SET @CurrentDate = dbo.GetCurrentJalaliDate();
ELSE
   SET @CurrentDate = Gnr.RevercePersianDate(@currentDateReverceString);

IF (@additionalDays IS NULL)
   SET @additionalDays = 2;

DECLARE @allDelayedItemsCount INT = 0;
BEGIN
   DECLARE @sql NVARCHAR(4000), @cmd VARCHAR(4000);
   DECLARE cursor_pdppt CURSOR
       FOR SELECT pdppt.ID, pdppt.ProgramQuantity, pdppt.[Date] FROM Prg.ProduceDailyProgramProductTree pdppt
       WHERE pdppt.SalesOrderProductID = @SalesOrderProductID
               AND pdppt.MainProductTreeID = @MainProductTreeID
               AND pdppt.ProductTreeID = @ProductTreeID
               AND pdppt.Process = @ProcessId
               AND ISNULL(pdppt.IsDelayed, 0) = 0
   OPEN cursor_pdppt;
   FETCH NEXT FROM cursor_pdppt INTO @ProduceDailyProgramProductTree_Id, @ProgramQuantity, @startDate;
   WHILE @@FETCH_STATUS = 0
       BEGIN
           SET @lastDate = Gnr.RevercePersianDate([Prg].[intGetLastDateForDelayedProcessProgram](@startDate, @additionalDays, @CurrentDate, 1));
           IF (@CurrentDate.GetDate() > @lastDate.GetDate())
           BEGIN
               IF ((SELECT COUNT(*) FROM Prg.ProduceDailyOperation pdo WHERE pdo.ProduceDailyProgramProductTreeID = @ProduceDailyProgramProductTree_Id AND pdo.Process = @ProcessId
                       AND pdo.[Date].GetDate() > @lastDate.GetDate()) = 0)
               BEGIN
                   SET @allDelayedItemsCount = @allDelayedItemsCount + @ProgramQuantity;
               END
               ELSE
               BEGIN
                   SET @allDelayedItemsCount = @allDelayedItemsCount +
                       (@ProgramQuantity - 
                           (SELECT SUM(pdo.ProducedQuantity) FROM Prg.ProduceDailyOperation pdo 
                               WHERE pdo.ProduceDailyProgramProductTreeID = @ProduceDailyProgramProductTree_Id 
                                   AND pdo.Process = @ProcessId AND pdo.[Date].GetDate() <= @lastDate.GetDate()));
               END;

               SELECT @sql = 'UPDATE [Prg].[ProduceDailyProgramProductTree] SET [IsDelayed] = 1 WHERE ID = ' + CONVERT(VARCHAR(10), @ProduceDailyProgramProductTree_Id);
               SELECT @cmd = 'sqlcmd -S ' + @@SERVERNAME + ' -d ' + DB_NAME() + ' -Q "' + @sql + '"'
               EXEC MASTER..XP_CMDSHELL @cmd , 'no_output'
           END;
           FETCH NEXT FROM cursor_pdppt INTO @ProduceDailyProgramProductTree_Id, @ProgramQuantity, @startDate;
       END;
   CLOSE cursor_pdppt;

   IF (@allDelayedItemsCount > 0)
   BEGIN
       IF (EXISTS(SELECT 1 FROM Prg.ProduceDailyProgramProductTreeDelayed pdppt
                       WHERE pdppt.SalesOrderProductID = @SalesOrderProductID
                           AND pdppt.MainProductTreeID = @MainProductTreeID
                           AND pdppt.ProductTreeID = @ProductTreeID
                           AND pdppt.Process = @ProcessId))
       BEGIN
           SELECT @allDelayedItemsCount = @allDelayedItemsCount 
               + (SELECT pdpptd.DelayedQuantity FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd
                   WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID
                       AND pdpptd.MainProductTreeID = @MainProductTreeID
                       AND pdpptd.ProductTreeID = @ProductTreeID
                       AND pdpptd.Process = @ProcessId
                       AND ISNULL(pdpptd.Active, 0) = 1
                       AND ISNULL(pdpptd.IsDeleted, 0) = 0);

           SELECT @sql = 'UPDATE [Prg].[ProduceDailyProgramProductTreeDelayed] SET DelayedQuantity = ' 
                               + CONVERT(VARCHAR(10), @allDelayedItemsCount) + 'WHERE SalesOrderProductID = ' 
                               + CONVERT(VARCHAR(10), @SalesOrderProductID) + 'AND MainProductTreeID = ' 
                               + CONVERT(VARCHAR(10), @MainProductTreeID) + 'AND ProductTreeID = ' 
                               + CONVERT(VARCHAR(10), @ProductTreeID) + 'AND Process = ' 
                               + CONVERT(VARCHAR(10), @ProcessId) +';';
       END
       ELSE
       BEGIN
           SELECT @sql = 'INSERT INTO [Prg].[ProduceDailyProgramProductTreeDelayed] (SalesOrderProductID, MainProductTreeID, Process, ProductTreeID, DelayedQuantity, Active, IsDeleted) VALUES ('
                    + CONVERT(VARCHAR(10), @SalesOrderProductID) + ', ' 
                    + CONVERT(VARCHAR(10), @MainProductTreeID) + ', '
                    + CONVERT(VARCHAR(10), @ProcessId) + ', '
                    + CONVERT(VARCHAR(10), @ProductTreeID) + ', '
                    + CONVERT(VARCHAR(10), @allDelayedItemsCount) + ', ''1'', ''0'')';
       END;

       SELECT @cmd = 'sqlcmd -S ' + @@SERVERNAME + ' -d ' + DB_NAME() + ' -Q "' + @sql + '"'
       EXEC MASTER..XP_CMDSHELL @cmd , 'no_output'
   END
   ELSE
   IF(EXISTS(SELECT 1 FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd
           WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID
           AND pdpptd.MainProductTreeID = @MainProductTreeID
           AND pdpptd.ProductTreeID = @ProductTreeID
           AND pdpptd.Process = @ProcessId
           AND ISNULL(pdpptd.Active, 0) = 1
           AND ISNULL(pdpptd.IsDeleted, 0) = 0))
   BEGIN
       SELECT @allDelayedItemsCount = 
               (SELECT pdpptd.DelayedQuantity FROM Prg.ProduceDailyProgramProductTreeDelayed pdpptd
                   WHERE pdpptd.SalesOrderProductID = @SalesOrderProductID
                       AND pdpptd.MainProductTreeID = @MainProductTreeID
                       AND pdpptd.ProductTreeID = @ProductTreeID
                       AND pdpptd.Process = @ProcessId
                       AND ISNULL(pdpptd.Active, 0) = 1
                       AND ISNULL(pdpptd.IsDeleted, 0) = 0);
   END;
END;
RETURN @allDelayedItemsCount;
END

JalaliDate是使用者定義的類型，由大會保存波斯日期時間。

Gnr.RevercePersianDate將字元串波斯日期轉換為 JalaliDate 的函式。 [Prg].[intGetLastDateForDelayedProcessProgram]其他按我認為不會發布的假期計算指定日期的功能。

更新 1

我通過內聯聲明和設置測試值傳遞參數值來測試功能程式碼。然後，使用者 Sql Server Profiler 檢查函式的腳本，看到腳本停止在SET @CurrentDate = dbo.GetCurrentJalaliDate()使用 Assembly 函式的這一行。執行執行沒有任何錯誤並且沒有響應！注意：我測試SELECT dbo.GetCurrentJalaliDate()響應非常快，沒有錯誤！

更新 2

我在一個不能呼叫的複雜語句中呼叫函式[Prg].[intCheckDelayedProcessProgram]，所以對SP我來說，唯一的解決方案是定義函式。程式碼在與所有數據和其他 SP 和功能一起執行的本地伺服器中執行。如果有替代方法可以幫助我用它重寫功能。SELECT``SP``select``CURSOR

不要使用 usingxp_cmdshell對 SQL Server 執行查詢，而是將程式碼更改為使用儲存過程、使用者定義的函式，甚至是呼叫 using的動態 T-SQLsp_executesql。如果您需要針對不同的伺服器執行程式碼，請使用連結伺服器。

最後，如果可能，請考慮重寫程式碼以不使用游標。SQL Server 中基於集合的操作要高效得多。

由於這些主題本身都是非常深入的主題，因此我無法在這裡充實答案，但我希望這些連結有所幫助。祝你好運。

您絕對應該找到比通過函式執行 DML 更好的方法。您不需要將此過程附加到SELECT語句中。您可以將查詢結果轉儲到臨時表並使用 CURSOR 對其進行迭代（就像您在此函式中所做的那樣），呼叫儲存過程來執行您目前在此函式中所做的事情。

但是，至少，您應該向游標添加一些選項，以便它們不會鎖定基表：

DECLARE cursor_pdppt CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
       FOR ...

並且，在 之後CLOSE cursor_pdppt;，您應該立即添加：

DEALLOCATE cursor_pdppt;

引用自：https://dba.stackexchange.com/questions/256052

相關問答

Sql-Server

STRING_SPLIT() 函式的結果是否以確定的順序返回？

  • November 15, 2021
檢視問答
Sql-Server

添加選擇時超出自引用標量函式嵌套級別

  • April 29, 2020
檢視問答
Sql-Server

SQL Server 2017 儲存過程和 TVF 性能問題

  • December 17, 2019
檢視問答
Sql-Server

SQL Server 2017：交錯執行如何工作？

  • October 4, 2017
檢視問答
Sql-Server

隨機錯誤 824 - 所有環境

  • August 17, 2022
檢視問答
Sql-Server

無法使用主機文件中的名稱連接到連結伺服器

  • August 1, 2022
檢視問答