SQL Server works slow after enable XP_CMDShell even disable until restart service

Question

2 days ago I created a function that needs INSERT and UPDATE, and I enabled XP_CMDShell and do it by running a script.

Afterwards, SQL Server works very slow for SELECT commands. Even very simple SELECT statement that runs after INSERT via separated command.

I tested this behaviour on other databases and it giae same result that SELECT runs after some minutes.

Also, I tested it on 2 other hosts with SQL Server 2014 and the result was the same.

Function that I created to get value of it within another select statement:

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 is user defined type to hold persian date time by Assembly.

Gnr.RevercePersianDate function to convert string persian date to JalaliDate. [Prg].[intGetLastDateForDelayedProcessProgram] other function to calculate specified date by Holidays that I think dose not issue.

UPDATE 1

I test function codes by pass parameter values by inline declare and set test values. Then, user Sql Server Profiler to check script of function, that see scripts stops on this line SET @CurrentDate = dbo.GetCurrentJalaliDate() that use function of Assembly. Execution running without any error and not responding!! Note: I test SELECT dbo.GetCurrentJalaliDate() that responding very fast without error!

UPDATE 2

I call function [Prg].[intCheckDelayedProcessProgram] within SP and inside a complex SELECT statement, that can't call SP in select, so just solution for me was define function. Code run in local server that is together with all data and other SP and functions. If there is alternative for CURSOR help me to rewrite function with it.

Answer 1

Instead of using xp_cmdshell to execute queries against SQL Server, change your code to use stored procedures, user-defined functions, or even dynamic T-SQL called using sp_executesql. If you need to execute code against a different server, use a linked server.

Finally, consider rewriting your code not to use a cursor if possible. Set-based operations in SQL Server are far more efficient.

Since these topics are all extremely in-depth topics on their own, there is no way I can flesh out an answer here, but I hope these links help. Good luck.

Answer 2

You should definitely find a better way than doing DML through a function. You don't need to attach this process to a SELECT statement. You can just dump the results of the query to a temp table and use a CURSOR to iterate over that (just like you are doing in this function), calling a stored procedure to do what you are currently doing in this function.

However, at the very least, you should add some options to your cursor so that they don't lock the base table(s):

DECLARE cursor_pdppt CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
        FOR ...

And, immediately after the CLOSE cursor_pdppt;, you should add:

DEALLOCATE cursor_pdppt;