Monitoring databases for optimal query performance, creating and maintaining required indexes, and dropping rarely-used, unused or expensive indexes is a common database administration task. As administrators, we’ve all wished, at some point, that these tasks were simpler to handle.
Read more »
In the previous article, we described, in detail, the different stages that a submitted SQL Server query goes through and how it processed by the SQL Server Relational Engine. The SQL Server Relational Engine generates the Execution Plan and the SQL Server Storage Engine performs the requested data retrieval or modification process. In this article, we will discuss the different types and formats for SQL Server Execution Plans.
Read more »
Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data.
Read more »
One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan. Read more »
In this series of articles, we will navigate the SQL Server Execution Plan ocean, starting from defining the concept of the Execution Plans, walking through the types, components and operators of Execution Plans analyze execution plans and we’ll finish with how to save and administrate the Execution Plans.
When you submit a T-SQL query, you tell the SQL Server Engine what you want, but without specifying how to do it for you. Between submitting the T-SQL query to the SQL Server Database Engine and returning the query result to the end user, the SQL Server Engine will perform four internal query processing operations, to convert the query into a format that can be used by the SQL Server Storage Engine easily use to retrieve the requested data, using the processes assigned to the SQL Engine from the Operating System to work on the submitted query.
Read more »
Fixing bad queries and resolving performance problems can involve hours (or days) of research and testing. Sometimes we can quickly cut that time by identifying common design patterns that are indicative of poorly performing TSQL.
Read more »
Fixing and preventing performance problems is critical to the success of any application. We will use a variety of tools and best practices to provide a set of techniques that can be used to analyze and speed up any performance problem!
Read more »
In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of the SQL Server tables and indexes, the best practices to follow when designing a proper index, the group of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes and finally the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification. In this article, we will discuss how to tune the performance of the bad queries using SQL Server Indexes.
Read more »
As per Wikipedia, the Halloween problem was first discovered by Don Chamberlin, Pat Selinger, and Morton Astrahan, on Halloween day, 1976.
Read more »
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:
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 more »
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.
Prior to SQL Server 2017, there were two ways how you could do it, both undocumented and involving undocumented trace flags.
Read more »
This article explores SQL Sort, Spill, Memory and Adaptive Memory Grant Feedback mechanism in SQL Server.
Read more »
In this post, we are going to look at the new feature in SQL Server 2017 – interleaved execution. You need to install SQL Server 2017 CTP 1.3 to try it, if you are ready, let’s start.
Now, when a CTP 2.0 of 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 more »
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.
Read more »
In this post, I continue the exploration of SQL Server 2017 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 newly inserted rows (when there will be enough rows in delta store, server compresses it and switches 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 more »
Some time ago, SQL Server 2017 was released and issued as CTP. The most exciting release 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 SQL Server 2017 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 investigations of the topic.
Read more »
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.
I’ll use Microsoft sample DB AdventureworksDW2016CTP3 and write the following simple scalar function, it always returns 1, regardless of the input parameter. I run my queries against Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
Read more »
In this post we continue looking at the Cardinality Estimator (CE). The article explores some join estimation algorithms in the details, however this is not a comprehensive join estimation analysis, the goal of this article is to give a reader a flavor of join estimation in SQL Server.
The complexity of the CE process is that it should predict the result without any execution (at least in the current versions), in other words it should somehow model the real execution and based on that modeling get the number of rows. Depending on the chosen model the predicted result may be closer to the real one or not. One model may give very good results in one type of situations, but will fail in the other, the second one may fail the first set and succeed in the second one. That is why SQL server uses different approaches when estimating different types of operations with different properties. Joins are no exception to this.
Read more »
Now that we understand what Clustered Index Scan and Clustered Index Seek are, how they occur, and how to eliminate table scans in my previous article SQL Server Query Execution Plans for beginners – Clustered Index Operators, the next topic would be looking at Non-Clustered Indexes
Read more »
Yesterday I came across a question on one of SQL forums, that I may rephrase like:
“Does a query plan compilation depend on how busy SQL Server is”.
Before we go further, I should explicitly mention that we talk about a Compiled plan, not an Executable plan. Plan execution will of course depend on how busy server is, for example, the query may wait for the memory grant to start execution, or execution may be slow because there are no cached pages in the Buffer Pool etc.
However, the question was about a Compiled plan: does the shape of a plan depend on the server load.
From the first glance it should not. But…
Read more »
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.
Read more »
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.
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.
Read more »
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.
Read more »
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.
Read more »© 2022 Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy
