In this post I’m going to look at the new feature of SQL Server’s 2019 Database Engine – Batch Mode on Row Store. I start with a quick introduction and then look at some curious details, that I have found playing with this feature so far. At the moment of writing this post I use the last public version of SQL Server – SQL 2019 CTP 2.0.
Read…
Category Archives: Adaptive Query Processing
SQL Server 2017: Adaptive Join Internals
SQL Server 2017 brings a new query processing methods that are designed to mitigate cardinality estimation errors in query plans and adapt plan execution based on the execution results. This innovation is called Adaptive Query Processing and consist of the three features:
- Adaptive Memory Grant Feedback;
- Interleaved Execution;
- Adaptive Joins.
We have discussed two of them in the previous posts: Adaptive Memory Grant Feedback and Interleaved Execution.
In this post, we will discuss the last one – Adaptive Joins.
SQL Server 2017: Sort, Spill, Memory and Adaptive Memory Grant Feedback
Sorting is one of the key operations in query processing. SQL Server can achieve sorting by either reading data in an ordered fashion, for example, performing ordered Rowstore index scan or performing an explicit sort. If we want to get sorted data from a Columnstore index, the only option is to perform a sort explicitly with a Sort operator in a query plan, because a Columnstore index has no particular order, at least at the moment of writing this post.
Columnstore indexes were first introduced in SQL Server 2012, and starting from this version, we got a new query execution mode, called Batch Mode. Batch Mode was originally designed for column store indexes as an execution technique optimized to deal with a large number of rows. Operators, running in a traditional Row Mode, process one row at a time, operators in a Batch Mode process one batch of rows at a time, where a batch is a portion of approximately 900 rows.
In 2012 and 2014 a Sort operator used to run in a Row Mode only, however, starting from SQL Server 2016 the Sort for a Batch Mode was also implemented. In this post, we are going to look at some Batch Mode Sort peculiarities.
SQL Server vNext: Interleaved Execution for mTVF
In this post, we are going to look at the new feature in SQL Server vNext – interleaved execution. You need to install SQL Server vNext CTP 1.3 to try it, if you are ready, let’s start.
Now, when a CTP 2.0 of SQL Server vNext (SQL Server 2017) is out, you don’t need to turn on the undocumented TF described further, and the plans are also different, so the examples from this post use CTP.1.3, probably not actual at the moment (I was asked to hold this post, until the public CTP 2 is out and interleaved execution is officially announced). However, the post demonstrates Interleaved execution details and might be still interesting.