BPSORT sort waits mean that I have batch mode sorts . This is okay. However, I'm aware that batch mode sort spills are very slow . How can I tell if a query that appears to have high BPSORT waits is suffering from batch mode sort spills? Assume that I am on a live production server. This makes both Extended Events and getting actual execution plans awkward. Other tools, such as the plan cache and Query Store, are available.

In traditional usages of Microsoft SQL Server, exactly one row flows from one operator in a query plan to the next. In Batch Mode, huge performance gains are obtained by moving thousands of rows at a time between operators. SQL Server has supported this since SQL Server 2012. Does Postgres have anything like this?

Azure SQL Database - Standard Edition (S3 Service Tier) Why does Batch Mode only come into effect when using Aggregate Functions? DROP TABLE IF EXISTS dbo.TransCS CREATE TABLE dbo.TransCS ( Col1 INT ,Col2 AS Col1*2 ) CREATE CLUSTERED COLUMNSTORE INDEX CS_TransCS on dbo.TransCS; WITH L0 AS(SELECT 1 AS c UNION ALL SELECT 1), L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5) INSERT INTO dbo.TransCS (Col1) SELECT TOP (1000000) n FROM Nums ORDER BY n; Insert 1 Million rows Row Processing SELECT * FROM dbo.TransCS Execution Plan Batch Processing SELECT Col1, Col2, SUM(Col2)OVER () FROM dbo.TransCS Execution Plan Is there a way to utilise the performance benefits without using Agg functions? Documentation on this is thin.

The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening? SELECT a, p, s, v, m, n, SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition FROM ( SELECT a, p, s, v, m, n, RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank FROM #t /* A columnstore table with 1,500 rows */ ) x WHERE x.rank = 1 --Msg 8115, Level 16, State 2, Line 1521 --Arithmetic overflow error converting expression to data type int. **Full script** See this file for a fully contained reproduction script. **Query plan** Here is an annotated estimated query plan (full XML on Paste the Plan ). **Similar queries that execute successfully** If any of the following modifications are made, the error does not occur: * Use trace flag 8649 to prefer a parallel plan regardless of the cost threshold for parallelism * Use trace flag 9453 to disable batch mode * Use the COUNT aggregation function instead of the SUM function * Remove the WHERE x.rank = 1 predicate For example, this query executes successfully: SELECT a, p, s, v, m, n, SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition FROM ( SELECT a, p, s, v, m, n, RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank FROM #t /* A columnstore table with 1,500 rows */ ) x WHERE x.rank = 1 OPTION (QUERYTRACEON 9453/* Disable batch mode */)

When a columnstore index is being used in a query SQL Server is able to use batch mode. **Documentation is thin on what can run in batch mode and what can't.** Please look at the following (motivating) query plan where a surprising number of things executes in batch mode (green): (This is an estimated plan. I used the actual plan to verify that the actual execution mode is indeed batch.) Note, that only the build side of T1 uses a columnstore index. All probe inputs (T2 and T3) are rowstores. **Their data seems to transition into batch mode.** I always thought that batch mode is being used for the data stream that runs through the probe side only. It seems that data can transition into batch mode even if it does not originate from a columnstore index. That poses the question: Why doesn't SQL Server use batch mode for rowstore-only queries as well? Could be beneficial for some of them. Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode? Could we maybe add a zero row dummy table with a columnstore index to induce batch mode and realize performance gains? What exactly can run in batch mode as of SQL Server 2014?

I have a query of the following form: SELECT ... FROM ColumnstoreTable cs CROSS APPLY ( SELECT * FROM (VALUES ('A', cs.DataA) , ('B', cs.DataB) , ('C', cs.DataC) ) x(Col0, Col1) ) someValues This takes every row from a Columnstore-backed subquery (ColumnstoreTable) and multiplies those rows. This is essentially an UNPIVOT. The real query is larger than this. This part of the query feeds into other processing. The problem here is that this CROSS APPLY is implemented as a loop join which is a reasonable choice. Unfortunately, loop joins do not support batch mode. This part of the query is very performance critical and I suspect that running it in batch mode could be very beneficial to performance. How can I rewrite this query so that I do not transition out of batch mode? I did try using a temporary table instead of VALUES, but that did not change the fact that there is no equality join condition to hash join on.

A big query is running right now. The result of a batch mode hash join that spills to disk is streamed into a temp table using select into. The query is showing wait types HTDELETE and HTREPARTITION occasionally. The query is not using columnstore indexes. I'm quite sure that these wait types are normal for batch mode hash joins that spill to disk. I'm trying to understand what these wait types mean out of curiosity. Maybe this insight can help optimize the query or find problems. So what do these wait types mean and under what circumstances are they expected? No columnstore indexes. I'm using the left join ZeroRowCsTable on 0=1 trick to enable batch mode for row mode tables.