CODEWITHSUNDEEP
sql-servert-sqlsql-jobarithabort
mark jerrom
mark jerrom

Reputation: 41

SQL Job runs slow after reindex (and adding/removing SET ARITHABORT ON)

I have a SQL 2019 Server that is a multi-tenanted environment, so I've got lots of identical (apart from the data) customer databases on it.

There is a need to aggregate some data every 30 minutes in each of these databases, so there's a SQL job that runs in a separate database and creates a table of customer database names to loop through and then generates some dynamic SQL to run against each one in turn. I've omitted the generation of the DB names but this is the code that loops:

DECLARE @StartTime DATETIME,
        @EndTime DATETIME,
        @EnterpriseId INT,
        @Error NVARCHAR(MAX);

SELECT @AutoId = MIN(AutoId) FROM @DBs;

WHILE @AutoId IS NOT NULL
BEGIN
    SELECT @DatabaseName = DatabaseName, @EnterpriseId = EnterpriseId, @DatabaseId = DatabaseId FROM @DBs WHERE AutoId = @AutoId

    IF @DatabaseId IS NOT NULL
    BEGIN
        BEGIN TRY
            SET @dynSql = 'USE ' + @DatabaseName + ';';
            SET @dynSql = @dynSql +  'EXEC Nightly_CustomerRewards_TransactionEntry_Update;';
            
            SET @StartTime = GETDATE();
            PRINT @dynSql;
            EXEC(@dynSql)
            SET @EndTime = GETDATE();
            
            UPDATE dbo.Enterprise SET AggregationsDuration = DATEDIFF(SECOND, @StartTime, @EndTime) WHERE EnterpriseId = @EnterpriseId
        END TRY

        BEGIN CATCH
           SELECT @Error = CONCAT(@DatabaseName,': ', ERROR_MESSAGE(), ' In ' , ERROR_PROCEDURE(), ' at ' , FORMAT(ERROR_LINE(), 'N0')) 
           PRINT @Error;
        END CATCH
    END

    SELECT @AutoId = MIN(AutoId) FROM @DBs WHERE AutoId > @AutoId
END

When the job was implemented (on a Monday), it completed in about 30 seconds and didn't have any significant impact on the CPU of the server, and it continued this way for the rest of the week. In the early hours of Sunday morning, the DB reindex ran and after that point the job then took around 2 minutes to complete and there was a huge spike in the CPU.

After much googling, I found something that mentioned setting "SET ARITHABORT ON" in the SQL job. I did this and hey presto the job went back to 30 seconds and no CPU pressure.

Fast forward to the next Sunday and after the reindex ran again, the same thing happened again - the job went back to running slowly and the CPU spikes re-occurred. I commented out the "SET ARITHABORT ON" line and once again it went back to running quickly with no CPU pressure.

It's been like this for the past few weeks where I have to add in/remove the arithabort statement after every reindex depending on what the last state was!

Has anyone got any suggestions as to what is causing this and how I can fix it?

Thanks

Upvotes: 0

Views: 92

Answers (1)

Charlieface
Charlieface

Reputation: 72194

It's really hard to say without further information regarding query plans, which for some reason you refuse to give us.

But it looks like all your ARITHABORT statement is doing is causing a recompile, because the code has changed. This in turn seems to make it up fresh statistics information.

You can do this much more easily by adding the following statement to the end of your indexing job:

EXEC sp_recompile N'dbo.YourProcName';

Upvotes: 1

Related Questions