Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Tempdb spill columns in sys.dm_exec_query_stats should account for batch mode operator spills

I am testing against SQL Server 2017 RTM-CU4. As far as I can tell, only tempdb spills associated with row mode operators are tracked in the new columns for tempdb spills in sys.dm_exec_query_stats: last_spills, total_spills, max_spills, and min_spills. Spills for batch mode operators are not included. None of the documentation that I can find mentions this as a limitation, so I assume that this is a bug.

Batch mode execution brings additional challenges around memory grant management and tempdb spills are common for the workloads that I look at. Adaptive query memory feedback is not always sufficient to resolve all spills. Please track batch mode spills in sys.dm_exec_query_stats. If it makes sense to add it as a separate column that's fine.

A reproduction of the issue is below.

-- batch mode test
DROP TABLE IF EXISTS #t_cci;

CREATE TABLE #t_cci (
ID BIGINT,
FILLER VARCHAR(1000),
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO #t_cci WITH (TABLOCK)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

SELECT TOP (1) ID, FILLER
FROM (
SELECT TOP (987654321) ID, FILLER
FROM #t_cci
ORDER BY ID DESC
) t
ORDER BY ID;

-- use whatever query you like to look at sys.dm_exec_query_stats
SELECT TOP (25)
last_execution_time, last_grant_kb, last_elapsed_time
, last_spills, total_spills, max_spills, min_spills
FROM sys.dm_exec_query_stats
WHERE last_grant_kb <> 0
ORDER BY last_execution_time DESC;

-- row mode test
DROP TABLE IF EXISTS #t_no_cci;

CREATE TABLE #t_no_cci (
ID BIGINT,
FILLER VARCHAR(1000)
);

INSERT INTO #t_no_cci WITH (TABLOCK)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

SELECT TOP (1) ID, FILLER
FROM (
SELECT TOP (987654321) ID, FILLER
FROM #t_no_cci
ORDER BY ID DESC
) t
ORDER BY ID;

-- use whatever query you like to look at sys.dm_exec_query_stats
SELECT TOP (25)
last_execution_time, last_grant_kb, last_elapsed_time
, last_spills, total_spills, max_spills, min_spills
FROM sys.dm_exec_query_stats
WHERE last_grant_kb <> 0
ORDER BY last_execution_time DESC;

32 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Joe Obbish shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • David commented  ·   ·  Flag as inappropriate

    This is really, really, really disappointing. No one is going to use "new" features if they're not treated as first class citizens. I've been scratching my head for ages wondering why these new columns on the DMVs weren't showing up the rather serious spills I was experiencing.

Feedback and Knowledge Base