Limit the Columns in Your SELECT List

The Sales.SalesTerritory table has a nonclustered index on the Name column. The clustered index on the table is on the TerritoryID column. Since the nonclustered index will use the clustered index key, you have a covering index for the query. Executing it, you get the metrics and execution plan shown in Figure 14-1:

Reads: 2

Duration: 190mcs

An illustration of the execution plan. The index seeks of non clustered with a cost of 100 percentage points to select with a cost of 0 percent.

If I modify the query to return all columns on the table, we’ll no longer see the nonclustered index used. Instead, we’ll see the metrics and execution plan shown in Figure 14-2:

Reads: 4

Duration: 1.1ms

An illustration of the execution plan. The index seeks of non clustered and key loops of clustered with a cost of 50 percentage points to nested loops and finally to select with a cost of 0 percent.

I’m no longer getting the benefit of a covering index. The extra work required to both perform a Key Lookup to get the columns from the clustered index and then the Nested Loops to join the two result sets together ends up doubling the number of reads, from 2 to 4. The query duration on average went up more than five times.

In short, only choose the columns you need at the moment.

Filter Your Data Through a WHERE Clause

In order to see benefits from an index on queries that involve looking up data, and this includes UPDATE and DELETE when they’re finding the data being modified, INSERT when a referential integrity check occurs, and, of course, all SELECT statements, a WHERE clause must be used. Further, as explained in Chapter 8, the selectivity of the column, or columns, referenced in the WHERE, ON, and HAVING clauses determines how that index is used. As much as possible, your queries should be filtering data through these clauses. Where possible, you want to use the most selective column as well.

The majority of applications are going to be working off of limited result sets. While you may need to perform data movement involving all, or just a very large subset, of the data, generally speaking, you should be seeing mostly small data sets being retrieved. The exception of course is analytical queries, but you can better support queries of that type through columnstore indexes. In the event that you are in a situation where you regularly have to move extremely large result sets, you may need to look to external processes and hardware to improve performance.

Use Effective Search Conditions

Search conditions within a WHERE, ON, or HAVING clause can use a very large number of logical operations. Some of those operations are highly effective in allowing the query to work with indexes and statistics. Other operations are not as effective and can actively prevent the efficient use of indexes. Traditionally, we refer to the effective operations as being “Search ARGument ABLE” or sargable for short.

There are a few search conditions, introduced in more recent versions of SQL Server, for which these rules don’t exactly apply. For example, SOME/ANY and ALL are dependent on the subquery that defines them. If you use a non-sargable condition in that subquery, then you’ll have issues.

The non-sargable conditions listed will prevent good index use by the optimizer. Instead of a seek, you’re much more likely to see scans when using these conditions. This is especially true to the logical NOT operators such as <>, !=, and NOT LIKE. Using these will always result in a scan of all rows to identify those that match.

Where you can, implement workarounds for these non-sargable search conditions to improve performance. In some cases, it may be possible to rewrite the logic of a query to use inclusion instead of exclusion operations. You will have to experiment with different mechanisms in order to determine which are going to work in a given situation. Also, don’t forget that additional filtering could limit the scans necessary when multiple columns from one table are in a WHERE clause. Testing is your friend when you have to deal with these types of situations.

BETWEEN vs. IN/OR

Listing 14-2 is an example of a query using an IN condition.

SELECT sod.CarrierTrackingNumber,

       sod.OrderQty

FROM Sales.SalesOrderDetail AS sod

WHERE sod.SalesOrderID IN ( 51825, 51826, 51827, 51828 );

Listing 14-2

A query using an IN condition

Logically, this query could be written using an OR condition as shown in Listing 14-3.

SELECT sod.CarrierTrackingNumber,

       sod.OrderQty

FROM Sales.SalesOrderDetail AS sod

WHERE sod.SalesOrderID = 51825

      OR sod.SalesOrderID = 51826

      OR sod.SalesOrderID = 51827

      OR sod.SalesOrderID = 51828;

Listing 14-3

A query using an OR condition

Since the values we’re searching for in this case are consecutive integers, another way to logically write this query is shown in Listing 14-4.

SELECT sod.CarrierTrackingNumber,

       sod.OrderQty

FROM Sales.SalesOrderDetail AS sod

WHERE sod.SalesOrderID

BETWEEN 51825 AND 51828;

Listing 14-4

A query using a BETWEEN condition

Logically, all three queries are the same. If you look at the result sets, they are all the same. If I capture all three execution plans and put them side by side, as shown in Figure 14-3, they are even visually identical.

A diagram of execution plans for 3 queries. The clustered index seeks of clustered with a cost of 100 percent, then points to select with a cost of 0 percent.

Even though plans may be visually the same, if we look at the performance metrics, we can see differences. Here are the metrics for the three queries in order:

• Using IN

Duration: 362mcs

Reads: 18

• Using OR

Duration: 336mcs

Reads: 18

• Using BETWEEN

Duration: 342mcs

Reads: 6

While the OR clause was slightly, 6 microseconds, faster than the BETWEEN, it used three times as many reads, 18 compared to 6. The key to understanding this is in how the optimizer chooses to deal with these queries. While they are logically the same, they are physically different, and the optimizer has choices. If we compare the IN execution plan to the BETWEEN plan using the plan comparison capabilities in SSMS (as explained in Chapter 4), we can quickly identify differences, especially this one in the Clustered Index Seek operator in Figure 14-4.

An illustration of 3 panels of difference between top and bottom plans. 1, Two execution plans of clustered index seek points to select. 2 and 3, a top and bottom plan with properties.

The only real difference is in the Seek Predicate values. Here is the predicate for the OR query in Listing 14-3:

[1] Seek Keys[1]: Prefix: [AdventureWorks].[Sales].[SalesOrderDetail].SalesOrderID = Scalar Operator((51825)), [2] Seek Keys[1]: Prefix: [AdventureWorks].[Sales].[SalesOrderDetail].SalesOrderID = Scalar Operator((51826)), [3] Seek Keys[1]: Prefix: [AdventureWorks].[Sales].[SalesOrderDetail].SalesOrderID = Scalar Operator((51827)), [4] Seek Keys[1]: Prefix: [AdventureWorks].[Sales].[SalesOrderDetail].SalesOrderID = Scalar Operator((51828))

This is the predicate for the BETWEEN query in Listing 14-4:

Seek Keys[1]: Start: [AdventureWorks].[Sales].[SalesOrderDetail].SalesOrderID >= Scalar Operator([@1]), End: [AdventureWorks].[Sales].[SalesOrderDetail].SalesOrderID <= Scalar Operator([@2])

First, the optimizer has chosen to apply simple parameterization to this query as you can see in the @1 and @2 values within the second predicate. Also, instead of a BETWEEN operation, >= and <= have been substituted. In the end, this approach results in far fewer reads. On a production system under load, this will doubtless lead to superior performance.

If we also take a look at STATISTICS IO for the two queries, we get a better sense of what’s happening:

• For the OR query:

Table "SalesOrderDetail". Scan count 4, logical reads 18

• For the BETWEEN query:

Table "SalesOrderDetail". Scan count 1, logical reads 6

As you can see, there are four scans to satisfy the OR condition, while a single scan satisfies the BETWEEN condition.

This is not to say that you will always see better performance under all circumstances between IN, OR, and BETWEEN. However, as you can tell, while three queries may share logical similarities, choosing different mechanisms can arrive at superior performance. Testing, and then using the tools to evaluate the queries, is the key. Different operators can, and will, use the indexes in different ways.

This is also an example where STATISTICS IO can give us additional information beyond what’s available to us within Extended Events. You won’t always need to use it, but keep that tool in mind when you need to drill down to better understand why you may be seeing differences in behavior between two queries.

!< Condition vs. >= Condition

Even though both !< and >= are logically equivalent and you will see an identical result set in a query using them, the optimizer is going to implement execution plans differently using these two conditions. The key is in the equality operator for >=. That gives the optimizer a solid starting point for using an index. The other operation, !<, has no starting point, resulting in the entire index, or the entire table, having to be scanned.

However, as I’ve already shown you in multiple examples within the book, the optimizer is capable of some modifications to the T-SQL code you’ve submitted. Here, we have a pair of queries that are using the conditions just discussed (Listing 14-6).

SELECT poh.TotalDue,

       poh.Freight

FROM Purchasing.PurchaseOrderHeader AS poh

WHERE poh.PurchaseOrderID >= 2975;

SELECT poh.TotalDue,

       poh.Freight

FROM Purchasing.PurchaseOrderHeader AS poh

WHERE poh.PurchaseOrderID !< 2975;

Listing 14-6

Comparing !< and >= conditions

Capturing the execution plans for both queries, they look visually identical in Figure 14-5.

An execution plan of two queries. Both queries with a clustered index seek points to compute scalar, which has a cost of 1 percent, and finally to select.

In fact, if you compare the plans, they are identical. It’s the same execution plan, used for both queries. You can see what happened if you look at the T-SQL stored with the plan. First, the optimizer changed the !< to >=. Second, it used simple parameterization, which leads to plan reuse. This is an example of when code is logically equivalent, the optimizer can help the performance of your query.

Avoid Operations on Columns

The execution plan is shown in Figure 14-6.

A diagram displays index scan and key lookup with costs, 67 and 18 percent point to nested loops, with cost 15 percent, then points at select with 0 percent cost.

There is an index on the PurchaseOrderID column, so the optimizer scanned that instead of scanning the clustered index. This is because the nonclustered index is smaller. However, that scan results in the need to then pull the remaining columns from the clustered key through the Key Lookup operation and a join.

In Listing 14-8, I move the calculation off the column to the hard-coded value.

Making this modification results in the execution plan and runtime metrics shown in Figure 14-7:

Reads: 2

Duration: 143mcs

A diagram depicts a clustered index seek, with a cost of 100 percent, points at select with a cost of 0 percent.

There’s not much to explain. Because of the calculation, the optimizer must scan the data. The optimizer tries to help you by scanning a smaller index and then looking up the remaining data, but as you can see, simply moving the calculation off the column makes all the difference.

Because we’re using DATEPART on the column, we’re going to see poor index use in the execution plan in Figure 14-8 and the following metrics:

Reads: 73

Duration: 8.89ms

A diagram of the execution plan with index scan and clustered index seek points to nested loops. Finally, from nested loops to select with a cost of 0.

This version of the query has the following metrics and execution plan in Figure 14-9:

Reads: 2

Duration: 198mcs

A diagram of the index seeks, and clustered index seek, with the cost at 50 percent, points to nested loops with 0 percent cost. Finally, points at select with 0 percent cost.

It’s not even close. In both examples, changing the logic to avoid running operations against columns results in radical performance enhancements.

Custom Scalar UDF

Running the query results in the runtime metrics and execution plan shown in Figure 14-10:

Reads: 14

Duration: 277mcs

A diagram execution plan with a scalar function. It includes the connections of clustered index seek, the index seeks to nested loops, compute scalar, and select.

This is a hard-to-read execution plan, so we’ll break it down into its component parts. Older versions of SQL Server won’t show this level of detail within the execution plan but instead will hide the scalar function within a single Compute Scalar operator. Capturing an Estimated Plan will show all the information you see here.

We’ll start with the section that shows how the scalar function is performing its data retrieval. Figure 14-11 shows the detail of that part of the plan.

A diagram depicts the data retrieval from the clustered index seek, top cost, stream aggregate, 2 compute scalars, then finally to nested loops.

The function is able to use a Clustered Index Seek to retrieve the data. You can then see it performs the necessary functions to prep the information, using a Top operator to support the TOP 1 part of the query and a Stream Aggregate to define the value returns. The Compute Scalar operators just ensure that the data types are correct.

The next section of the plan (Figure 14-12) at the bottom is how the optimizer deals with the IF clause in the query.

A diagram of the execution plan has 2 constant scan that points to nested loops with a cost of 0 percent.

The Constant Scan operators are used to evaluate whether or not the @Cost value is NULL. The join operation will ensure that a value, either the @Cost value or the value of 0, gets returned. This is then joined to the output of the data retrieval in the scalar operator.

At the top of the plan is the query from Listing 14-14, and it’s part of the data retrieval in Figure 14-13.

A diagram of satisfying the rest of the query. It contains index seeks and key lookup that points to 2 nested loops, then computes scalar. Finally, points to select with a cost of 0 percent.

The Index Seek filters the data from the WHERE clause, and then the Name column is retrieved using the Key Lookup. This data is then joined and then joined again with the data from the scalar function.

This query runs in about 239 microseconds, a small improvement over the scalar function. Figure 14-14 shows the execution plan for this query.

A diagram of index seeks and key lookup that points to 2 nested loops. The clustered index seeks points to the top, then to the second nested loops. Finally, points to select with a cost of 0 percent.

If you compare this plan to the plan shown previously, you can see most of the same functionality in play. Yet because there are fewer join operations and others, the speed is improved.

JOIN Hint

This query results in the following performance metrics and execution plan (Figure 14-15):

Reads: 2,364

Duration: 6.22ms

A diagram depicts the index seeks and key lookup that points to a hash match, then to a nested loop. The clustered index seeks points to nested loops. Then nested loop points to compute scalar and finally select.

A diagram of the clustered index scan and clustered index seek points to 2 nested loops. The clustered index seeks points to second nested loops. Then nested loop points to compute scalar and finally to select.

This results in the execution plan visible in Figure 14-17.

A diagram depicts that the clustered index scan points to sort, nested loops, merge join, nested loops, compute scalar, and select. The clustered index seeks points to nested loops. The clustered index seeks points to merge join, and clustered index seeks points to second nested loops.

While hints can sometimes improve performance, it’s absolutely not a guarantee. Only use them after very thorough testing.

INDEX Hints

Figure 14-18 shows the resulting execution plan, and these are the runtime metrics:

Reads: 44

Duration: 682mcs

A diagram of a scan from an index hint. The cluster index scan with a cost of 100 percentage points to select, which has a cost of 0 percent.

This is a mixed result. The reads went from 11 to 44. However, the execution time went from 2.5ms to 682mcs, a very substantial reduction. It’s still not as fast as simply removing the calculation from the column, which ran in 142mcs and only had 2 reads. Fixing the code is absolutely the superior choice by every measure. However, the INDEX hint did help the original query. This kind of experimentation can sometimes result in wins.

NOT NULL Constraint

The NOT NULL constraint ensures that a given column will never allow NULL values, helping ensure domain integrity of the data there. SQL Server will enforce this constraint in real time as data is manipulated within the table. The optimizer uses the information that there will be no NULL values to help improve the speed of the queries against the column.

These two queries have visually identical execution plans as you seen in Figure 14-19.

A diagram of two queries. Index scan of non clustered with a cost of 100 percentage points to select with a cost of 0 percent.

The key difference is visible in the plan. The first query is returning 18,967 rows out of an estimated 18,942. The second is returning 11,134 out of 11,372. That’s about the only real difference. Because neither the FirstName nor the MiddleName column has an index to support these queries, a scan of the IX_Person_LastName_FirstName_MiddleName index is used.

I’m now going to go back and rerun the queries, which results in the execution plans seen in Figure 14-20.

A diagram of two queries. 1, index seek points to select. 2, three constant scan points to three compute scalar, then concatenation, compute scalar, sort, merge interval. Merge interval and index seek points to nested loops, then finally select.

The first query against the FirstName column used the new index to simply retrieve the data. While the second query was able to use the new index, because of the IS NULL operation, you can see that the plan has greatly expanded in scope in order to satisfy that criterion. Three values are created using the three Constant Scan and Compute Scalar operators, one for each criterion in the WHERE clause. These are then sorted and merged and used in a join to arrive at the filtered data set.

An interesting point worth noting is that the first execution plan has a higher estimated cost. This is meaningless since the queries and data sets are different. However, there is some accuracy here as the first query runs in about 14.5ms while the second runs in 9.6ms.

This is a place where experimenting with using a filtered index to deal with the NOT NULL may result in a performance enhancement. In this case, it actually doesn’t. There are more reads and a slightly slower performance when making TestIndex1 a filtered index. One other point worth bringing up about NULL values is that you can use Sparse columns. This helps reduce the overhead and space associated with storing NULL values. It’s handy for analytical data when you have a lot of NULLs. However, it comes at a performance overhead, so generally it is not considered to be a way to enhance query behaviors.

User-Defined Constraints

However, that’s not the interesting part. The interesting part is how the optimizer chooses to satisfy this query here in Figure 14-21.

A diagram depicts a constant scan with 100 percent cost points to select with 0 percent cost.

The Constant Scan operator is simply a placeholder that the optimizer will use in execution plans to build data sets against. However, in this case, there is only the Constant Scan operator. So what’s happening?

The optimizer recognizes that this query cannot possibly return any rows at all. However, it still has to return a result set, even if it’s just going to be empty. So it uses the Constant Scan to create the empty result set. You can see it in the Output List property in Figure 14-22.

A dialog box of Output List. It contains 5 entries, Order date, ship date, order quantity, unit price, and product name, and a close button at the bottom right.

If the optimizer didn’t make this choice, since there is no index in support of the preceding query, you’d get a Clustered Index Scan, reading all the data in the table, but returning zero rows. The optimizer saved us from a nasty performance hit because it can read the constraints.

Declarative Referential Integrity

Declarative referential integrity (DRI) is the most used mechanism within SQL Server to ensure data cleanliness between a parent and a child table. DRI ensures that rows in the child table only exist when the corresponding row exists in the parent table. The only exception here is when a child table has NULL values in the column, or columns, that refer back to the parent. In SQL Server, DRI is defined through the use of FOREIGN KEY constraint on the child that matches a PRIMARY KEY or UNIQUE INDEX on the parent.

When DRI is established between two tables and the foreign key columns of the child table are set to NOT NULL, the optimizer is assured that for every row in the child table, a corresponding row exists in the parent table. The optimizer can use this knowledge to improve performance because accessing the parent table isn’t necessary to verify the existence of a row for the corresponding child row.

The only difference between these two queries is that the second one, as noted, is using Address.StateProvinceID instead of StateProvince.StateProvinceID in the SELECT statement. With the foreign key constraint gone, these two queries generate the execution plans, shown in the Compare ShowPlan view, in Figure 14-23.

A diagram of 2 queries with identical execution plans, which consists of 2 clustered index seek, points to nested loop, then to select.

Running the queries from Listing 14-27 again, I get the execution plans shown in Figure 14-24:

A diagram of execution plans of two queries. 1, Two cluster indexes seek points to nested loops and finally select. 2, cluster index seeks points to select.

Clearly, the second query now has a new execution plan. When the foreign key wasn’t present, the optimizer didn’t know if rows in the parent and the child table, StateProvince and Address, matched. So it had to validate that by using join operation, Nested Loops in this case. However, with the foreign key put back in place, specifically using the WITH CHECK option to validate the data, the optimizer could now simplify the execution plan and ignore the JOIN operation entirely, arriving at superior performance.