Execution plans

Query Trace Column Values in SQL Server

April 23, 2018 by Dmitry Piliugin

Sometimes, when I saw expressions like ‘Expr1002’ or ‘WindowCount1007’ or something similar in the columns Output List of a query plan, I asked myself, is there a way to project those columns into the final result to look at the values. That question first came to me out of curiosity when I was playing with window aggregate functions and a Window Spool plan operator in SQL Server 2012, I wanted to look into the Window Spool to understand, how it performs an aggregation.

Interestingly, that SQL Server 2016 CTP3.0 allows us to look deep inside into the iterator and observe the data flowing through it. Let’s turn on an “x-ray machine” and take a look.

Runtime Constants Sniffing in SQL Server

April 23, 2018 by Dmitry Piliugin

Most of the people know about the so-called “Parameter Sniffing”. This topic was discussed in many aspects in a number of great articles. It is interesting that not only parameters might be “sniffed” during the first execution, but also a runtime constant functions. Let’s look at the example.

Test Data

I will use a test server and administrator account to run the script below, be sure you have enough privileges on your test server if you want to try out the script below.

Few Outer Rows Optimization in SQL Server

April 20, 2018 by Dmitry Piliugin

In this blog post, we will look at one more Nested Loops (NL) Join Post Optimization Rewrite. This time we will talk about parallel NL and Few Outer Rows Optimization.

For the demonstration purposes, I will use the enlarged version of AdventureWorks2014. In the sample query, I will also use the trace flag (TF) 8649 – this TF forces parallel plan when possible and is very convenient here, as we need one for the demo. There are also a few other undocumented TFs: TF 3604 – direct diagnostic output to console, TF 8607 – get a physical operator tree, before Post Optimization Rewrite, TF 7352 – get a tree after Post Optimization Rewrite phase.

The sample query is asking for some data based on the period’s table.

Batch Sort and Nested Loop in SQL Server

April 18, 2018 by Dmitry Piliugin

Continuing my blog post series after 24HOP Russia “Query Processor Internals – Joins”. In this (and the next one) blog post, we will talk about the Nested Loop Post Optimization Rewrite optimizations.

Some of you may know that a Nested Loop join algorithm preserves order of the outer table.

SQL Server – Hash Join Execution Internals

April 17, 2018 by Dmitry Piliugin

Some time ago, on the 24HOP Russia I was talking about the Query Processor internals and joins. Despite I had three hours, I felt the lack of time, and something left behind, because it is a huge topic, if you try to cover it in different aspects in details. With the few next articles, I’ll try to describe some interesting parts of my talk in more details. I will start with Hash Join execution internals.

The Hash Match algorithm is one of the three available algorithms for joining two tables together. However, it is not only about joining. You may observe a complete list of the logical operations that Hash Match supports in the documentation:

SQL Server – Yet another X-Ray for the QP

April 16, 2018 by Dmitry Piliugin

This post is for the “optimizer” fan people. For those who are not afraid when they hear: “query transformation rules”. It this is you and you are interested in the optimizer internals – welcome.

Consider the following DB – opt. And the following query:

MTVF and CE Model Variation in SQL Server

April 5, 2018 by Dmitry Piliugin

This is a note about multi-statement table valued functions (MTVF) and how their cardinality is estimated in the new CE framework.

In the old CE framework the MTVF had fixed estimate of one row, in the new one the estimate is still fixed, however, now it is 100 rows. That’s the whole story. =)

Ascending Key and CE Model Variation in SQL Server

April 5, 2018 by Dmitry Piliugin

In this note, I’m going to discuss one of the most useful and helpful cardinality estimator enhancements – the Ascending Key estimation.

We should start with defining the problem with the ascending keys and then move to the solution, provided by the new CE.

Overpopulated Primary Key and CE Model Variation in SQL Server

April 4, 2018 by Dmitry Piliugin

In this blog post, we are going to talk about another cardinality estimation model enhancement in SQL Server 2014 – Overpopulated Primary Key (OPK).

Consider a fact table that contains information about some sales, for example, and a date dimension table. Usually, a fact table contains the data about the current year and past years, but a dimension table usually contains the data for the next few years also.

Join Containment Assumption and CE Model Variation in SQL Server

April 3, 2018 by Dmitry Piliugin

In this post we are going to talk about one of the model assumptions, that was changed in the new cardinality estimation mechanism in SQL Server 2014 – Join Containment Assumption.

You may find some information about this assumption in the Ian Jose’s blog post: Query Processor Modelling Extensions in SQL Server 2005 SP1, there you may find the description of the so-called simple assumption and base assumption. Another source of available information is a white paper from Joseph Sack Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

Filtered Stats and CE Model Variation in SQL Server

April 2, 2018 by Dmitry Piliugin

In this blog post, we are going to view some interesting model variation, that I’ve found while exploring the new CE.

A model variation is a new concept in the cardinality estimation framework 2014, that allows easily turn on and off some model assumptions and cardinality estimation algorithms. Model variations are based on a mechanism of pluggable heuristics and may be used in special cases. I think they are left for Microsoft support to be able to address some client’s CE issues pointwise.

Today we are going to view some interesting model variation, that creates filtered statistics on-the-fly. I should give a disclaimer here.

Performance tuning – Nested and Merge SQL Loop with Execution Plans

April 2, 2018 by Thomas LeBlanc

In this article, we will explore Nested and Merge SQL Loops in the SQL Execution plan from a performance tuning view.

Cardinality Estimation Framework Version Control in SQL Server

March 30, 2018 by Dmitry Piliugin

This is a small post about how you may control the cardinality estimator version and determine which version was used to build a plan.

The version of the cardinality framework is determined by the query database context, where the database has a specific compatibility level.

When you create a database in SQL Server 2014 it has the latest compatibility level equals 120 by default. If you issue a query in that database context, the new cardinality version will be used. You may verify this by inspecting the plan property “CardinalityEstimationModelVersion” of the root language element (the one with the green icon), SELECT, for example.

Cardinality Estimation Process in SQL Server

March 29, 2018 by Dmitry Piliugin

In this post, we are going to take a deeper look at the cardinality estimation process. We will use SQL Server 2014, the main concepts might also be applied to the earlier versions, however, the process details are different.

Calculators

The algorithms responsible for performing the cardinality estimation in SQL Server 2014 are implemented in the classes called Calculators.

Cardinality Estimation Concepts in SQL Server

March 28, 2018 by Dmitry Piliugin

In this blog post we are going to talk about the principles and the main concepts which are used by the optimizer to perform an estimation, and also, we will do a little bit math, so be prepared.

Base Statistics

A cardinality estimation mechanism, as a first step, usually uses base statistics to estimate the expected number of rows that should be returned from the base table. You may look at these statistics using DBCC command – DBCC SHOW_STATISTICS.

Cardinality Estimation Place in the Optimization Process in SQL Server

March 27, 2018 by Dmitry Piliugin

In this blog post, I’m going to look at the place of the Cardinality Estimation Process in the whole Optimization Process. We’ll see some internals, that will show us, why the Query Optimizer is so sensitive to the cardinality estimation. To understand that we should observe the main steps that a server performs when the query is sent for execution.

Plan Construction Process

Below you may see the picture of the general plan-building process in SQL Server from the moment when a server receives a query till the storage engine is ready to retrieve the data. Take a quick look first, and next I’ll provide explanations.

Cardinality Estimation Role in SQL Server

March 26, 2018 by Dmitry Piliugin

This post opens a series of blog posts dedicated to my observations of the new cardinality estimator in SQL Server 2014. But, before we jump to the new features, I’d like to provide some background, to make the next posts clearer.

We’ll start by discussing the role of Cardinality Estimation in SQL Server, trying to answer – what is it and why it is needed.

SQL Server Query Execution Plans for beginners – Clustered Index Operators

March 5, 2018 by Ayman Elnory

We have discussed how to created estimated execution plans and actual execution plans in various formats in my previous article SQL Server Query Execution Plan for beginners – Types and Options.

In this article we will continue discussing the various execution plan operators related to clustered indexes, and what they do, when do they appear and what happens when they do.

OpenQueryStore for SQL Server – Installation and configuration

January 24, 2018 by Marko Zivkovic

The OpenQueryStore is an Open source implementation of the popular Query store functionality introduced in SQL Server 2016 CTP2. The OpenQueryStore was first introduced in June 2017. Its main contributors are William Durkin and Enrico van de Laar

Understanding SQL Server query plan cache

January 18, 2018 by Ben Richardson

Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance.

The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa.

SQL Server Query Execution Plans for beginners – Types and Options

January 4, 2018 by Ayman Elnory

When a DBA is working with the SQL Server, he/she might sometimes say that the execution plan is like a black hole. As a matter of fact, though, you have to look at execution plans as weather forecasts.

In this article, I will be discussing various aspects of executions plans, types and common options.

SQL Server Query Store – Overview

December 29, 2017 by Marko Zivkovic

The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database. This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk.

How to use parallel insert in SQL Server 2016 to improve query performance

December 8, 2017 by Esat Erkec

Introduction

In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time.

SQL Server can execute queries in parallel
SQL Server creates a path for every query. This path is execution plan
The SQL Server query optimizer creates execution plans
SQL Server query optimizer decides the most efficient way for create execution plan

Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed.

How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs

December 1, 2017 by Ayman Elnory

Quite often developers put the responsibility of the server performance on the shoulders of DBAs. But to be honest, as a developer, it is your responsibility to write code which is efficient and optimal. However, when writing code, the developer often lacks the knowledge and/or experience about SQL Server to meet this requirement.

In this series, I will be discussing various aspects of query designing, keeping performance and scalability in mind. We will be addressing the most common concepts which we often see that negatively impact SQL code written by developers.

SQL query performance tuning tips for non-production environments

September 12, 2017 by Derik Hammer

It is a common misconception that you need real production data, or production like data, to effectively tune queries in SQL Server. I am going to explain how you can compile the same execution plans as what your production environment would compile, so that you can tune them in a non-production environment, gaining these benefits.