#PurchaseOrderProducts is a realistic depiction of your real table but with less data (and that your real table is also a heap), then I get this plan.
This is a pretty reasonable plan, where I wouldn't necessarily expect it to be that bad.
There are a few things we could do to make this better, including:
- Batch-mode for rowstore is enabled (via hacks) in SQL Server 2016 if you include a CCI in your query, even if it isn't used. This can likely make the window function better. This gets me a batch-mode operator for everything but the table scan, and cleans up a lot of the aggregation.
- Creating an appropriate index that your window function can use will help as well by removing the sort (the index maintains a sorted order). A clustered index on the
Id column moves all of the cost onto the index scan. A non-clustered index on
Product (order didn't matter in my testing) does the same. You'll have to
INCLUDE any columns you want in the output, however - otherwise it will just table scan, or if you force it to use the index it'll key lookup. That won't be great for performance.
If we look at all of these side-by-side, we notice that the non-clustered without an
INCLUDE is the worst by far, and the rest all look about the same.
You may notice that the batch-mode query is also using the non-clustered index; getting all of the plans side-by-side without indices is a pain. If you use the batch-mode hack without any indices then it will still have to sort the data, but it can do that in batch mode which will provide a nice performance bump. This is also good if you rely on the table being a heap elsewhere (e.g. parallel inserts, minimal logging, etc).
You may also notice that the plans using the clustered index vs non-clustered index have no apparent difference (besides the index they use). This is because all of the columns in the table are selected in both queries, and all indices include all columns. You can get substantial performance and memory savings if you only need a subset of columns and have a good non-clustered index; this might be helpful for your real data if the temp table isn't representative.
Batch mode w/ rowstore table
DROP TABLE IF EXISTS #FakeCciForRowstoreBatchMode;
CREATE TABLE #FakeCciForRowstoreBatchMode
__zzDontUseMe bit NULL,
INDEX CCI CLUSTERED COLUMNSTORE
DROP TABLE IF EXISTS #PurchaseOrderProducts;
CREATE TABLE #PurchaseOrderProducts
INSERT INTO #PurchaseOrderProducts
( 1, 'Item A', 5 ),
( 2, 'Item B', 1 ),
( 3, 'Item C', 8 ),
( 4, 'Item D', 2 ),
( 5, 'Item E', 1 );
FROM ( SELECT Id,
SUM( Quantity ) OVER ( ORDER BY Id ) RunningQty
LEFT OUTER JOIN #FakeCciForRowstoreBatchMode
ON 1 = 0 ) P
WHERE P.RunningQty - P.Quantity < 12;
Indices I created for #2 (plans generated using
CREATE CLUSTERED INDEX whatever ON #PurchaseOrderProducts( Id ASC )
CREATE NONCLUSTERED INDEX whatever2 ON #PurchaseOrderProducts( Id ASC ) INCLUDE( Quantity, Product )
CREATE NONCLUSTERED INDEX whatever3 ON #PurchaseOrderProducts( Id ASC ) INCLUDE( Product, Quantity )
CREATE NONCLUSTERED INDEX whatever4 ON #PurchaseOrderProducts( Id ASC )