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…
Troubleshooting with windows debugger
There is a lot of information about how SQL Server works: documentation, books, blogs etc. But sometimes you can’t find the information you need. Windows Debugger (WinDbg) and public debug symbols might be helpful in this case. I usually use them for learning about how does the Query Processor work. However, this post is not about query processing.
In this post I’m going to share a real-world example from my recent practice where some of the internal’s knowledge helped me to reveal the problem and make a quick fix. I hope you find this small investigation interesting.
Read…
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: How to Get a Parallel Plan
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.
SQL Server 2017: Statistics to Compile a Query Plan
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.
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.
SQL Server vNext: Scalar Subquery Simplification
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.
SQL Server vNext: Columnstore in-place updates
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.
SQL Server vNext: Columnstore Indexes and Trivial Plan
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.