SQL Server 2017: Adaptive Join Internals

Posted on by Posted in Adaptive Query Processing, SQL Server 2017, Uncategorized

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: How to Get a Parallel Plan

Posted on by Posted in query plan, SQL Server 2017

SQL Server chooses parallel plans based on the costing (there are also some other factors that should be met for the plan that it can go parallel). Sometimes serial plan is slightly cheaper than a parallel, so it is assumed to be faster and picked by the optimizer, however, because the costing model is just a model it is not always true (for a number of reasons, enlisted in Paul’s article below) and parallel plan runs much faster.

Read…

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

Posted on by 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 by 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…

SQL Server vNext: Scalar Subquery Simplification

Posted on by Posted in Uncategorized

Nowadays a lot of developers use Object-Relational Mapping (ORM) frameworks. ORM is a programming technique that maps data from an object-oriented to a relational format, i.e. it allows a developer to abstract from a relational database (SQL Server, for example), use object-oriented language (C#, for example) and let an ORM to do all the “talks” to a database engine by generating query texts automatically. ORMs are not perfect, especially if they are used in a wrong way. Sometimes they generate inefficient queries, e.g. a query with redundant expressions. SQL Server has a mechanism to struggle with that inefficiency called a query simplification.

Query simplification is a pre-optimization phase that is run during the query compilation, but before the actual optimization search is started. During that phase the optimizer applies simplification rules against a query tree. The simplification rule represents an algorithm that transforms some portion of a query tree or the whole tree into a simpler form. In this post, we will talk about the new optimizer rule in SQL Server vNext – CollapseIdenticalScalarSubquery.

Read…

SQL Server vNext: Columnstore in-place updates

Posted on by Posted in columnstore, query execution, query plan, Uncategorized

In this post, I continue exploration of SQL Server vNext and we will look at the nonclustered columnstore index updates.

Columnstore index has some internal structures to support updates. In 2014 it was a Delta Store – to accept new inserted rows (when there will be enough rows in delta store, server compresses it and switchs to Columnstore row groups) and a Deleted Bitmap to handle deleted rows. In 2016 there are more internal structures, Mapping Index for a clustered Columnstore index to maintain secondary nonclustered indexes and a deleted buffer to speed up deletes from a nonclustered Columnstore index.

Updates were always split into insert + delete. But that is now changed, if a row locates in a delta store, now inplace updates are possible. Another change is that it is now possible to have a per row (narrow) plan instead of per index (wide) plan.

Let’s make some experiments.

Read…

SQL Server vNext: Columnstore Indexes and Trivial Plan

Posted on by Posted in columnstore, query execution, query plan, Uncategorized

Not so long time SQL Server vNext was announced and issued as CTP. The most exciting announcement in that CTP was that SQL Server now supports Linux! This is awesome and I consider it to be great news for many people.

I am personally interested in the new features of query processing, and finally I had some time to install the vNext and dig a little bit into it. Currently it is CTP 1.2 available, and I will use this version for my experiments.

While exploring new extended events, I’ve found an interesting event compilation_stage_statistics and one of the columns of this event was trivial_plan_scanning_cs_index_discarded with the following description “Number of trivial plans discarded or could have been discarded which scan columnstore index”. That pushed me to do some investiagations of the topic.

Let’s try to make some experiments.

Read…

Scalar UDF Estimation and Project Normalization

Posted on by Posted in cardinality estimation, query plan, Uncategorized

In this post, we will continue to look at the cardinality estimation changes in SQL Server 2016. This time we will talk about scalar UDF estimation. Scalar UDFs (sUDF) in SQL Server have quite bad performance and I encourage you try to avoid them in general, however, a lot of systems still use them.

Read…