sql server 2017 – Query Processor

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.

Adaptive Joins were publicly introduced in the CTP 2.0, I advise you to read a post by Joe Sack Introducing Batch Mode Adaptive Joins to get know about this feature, because I will give only a brief introduction before making a deep dive into the Adaptive Join internals.

Read…

SQL Server 2017: Statistics to Compile a Query Plan

Posted on May 10, 2017 by Dmitry Pilugin Posted in SQL Server 2017, Uncategorized

While preparing the post about Adaptive Joins, I’d like to share a quick post about the hidden gem in SQL Server 2017 CTP 2.0, discovered recently. In this short post, we will look at how you can determine what statistics are used by the optimizer during a plan compilation in SQL Server 2017.

Read…

SQL Server 2017: Sort, Spill, Memory and Adaptive Memory Grant Feedback

Posted on May 9, 2017 by Dmitry Pilugin Posted in Adaptive Query Processing, memory, query execution, query plan, Uncategorized

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.

Read…

SQL Server vNext: Interleaved Execution for mTVF

Posted on April 19, 2017 by Dmitry Pilugin Posted in Adaptive Query Processing, Uncategorized

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.

Read…