I have a SP who's execution time went from 5 minutes > 20 minutes > 30 minutes > 53 minutes over four days
Waits were showing increasing CPU and suspended status
I isolated a single query that pegs the CPU
UPDATE thing.table SET YYYYMM = CASE WHEN DAY(SnapshotDate) = 1 OR SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table) THEN CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT) ELSE NULL END
I ran it again, adding
WITH (RECOMPILE) at the end - no difference
UPDATE STATISTICS thing.table - no difference
It would be interesting to run it and get the actual plan but I don't want to peg the CPU for an hour. I checked
sys.dm_exec_cached_plans but it appears to only have the estimated plan not the actual plan
I rewrote using
CONVERT instead of
FORMAT (because I am suspicious of new things) - no difference
So I rewrote like this and took execution back down to a few seconds:
BEGIN TRAN; UPDATE thing.table SET YYYYMM = NULL; UPDATE thing.table SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT) WHERE ( DAY(SnapshotDate) = 1 OR SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table) ); COMMIT TRAN;
The table has about 150,000 records in it. It's quite possible that it recently had a lot more records dumped in it, skewing statistics, but why would
UPDATE STATISTICS not fix that?. It takes a daily snapshot and possibly the number of records increased due to end of month.
So the questions are:
- Is actual query plan stored anywhere?, to save me running it interactively?
- Normally when a query suddenly takes forever it stale statistics but that doesn't seem to be the case here
This is my version of SQL Server
Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Here are the slow and fast query plans. No suprise they are different because they are doing different things:
I notice slowpoke uses an loop join and fasty uses a hash match.
I notice the small leg of the loop join has filter
[Expr1006]=DB.thing.table.[SnapshotDate]. Maybe that wasn't so small anymore?