Wait Stats#1: SOS_SCHEDULER_YIELD

In order for you to have a happy life as someone responsible for a database, you need to ensure that it follows a strict diet consisting of well-designed queries and indexes, plenty of CPU, memory and disk capacity. Neglecting any of these aspects will compromise the entire system and consequently your happiness.

Regarding improving CPU usage, we can roll up our sleeves and start tuning our server, our queries and indexes, so they perform better and use less CPU. This is the preferable approach, because the alternative of adding processing power to SQL Server can be quite expensive. Nonetheless, it’s possible to reach a point where your CPUs are simply not enough to support your workload and tuning queries will feel like rearranging chairs on a sinking Titanic.

In theory there are two options when increasing CPU capacity:

  • Adding more CPUs, hence increasing the thread pool and improving parallel queries.
  • Get faster CPUs, which will improve serial query speed and thread consumption

In practice, the second option is becoming gradually less common because most people are in the cloud and using virtual machines, which typically imply less flexibility.

But, in order to confirm that the CPU is really the bottleneck, we need to have a closer look. Wait stats are a great starting point and in this specific matter, wait types like SOS_SCHEDULER_YIELD, CXPACKET, or THREADPOOL can give us pretty useful hints of what’s happening. It’s important to stress that waits are not intrinsically bad, they are an indivisible part of SQL Server functioning and we must be able to separate evil waits from benign waits.

In this post, we’re going to overview SOS_SCHEDULER_YIELD wait type. However, to have a clearer understanding about the underlying information, we must grasp some basic concepts, namely schedulers and the query lifecycle.

Schedulers and Query Lifecycle

A scheduler is the logical equivalent of a CPU, where worker threads are scheduled to actively carry out the tasks assigned to them. We can view scheduler information using the DMV sys.dm_os_schedulers. Inernally, schedulers have three components:

  • Processor: Phisycal or logical CPU core that is responsible for processing the thread. Each core will process only a thread at a time.
  • Waiter List: List that contains all threads that are waiting for a resource to became available. The threads in this list are in Suspended state (we’ll talk more about these states shortly). We can view information about these resource waits in the DMV sys.dm_os_waiting_tasks.
  • Runnable Queue: Encompasses all threads that are waiting for processor time (Runnable state). When priorities are not assigned to workload groups the processing order on this queue is First-In-First-Out. (more info on sys.dm_exec_requests).

Accordingly, these components are directly related to the states encompassed by the query lifecycle:

  • Running: Query is being processed on a CPU.
  • Runnable: Query is ready to execute, but it’s waiting for CPU time.
  • Suspended: Query is waiting on a particular resource to become available.

During one execution, a query can go through these states multiple times. This behavior is enforced by the cooperative (or non-preemptive) scheduling used by SQL OS. Basically, only one thread will be running on the scheduler at a given moment and each one has a quantum of 4 milliseconds, which represents the maximum consecutive CPU time for *any* of them. After these 4 milliseconds, the thread voluntarily yields the CPU and moves back into the Runnable state. Even if there aren’t other threads waiting, nor the necessity for the thread to wait for any resource (and consequently transitioning into Suspended state), the quantum is not exceeded.

It’s important to mention that preemptive function calls work differently, they are selfish and don’t like to cooperate. They are nothing more than calls to functions outside the realm of SQL OS, like Win32 API functions, where the waiting logic rules no longer apply. Under these circumstances, and before the external function is invoked, a sibling worker is signaled and given ownership of the scheduler, while the current thread continues to run. Then, when the thread goes preemptive, its quantum “ends” and no additional accounting is done for CPU usage until it reverts to non-preemptive scheduling. When the preemptive work is done and the thread transitions into cooperative scheduling again, it declares the end of its preemptive wait and goes into the runnable queue. If you want to know more about preemptive scheduling check this awesome blog post from Ewald Cress.

SOS_SCHEDULER_YIELD

This wait type occurs when a thread voluntarily yields the scheduler for other tasks to execute, waiting for its quantum to be renewed.

Since the thread doesn’t wait for any resource and it just voluntarily goes into the runnable queue, SOS_SCHEDULER_YIELD won’t imply resource wait time. This will create the signal wait time that will show how long the thread has waited in the runnable queue to get back in the running state again.

Problem?

Probably not. As we saw, SOS_SCHEDULER_YIELD just indicates that threads are exhausting their quantum, hence generally being a benign wait type. Even if this wait type is prevalent, if signal wait time is low, that just means that there are multiple threads running on the scheduler and you should look somewhere else in order to find the root case of your problems.

Nonetheless, when signal wait time is high things can get interesting. Frequent SOS_SCHEDULER_YIELD waits with high signal wait time may suggest that a substantial number of CPU intensive queries are fighting for CPU and each ends up waiting a long time to be signaled. In this case it’s probably a good idea to look for the top CPU consuming queries and analyze their execution plans.

Contrarily, less frequent waits with high signal time indicates that there are not many threads fighting for CPU time. In such case, an external source may be causing CPU pressure, either from preemptive function calls or something completely external to SQL Server.

The Execution Plan Saga #2: Scans

Scan operations are processed on account of data reading operators that differ according to the underlying data structure:

  • Clustered Index Scan: Scan is done on the leaf-level pages of a Clustered Index
  • Index Scan (NonClustered): Scan is done on the leaf-level pages of a Non-Clustered Index
  • Table Scan: Scan is done on a Heap (table without a Clustered Index)

A Scan often implies reading all pages of a specific index or heap, but there are a couple of reasons why SQL Server may choose to do a Scan instead of a Seek. e.g.:

  • Queries where the estimated number of rows to be read exceed the threshold from which SQL Server thinks it’s more efficient to do a Scan instead of a Seek.
  • There is no index to support a Seek operation
  • A non-Sargable predicate forces SQL Server to perform a Scan. This behavior was discussed in a previous post.

Scans are not always evil nor Seeks are always good. In the next sections we’ll briefly overview different types of scans as well as which properties can help us to have a better understanding of them. We’ll be focusing on rowstore format and it’s important to emphasize that Columnstore and Memory-Optimized Indexes will not behave entirely the same way. If you want to know more about them, check it here.

Index Scan

As already stated, this operator reads data from a non-clustered index. The general algorithm it’s quite simple:

  • First row (or batch if we’re on Batch Mode) is read.
  • If there’s a predicate, it is evaluated against the row(s) and matches are returned.
  • Next row / batch gets evaluated and the cycle repeats itself until there are no rows left.

These rows can be read in three distinct ways and this access method is chosen at execution time:

  • Allocation Order Scan: Reads data in an unspecified order. This method is faster for large tables but has a higher startup cost.
  • Ordered Forward Scan: Reads data according to the order specified on the index columns.
  • Ordered Backward Scan: Reads data in the inverse order specified on the index columns.

The predicate mentioned above is one of the properties that is useful to analyze when reading execution plans, as it represents the filter that was applied to the rows. If no predicate is specified, every row is returned. You can also have predicates that don’t filter any rows. I don’t know why, but you can.

The following image shows a query execution that led to a non-Clustered Index Scan. Below the execution plan some of the operator’s properties are visible.

The property that evidences which of the previously three presented access methods was chosen is Ordered. In this case, this property holds the value “False” which means that an Allocation Order Scan was done, because no specific order was implied. When this value is “True”, another property named Scan Direction will specify the direction of the ordered scan.

The last highlighted property, TableCardinality, shows the total number of rows in the index when the plan was compiled.

Estimates Vs Actuals

Although these aren’t specific to Index Scans, they are important to inspect when troubleshooting execution plans.

The above image shows some of these properties and their respective values. Knowing what each means is the key to have a clear picture of what’s going on.

Estimated Operator Cost: Total operator cost estimation, usually and as in this case, it’s equal to Estimated CPU Cost + Estimated I/O Cost. Another interesting fact is that, in this case, it’s also equal to Estimated Subtree Cost, simply because there are no other operators on the subtree.

Estimated Number of Executions: Estimated number of executions for the operator, in the current plan. Operators in the inner input of Nested Loops may cause values greater than 1, which is not the case.

Estimated Number of Rows per Execution (Formerly Known As “Estimated Number of Rows”): Number of rows returned per execution. Multiplying this number to Estimated Number of Executions will provide the estimated total number of rows.

Estimated Number of Rows to be Read:  Estimated number of rows to be read by the operator.

Number of Rows Read: Number of rows read by the operator during the index scan, which typically equals to the total number of rows in the index.  The difference between this property and the Actual Number of Rows property represents the number of rows that was read but not returned due to the Predicate property.

Actual I/O Statistics: I/O stats at the operator level, translating rows into more specific and measurable values. For example, the 357 Logical Reads presented, evidences that 357 8KB pages were read in this execution.

Actual Number of Rows for All Executions (FKA “Actual Number of Rows”): Total number of rows returned, across all executions

Actual Time Statistics: Actual time spent by this operator, divided into “Actual Elapsed CPU Time” and “Actual Elapsed Time

But There’s Life Beyond This

In addition to the list presented above, there are other properties that also might be helpful:

  • Output List: Shows the list of all columns regarding the rows returned by the operator.
  • Forced Index: Its value will be “True” if an index is explicitly hinted in the query text, otherwise will be “False”.
  • ForceScan: Its value will be true if FORCESCAN is explicitly hinted in the query text, otherwise will be “False”.
  • ForceSeek: Its value will be true if FORCESEEK is explicitly hinted in the query text, even though the optimizer ended up choosing to perform a scan. Otherwise, will be “False”.

Be aware that forcing Indexes, Scans or Seeks can lead to unwanted outcomes. Sure, they can be lifesavers and are great tools when applying quick fixes, but we’re fundamentally force SQL Server to choose a route, that over time may not be the best one.

Clustered Index Scan

Not surprisingly, in a Clustered Index Scan data is read from a clustered index. Its behavior is basically the same as in a non-Clustered Index Scan and all previous properties can be seen and used under the same premises.

Table Scan

Table Scan occurs when a scan is done over a Heap – a table without a clustered index. Its behavior is also similar to the previous scans but due to the different underlying structure, there are a couple of aspects to consider. Instead of having a B-Tree structure, with root, intermediate and leaf nodes, it just has unordered data pages that are not linked, hence sequential access needs to go through the Index Allocation Map (IAM) pages.

Heap structure (left) vs Index structure (right) Source

If records are updated on Heaps, in a way that they can’t fit on the current page no more, the row is moved to a new page, where is marked as “forwarded” and a “forwarding-pointer” to this new location is stored in the “old” page. Table Scans read forwarded rows when the scan encounters the original location, hence skipping them on their current location.

Heaps are suitable under specific conditions, because the absence of a clustered index means no overhead on index maintenance and less overhead on storage. Nonetheless, searching for specific data can be a pain in the buttocks, unless the Heap is not that large, or non-clustered indexes are created. The bottom line here is that you need to be careful, even though Heaps Don’t Lie. If you didn’t get this Shakira reference you are a lucky person. If you did, I’m sorry.

More Threads! Call the Proletariat

When the plan and specifically the Scan operator goes parallel, things get more interesting. Threads are listened for row requests and data gets returned to each thread that requested data and had already processed all data from last page received. We can also see more information of data distribution across threads in the operator properties.

Analyzing these numbers will help us to troubleshoot potential skewed distributions.

Parallelism in Table Scans is a bit different due to the specificities already discussed and, in theory, they are more prone to race conditions. See an awesome explanation from Hugo Kornelis here.

And that’s it! Thank you for your time and stay safe🖖

Aventura-profissional PNG from pngtree.com

The Execution Plan Saga #1: Getting Started

When we need to troubleshoot a query that is having performance issues, there are two possible routes: one is to empirically modify the query, pushing buttons and knobs until, eventually, things get better. The other is to analyze the execution plan and take specific, focused actions, based on the identified problematic areas. Sometimes both approaches can lead to similar outcomes and the first can even be faster, especially when we’re not used to analyze execution plans. Nevertheless, the latter is a much more consistent approach, simply because there will be times when blindly applying modifications won’t fix the problem and you’ll need to figure out the root cause, in order to solve it.

An execution plan is a blueprint, a set of algorithms and properties that SQL Server processes in order to retrieve the requested data. If we are able to understand these algorithms and their inherent properties, putting them into context, we will be incredibly more efficient solving problems.

Elements of an Execution Plan

As already mentioned, an execution plan comprehends operators, with their respective properties and data flows. Operators represent specific algorithms chosen by SQL Server to perform the logical operations that materialize the specified query. Hence, they serve multiple purposes:

  • Read Data
  • Combine Data
  • Group / Sort Data
  • Manipulate Data
  • Modify Data
  • Performance-Related

On the other hand, arrows represent the data flow between operators. But just these operators and arrows alone tell us little, we must analyze properties to have a proper understanding of what’s happening. Each operator has its own specificities, so, inevitably, they will hold distinct properties, that are relevant in the context of that specific operation.

Despite that, we can make some conclusions just by looking at the execution plan e.g. the width of data flow arrows, the cost of each operator, or the time spent on each operation. 

It’s important to understand that we can look to the plan in distinct moments:

  • Before Execution: Estimated Execution Plan
  • After / During Execution: Actual Execution Plan

The names may suggest that two separate plans are created, but that’s not the case. It’s actually the same plan, the only thing that differs is the amount of information available.

In the Estimated Execution Plan, we can see, for example, how many rows SQL Server estimates that a particular operator will return, but as this is prior to execution, we obviously won’t be able to see the actual number of rows returned.

To compare the estimated vs actual row count, we’ll need to look into the Actual Execution Plan. Since it comprehends runtime statistics, it’s evidently preferable to use it rather than the estimated plan to troubleshoot problems, however, in those dark moments when for some reason we’re unable to get the Actual Execution Plan, having the Estimate Execution Plan is much better than nothing.

Troubleshooting Guidelines

Examining an execution plan can be overwhelming if we don’t know what to look for. The following guidelines are based on a set of sequential steps proposed by Grant Fritchey on his book “SQL Server Execution Plans”

Warnings

When a yellow warning sign or a red exclamation mark arises attached to an operator, it means SQL Server is telling us that there is something we should check. Despite the fact that sometimes the behavior related to the warning message is not the root cause of the problem we’re trying to solve, often it is and it’s just there at plain sight – easy buck.

To view the warning message, you can hover the operator’s icon or open its properties.

First Operator

The first operator (typically SELECT, UPDATE, INSERT or DELETE) holds very relevant information about the entire plan. The following list include some properties that will frequently be helpful to us:

  • QueryTimeStats: Values for CPU time and elapsed time (in milliseconds).
  • MemoryGrantInfo: Information about query memory requirements. More details about each property can be viewed in a previous post.
  • WaitStats: Top wait stats on the query execution, including the number of waits (WaitCount) and the total wait time (WaitTimeMs).
  • Parameter List: All parameters, including data type, value at compile time (if the parameter was sniffed), and value at runtime (in an actual execution plan). These values will help us to understand how parameter sniffing impacted the query.
  • QueryHash: Hash value resultant from the query text. It is used by SQL Server to find plans for similar or equal queries.
  • QueryPlanHash: Hash value resultant from the execution plan. It is used by SQL Server to find plans for similar or equal execution plans.
  • Set Options: Values of specific SET options at compilation time. It’s important to look at these options because different value combinations generate different plans. Sometimes these variations are innocuous and almost invisible, but other times they might shape a completely different plan. For example, ARITHABORT is one of those options that can trigger very distinct behaviors on the execution plan, depending on its value.
  • Type of optimization: Can either be “TRIVIAL” or “FULL”. A “TRIVIAL” optimization means that there were no meaningful execution alternatives for the optimizer to evaluate. It is important to stress that this not related to workload volume. If we execute SELECT * FROM HugeTable, the query can run for minutes, still the optimization level will show “TRIVIAL”, just because SQL Server hadn’t any other choice but to do a full table/index scan. In a “FULL” optimization SQL Server will evaluate the maximum alternatives possible, given the time and resources limitations. This optimization level also unlocks other properties, e.g.:
    • Reason For Early Termination Of Statement Optimization: Specifies why the optimizer gave up on optimizing the plan. The possible values are:
      • Good Enough Plan Found: The Optimizer found a plan with a cost below the threshold.
      • Time Out: Compilation time exceeded the maximum optimization time, defined at the start of the optimization phase.
      • Memory Limit Exceeded: Compilation stopped due to memory shortage.

Operator Cost

Commonly, we’ll see expensive operators standing out from the rest. When the cost is mainly in one or a couple operators, if we manage to improve those specific areas, it will have a substantial impact on the whole execution. Just remember that these costs provide guidelines and aren’t always accurate, some operators even have a fixed cost, which can be very misleading. Another visual hint that is quite useful in the Actual Execution Plan is the time displayed below each operator. This value can be cumulative (including time spent on child operators) or individual when dealing with Batch Mode operators.

Data Flow

Still regarding these visual hints, the thickness of data flow arrows is one of the coolest. Their width is proportional to the row count (based on actual row count when available, or on estimated row count otherwise).

Additionally, we must analyze abrupt transitions, i.e. where wide arrows become narrow, because that may indicate that the filtering is occurring too late. We want to “push” filters as far to the right side of the plan as possible.

Estimates Vs Actuals

As already covered in a previous post, statistics are absolutely vital in the process of creating an execution plan. Estimates underpin some algorithm choices and consequently, bad estimates can lead to bad choices. A particular algorithm might be efficient when dealing with few rows, but extremely inefficient when dealing with a huge number of rows. Nested Loops is a great example of that. That’s why it is so important to keep an eye on estimated vs actual row counts. If a large gap between estimated and actual row count led to a catastrophic algorithm choice, we’ll need to figure out how to improve those estimates or how to hint SQL Server to choose differently.    

Read Operators

Analyzing read operations is an important part of troubleshooting execution plans. Althought there is no such thing as an evil operator and we can’t state that a scan is a bad thing or a seek is always good, patterns like a scan that returns few rows or a seek that returns a huge amount of rows might be worth checking.

Posteriorly, we’ll analyze read data operators in greater detail.🖖

Aventura-profissional PNG from pngtree.com

Performance Woes & Wows #2: LIKE ‘%Make This Query Go Slower Please%’

Sargability is a funny word that’s used to characterize the ability of the engine to take advantage of and index, in order to speed up the execution of a query. The legend says that it is derived from a contraction of Search ARGument ABLE.

Usually, when some writes a query, the goal it’s to retrieve the desired information, as fast as possible. What’s interesting is that “as fast as possible” can either refer to “execution time” or “time I will spend writing this query” and we all know which one is, most of the times. As a rule of thumb, queries must be written in the simplest way possible, being the underlying reason that if it’s easy for you to read, it will also be easier for the Optimizer to read and process it.

But that’s not always true, for example, we use functions because they save us effort and improve query readability, but typically they don’t make Optimizer’s life easier. Au contraire, because they tend to make predicates non-sargable. On other occasions, bighearted developers decide to allow the customer to search on every damn existing column, using LIKE with leading and trailing % wildcards and that’s also a sargable capital sin, as we’re going to look further.

Non-Sargable Predicates

So, let’s talk a bit more about bad ideas. Not those that start with “Hold my beer and watch this”, but instead “It runs fast on my machine, let’s take it to Production”.

Non-sargable predicates have a negative impact on query performance, e.g. by causing poor cardinality estimates or inability to perform index seeks, which consequently will lead to inappropriate plan choices and increased CPU consumption.

The following list include some of the most common patterns that kill predicate sargability. We’ll very briefly overview them and posteriorly focus on one specific case.

ColumnX = Function(ColumnX): As already pointed out, functions can be naughty. One might be tempted to use functions because they encapsulate logic in a very practical way to us humans, but this often implies performance loss. The query presented below, where we extract the year from a date column, comparing it against a specific value, represents one of those cases. SQL Server won’t be able to use any index efficiently, even if we have an index on that field. We’ll shortly discuss why this happens.

SELECT COUNT(1) FROM Matches WHERE YEAR(Date) = 1999

ColumnX = ColumnY + ColumnZ: A similar behaviour is observed when we use the result of arithmetic operations between columns to retrieve results of a particular column. It’s important to stress that a predicate containing an arithmetic operation between a column and a constant literal value would also be non-sargable.

SELECT * 
FROM Matches 
WHERE FullTimeGoals = HalfTimeHomeGoals + HalfTimeAwayGoals

@Parameter1 IS NULL OR ColumnX = @Parameter1: The heart of that single query where search dreams come true and someone got really proud of it. It’s possible to search for everything. Slowly. In runtime SQL Server won’t be able to figure this out, hence the non-sargability. There are a couple of ways to get around this, being one of them to add OPTION(RECOMPILE). This hint enables Parameter Embedding Optimization, which gives the Parser the ability to replace query parameters with literal constant values during query parsing and enables further complex simplifications, like removing contradictions. Nevertheless, as this hint forces the recompilation of a statement, it introduces some overhead that needs to be taken into account.

CREATE OR ALTER PROCEDURE SearchNonFriendlyMatch 
(
    @Hometeam INT = NULL,
    @Awayteam INT = NULL,
    @Date DATE    = NULL,
    @FTG TINYINT  = NULL
)AS
BEGIN
	SELECT 
		HomeTeam,AwayTeam,Date, FullTimeGoals
	FROM 
		Matches
	WHERE
		League IS NOT NULL AND
		(@HomeTeam IS NULL OR HomeTeam = @HomeTeam) OR 
		(@AwayTeam IS NULL OR HomeTeam = @HomeTeam) OR 
		(@Date IS NULL OR [Date] = @Date) OR 
		(@FTG IS NULL OR FullTimeGoals >= @FTG)
END

ColumnX LIKE ‘%Make This Query Go Slower Please%’

When we have this wildcard prefixing and suffixing a string, we’re telling SQL Server to retrieve all records that contain the specified text, regardless of what comes before or after it.

SELECT  TOP 5 * FROM Teams WHERE Name LIKE '%ar%'

The execution of the previous query returns these results:

IdCountryName
7PortugalSC Beira-Mar
8EnglandCardiff City FC
23EnglandArsenal FC
28SpainFC Barcelona
68ItalyParma

But why is this bad for performance?

Let’s put ourselves in SQL Server shoes and pretend that we’ve received a list containing names of football teams, ordered alphabetically. We need to count how many of these names contain the letters “ar”. We roll up our sleeves and start analyzing all characters from the first record, then the second, then the third, and so on, until we reach the last item in the list. We had no alternative but to scan through all values, simply because the order of this list wasn’t helpful at all. Recalling the table above it’s easy to understand why: the substring can be everywhere, there is no shortcut, no possibility to discard any subset of these names. SQL Server will have the same exact struggle scanning through all the index represented by our list.

Now, let’s execute a similar query:

SELECT  COUNT(1) FROM Teams WHERE t.Name LIKE '%Benf%'

And look into its execution plan:

This is a pretty small table but it’s enough to prove the point. This query ran in about 600 miliseconds and IO statistics show 1 Scan Count and 1226 logical reads. The cost is almost entirely in the Index Scan, that was chosen even thought SQL Server estimated just 9 rows to be retrieved. Well, that wasn’t really a choice, in this case an Index Scan was made because the non-sargable search prevented SQL Server from using an Index Seek, as we already know.

With that being said, let’s remove the leading wildcard and execute the query:

SELECT  COUNT(1) FROM Teams t WHERE t.Name LIKE 'Benf%'

And this time, it is possible to confirm by the execution plan that the query was much more efficient using the Index Seek.

It has executed in virtually no time and IO statistics show 1 Scan Count and 3 logical reads.

If we return to our ordered list, it’s very easy to understand why the query executed so much faster: Now we can benefit from that alphabetical order, going directly to the letter B, looking through a few lines and quickly return the row that matches the search.

What Should I Do, Then?

We saw that the sargable version of our query ran in virtually no time, while the non-sargable version took about 600ms and even though proportionally this gap is huge, users don’t complain about half a second queries. Nevertheless, the real problem with this type of queries is they won’t scale. When you try to run this query against a table with millions of rows instead of a couple thousand, it will be much more painful. You’ve been warned.

But what can we do to avoid it?

The first and obvious option would be to analyze if that leading wildcard is really needed.
If you can simply get rid of it, problem solved! If not, there’s still hope…

One way to improve these searches is by using Full Text Search (FTS). FTS is able to quickly find all matches for a term search in a table, because it doesn’t have to scan rows. Instead it takes advantage of text indexes that store positional information for all terms found in the columns where we create the text index on. Instead of using LIKE operator, FTS enables the use of CONTAINS/CONTAINSTABLE or FREETEXT/FREETEXTTABLE, as exemplified below:

SELECT COUNT(1) FROM Teams t WHERE CONTAINS((t.Name), @Name) 

FTS has a few limitations and requires some setup effort, but I often work with it and it can be pretty awesome.

Additionally, there are newer, hotter technologies designed to solve these issues. For example ElasticSearch, that allows fast text searches in a very scalable way. It also allows pretty complex searches but, to me, the nicest advantage comparing to Full-Text Search is the possibility to take the heavy processing completely outside SQL Server. CPU cores in SQL Server can be very expensive and should be treated with respect, my boss says.

Now you now why you should avoid queries LIKE ‘%these%’ and you have some ideias to improve them. Hope this was helpful to you 🖖

Deny User Logins

Imagine that you live in a world where you need an applicational user with god-like powers because, in fact, it needs to. If you want to make sure that applicational users are only used by the application you’ll have to create strong passwords, keep’em secret, change’em often, all that good stuff, sure. But you know what they say – when there’s a will, there’s a way – and there are people who simply like to leave in Santa’s naughty list. So today we’ll talk about an old trick that can be used to help prevent logins from a user, under specific cirumstances – Logon Triggers:

“Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.”

Firstly, we’ll try to deny any SSMS login attempt with the user “appUser”. To accomplish that we’ll create a new trigger, as presented below:

CREATE OR ALTER TRIGGER DenySSMSLogin
ON ALL SERVER 
FOR LOGON
AS
BEGIN
  DECLARE @AppName varchar(max)
  DECLARE @LoginName sysname
  DECLARE @LoginType sysname
  
  SET @AppName = APP_NAME()
  SET @LoginName = ORIGINAL_LOGIN()
 
  IF(@LoginName = 'appUser' AND @AppName LIKE 'Microsoft SQL Server Management Studio%')
  BEGIN
	RAISERROR('Call the Federales',16,1)
    ROLLBACK;
  END
END

Now, we won’t be able to login this user on SSMS, as the following error message indicates:

Most importantly, queries done by this user through the application’s services will be processed as intended. Now just sit and wait for people to start asking what’s happening and then beat them with the nearest heavy object, calmly explain them why this is beeing done.

Ok, so now I have to come clean. The bad news is that I lied and it’s still possible to login appUser on SSMS. If you are a really evil user, you can change the application name under the “Additional Connection Parameters” on connection Options.

Furthermore, even if you deny connections on SSMS you might want to keep an eye on things like SQLCMD.EXE. Nevertheless, if all you need is love a soft remainder that using the applicational user is not cool, that might do the trick. Otherwise, sorry for wasting your time. But there is still hope.

The good news is that we can use alternative approaches, if blacklisting application names is not an option. For example, we can restrict logins to a range of authorized IPs, as suggested by Solomon Rutzky in this post.

--(...)
IF (ORIGINAL_LOGIN() = N'appUser' AND 
 (
   CONVERT(VARCHAR(10), CONNECTIONPROPERTY('net_transport')) <> 'TCP' OR 
   CONVERT(VARCHAR(10), CONNECTIONPROPERTY('client_net_address')) <> '10.10.1.1'
 )
)
BEGIN
  RAISERROR('Call the Federales',16,1)
  ROLLBACK;
END;

This method implies less security risks as it would be necessary to be logged onto the production server, or spoof its IP, in order to overcome this restriction.

Finally, if you need to remove the trigger, just execute the following command. Be sure to specify “ON ALL SERVER” if the same was specified upon creation, otherwise the trigger won’t be removed.

DROP TRIGGER DenySSMSLogin ON ALL SERVER;

And that’s it! 🖖

Slow Query Store

I’m in a SQL Server 2016 database trying to view the Top Resource Consuming Queries. Since this database has Query Store enabled, I went to Query Store > Top Resource Consuming Queries and the usual screen pops-up, as the data is loading:

After a few seconds waiting, I gave up on Query Store and went to see random videos on the internet do other very professional task, that took me about ten minutes. When I switched back to check the result, the query responsible to retrieve this information was still running.

At this stage, I was wondering that maybe the Query Store got too fat to be fast. The results shown that it was occupying about 1.5 GB. That’s not very impressive, isn’t it?! But that’s something, I guess.

Initially, I thought using DBCC CLONEDATABASE. This command creates a schema-only copy of a database, to the same server as the source database and allows us to include Query Store data. After that, it would be safe to clear the Query Store without losing those statistics, and it would make a fresh start, collecting enough data to present the recent results, without the weight of historical data. Nevertheless, I would have to go through the same pain to analyze older data, so I decided to leave this path and adapt the query that the Query Store was running instead:

SELECT TOP 25 
	q.query_id,
	OBJECT_NAME(q.object_id),
	rs.avg_duration,
	rs.avg_cpu_time,
	rs.avg_logical_io_reads,
	rs.avg_logical_io_writes,
        rs.last_execution_time
FROM 
	sys.query_store_query AS q
JOIN 
	sys.query_store_plan  AS p ON q.query_id = p.query_id
JOIN 
	sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE 
	q.object_id > 0
	AND rs.last_execution_time > DATEADD(HOUR, 1, GETUTCDATE())
ORDER BY rs.avg_duration DESC

By including Live Query Statistics, I was able to see that something was going off with the amount of data on the TVF used by the Query Store, that was posteriorly going through a Filter operator, a Row Count Spool and ended up being joined using Nested Loops.

As quick workaround, I hinted the query with OPTION(HASH JOIN) to change the plan and…

It ran in about 2.5 seconds! As we can see, the plan changed completely – No nasty Filters, no Spools, nor Nested Loops, obviously.

We can use sp_create_plan_guide to apply this principle in the query running behind the Top Resource Consuming Queries in the Query Store GUI, but removing the power of choice from SQL Server is dangerous and often ends up in tears. So, proceed carefully!

It’s All About Stats!

Statistics shape the way executions plans are built and have a massive impact on query performance. In order to achieve a better understanding on this relation, we will start to briefly explore what happens when a query is executed and where do statistics come into play.

Query Execution Phases

When a query is executed, there’s quite a lot happening in the Relational Engine inside SQL Server. In a higher plan, this process can be divided into four sequential phases: Parsing, Binding, Optimization and Execution. Each phase requires an input that is consumed and transformed into an output that is forwarded to the next phase.

Parsing

In the first step, a syntactic validation of the T-SQL is done, also known as query parsing. The example provided in the image below, represents a query that failed to be parsed, in that case, because the SELECT clause was miswritten.

The output of this phase is a parse tree, which comprehends the logical steps required to perform the query.

Binding

When the query is successfully parsed, query binding takes place. This process, owned by the algebrizer will resolve the parse tree, trying to bind the respective strings to specific objects within the database. This translates to, for instance, verifying if referenced tables and columns exist under the current execution context. It’s important to mention that the query can use names that do not match directly to any database object, e.g. if aliases are being used. So, it’s also the algebrizer job to resolve these cases. Additionally, it will also identify datatypes and process aggregate functions to some extent (known as aggregate binding). The following example shows a query that could not be resolved, because it refers to a column that does not exist.

What do you mean, “invalid”? How am I supposed to fix slow queries?

The output of this phase is a query processor tree, that will be handed to the query optimizer.

Optimization

Well, in fact, the query processor tree isn’t the only input received by the optimizer. Building a new plan from scratch consumes resources, specifically time and CPU. In order to manage effectively this resources, SQL Server will keep plans in cache, whenever possible, in order to reuse them. So, the optimizer will also receive the hash code of the query currently being executed, as an input. This hash code will be used to probe the existence of a valid execution plan in cache, that matches the query. If there is a match with a valid plan, the optimization process stops and the cached plan is reused. However, if changes were made to any table referenced in the query, or statistics were updated in the meantime, the plan is considered invalid and must be rebuilt.

If no valid plan is found, the optimizer creates a new plan, which in essence is an analysis of many alternate ways to achieve the expectable result. The optimizer estimates a cost for each alternative approach and tries to find a plan, cheap enough, in an incredible short amount of time. Trivial plans (e.g. SELECT Col1 FROM Table1) don’t go through this optimization process, as they typically don’t have alternative approaches to be considered, due to their simplicity. More complex queries will be subject to a full cost-based optimization process, resulting in a cost-based plan, calculated according to the cardinality estimations. After the cheapest possible plan is chosen and built, it is handed to the Execution Engine.

Execution

Finally, in the execution phase, the Execution Engine will receive and process the execution plan. Validations on the plan are reinforced at runtime, to confirm that it is still valid. If something happened in the meanwhile that changed the initial state, for example because statistics were updated, execution is paused, the compilation process is invoked and a new plan is built for the affected statement(s).

The Importance of Statistics

Statistics provide information about the distribution and selectivity of values across columns, using a sample that will represent the entire universe of values. The selectivity of a value is the ratio between the rows that pass the selection criteria and the total number of rows. We can extrapolate this principle and think in the selectivity of a column as the ratio between the number of distinct values and the total number of rows. This basically defines the uniqueness of what you are trying to find and it will provide guidance to the optimizer on how to shape the plan. In a side note, keep in mind that statistics can be manually created, (whether directly or through the creation of indexes) or automatically created by the SQL Server when a query is executed, for the optimizer to consume them.

This process is fundamental because we want our queries to run as fast as possible, what wouldn’t happen if the optimizer had to scan all data in all referenced tables.

Going Under the Hood

For the purposes of this demo, we’ll only cover the usage of single column statistics, equality predicates and single table queries. Bear in mind that the behavior differs when using multiple column statistics, joining other tables or applying more complex predicates.

First, we’ll run the following query, which will retrieve the matches where Manchester City (Id=77) played at Etihad Stadium.

SELECT  * FROM Matches WHERE HomeTeam = 77

In the execution plan, we’ll see that SQL Server estimated, correctly, that 390 would be retrieved, from a total of 535 983 rows.

If we open the XML execution plan, we’ll be able to confirm which statistics were used in order to make the estimate, under “OptimizerStatsUsage“.

Next, we use DBCC SHOW_STATISTICS to view to view those statistics, related to HomeTeam column.

DBCC SHOW_STATISTICS(N'Matches', _WA_Sys_00000005_73BA3083)

The information that makes up statistics is divided in three sections:

  • Header: Metadata about the statistics.
  • Density Vector: Selectivity of the data, used to measure cross-column correlation.
  • Histogram: Distribution of values in the first key column of the statistics object.

A histogram is composed by the following columns:

  • RANGE_HI_KEY: Upper-bound value for a step (always based on leftmost column).
  • RANGE_ROWS: Number of rows with a value falling within a histogram step, excluding the upper bound.
  • EQ_ROWS: Number of rows whose value equals the RANGE_HI_KEY.
  • DISTINCT_RANGE_ROWS: Number of rows with a distinct column value within a histogram step, excluding the upper bound.
  • AVG_RANGE_ROWS: Average number of rows with duplicate column values within a histogram step, excluding the upper bound calculation (RANGE_ROWS / DISTINCT_RANGE_ROWS)

In this case, because 77 hits a RANGE_HI_KEY in the histogram, we’ll be able to notice that the estimated value is on the column “EQ_ROWS” of that row.

The formulas to calculate the estimated number of rows and selectivity, can be expressed as it follows:

Selectivity is the key to combine statistics. Next, we’ll combine two single column statistics, by adding another equality predicate do the WHERE clause of the previous query. It’s important to stress that the version of the Cardinality Estimator (CE) dictates how calculations are made. The legacy CE (prior to SQL Server 2014) assumes that there is no correlation between multiple columns. Therefore, the cardinality is calculated with the following formula, that comprehends these variables: estimated number of rows (E), selectivity of the first (S1) and second (S2) columns and the total number of rows (R).

Contrarily, the new CE assumes some correlation between columns and estimates the number of rows as it follows:

Now, let’s run a new query that retrieves the games where Manchester City played at home and the full-time result was a win to the away team.

SELECT  * FROM Matches WHERE HomeTeam = 77 AND FTR = 'A'

We already know the values related to the predicate HomeTeam = 77, so let’s focus on the full-time result column (FTR) and the specific value “A”. If we open the XML execution plan, we’ll notice that the statistics of another column were added.

We then repeat the DBCC SHOW_STATISTICS command, changing it to match the above highlighted stats, related to FTR column.

DBCC SHOW_STATISTICS(N'Matches',_WA_Sys_00000009_73BA3083)

These statistics will show us the same 535 983 total rows and 9597 occurences of “A” in the FTR column.

Ok, now we have all the data we need, so let’s calculate the estimated number of rows for our query.

If we open the execution plan, we’ll be able to confirm that the our calculations match the ones made by SQL Server.

And as expected, if we change to the legacy CE, we will observe that the estimate has changed.

Accordingly, if we apply the formula used by the legacy CE, our results will also match:

In this case, the new CE did a better job estimating the workload, nevertheless, it doesn’t mean that the legacy CE is ready for retirement. In fact, in many cases the tables will turn and grandpa CE will do a better job. But, generally speaking, you won’t solve that many problems just by changing the CE version, despite the fact that it can have an observable impact, depending on the query specificities.

Conclusion

We’ve briefly looked into the phases of query execution and stated that the optimizer will choose the lowest-cost plan, based on its estimated cost that largely depends on the cardinality estimations. This implies that the quality of the plan choice is highly correlated to the quality of the statistics used by the optimizer. In a future post we’ll see in practice the negative impacts of bad estimates and what can be done to avoid some tears.

Until then, stay safe 🖖

Performance Woes & Wows #1: Implicit Conversions

Implicit conversion… Sounds very clever, so it can’t be that bad, right? Well, in fact, implicit conversions can be huge performance killers and silent enough to stay off the radar, if we are not aware. The good news is that they tend to be very easy to solve, so let’s make an easy buck.

The story begins with mismatched data types on a JOIN operator or a WHERE clause that force SQL Server to implicitly convert values, on the fly. This can be as subtle as mistaking a VARCHAR for an NVARCHAR, although the consequences can be pretty awful. But, before diving into the problem itself, let’s talk about conversions and data type precedence.

Implicit vs Explicit Conversion

Data types can be either implicitly or explicitly converted.

Implicit conversions are nearly invisible to the user because they are automatically executed, i.e. they do not require the explicit use of any function.

Contrarily, in explicit conversions, CAST or CONVERT functions must be used. The recommendation is to use CAST function if ISO compliance is desirable and CONVERT function in order to take advantage of the style functionality.

Nonetheless, it’s not possible to convert every possible data type combination. The following matrix (provided by Microsoft here) shows all data type conversions for SQL Server system-supplied data types.

It’s important to notice that when an explicit conversion is done, the resulting data type is determined by the statement itself.

Implicit conversions like assignment statements, result in the data type that was defined by the variable declaration or column definition. For comparison operators or other expressions, the resulting data type depends on the rule of data type precedence.

Data Type Precedence

This rule simply states that, when an operator combines expressions of different data types, the data type with the lower precedence is converted to the data type with the higher precedence. The order is defined as follows:

1. user-defined data types (highest)11. real21. text
2. sql_variant12. decimal22. image
3. XML13. money23. timestamp
4. datetimeoffset14. smallmoney24. uniqueidentifier
5. datetime215. bigint25. nvarchar
6. datetime16. int26. nchar
7. smalldatetime17. smallint27. varchar
8. date18. tinyint28. char
9. time19. bit29. varbinary
10. float20. ntext30. binary (lowest)
Data type precedence (Transact-SQL)

How to Uncover Implicit Conversions

There are a couple of ways to uncover implicit conversions. The most usual is, probably, by analyzing the Execution Plan. If we’re inspecting the execution of a query where an implicit conversion that may affect cardinality estimate happened, a warning will be displayed. We’ll further look into this.

It’s also possible to capture these occurrences through the use of DMVs. I’ve seen some examples using sys.dm_exec_query_plan DMV in order to accomplish it, but we’re going to use the brand new and awesome sys.dm_exec_query_plan_stats DMV brought by SQL Server 2019. We’ll take a deeper look into it in a future post, but for now just keep in mind that this DMV will show us the last actual plan for a given query. Also, make sure to activate the scoped configuration as specified below:

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

--ImplicitConversions
DECLARE @DBName VARCHAR(20) = 'FStats';

SELECT TOP 30
	t.text AS 'Query',
	qs.total_worker_time/qs.execution_count AS 'Worker Time (Avg)', 
	qs.total_elapsed_time/qs.execution_count AS 'Elapsed Time (Avg)',
	qs.total_logical_reads/qs.execution_count AS 'Logical Reads (Avg)',
	qs.total_physical_reads/qs.execution_count AS 'Physical Reads (Avg)',
	qs.max_elapsed_time AS 'Max Elapsed Time',
	qs.max_logical_reads AS 'Max Logical Reads', 
	qs.max_physical_reads AS 'Max Physical Reads', 
	qs.execution_count AS 'Execution Count',
	qp.query_plan AS 'Query Plan'
FROM 
	sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY 
	sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY 
	sys.dm_exec_query_plan_stats(plan_handle) as qp
WHERE 
	t.text NOT LIKE '--ImplicitConversions%' AND
	CAST(query_plan AS NVARCHAR(MAX)) LIKE ('%PlanAffectingConvert%') AND
	t.[dbid] = DB_ID(@DBName)

Finally, another more comprehensive approach might be setting an Extedend Event to collect implicit conversions happening in the database. Creating a plan_affecting_convert event will allow us to uncover these CONVERT_IMPLICIT warnings. For the purposes of this demo it will be configured as it follows:

CREATE EVENT SESSION [xe_ImplicitConversions] ON SERVER 
	ADD EVENT sqlserver.plan_affecting_convert
	(
    	     ACTION(sqlserver.transaction_id)
    	     WHERE ([sqlserver].[database_name]=N'FStats')
        )
WITH (STARTUP_STATE=ON)

Implicit Conversions Woes

But why should we care about implicit conversions? Does it impact performance that much? Let’s find out! The following query retrieves the disciplinary sheet of football matches where 4 or more players were sent off.

DECLARE @NumRedCards tinyint = 4;
SELECT  
	MatchDetails, 
	HF HomeFouls, AF AwayFouls, 
	HY HomeYellowCards, AY AwayYellowCards, 
	HR HomeRedCards, AR AwayRedCards, TRC TotalRedCards
FROM 
	MatchesDetails 
WHERE 
	TRC >= @NumRedCards

Everything looks fine, right? Despite that, if you sense evil lurking, you’re absolutely correct. Using the previous xe_ImplicitConversions session, we’ll be able to see this:

Accordingly, the set of DMVs specified above, will reveal conversion problems that may affect cardinality estimate in the query plan choice.

If we click on the query plan and then hover on both operators, the following information will pop up:

Besides the obvious warnings, it’s clear that a nasty Clustered Index Scan was done and it’s quite strange that SQL Server didn’t cry for an index, isn’t it? Let’s create the bloody thing and ignore the implicit conversion, for now.

CREATE NONCLUSTERED INDEX IDX_MatchesDetails_TRC_Includes ON MatchesDetails(TRC)
INCLUDE(MatchDetails,HF,AF,HY,AY,AR,HR)
WITH(DATA_COMPRESSION=PAGE)

Noice! Now, with an unstoppable confidence, we will rerun the query and the plan will unequivocally show us that we’ve…

Failed. Miserably. An ugly Clustered Index Scan is still happening and the logical reads remain at 161 516.

Ok, so let’s change the approach here. First by dropping the index:

DROP INDEX IDX_MatchesDetails_TRC_Includes ON MatchesDetails

Then by analyzing the warning presented in the execution plan. According to it, there’s a data type mismatch between TRC and @NumRedCards. If we look into the definition of MatchesDetails table, we’ll confirm that TRC is, in fact, a varchar – genius.

Solving the Case

Elementary, my dear Watson.

Although the correct path here would be to alter the column data type, for simplicity’s sake we’ll just change the data type of @NumRedCards to varchar(2) and rerun the query.

DECLARE @NumRedCards varchar(2) = 4;
SELECT  
	MatchDetails, 
	HF HomeFouls, AF AwayFouls, 
	HY HomeYellowCards, AY AwayYellowCards, 
	HR HomeRedCards, AR AwayRedCards, TRC TotalRedCards
FROM 
	MatchesDetails 
WHERE 
	 TRC >= @NumRedCards

This time, although the Clustered Index Scan hasn’t vanished, we got a missing index hint that corresponds exactly to the one created earlier. After recreating it, and rerunning the last query, the result will be the following:

Success! Just like a rainy cloud, the warning has disappeared and the day is sunny again. We’re now efficiently using the index with an Index Seek and the logical reads dropped to a glorious 4.

Cry Me a River

There’s nothing much left to say, in this context, regarding how implicit conversions can mess up the cardinality estimates and consequently ruin SARGability, index usage, and burn CPU. Despite that, it’s relevant to mention that implicit conversions aren’t always a problem, even if SQL Server says so. One example of that is when the conversion occurs on the SELECT statement, after the rows are fetched. SQL Server will have an embarrassing outbreak, threatening you with the same old warnings, stating that cardinality estimate can be affected, but it’s all a lie, it certainly won’t. Nevertheless, stay alert for the real performance killers! 🖖

MemoryGrantInfo Info

As suggested by the title, I like redundancy, bad titles and we’re going to have a brief look into MemoryGrantInfo.

In a previous post, we’ve talked about an awesome feature from the new SQL Server 2019 – Row Mode Adaptive Memory Grant Feedback. We saw this feature in action and briefly explored MemoryGrantInfo, but some fields were not explained. So, let’s recall the MemoryGrantInfo section and analyze its properties:

DesiredMemory: Amount of memory (in kilobytes) estimated to be needed to run the execution plan, based on the cardinality and data size estimation. This value will be cached as part of the plan and will remain the same – if Adaptive Memory Grant Feedback is not enabled.

GrantedMemory: Amount of memory that was actually granted at runtime. This value can be lower than DesiredMemory if the server doesn’t have enough available memory at the moment of the grant. Due to this fact, it’s possible that its value differs across similar executions, depending on the server’s available workspace memory at the time. When the grant isn’t high enough to keep all data in memory it will write it into tempdb – this is also known as a spill and it can degrade query performance. This doesn’t mean that a lower grant will always cause a spill, nor that a spill will always have a substantial impact on performance, nevertheless, that’s definitely something to keep an eye on.

GrantWaitTime: Time (in seconds) that the query had to wait before it was granted the memory to execute. This will translate into RESOURCE_SEMAPHORE waits.

IsMemoryGrantFeedbackAdjusted: Current state of Adaptive Memory Grant Feedback, that will be represented by one of the following values: NoFirstExecution, YesAdjusting, YesStable, NoAccurateGrant, NoFeedbackDisabled. For more details, check this post about Adaptive Memory Grant Feedback.

LastRequestedMemory: Indicates RequestedMemory value from the previous execution. While trying to calculate the ideal memory grant, Adaptive Memory Grant Feedback will use the requested value from an execution in order to improve the following. This property will allow us to easily check how the grant is being adapted.

MaxQueryMemory: Maximum memory allowed for a single query, according to Resource Governor’s MAX_MEMORY_PERCENT configuration, which by default is 25% of total query memory. In a case where operators are spilling data into tempdb and estimates are fairly correct, we might be experiencing memory starvation.

MaxUsedMemory: Amount of memory actually used by the query during execution.

RequestedMemory: Amount of memory requested, based on RequiredMemory, DesiredMemory, SerialRequiredMemory, SerialDesiredMemory, and MaxQueryMemory. When any query is executed, SQL Server Engine checks if the desired memory exceeds the Max Query Memory or not, if it exceeds, then it reduces the requested memory by adjusting parameters as Degree Of Parallelism (DOP), until it fits in.

RequiredMemory: This property represents the required memory to create the respective internal data structures, for a given DOP, when the query runs in parallel. If the query is running in serial mode, RequiredMemory value will be the same as SerialRequiredMemory. It’s mandatory that this amount of memory is available, in order to the query to begin execution.

SerialDesiredMemory: Amount of memory estimated to be needed to run the execution plan on serial mode, without spilling to disk.

SerialRequiredMemory: Required memory for a serial query plan to execute. It’s mandatory that this amount of memory is available in order to the query to begin execution.

And that’s it.

Rowstore Adaptive Joins

In this day and age, join operations in SQL Server can be made using one of the following algorithm types: Nested Loops, Merge or Hash. In theory, each one provides better performance under different conditions (e.g. amount of data).

  • Nested Loops: One of the tables is designated as the outer and other as the inner table. Each row of the outer table, is validated against all rows from the inner table and if a match is found, the row is included in the result. Nested Loop is tipically preferred when tables are small. There are three variants for a Nested Loop Join:
    • Naive: Scans an entire table or index;
    • Index: Uses an existing index to seek data;
    • Temporary: Index is built as part of the query plan (and destroyed upon completion of the query);
  • Hash Match: Hash Match consumes two inputs, corresponding to the joined tables. A hash table is created using the first input (also known as Build), then the same is done for the second input (Probe). The process of creating a hash table starts with hashing joined key values of the Build table and placing them on the appropriate bucket, depending on the hash value. Afterwards, Probe is processed, same hash values are applied, target bucket is determined and finally the value is compared to the ones already inside. If there is a match and after SQL Server verifies that no hash collision happened, the row is returned. Build should always be smaller because it will be stored in memory.

And that’s it. No, I did not forget about Merge Join, it happens that Merge was just ignored (like Internet Explorer or Terms of Agreement). Adaptive Join allows SQL Server to choose between Nested Loops and Hash joins on the same execution plan, deferring this choice, so it can understand which one will be better, based on the query input.

So, if until now (at least in rowstore) there wasn’t a way to adapt to different workloads inside the same execution plan and specific join algorithms thrive under different conditions, how can, in theory, Adaptive Joins save the day?

The Horrors of Parameter Sniffing

Parameter sniffing is easily one of the most common problems that almost anyone who works with databases have to deal with. Well, to be fair, parameter sniffing is not a problem by itself. The problem is giving the power of choice to users. Choice is the root of all evil.

A store procedure gets executed for the first time, the plan is created based on its input and then is cached. If no recompile is done, the plan will remain the same and future executions will reuse it, regardless of the input. In theory that’s good, because bad things happen when there isn’t enough memory available – and creating new plans from scratch is quite expensive. The problem begins when, in a subsequent execution, the user changes the input in a way that the query returns a million rows, instead of ten. As mentioned in the previous section, each join type thrives under distinct conditions. If in the first execution, a small amount of data was scanned, SQL Server has potentially used Nested Loops and done something like an Index Seek plus a couple Key Lookups. In the later execution, when SQL Server tries to process a million rows using the same approach, it suddenly has a heart attack. Telephones start to ring and clients start o yell as its soul quickly ascends to heaven.

We can try defibrillating by using RECOMPILE, but if that query runs too often, CPU will quickly escalate to 100% and we lose the patient, again. We can also try to optimize the query to a particular case by specifying a join hint or using OPTIMIZE FOR hint, but even if we get a plan that fits appropriately to the majority of executions, the deviations will potentially cause SQL Server to die in agony. Dividing the store procedure into multiple store procedures according to the input, can do the trick, but in some cases, queries can be too complex, not to mention that business logic and magic numbers hardcoded in queries are generally not a good idea. So, what’s left besides a bunch of bad ideas?

Perhaps Adaptive Joins is the hero we deserve. Basically, SQL Server defines a threshold (related to the number of rows) that represents the tipping point from which becomes more efficient to use Nested Loops instead of Hash Match. Adaptive Join will start the execution as a Hash Join, then, after processing the input from the Build phase, it will compare the resulting number of rows to this threshold. If the number of rows is greater or equal, it will proceed execution, otherwise the algorithm type is change to Nested Loops.

Therefore, SQL Server becomes able to adapt a single plan to different workloads. If a large number of rows are being processed, it can use Hash Join, otherwise, if few rows are involved, Nested Loop Join will be chosen. No recompile. No hints. No Columnstore wizardry needed.

Now, let’s check if Adaptive Joins will live to the expectations.

Improvise Adapt Overcome

For the purposes of this demo, I’m using a sample of FootballData public stats. Additionally, I’ve added some data in order to facilitate the demonstration of some concepts and behaviors. Bear in mind that in Standard Edition you won’t be able to use this feature.

We’ll firstly set database compatibility level to 140 and execute a store procedure with two different parameters, so we can understand the behavior prior to SQL Server 2019.

ALTER DATABASE [FStats] SET COMPATIBILITY_LEVEL = 140
GO
SET STATISTICS IO ON
GO
CREATE OR ALTER PROCEDURE GetHomeVictoriesByTeamId      
	@TeamId INT      
AS          
BEGIN          
SELECT  
	m.Id,  
	m.FTHG HomeGoals,  
	m.FTAG AwayGoals,    
	d.Att Attendance,  
	d.Stadium,  
	d.Att  
FROM           
	Matches m          
JOIN           
	MatchesDetails d ON m.id = d.MatchDetails       
WHERE      
	m.HomeTeam = @TeamId AND 
	FTR = 'H' AND 
	d.Att > 20000           
ORDER BY       
	d.att DESC        
END 
GO
EXEC GetHomeVictoriesByTeamId 2
EXEC GetHomeVictoriesByTeamId 1

Ouch! Both execution plans used Nested Loops and Index Seek to retrieve results. The plan was created and cached in the first execution that retrieved 18 rows. Then SQL Server reused the plan to process 500304 victories from Benfica (Id = 1) and things got nasty. We can confirm in Messages that the second plan was not efficient.

By executing the same statement again with recompile, we can notice how a more appropriate plan differs. MatchesDetails’ Scan count dropped from 500304 to 5 and logical reads from 1597472 to 1539. That’s more like it! But, as we discussed, using option recompile is far from being always appropriate, due to the fact that it is a heavy resource consumer.

EXEC GetHomeVictoriesByTeamId 1 WITH RECOMPILE;

Next, we’ll set database compatibility level up to 150 and run the store procedure again, so we can see Adaptive Joins in action.

ALTER DATABASE [FStats] SET COMPATIBILITY_LEVEL = 150
GO
EXEC GetHomeVictoriesByTeamId 1;
EXEC GetHomeVictoriesByTeamId 2;
EXEC GetHomeVictoriesByTeamId 1;

By looking into the first execution plan, we’ll notice the “new” Adaptive Join operator (this looks intuitive), so it looks like we’re ready to adapt and overcome. There are a few things going on in this plan, so let’s analyze it step by step.

The properties of this operator will show us some interesting information, e.g. that the threshold for the algorithm to change to Nested Loops is 9607.83. We also can see that 500304 rows were returned, so the chosen join type was “HashMatch”. This is the expected behavior as the number of rows is higher than the threshold, hence not changing to Nested Loops.

If we recall the execution plan, it’s easy to corroborate this information. Since the number of rows exceeded the threshold and a Hash join was done, the execution went to the Index Scan branch. This can also be confirmed by the displayed number of rows that flowed through this operator, in this case 500304. Correspondingly, the lower Index Seek branch, had no rows.

The second plan, corresponding to an input that retrieves far less rows, shows a different execution branch. In the Adaptive Join operator info, it’s possible to notice that the chosen join algorithm type (NestedLoops) is appropriate to the number of rows – 18. It’s also visible that these rows were retrieved by an Index Seek instead of an Index Scan. So far, so good.

The third and last plan, with the same input as the first will be exactly the same, right? Well… Sadly no. Although the operators are the same and the execution went through the same branch, it looks like we landed on spillage village. Both Sort and Adaptive Join have warning signs on them, stating that data was spilled to tempdb and we can see that the query took longer to run. Despite that, IO statistics maintain the same values of the first execution.  Interestingly, this behavior is caused by another adaptive query processing feature that was explored in a previous post– Adaptive Memory Grant Feedback.

If you are lucky enough, after a couple executions, SQL Server will be able to adapt the memory grant to its ideal value and just like clouds, when these warnings disappear, it will be a beautiful day. Unfortunately, sometimes SQL Server isn’t able to do that and gives up on adapting the grant (for more details, check the mentioned post). Let’s execute the store procedure a couple more times and check the outcome.

EXEC GetHomeVictoriesByTeamId 1
GO 30

Under these circumstances, SQL Server wasn’t able to adapt the memory grant and we didn’t get the stable ending we were looking for. Nevertheless, things could be worse: The initial memory grant is fairly adequate, so no spills this time. Furthermore, the chosen join algorithm is appropriate, IO statistics and execution time are pretty similar too.

In conclusion, looks like Adaptive Joins can really improve the performance of our servers, with virtually no effort needed. Nonetheless, it’s probably not a good idea to go all-in and change your production server compatibility level to 150, hoping that the robots will do what they were programmed to do. You must have seen enough movies to know that it’s not how it ends. But seriously, Adaptive Joins won’t fix all problems related to parameter sniffing and can actually cause problems that aren’t easy to troubleshoot. So, it’s reasonable to proceed cautiously and make use of tools like Query Store to fix regressions.