Trivial Plans And Columnstore Indexes

Erik Darling warns us that trivial plans against clustered columnstore indexes could lead to row execution rather than batch execution:

Let’s look at one query with a few variations.

SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM dbo.t1 AS t
WHERE t.Id > 0 AND t.Id < 3;

The plan for it is alright. It’s fairly straightforward and the query finishes in about 170ms.

We can see from the graphical execution plan that it’s been Simple Parameterized. SQL Server does this to make plan caching more efficient.

Check out the entire post.

Related Posts

Logical Equivalence and Query Tuning

Erik Darling didn’t warn us that there would be math: Often when query tuning, I’ll try a change that I think makes sense, only to have it backfire. It’s not that the query got slower, it’s that the results that came back were wrong different. Now, this can totally happen because of a bug in previously used […]

Read More

SQL Server Execution Plan Operators

Bert Wagner takes us through some of the more common execution plan operators: Spools come in a variety of types, but most of them can be summarized as operators that store an intermediary result table in tempdb. SQL Server often uses spools to process complex queries, transforming the data into a tempdb worktable to allow […]

Read More

Categories

June 2017
MTWTFSS
« May Jul »
 1234
567891011
12131415161718
19202122232425
2627282930