Runaway SQL query

Scott,

Thanks for your feedback. What SolarWinds products do you have installed (Toolset, Orion, Kiwi, etc)? You reference an upgrade to 10.5.0.79 which would indicate the SolarWinds Toolset product. However, those stored procedures do not exist in the Toolset product. If we can identify which product you are having trouble with we can better route your comments to the appropriate team to get the issue resolved.

--Steve

From our techs:

Can you please open a support ticket so we can investigate and see what is going on

One of our techs is going to do so.

Thanks.

This workaround works quite well:

CREATE NONCLUSTERED INDEX [IX_T_D]
ON [dbo].[Traps]
(
  [DateTime]
)
WITH (FILLFACTOR = 70);
GO

CREATE TABLE #tmpdel ([TrapID] bigint);

DECLARE @date datetime, @rowcountvar bigint;
--NB deletes everything up to 24 hours ago from now
--   change the -1 to number of days previous data to keep
SET @date = DATEADD(d, -1, GETDATE());

Nextchunk:
TRUNCATE TABLE #tmpdel;
INSERT INTO #tmpdel ([TrapID])
  SELECT TOP 10000 [TrapID] FROM [dbo].[Traps] WHERE [DateTime] < @date;

DELETE FROM [dbo].[Traps]
WHERE [TrapID] IN (SELECT [TrapID] FROM #tmpdel);

SELECT @RowCountVar = @@ROWCOUNT;

DELETE FROM [dbo].[TrapVarBinds]
WHERE [TrapID] IN (SELECT [TrapID] FROM #tmpdel);

IF @RowCountVar = 10000 GOTO NextChunk;

DROP TABLE #tmpdel;
GO

Wasn't too taxing on the CPU, finished in under 15 minutes for a 44 million row TrapVarbinds table & 5 million row Traps table.

Edit: If regular maintenance had to be unceremoniously halted (we had to restart our SQL Server to kill the running maintenance), you will end up with the TrapVarBinds table full of orphaned records (i.e. no matching TrapID in the Traps table).  The following command will delete those orphaned records in batches of 1,000,000 (you'll need to run it multiple times, set up a loop, or just remove the TOP (1000000) bit):

DELETE TOP (1000000) FROM [dbo].[TrapVarBinds]
WHERE [TrapID] NOT IN (SELECT [TrapID] FROM [dbo].[Traps]);

This takes about 10-20 seconds per batch on our system (64-bit VM with 6GB RAM, SAN-attached).  Your mileage may vary.  I suggest starting with a batch of 10,000 first to monitor impact on system (especially if it is shared with other systems).

It seems to max degree of parallelism setting can have an adverse effect as well.  I was looking at query plans for the delete process and found that when max degree of parallelism was set to 0 (default), a parallel query plan was being created and it would be doing an index scan of IX_TrapNodeID on the Traps table rather than using the primary key index.  Setting max degree of parallelism to 1 changes this (effectively allows any query to only use 1 CPU) and makes the subquery used in the delete a lot more efficient (as far as I can tell).  This also depends on the number of rows in the Traps & TrapVarBinds tables.