What causes blocking?

We have alluded to it already, and the answer is that when you use resources, they are locked. These locks can be on several different levels and types of resources, as seen in Table 14-2.

Locks on a given resource are of a mode. Table 14-3 lists the modes that a data object might be in. Two of the most important ones are shared (indicating a row is being read only) and exclusive (indicating a row should not be accessible by any other connection.)

As queries are performing different operations, such as querying data, modifying data, or changing objects, resources are locked in a given mode. Blocking comes when one connection has a resource locked in a certain mode, and another connection needs to lock a resource in an incompatible mode. You can see the compatibility of different modes in Table 14-4.

If a connection is reading data, it will take a shared lock, allowing other readers to also take a shared lock, which will not cause a blocked situation. However, if another connection is modifying data, it will get an exclusive lock, which will prevent the connection (and any other connections) from accessing the exclusively locked resources in any manner (other than ignoring the locks, discussed later in this section).

How to observe locks and blocking

You can find out in real time whether a request is being blocked. The dynamic management object (DMO) sys.dm_db_requests, when combined with sys.dm_db_sessions on the session_id column, provides data about blocking and the state of sessions on the server. This provides much more information than the legacy sp_who or sp_who2 commands, as you can see in this query:

Click here to view code image

--This query will return a plethora of information
--in addition to just the session that is blocked
SELECT r.session_id, r.blocking_session_id, *
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_requests r ON r.session_id = s.session_id;
--note: requests represent actions that are executing, sessions are connections,
--hence LEFT OUTER JOIN

You can see details of what objects are locked by using the sys.dm_tran_locks DMO, or what locks are involved in blocking by using this query:

Click here to view code image

SELECT
        t1.resource_type,
        t1.resource_database_id,
        t1.resource_associated_entity_id,
        t1.request_mode,
        t1.request_session_id,

        t2.blocking_session_id
    FROM sys.dm_tran_locks as t1
    INNER JOIN sys.dm_os_waiting_tasks as t2
        ON t1.lock_owner_address = t2.resource_address;

The output of this query reveals the type of resource that is locked (listed in Table 14-3) and the mode listed in Table 14-4, with a few exceptions.

• You can find more details about locks and sys.dm_tran_locks at https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql.

Now, let’s review some scenarios to detail exactly why and how requests can block one another in the real world when using disk-based tables. This is the foundation of concurrency in SQL Server and helps you understand why the NOLOCK query hint appears to make queries perform faster.

Use the SET TRANSACTION ISOLATION LEVEL statement

You can change the isolation level of a connection any time, even when already executing in the context of a transaction that is uncommitted. You are not allowed to swap to and from the SNAPSHOT isolation level because, as we’ll discuss later in this chapter, this isolation level works very differently.

For example, the following code snippet is technically valid to change from READ COMMITTED to SERIALIZABLE isolation levels. If one statement in your batch required the protection of SERIALIZABLE, but not others, you can execute:

Click here to view code image

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT...;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
COMMIT TRAN;

This code snippet is trying to change from the READ COMMITTED isolation level to the SNAPSHOT isolation level:

Click here to view code image

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT...

Attempting this results in the following error:

Click here to view code image

Msg 3951, Level 16, State 1, Line 4
Transaction failed in database 'databasename' because the statement was run under
snapshot isolation but the transaction did not start in snapshot isolation. You cannot
change the isolation level of the transaction after the transaction has started.

In .NET applications, you should change the isolation level of each transaction when it is created, as it might not be in READ COMMITTED by default, which offers far better performance.

Use table hints to change isolation

You also can use isolation level hints to change the isolation level at the individual object level. This is an advanced type of coding that you shouldn’t use frequently, because it generally increases the complexity of maintenance and muddies architectural decisions with respect to concurrency. Just as in the previous session, however, you might want to hold locks at a SERIALIZABLE level for one table but not others in the query. For example, you might have seen developers use NOLOCK at the end of a table, effectively (and dangerously) dropping access to that table into the READ UNCOMMITTED isolation level:

Click here to view code image

SELECT col1 FROM dbo.Table (NOLOCK);

SELECT col1 FROM dbo.TableName WITH (READUNCOMMITTED);

In addition to the (generally undesirable) NOLOCK query hint, there are 20-plus other table hints that can be useful, including the ability for a query to use a certain index, to force a seek or scan on an index, or to override the Query Optimizer’s locking strategy. We discuss how to use UPDLOCK later in this chapter—for example, to force the use of the SERIALIZABLE isolation level.

In almost every case, table hints should be considered for temporary and/or highly situational purposes. Table hints can make maintenance of these queries problematic, and could even cause surprise errors in the future. For example, using the INDEX or FORCESEEK table hint could result in poor query performance or even cause the query to fail if the table’s indexes are changed.

• For detailed information on all possible table hints, see https://learn.microsoft.com/sql/t-sql/queries/hints-transact-sql-table.

Understand two requests updating the same rows

Two users attempting to modify the same resource is possibly the most obvious concurrency issue. As an example, suppose one user wants to add $100 to a total, and another wants to add $50. If both processes happen simultaneously, only one row may be modified—or if we take it to the absurd extreme, data corruption could occur to the physical structures holding the data if pointers are mixed up by multiple modifications.

Consider the following steps involving two writes, with each transaction coming from a different session. The transactions are explicitly declared using the BEGIN/COMMIT TRANSACTION syntax. In this example, the transactions use the default isolation level READ COMMITTED.

All examples have these two rows, simply so it isn’t just a single row—though we do only manipulate the row where Type = 1. When testing more complex concurrency scenarios, it is best to have large quantities of data to work with, as indexing, server resources, and so on do come into play. These examples illustrate fundamental concurrency examples.

1. A table contains only two rows with a column Type containing values of 0 and 1.

   Click here to view code image

   CREATE SCHEMA Demo;
   GO
   CREATE TABLE Demo.RC_Test (Type int);
   INSERT INTO Demo.RC_Test VALUES (0),(1);

2. Transaction 1 begins and updates all rows from Type = 1 to Type = 2.

   Click here to view code image

   --Transaction 1
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   BEGIN TRANSACTION;
   UPDATE Demo.RC_Test SET Type = 2
   WHERE  Type = 1;

3. Before transaction 1 commits, transaction 2 begins, and issues a statement to update Type = 2 to Type = 3. Transaction 2 is blocked and waits for transaction 1 to commit.

   Click here to view code image

   --Transaction 2
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   UPDATE Demo.RC_Test SET Type = 3
   WHERE  Type = 2;

4. Transaction 1 commits.

   --Transaction 1
   COMMIT;

5. Transaction 2 is no longer blocked and processes its update statement. Transaction 2 then commits.

The resulting table will contain a row of Type = 3, and one of Type = 0, as the second transaction will have updated the row after the block was ended. This is because when transaction 2 started, it waited for the exclusive lock to be removed after transaction 1 committed.

Understand how a write blocks a read

One of the most painful parts of blocking comes when users are trying to write data that other users are blocked from reading. What can even be more problematic is that some modification statements actually lock rows in the table even if they don’t make any changes (typically due to poorly written WHERE clauses or a lack of indexing causing full table scans).

Consider the following steps involving a write and a read, with each transaction coming from a different session. In this scenario, an uncommitted write in transaction 1 blocks a read in transaction 2. The transactions are explicitly started using the BEGIN/COMMIT TRANSACTION syntax. In this example, the transactions do not override the default isolation level of READ COMMITTED:

1. A table with a column Type contains only two rows, with values of 0 and 1.

   Click here to view code image

   CREATE SCHEMA Demo AUTHORIZATION dbo;
   CREATE TABLE Demo.RC_Test_Write_V_Read (Type int);
   INSERT INTO Demo.RC_Test_Write_V_Read VALUES (0),(1);

2. Transaction 1 begins and updates rows with Type = 1 to Type = 2.

   Click here to view code image

   --Transaction 1
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   BEGIN TRANSACTION;
   UPDATE Demo.RC_Test_Write_V_Read SET Type = 2
   WHERE  Type = 1;

   Note that transaction 1 has not committed or rolled back.

3. Before transaction 1 commits, in another session, transaction 2 begins and issues a SELECT statement for rows WHERE Type = 2. Transaction 2 is blocked and waits for transaction 1 to commit.

   Click here to view code image

   --Transaction 2
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   SELECT Type
   FROM   Demo.RC_Test_Write_V_Read
   WHERE  Type = 2;

4. Transaction 1 commits.

   --Transaction 1
   COMMIT;

5. Transaction 2 is no longer blocked and processes its SELECT statement.

6. Transaction 2 returns one row where Type = 2. This is because when transaction 2 started, it saw only one row where Type = 2, but still waited for committed data until after transaction 1 committed.

Understand nonrepeatable reads

There are certain scenarios where you need to have the same row values returned every time you issue a SELECT statement, or read data in any data manipulation language (DML). A prime example is the case where you look for the existence of some data before allowing some other action to occur. For example: Insert an order row, but only if a payment exists. If that payment is changed or deleted while you are creating the order, free products might be shipped!

Consider the following steps involving a read and a write. In this example, the transactions do not override the default isolation level of READ COMMITTED, and each transaction is started from a different session. The transactions are explicitly declared using the BEGIN/COMMIT TRANSACTION syntax. In this scenario, transaction 1 will suffer a nonrepeatable read when it reads rows that are changed by a different connection because the default READ COMMITTED does not offer any protection against phantom or nonrepeatable reads.

1. A table contains only two rows with a column Type value of 0 and 1.

   Click here to view code image

   CREATE TABLE Demo.RR_Test (Type int);
   INSERT INTO Demo.RR_Test VALUES (0),(1);

2. Transaction 1 starts and retrieves rows where Type = 1. One row is returned for Type = 1.

   Click here to view code image

   --Transaction 1
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   BEGIN TRANSACTION
   SELECT Type
   FROM   Demo.RR_Test
   WHERE  Type = 1;

3. Before transaction 1 commits, transaction 2 starts and issues an UPDATE statement, setting rows of Type = 1 to Type = 2. Transaction 2 is not blocked and is immediately processed.

   Click here to view code image

   --Transaction 2
   BEGIN TRANSACTION;
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   UPDATE Demo.RR_Test
   SET  Type = 2
   WHERE Type = 1;

4. Transaction 1 again selects rows where Type = 1 and is blocked.

   --Transaction 1
   SELECT Type
   FROM   Demo.RR_Test
   WHERE  Type = 1;

5. Transaction 2 commits.

   --Transaction 2
   COMMIT;

6. Transaction 1 is immediately unblocked. No rows are returned, because no committed rows now exist where Type = 1. Transaction 1 commits.

   --Transaction 1
   COMMIT;

The result set from transaction 1 contains a row where Type = 2, because the second transaction has modified the row. When transaction 2 started, transaction 1 had not placed any locks on the data, allowing for writes to happen. Because it is doing only reads, transaction 1 does not place exclusive locks on the data. Transaction 1 suffered from a nonrepeatable read: The same SELECT statement returned different data during the same multistep transaction.

Prevent a nonrepeatable read

Consider the following steps involving a read and a write, with each transaction coming from a different session. This time, we protect transaction 1 from dirty reads and nonrepeatable reads by using the REPEATABLE READ isolation level. A read in the REPEATABLE READ isolation level will block a write. The transactions are explicitly declared using the BEGIN/COMMIT TRANSACTION syntax:

1. A table contains only rows with a column Type value of 0 and 1.

   Click here to view code image

   CREATE TABLE Demo.RR_Test_Prevent (Type int);
   INSERT INTO Demo.RR_Test_Prevent VALUES (0),(1);

2. Transaction 1 starts and selects rows where Type = 1 in the REPEATABLE READ isolation level. One row with Type = 1 is returned.

   Click here to view code image

   --Transaction 1
   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN TRANSACTION;
   SELECT Type
   FROM   Demo.RR_Test_Prevent
   WHERE  TYPE = 1;

3. Before transaction 1 commits, transaction 2 starts and issues an UPDATE statement, setting rows of Type = 1 to Type = 2. Transaction 2 is blocked by transaction 1.

   Click here to view code image

   --Transaction 2
   BEGIN TRANSACTION;
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   UPDATE Demo.RR_Test_Prevent
   SET  Type = 2
   WHERE Type = 1;

4. Transaction 1 again selects rows where Type = 1. The same rows are returned as in step 2.

5. Transaction 1 commits.

   --Transaction 1
   COMMIT TRANSACTION;

6. Transaction 2 is immediately unblocked and processes its update. Transaction 2 commits.

   --Transaction 2
   COMMIT TRANSACTION;

Transaction 1 returned the same rows each time and did not suffer a nonrepeatable read. The resulting table contains two rows, one where Type = 2, and the original row where Type = 0. This is because when transaction 2 started, transaction 1 had placed read locks on the data it was selecting, blocking writes until it committed. Transaction 2 processed its updates only when it could place exclusive locks on the rows it needed.

Understand phantom rows

Phantom rows cause issues for transactions when you expect the exact same result back from a query. Say you’re writing a value to a table that sums up 100 other values (flaunting the fundamentals of database design’s normalization rules!)—for example, a financial transactions ledger table that calculates the current balance. You sum the 100 rows, then write the value. If it is important that the total of the 100 rows matches perfectly, you cannot allow nonrepeatable reads or phantom rows.

Consider the following steps involving a read and a write, with each transaction coming from a different session. In this scenario, we describe a phantom read:

1. A table contains only two rows, with Type values 0 and 1.

   Click here to view code image

   CREATE TABLE Demo.PR_Test (Type int);
   INSERT INTO Demo.PR_Test VALUES (0),(1);

2. Transaction 1 starts and selects rows where Type = 1 in the REPEATABLE READ isolation level. Rows are returned.

   Click here to view code image

   --Transaction 1
   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN TRANSACTION;
   SELECT Type
   FROM   Demo.PR_Test
   WHERE  Type = 1;

3. Before transaction 1 commits, transaction 2 starts and issues an INSERT statement, adding another row where Type = 1. Transaction 2 is not blocked by transaction 1.

   Click here to view code image

   --Transaction 2
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   INSERT INTO Demo.PR_Test(Type)
   VALUES(1);

4. Transaction 1 again selects rows where Type = 1. An additional row is returned compared to the first time transaction 1 ran the SELECT.

   --Transaction 1
   SELECT Type
   FROM   Demo.PR_Test
   WHERE  Type = 1;

5. Transaction 1 commits.

   --Transaction 1
   COMMIT TRANSACTION;

Transaction 1 experienced a phantom read when it returned a different number of rows the second time it selected from the table inside the same transaction. Transaction 1 had not placed any locks on the range of data it needed, allowing for writes in another transaction to happen within the same dataset. The phantom read would have occurred to transaction 1 in any isolation level, except for SERIALIZABLE. Let’s look at that next.

Prevent phantom reads

Consider the following steps involving a read and a write, with each transaction coming from a different session. In this scenario, we protect transaction 1 from a phantom read.

1. A table contains two rows with Type values of 0 and 1.

   Click here to view code image

   CREATE TABLE Demo.PR_Test_Prevent (Type int);
   INSERT INTO Demo.PR_Test_Prevent VALUES (0),(1);

2. Transaction 1 starts and selects rows where Type = 1 in the SERIALIZABLE isolation level. The one row where Type = 1 is returned.

   Click here to view code image

   --Transaction 1
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION;
   SELECT Type
   FROM   Demo.PR_Test_Prevent
   WHERE  Type = 1;

3. Before transaction 1 commits, transaction 2 starts and issues an INSERT statement, adding a row of Type = 1. Transaction 2 is blocked by transaction 1.

   Click here to view code image

   --Transaction 2
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   INSERT INTO Demo.PR_Test_Prevent(Type)
   VALUES(1);

4. Transaction 1 again selects rows where Type = 1. The same result set is returned as it was in step 2—the one row where Type = 1.

   --Transaction 1
   SELECT Type
   FROM   Demo.PR_Test_Prevent
   WHERE  Type = 1;

5. Transaction 1 executes COMMIT TRANSACTION.

   --Transaction 1
   COMMIT TRANSACTION;

6. Transaction 2 is immediately unblocked and processes its insert. Transaction 2 commits.

If you query the table again, you will see there are now two rows where Type = 1.

Transaction 1 did not suffer from a phantom read the second time it selected from the table because it had placed a lock on the range of rows it needed. The table now contains additional rows where Type = 1, but they were not inserted until after transaction 1 had committed.

The case against the READ UNCOMMITTED isolation level

If locks take time, ignoring those locks will make things go faster. While this is true, the tradeoffs are often not worth it.

Locks coordinate our access to resources, allowing multiple users to do multiple things in the database without crushing the other users’ changes. The READ COMMITTED isolation level (and an extension we will discuss in the section on the SNAPSHOT isolation level called READ COMMITTED SNAPSHOT) does the best to balance locks with performance. Locks are still held for dirty resources (exclusively locked data that has been changed by the user). But they are held only long enough to perform reads on a row and are then released after resources are read. The process is as follows:

1. Grab a lock on a resource.

2. Read that resource.

3. Release the lock on the resource.

4. Repeat until you are out of resources to read.

No one else can dirty (modify) the row we are reading because of the lock, but when we are done, we release the lock and move on. Locks on modifications to on-disk tables work the same way in all isolation levels, even READ UNCOMMITTED, and are held until the transaction is committed.

The effect of the NOLOCK table hint and the READ UNCOMMITTED isolation level is that no locks are taken inside the database for reads, save for schema stability locks. Though, a query using NOLOCK could still be blocked by data definition language (DDL) commands, such as an offline indexing operation. Put another way, if you enable the READ UNCOMMITTED isolation level for your connection, things will go faster.

This is a strategy that many DBA programmers have tried before: “We had performance problems, but we’ve been putting NOLOCK in all our stored procedures to fix it.” It will improve performance, but it can easily be detrimental to the integrity of your data.

The biggest issue is that a query might be reading a set of data and see data that doesn’t even meet the constraints of the system. So, if a transaction for $1,000,000 is in a query, and the transaction is later rolled back (perhaps because the payment details failed), who knows what celebratory alarms might have gone off, thinking we had $1,000,000 in sales today!

The case against using the READ UNCOMMITTED isolation level is deeper than performance and more than simply reading dirty data. A developer might argue that data is rarely ever rolled back, or that the data is for reporting only. In production environments, however, these are not enough to justify the potential problems.

A query in the READ UNCOMMITTED isolation level could return invalid data in the following real-world, provable ways:

• Read uncommitted data (dirty reads).

• Read committed data twice.

• Skip committed data.

• Return corrupted data.

• The query could fail with the error “Could not continue scan with NOLOCK due to data movement.” In this scenario, where you ignored locks, the data structure that was to be scanned now no longer exists because of other changes to data pages. The solution to this problem is the solution to a lot of concurrency issues: Be prepared to re-execute your batch on this failure.

One final caveat: In SQL Server, you cannot apply NOLOCK to tables when used in modification statements, and it ignores the declaration of the READ UNCOMMITTED isolation level in a batch that includes modification statements. For example:

Click here to view code image

INSERT INTO dbo.testnolock1 WITH (NOLOCK)
SELECT * FROM dbo.testnolock2;

SQL Server knows that it will use locks for the INSERT, and makes it clear by way of the following error being thrown:

Click here to view code image

Msg 1065, Level 15, State 1, Line 17
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT,
UPDATE, DELETE or MERGE statements.

However, this protection doesn’t apply to the source of any writes, hence yet another danger. This following code is allowed and is very dangerous because it could write invalid, uncommitted data!

Click here to view code image

INSERT INTO testnolock1
SELECT * FROM testnolock2 WITH (NOLOCK);

In summary, don’t use READ COMMITTED isolation level or NOLOCK unless you really understand the implications of reading dirty data and have an ironclad reason for doing so. For example, it is an invaluable tool as a DBA to be able to see the changes to data being made in another connection. For example,

Click here to view code image

SELECT COUNT(*) FROM dbo.TableName WITH (NOLOCK);

allows you to see the count of rows in dbo.TableName. However, using NOLOCK for performance gains is short-sighted.

Continue reading for the recommended ways to increase performance without the chance of seeing dirty data, as we introduce version-based concurrency in the next section.

Access data in SNAPSHOT isolation level

The beauty of the SNAPSHOT isolation level is its effect on readers of the data. If you want to query the database, you generally want to see it in a consistent state, and you don’t want to block others. A typical example is when you are writing an operational report. Suppose you query a child table and you get back 100 rows with distinct parentId values. But querying the parent table indicates there are only 50 parentId values (because between queries, another process deleted the other 50).

Consider the following steps involving a read and a write, with each transaction coming from a different session. In this scenario, we see that transaction 2 has access to previously committed row data, even though those rows are being updated concurrently.

1. A table contains only rows with a column Type value of 0 or 1.

   Click here to view code image

   CREATE TABLE Demo.SS_Test (Type int);
   INSERT INTO Demo.SS_Test VALUES (0),(1);

2. Transaction 1 starts and updates rows where Type = 1 to Type = 2.

   Click here to view code image

   --Transaction 1
   BEGIN TRANSACTION;
   SET TRANSACTION ISOLATION READ COMMITTED;
   
   UPDATE Demo.SS_Test
   SET  Type = 2
   WHERE Type = 1;

3. Before transaction 1 commits, transaction 2 sets its session isolation level to SNAPSHOT and executes BEGIN TRANSACTION.

   Click here to view code image

   --Transaction 2
   SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
   BEGIN TRANSACTION;

4. Transaction 2 issues a SELECT statement WHERE Type = 1. Transaction 2 is not blocked by transaction 1, a row where Type = 1 is returned.

   --Transaction 2
   SELECT Type
   FROM   Demo.SS_Test
   WHERE  Type = 1;

5. Transaction 1 executes a COMMIT TRANSACTION.

6. Transaction 2 again issues a SELECT statement WHERE Type = 1. The same rows from step 3 are returned. Even if the table has all its data deleted, the results will always be the same for the same query while in the SNAPSHOT level transaction. When transaction 2 is committed or rolled back, queries on that connection will see the changes that have occurred since the transaction started.

Transaction 2 was not blocked when it attempted to query rows that transaction 1 was updating. It had access to previously committed data, thanks to row versioning.

Implement row-versioned concurrency

You can implement row-versioned isolation levels in a database in two different ways:

• Enabling SNAPSHOT isolation. This simply allows for the use of SNAPSHOT isolation and begins the background process of row versioning.

• Enabling RCSI. This changes the default isolation level to READ COMMITTED SNAPSHOT.

You can implement both or either.

It’s important now to introduce another fundamental database concept: pessimistic versus optimistic concurrency. Pessimistic concurrency uses locks to prevent write conflict errors. This is the approach SQL Server takes by default with the READ COMMITTED isolation level. Optimistic concurrency uses row versions with a tolerance for write conflict errors and requires sometimes sophisticated conflict resolution.

It’s important to understand the differences between these two settings, because they are not the same:

• READ COMMITTED SNAPSHOT configures optimistic concurrency for reads by overriding the default isolation level of the database. When enabled, all queries will use RCSI unless overridden, not READ COMMITTED.

• SNAPSHOT isolation mode configures optimistic concurrency for reads and writes. You must then specify the SNAPSHOT isolation level for any transaction to use SNAPSHOT isolation level. It is possible to have update conflicts with SNAPSHOT isolation mode that will not occur with READ COMMITTED SNAPSHOT. Update conflicts are covered in the next section.

The statements to implement SNAPSHOT isolation in the database are not without consequence. Even if no transactions or statements use the SNAPSHOT isolation level, behind the scenes, tempdb begins storing row version data for disk-based tables, minimally for the length of the transaction that modifies the row. This way, if a row-versioned transaction starts while rows are being modified, the previous versions are available.

The following code snippet allows all transactions in a database to start in the SNAPSHOT isolation level:

Click here to view code image

ALTER DATABASE databasename SET ALLOW_SNAPSHOT_ISOLATION ON;

After you execute only the preceding statement, all transactions will continue to use the default READ COMMITTED isolation level, but you now can specify the use of the SNAPSHOT isolation level at the session level or in table hints, as shown in the following example:

Click here to view code image

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Using SNAPSHOT isolation level on an existing database can be a lot of work, and, as we discuss in the next section, it changes how we handle executing queries in some very important ways. Because of the optimistic locking approach, what once was write blocking becomes an error message for you to try again. Alternatively, if you want to apply the “go faster” solution that mostly works with existing code, you need to alter the meaning of READ COMMITTED to read row versions instead of waiting for locks to clear.

While SNAPSHOT isolation works at the transaction level, READ COMMITTED SNAPSHOT works at the statement level. You can use READ_COMMITTED_SNAPSHOT independently of ALLOW_SNAPSHOT_ISOLATION. Similarly, these settings are not tied to the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT database setting to promote memory-optimized table access to SNAPSHOT isolation.

Here’s how to enable RCSI:

Click here to view code image

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

• Chapter 7 discusses memory-optimized tables in greater detail.

For either of the previous ALTER DATABASE statements to succeed, no other transactions can be open in the database. It might be necessary to close other connections manually or to put the database in SINGLE_USER mode. Either way, we do not recommend that you perform this change during production activity.

It is essential to be aware and prepared for the increased activity in the tempdb database, both in activity and space requirements. To avoid autogrowth events when enabling RCSI, increase the size of the tempdb data and log files and monitor their size. Although you should try to avoid autogrowth events by growing the tempdb data file(s) yourself, you should also verify that your tempdb file autogrowth settings are appropriate in case things grow larger than expected.

• For more information on file autogrowth settings, see Chapter 8.

If tempdb exhausts all available space on its volume, SQL Server will be unable to row-version rows for transactions and will terminate them with SQL Server error 3958. You can find these in the SQL Server Error Log. (Refer to Chapter 1, “Get started with SQL Server tools.”) SQL Server will also issue errors 3967 and 3966 as the oldest row versions are removed from tempdb to make room for new row versions needed by newer transactions.

Understand update operations in the SNAPSHOT isolation level

Transactions that read data in SNAPSHOT isolation or RCSI have access to previously committed data—instead of being blocked—when data needed is being changed. This is important to understand and could result in an UPDATE statement experiencing a concurrency error when you start to change data. Update conflicts change how systems behave; you need to understand this concept before deciding to implement your code in the SNAPSHOT isolation level.

When modifying data in the SNAPSHOT isolation level, you can only have one dirty version of a physical resource. So, if another connection modifies a row and you only read the row, you see previous versions. But if you change a row that another connection has also modified, your update was based on out of data information and will be rolled back.

For example, consider the following steps, with each transaction coming from a different session. In this example, transaction 2 fails due to a concurrency conflict, or write-write error:

1. A table contains multiple rows, each with a unique Type value.

   Click here to view code image

   CREATE TABLE Demo.SS_Update_Test
   (Type int CONSTRAINT PKSS_Update_Test PRIMARY KEY,
    Value nvarchar(10));
   INSERT INTO Demo.SS_Update_Test VALUES (0,'Zero'),(1,'One'),(2,'Two'),(3,'Three');

2. Transaction 1 begins a transaction in the READ COMMITTED isolation level and performs an update on the row where ID = 1.

   --Transaction 1
   BEGIN TRANSACTION ;
   UPDATE Demo.SS_Update_Test
   SET  Value = 'Won'
   WHERE Type = 1;

3. Transaction 2 sets its session isolation level to SNAPSHOT and issues a statement to update the row where ID = 1. This connection is blocked, waiting for the modification locks to clear.

   Click here to view code image

   --Transaction 2
   SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
   BEGIN TRANSACTION
   UPDATE Demo.SS_Update_Test
   SET  Value = 'Wun'
   WHERE Type = 1;

4. Transaction 1 commits using a COMMIT TRANSACTION statement. Transaction 1’s update succeeds.

5. Transaction 2 immediately fails with error 3960:

Click here to view code image

Msg 3960, Level 16, State 2, Line 8
Snapshot isolation transaction aborted due to update conflict. You cannot use
snapshot isolation to access table 'dbo.AnyTable' directly or indirectly in
database 'DatabaseName' to update, delete, or insert the row that has been
modified or deleted by another transaction. Retry the transaction or change
the isolation level for the update/delete statement.

Transaction 2 was rolled back. Let’s try to understand why this error occurred, what to do about it, and how to prevent it.

In SQL Server, SNAPSHOT isolation uses locks to create blocking, but it doesn’t prevent updates from colliding for disk-based tables. It is possible for a statement to fail when committing changes from an UPDATE statement if another transaction has changed the data needed for an update during a transaction in SNAPSHOT isolation level.

For disk-based tables, the update conflict error will look like error 3960, which we saw a moment ago. For queries on memory-optimized tables, the update conflict error will look like this:

Click here to view code image

Msg 41302, Level 16, State 110, Line 8
The current transaction attempted to update a record that has been updated since this
transaction started. The transaction was aborted.

If you decide to use SNAPSHOT as your modification query’s isolation level, you must be ready to handle an error that isn’t really an error; rather, it’s just warning to re-execute your statements after checking to see if anything has changed since you started your query. This is the same when handling deadlock conditions, and will be the same for handling all modification conflicts using memory-optimized tables.

• For more information, see https://learn.microsoft.com/sql/connect/ado-net/configurable-retry-logic and https://learn.microsoft.com/azure/azure-sql/database/troubleshoot-common-connectivity-issues.

Even though optimistic concurrency of the SNAPSHOT isolation level increases the potential for update conflicts, you can mitigate these by doing the following to specifically attempt to avoid update conflicts:

• Minimize the length of transactions that modify data. While it seems like this would be less of an issue because readers aren’t blocked, long-running transactions increase the likelihood of modification conflicts. Also, tempdb needs to keep track of more row versions.

• When running a modification in SNAPSHOT isolation level, avoid using statements that place update locks on disk-based tables inside multistep explicit transactions.

• Specify the UPDLOCK table hint to prevent update conflict errors for long-running SELECT statements. UPDLOCK places locks on rows needed for the multistep transaction to complete. The use of UPDLOCK on SELECT statements with SNAPSHOT isolation level is not a panacea for update conflicts, however, and it could in fact create them. For example, frequent SELECT statements with UPDLOCK could increase the number of update conflicts. Regardless, your application must handle errors and initiate retries when appropriate.

  Note

  If two concurrent statements use UPDLOCK, with one updating and one reading the same data, even in implicit transactions, an update conflict failure is possible if not likely.

• Consider avoiding writes altogether while in SNAPSHOT isolation mode. Use it only to do reads where you do not plan to write the data in the same transaction you have fetched it in.

Specifying table granularity hints such as ROWLOCK or TABLOCK can prevent update conflicts, although at the cost of concurrency. The second update transaction must be blocked while the first update transaction is running—essentially bypassing SNAPSHOT isolation for the write. If two concurrent statements are both updating the same data in SNAPSHOT isolation level, an update conflict failure is likely for the statement that started second.

Understand memory-optimized data and isolation

All data read by a statement in memory-optimized tables behaves like the SNAPSHOT isolation level. Once your transaction starts and you access memory-optimized data in the database, further reads from memory-optimized tables will be from a consistent view of those objects. (The memory-optimized and on-disk tables are in different “containers,” so your consistent view of the memory-optimized data doesn’t start if you read on-disk tables only.)

However, what makes your work more interesting is that when in the REPEATABLE READ or SERIALIZABLE isolation level, the scan for phantom and non-repeatable read rows is done during commit rather than as they occur.

For example, consider the following steps, with each transaction coming from a different session. A typical scenario might be running a report of some data. You read a set of data, perform some operation on it, and then read another set of rows, and your process requires the data to all stay the same.

1. A table contains many rows, each with a unique ID. Transaction 1 begins a transaction in the SERIALIZABLE isolation level and reads all the rows in the table.

2. Transaction 2 updates the row where ID = 1. This transaction commits successfully.

3. Transaction 1 again reads rows in this same table. No error is raised, and rows are returned as normal.

4. Transaction 1 commits. An error is raised (41305), alerting you to a non-repeatable read. Even though this was in the SERIALIZABLE isolation level, the check for a non-repeatable read is done first, since this is a reasonably easy operation, whereas the scan for phantom rows requires the engine to run a query on the data to see if extra rows are returned.

Most uses of isolation levels other than SNAPSHOT with memory-optimized data should be limited to operations like data-integrity checks, where you want to make sure that one row exists before you insert the next row.

• For more details on conflict detection and retry logic with memory-optimized tables, see https://learn.microsoft.com/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables#conflict-detection-and-retry-logic.

Specify the isolation level for memory-optimized tables in queries

The isolation level is specified in a few mildly confusing ways for memory-optimized tables. The first method is in an ad hoc query, not in an existing transaction context, where you can query the table as you always have and it will be accessed in the SNAPSHOT isolation level.

If you are in the context of a transaction, it will not automatically default to the SNAPSHOT isolation level. Rather you need to specify the isolation level as a hint, such as:

Click here to view code image

BEGIN TRANSACTION;
SELECT *
FROM   dbo.MemoryOptimizedTable WITH (SNAPSHOT);

You can make it default to SNAPSHOT isolation level by enabling the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT database option. This promotes access to all memory-optimized tables in the database up to the SNAPSHOT isolation level if the current isolation level is not REPEATABLE READ or SERIALIZABLE. It also promotes the isolation level to SNAPSHOT from isolation levels such as READ UNCOMMITTED and READ COMMITTED. This option is disabled by default, but you should consider enabling it; otherwise, you cannot use the READ UNCOMMITTED or SNAPSHOT isolation levels for a session including memory-optimized tables.

If you need to use REPEATABLE READ or SERIALIZABLE, or your scenario does not meet the criteria for automatically choosing the SNAPSHOT isolation level, you can specify the isolation level using table concurrency hints. (See the section “Use table hints to change isolation” earlier in this chapter.) Only memory-optimized tables can use this SNAPSHOT table hint, not disk-based tables.

Finally, you cannot mix the disk-based SNAPSHOT isolation level with the memory-optimized SNAPSHOT isolation level. For example, you cannot include memory-optimized tables in a session that begins with SET TRANSACTION ISOLATION LEVEL SNAPSHOT, even if MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON or you specify the SNAPSHOT table hint.

• For more information on configuring memory-optimized tables, see Chapter 7. We discuss more about indexes for memory-optimized tables in Chapter 15.

Display the estimated execution plan

You can generate the estimated execution plan quickly and view it graphically from within SSMS or Azure Data Studio by choosing the Display Estimated Execution Plan option in the Query menu or by pressing Ctrl+L. An estimated execution plan will return for the highlighted region or for the entire file if no text is selected.

You can also retrieve an estimated execution plan in T-SQL code by running the following statement. It will be presented in an XML format:

SET SHOWPLAN_XML ON;

In SSMS, in Grid mode, the results are displayed as a link as for any XML output. SSMS knows this is a plan, however, so you can select the link to view the plan graphically in SSMS. You can then save the execution plan as a .sqlplan file by right-clicking the neutral space of the plan window and selecting Save Execution Plan As.

You can also configure the estimated text execution plan in code by running one of the following statements, which return the execution plan in one result set or two, respectively:

SET SHOWPLAN_ALL ON;
SET SHOWPLAN_TEXT ON;

The text plan of the query using one of these two statements can be useful if you want to send the plan to someone in an email in a compact manner.

As expected, the estimated execution plan is not guaranteed to match the actual plan used when you run the statement, but it is usually a very reliable approximation that you can use to see if a query looks like it will execute well enough. The Query Optimizer uses the same information for the estimate as it does for the actual plan when you run it.

To display information for individual steps, hover over a step in the execution plan. In SSMS or Azure Data Studio, select an object, right-click on it, and select Properties. After you have a bit of experience with plans, you’ll notice the estimated execution plan is missing some information that the actual plan returns. The missing fields are generally self-explanatory in that they are values you would not have until the query has actually executed—for example, Actual Number of Rows for All Executions, Actual Number of Batches, and Estimated Number of Rows for All Executions.

Display the actual execution plan

You can view the actual execution plan used to execute the query along with the statement’s result set from within SSMS by choosing the Include Actual Execution Plan option in the Query menu or by pressing Ctrl+M to enable the setting. After enabling this setting, when you run a statement, you will see an additional tab appear along with the execution results after the results of the statements have completed.

You can also return the actual execution plan as a result set using T-SQL code, returning XML that can be viewed graphically in SSMS, by running the following statement:

SET STATISTICS XML ON;

The actual execution plan is returned as an XML string. In SSMS, in Grid mode, the results display as a link, which you can view graphically by selecting the link. Remember to disable the SET STATISTICS option before you reuse the same session if you don’t want to get back the actual plan for every query you run on this connection.

You can save both estimated and actual execution plans as a .sqlplan file by right-clicking the neutral space of the plan window and selecting Save Execution Plan As.

You might see that the total rows to be processed does not match the total estimated number of rows for that step—or rather, the multiple of that step’s estimated number of rows and a preceding step’s estimated number of rows.

For an example of an execution plan, consider the following query in the WideWorldImporters sample database:

Click here to view code image

SELECT * FROM Sales.Invoices
JOIN Sales.Customers
on Customers.CustomerId = Invoices.CustomerId
WHERE Invoices.InvoiceID like '1%';

Figure 14-1 shows part of the actual execution plan for this query.

In Figure 14-1, on the Merge Join (Inner Join) operator, you can see that 11111 of 6654 rows were processed. The 6654 was the estimate, and 11111 was the actual number of rows.

Displaying live query statistics

Live query statistics, introduced in SSMS v16, are an excellent feature. With this feature, you can generate and display a “live” version of the execution plan using SSMS. It allows you to access live statistics on versions of SQL Server starting with SQL Server 2014. To use it, enable the Live Execution Statistics option for a connection via the Query menu in SSMS.

If you execute the query from the previous section with live query statistics enabled, you will see something like what’s shown in Figure 14-2. Notice that 1,684 of the estimated 6,654 rows have been processed by the Merge Join operator, and 93 rows have been processed by the Nested Loops (Left Semi Join) operator.

The Live Query Statistics window initially displays the execution plan more or less like the estimated plan, but fills out the details of how the query is being executed as it is processing it. If your query runs quickly, you’ll miss the dotted, moving lines and the various progress metrics, including the duration for each step and overall percentage completion. The percentage is based on the actual rows processed currently incurred versus a total number of rows processed for that step.

The Live Query Statistics window contains more information than the estimated query plan, such as the actual number of rows and number of executions, but less than the actual query plan. The Live Query Statistics window does not display some data from the actual execution plan such as actual execution mode, number of rows read, and actual rebinds.

Returning a live execution plan will noticeably slow down query processing, so be aware that the individual and overall execution durations measured will often be longer than when the query is run without the option to display live query statistics. However, it can be worth it to see where a query is hung up in processing.

If your server is configured correctly to do so, you can see the live query plan of executing queries in action by using the Activity Monitor in SSMS. In the Activity Monitor you can access the live execution plan by right-clicking any query in the Processes or Active Expensive Queries panes and selecting Show Live Execution Plan.

Permissions necessary to view execution plans

Not just anyone can view a query’s execution plans. There are two ways you can view plans, and they require different kinds of permissions.

If you want to generate and view a query plan, you will first need permissions to execute the query, even to get the estimated plan. Additionally, retrieving the estimated or actual execution plan requires the SHOWPLAN permission in each database referenced by the query. The live query statistics feature requires SHOWPLAN in each database, plus the VIEW SERVER STATE permission to see live statistics, so it cannot (and should not) be done by just any user.

It might be appropriate in your environment to grant SHOWPLAN and VIEW SERVER STATE permissions to developers. However, the permission to execute queries against the production database might not be appropriate in your regular environment. If that is the case, there are alternatives to providing valuable execution plan data to developers without production access. For example:

• Providing database developers with saved execution plan (.sqlplan) files for offline analysis.

• Configuring dynamic data masking, which might already be appropriate in your environment for hiding sensitive or personally identifying information for users who are not sysadmins on the server. Do not provide UNMASK permission to developers, however; assign that only to application users.

• Sometimes, an administrator may need to execute queries on a production server due to differences in environment or hardware, but be cautioned on all of what that means in terms of security, privacy, and so on.

  • For more information on dynamic data masking, see Chapter 13, “Protect data through classification, encryption, and auditing.”

There are several tools that provide the ability to see existing execution plans. For example, Extended Events and Profiler can capture execution plans. Query reports in Activity Monitor, such as Recent Expensive Queries, also allow you to see the plan (if it is still available in cache). Dynamic management views (DMVs) that provide access to queries that executed (such as sys.dm_exec_cached_plans) or requests (sys.dm_exec_requests) will have a column named plan_handle that you can pass to the sys.dm_exec_query_plan DMV to retrieve the plan. To access plans in this manner, the server principal needs the VIEW SERVER STATE permission or must be a member of the sysadmin server role.

Finally, and perhaps the best way to view execution plans, is the Query Store, which is covered later in this chapter. For this, you need VIEW DATABASE STATE permissions or to be a member of the db_owner fixed role.

Read graphical execution plans

This section steps you through reviewing execution plans in SSMS and covers some of the most common things to look for.

Start an execution plan

First, start an execution plan. For our purposes, run a simple one, like for the following query:

Click here to view code image

SELECT Invoices.InvoiceID
FROM Sales.Invoices
WHERE Invoices.InvoiceID like '1%';

Figure 14-3 shows the resulting estimated query plan.

To display details for individual steps, position your pointer over a step in the execution plan to open a detailed tooltip-style window, much like the one shown in Figure 14-4. An interesting detail immediately should come to you when you look at the plan. It doesn’t say it is scanning the Sales.Invoices table, but rather an index. If an index has all the data needed to execute the query, the index “covers” the needs of the query, and the table’s other data structures are not touched.

In SSMS, you can also select an operator in the execution plan and then press F4 or open the View menu and choose Properties Window to open its Properties window, where you will see the same estimated details.

Now, let’s get the actual execution plan, by pressing Ctrl+M or using one of the other methods discussed earlier. Execute the query and you will see the actual plan, as shown in Figure 14-5. It will be nearly identical to the estimate plan in Figure 14-3, but with more information.

The first thing you should notice is that the query plan has a few more details. In particular, 11,111 of 6,346 rows were processed, where 6,346 is the estimated number of rows that would be returned by the query. This guess was based on statistics, which do not give perfect answers. When you are tuning larger queries, very large disparities between such guesses and the actual number require investigation.

Open the Properties pane and you’ll notice the returned estimate and actual values for some metrics, including Number of Rows, Executions, IO Statistics, and more. Look for differences in estimates and actual numbers here; they can indicate an inefficient execution plan and the source of a poor performing query. Your query might be suffering from a poorly chosen plan because of the impact of parameter sniffing or due to stale, inaccurate index statistics.

• We discuss parameter sniffing later in this chapter, including a new feature in SQL Server 2022 intended to avoid this classic problem, PSP optimization. Chapter 15 discusses index statistics.

Notice that some values, like Cost Information, contain only estimated values, even when you are viewing the actual execution plan. This is because the operator costs aren’t sourced separately; rather, they are generated the same way for both the estimated and actual plans and do not change based on statement execution. Furthermore, cost is not just comprised entirely of duration. You might find that some statements far exceed others in terms of duration, but not in cost.

Start with the upper-left operator

The upper-left operator reflects the basic operation the statement performed—for example, SELECT, DELETE, UPDATE, INSERT, or any of the DML statements. (If your query created an index, you could also see CREATE INDEX.) This operator might contain warnings or other items that require your immediate attention. These typically appear with a small yellow triangle warning icon, over which you can position your mouse pointer to obtain additional details.

This was an intentional teaching opportunity! For example, in our sample plan, the SELECT operator is at the far left, and it has a triangle over it. You can see in the ToolTip that this is due to the following:

Click here to view code image

Type conversion in expression (CONVERT_IMPLICIT(varchar(12),[WideWorldImporters].
[Sales].[Invoices].[InvoiceID],0)) may affect "CardinalityEstimate" in query plan
choice, Type conversion in expression (CONVERT_IMPLICIT(varchar(12),[WideWorldImport
ers].[Sales].[Invoices].[InvoiceID],0)>='1') may affect "SeekPlan" in query plan choice.

In other words, we used a LIKE on an integer value, so the query plan is warning us that it cannot estimate the number of rows as well as it can if our WHERE clause employed integer comparisons to integers.

Select the upper-left operator and press F4 or open the View menu in SSMS and choose Properties Window. It lists some other things to look for. You’ll see warnings repeated in here, along with additional aggregate information.

Also look for the Optimization Level entry. This typically says Full. If the Optimization Level is Trivial, it means the Query Optimizer bypassed optimization of the query altogether because it was straightforward enough—for example, if the plan for the query only needed to contain a simple Scan or Seek operation along with the operation operator, like SELECT. If the Optimization Level is not Full or Trivial, this is something to investigate.

Look next for the presence of a value for Reason For Early Termination. This indicates the Query Optimizer did not spend as much time as it could have selecting the best plan. Here are a few possible reasons:

• Good Enough Plan. This is returned if the Query Optimizer determined that the plan it picked was good enough to not need to keep optimizing.

• Time Out. This indicates the Query Optimizer tried as many times as it could to find the best plan before taking the best plan available, which might not be good enough. If you see this, consider simplifying the query—in particular, by reducing the use of functions and potentially modifying the underlying indexes.

• Memory Limit Exceeded. This is a rare and critical error indicating severe memory pressure on the SQL Server instance.

Look right, then read from right to left

Graphical execution plans build from sources (rightmost objects) and apply operators to join, sort, and filter data from right to left, eventually arriving at the leftmost operator. Among the rightmost objects, you’ll see scans, seeks, and lookups of different types. You might find some quick, straightforward insight into how the query is using indexes.

Each of the items in the query plan are referred to as operators. Each operator is a module of code that does a certain task to process data.

Two of the main types of operators for fetching data from a table or index are seeks and scans.

A seek operator finds a portion of a set of data through the index structure, similar to how you would use the index of a book to locate coverage of a specific topic. Seek operators are generally the most efficient operators and can rarely be improved by additional indexes. The seek operation finds the leaf page in the index, which contains the keys of the index, plus any included column data (which in the case of a clustered table will be all the data for the row).

If the leaf data contains everything you need, it means the operation was covered by the index. However, if you need more data than the index contains, a lookup operator will join with the seek operator using a join operator. This means that although the Query Optimizer used a seek, it needed a second pass at the table in the form of a lookup on another object, typically the clustered index, using a second seek operator.

Key lookups (on clustered indexes) and RID lookups (on heaps) are expensive and inefficient, particularly when many rows are being accessed. These lookups can add up to a very large percentage of the cost of a query.

If key lookup operators are needed frequently, they can usually be eliminated by modifying the index that is being scanned. For instance, you could modify an existing nonclustered index to include additional columns. For an example, see the section “Design rowstore nonclustered indexes” in Chapter 15.

The other typical data source operator is a scan. Scan operations aren’t great unless your query is intentionally returning a large number of rows out of a table or index. Scans read all rows from the table or index, which can be very inefficient when you need to return only a few rows, but more efficient when you need many rows. Without a nonclustered index with a well-designed key (if one can be found) to enable a seek for the query, a scan might be the Query Optimizer’s only option. Scans can be ordered if the source data is sorted, which is useful for some joins and aggregation options.

Scans on nonclustered indexes are often better than scans on clustered indexes, in part due to what is likely a smaller leaf page size, because a nonclustered index doesn’t usually have all the columns on the leaf page. Nonclustered indexes suffer from the same issues with key lookups.

Other types of scans include the following:

• Table scan. This indicates that the table has no clustered index. Chapter 15 discusses why this is probably not a good idea.

• Index scan. This scans the rows of an index, even the included columns of the index, for values.

• Remote scan. This includes any object that is preceded by “remote,” which is the same operation but over a linked server connection. You troubleshoot these the same way, but potentially by making changes to the remote server instead. An alternative to linked server connections that might be superior in many cases is PolyBase. PolyBase can use T-SQL to query a variety of external data sources, including nonrelational and other relational data sources. Like a linked server, PolyBase accomplishes this by reading the data in place without data movement, or “virtualizing” the data.

  • For more details on PolyBase, see Chapter 7.

• Constant scan. These appear when the Query Optimizer deals with scalar values, repeated numbers, and other constants. These are necessary operators for certain tasks and generally not actionable from a performance standpoint.

• Columnstore index. This is an incredibly efficient operator when you are working with lots of rows but relatively few columns, and likely will outperform a clustered index scan or index seek where millions of rows, for example, must be aggregated. There is no need to create a nonclustered index to replace this operator unless your query is searching for a few rows.

The weight of the lines connecting operators tells part of the story, but isn’t the full story

SQL Server dynamically changes the thickness of the gray lines to reflect the actual number of rows. You can get a visual idea of where the bulk of data is coming from by observing the pipes (they look like arrows pointing to the left), which draw your attention to the places where performance tuning could have the biggest impact. If you hover over the line in your query plan, you can see the rows transmitted in each step. (See Figure 14-6.)

The visual weight and the sheer number of rows does not, however, directly translate to cost. Look for where the pipe weight changes from light to heavy, or vice versa. Be aware of when bolder pipes are joined or sorted.

Operator cost share isn’t the full story, either

When you run multiple queries, the cost of a query relative to the batch is displayed in the query execution plan header. The batch cost relative to the rest of the operators in the statement is displayed within each plan. SQL Server uses a cost-based process to decide which query plan to use.

When optimizing a query, it is usually useful to start with the costliest operators. But deciding to address only the highest-cost single operator in the execution plan might be a dead end.

Look for join operators and understand the different algorithms

As you read from right to left in a query of any complexity, you’ll likely see the paths meet at a join operator. Two tables, indexes, or the output from another operator can be joined together. There are three types of join operators to be aware of, particularly because they represent where a large percentage of the cost of an execution plan stems from: merge join, hash match, and nested loop. Any one of these can be the fastest way to join two sets of data, depending on the size of the sets, whether they are indexed, and whether they are sorted already (and if not, whether it would be too costly to sort them with a sort operator).

A merge join operator merges two large, sorted sets of data. The query processor can scan each set, in order, matching rows from each table with a single pass through the sets. This can be quite fast, but the requirement for ordered sets is where the cost comes in. If you are joining two sets that are keyed on the same column, they are then sorted, so two ordered scans can be merged. The Query Optimizer can choose to sort one or both inputs to the merge join, but this is often costly.

Hash match is the join operator used to join two large sets, generally when there is no easily usable index and sorting is too costly. As such, it has the most overhead, because it creates a temporary index based on an internal hashing algorithm to bucketize values to make it easier to match one row to another. This hash structure is in memory if possible, but might spill onto disk (using tempdb). The presence of a hash join is not necessarily a bad thing; just know that it is the least efficient algorithm to join two data set, precisely because they are not suited for the other two operators.

The most efficient join algorithm is the one that sounds the least optimized: nested loops. This join algorithm is the basic row-by-row processing that people preach about you as a programmer never doing. It takes one row in one input and searches the other input for values that meet the join criteria. When joining two sets together, one is indexed on the join key, and doesn’t need to fetch additional data using a key lookup. Nested loops are very fast. The additional operators were implemented to support larger, ideally reporting-style, workloads.

Each of the following options could reduce the cost of a join operator.

• There might be an opportunity to improve the indexing on the columns being joined, or perhaps, you have a join on a compound key that is incompletely defined. Look for common joins in queries—can an index be crafted to match?

• In the case of a merge join, you might see a preceding sort operator. This can be an opportunity for you to sort the data already sorted according to how the merge join requires the data to be sorted. If this is a composite key, perhaps change the ASC/DESC property of an index key column, or create a new nonclustered index with columns sorted differently. The result could result in a new execution plan without the cost of the sort operator.

• Filter at the lowest level possible. Perhaps a WHERE clause could exist in a subquery instead of at the top level of the query, or in the definition of a derived table or common table expression (CTE) instead of in the subsequent query.

• Hash operators are the most expensive. Reducing the row counts going into a hash match or hash join could allow the Query Optimizer to use a less memory-intensive and less costly join operator.

• Nested loops are often necessitated by key lookups and are sometimes quite costly. Address them with a new or modified nonclustered index to eliminate the nearby key lookup, or to make an accompanying index seek more capable.

Look for parallel icons

The left-pointing pair of arrows in a yellow circle shown in Figure 14-7 indicate that this operator has been run with a parallel-processing execution plan. We talk more about parallelism later in this chapter; the important thing here is to be aware that your query has gone parallel.

This doesn’t mean multiple sources or pipes are being read in parallel; rather, it means the work for individual tasks has been broken up behind the scenes. The Query Optimizer decided it was faster if your workload was split up and run into multiple parallel streams of rows. Typically, this is a good thing, but at scale, excessive parallelism can lower overall performance.

• We discuss parallelism in more detail later in this chapter in the section “Understand parallelism.”

You might also see one of the three different parallelism operators—distribute streams, gather streams, and repartition streams—each of which appear only for parallel execution plans.

Understand cardinality estimation

When a query plan is being generated, one of the most important factors you will deal with is the cardinality estimation (CE). Cardinality is defined as the number of items in a set (hence, a cardinal number is a non-negative integer). The importance of cardinality estimation cannot be overstated and is analogous to how you might do a task. If you own an e-commerce store and ship three products a week, you might be able to walk two miles with your stack of packages to the post office at the end of the week and be efficient enough. If you must ship 300,000 products a day, however, the net effect of each product being shipped needs to be the same, but the way you achieve this must be far more optimized and include more than just one person.

SQL Server makes the same choices. You join table X with table Y on column ID. If X has 1,000 rows, and Y has 10, the solution is easy. If they each have a billion rows, and you are looking for a specific value in table X—say, value = 'Test'—there are many choices to make. How may rows in X have a value of 'Test'? And once you know that value, how many values of ID in X will match ID values in Y?

This estimation is done in two ways. The first is with guesses based on histograms of the data. The table is scanned when creating statistics. Statistics are created by executing UPDATE STATISTICS or through the automatic gathering of statistics that occurs as data changes, based on the AUTO_UPDATE_STATISTICS database setting.

Take the first case, where the tables are small. The engine stores a histogram that has something like what’s shown in Table 14-5.

From this, the number of rows equal to 'Test' can be guessed to be less than 400 because Test is between Smith and Tests; less than 200, because approximately 200 rows matched Smith; and approximately 10 matches because there are approximately 20 distinct values. Exactly how this estimation is done is proprietary, but it is important to keep your statistics up to date using maintenance methods. (See Chapter 8 for more details on proper upkeep of SQL Server.)

From SQL Server 7.0 to SQL Server 2012, the same CE algorithms were used. However, since SQL Server 2014, Microsoft has been significantly tweaking the cardinality estimator with each release. And now, in SQL Server 2022, the new CE feedback feature allows SQL Server to automatically adapt query plans based on the estimate and actual number of rows processed, enhancing performance stability and automating an otherwise complex troubleshooting exercise.

• CE feedback is discussed later in this chapter. You can find out more at https://learn.microsoft.com/sql/relational-databases/performance/intelligent-query-processing-feedback#cardinality-estimation-ce-feedback.

The problem with cardinality estimation is that it is an inexact science. The guesses made are usually close enough, but sometimes can be off just enough to affect performance. Outdated statistics can lead to this, but that’s not the only explanation. Estimating filtered rowcounts in query plans is complex. That’s why it’s important that the new SQL Server 2022 “feedback” features allow SQL Server to quickly learn from mistakes.

There are a few methods to control which cardinality estimator is used. The first is to use the database’s compatibility level, like with this command to use the SQL Server 2022 cardinality estimator:

Click here to view code image

ALTER DATABASE [db_name] SET COMPATIBILITY_LEVEL = 160;

Or you can elect to go backward and use the legacy SQL Server 2012 compatibility level:

Click here to view code image

ALTER DATABASE [db_name] SET COMPATIBILITY_LEVEL = 110;

Changing the database’s compatibility level is often an unnecessary or drastic change. In your testing, consider keeping your modern compatibility level and reverting only to the legacy cardinality estimator (which pre-dated SQL Server 2014):

Click here to view code image

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

Here also, reverting to the legacy cardinality estimator can be too drastic a change for the entire database. In compatibility level 130 (SQL Server 2016) and higher, there is a query hint you can use to modify an individual query to use legacy cardinality estimation (CE version 70):

Click here to view code image

SELECT …
FROM …
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

What if you cannot modify the query to use the OPTION syntax because it resides deep within a business intelligence suite, an ETL tool, or third-party software? SQL Server 2022 has a solution for you: Identify the query in Query Store metadata and then provide a Query Store hint, shaping the query without altering any code. For example:

Click here to view code image

EXEC sys.sp_query_store_set_hints @query_id= 555,
@query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

In this example, the Query Store is used to identify a specific problematic query, which had been assigned query_id 555. This new SQL Server 2022 feature is only available when Query Store is enabled for the database, which we recommend.

• For more information about Query Store hints, see the “Query Store hints” section later in this chapter.

For the most part, we suggest using the latest cardinality estimator possible, and addressing issues in your queries, but realize this is not always a feasible solution.

• For more information, including how to tell which version of cardinality estimation was used, see https://learn.microsoft.com/sql/relational-databases/performance/cardinality-estimation-sql-server.

• For more details, see Chapter 19, “Migrate to SQL Server solutions in Azure,” where we discuss using QTA for migrations. Also check out this lab that Microsoft created: https://github.com/microsoft/tigertoolbox/blob/master/Sessions/Winter-Ready-2019/Lab-QTA.md.

Clear the procedure cache

You might find that manually clearing the procedure cache is useful when performance testing or troubleshooting. Typically, you want to reserve this activity for preproduction systems.

There are a few common reasons to clear out cached plans in SQL Server. One is to compare two versions of a query or the performance of a query with different indexes; you can clear the cached plan for the statement to allow for proper comparison.

You can manually flush the entire procedure cache, or individual plans in cache, with the following database-scoped configuration command, which affects only the current database context, as opposed to the entire instance’s procedure cache:

Click here to view code image

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

This command was introduced in SQL Server 2016 and is effectively the same as the DBCC FREEPROCCACHE command within the current database context. It works in both SQL Server and Azure SQL Database. DBCC FREEPROCCACHE is not supported in Azure SQL Database, and should be deprecated from your use going forward in favor of ALTER DATABASE.

You can also remove a single plan from cache by identifying its plan_handle and then providing it as the parameter to the ALTER DATABASE statement. Perhaps this is a plan you would like to remove for testing or troubleshooting purposes that you have identified with the script in the previous section:

Click here to view code image

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000700CA920912307B86
7DB701000001000000000000000000000000000000000000000000000000000000;

You can alternatively flush the cache by object type. This command clears cached execution plans that are the result of ad hoc statements and prepared statements (from applications, using sp_prepare, typically through an API):

Click here to view code image

DBCC FREESYSTEMCACHE ('SQL Plans');

The advantage of this statement is that it does not wipe the cached plans from “programmability” database objects such as stored procedures, multi-statement table-valued functions, scalar user-defined functions, and triggers. The following command clears the cached plans from these types of objects:

Click here to view code image

DBCC FREESYSTEMCACHE ('Object Plans');

You can also use DBCC FREESYSTEMCACHE to clear cached plans associated with a specific Resource Governor Pool, as follows:

Click here to view code image

DBCC FREESYSTEMCACHE ('SQL Plans', 'poolname');

Analyze cached execution plans

You can analyze execution plans in aggregate starting with the sys.dm_exec_cached_plans DMV, which contains a column named plan_handle. The plan_handle column contains a system-generated varbinary(64) string that can be used with a number of other DMVs. As seen in the code example that follows, you can use the plan_handle to gather information about aggregate plan usage, obtain plan statement text, and retrieve the graphical execution plan itself.

You might be used to viewing the graphical execution plan only after a statement is run in SSMS, but you can also analyze and retrieve plans for queries executed in the past by using the following query against a handful of dynamic management objects (DMOs). These DMOs return data for all databases in SQL Server instances, and for the current database in Azure SQL Database. The following query can be used to analyze different aspects of cached execution plans. Note that this query might take a considerable amount of time as written, so you might want to pare down what is being output for your normal usage.

Click here to view code image

SELECT
    p.usecounts AS UseCount,
    p.size_in_bytes / 1024 AS PlanSize_KB,
    qs.total_worker_time/1000 AS CPU_ms,
    qs.total_elapsed_time/1000 AS Duration_ms,
    p.cacheobjtype + ' (' + p.objtype + ')' as ObjectType,
    db_name(convert(int, txt.dbid )) as DatabaseName,
    txt.ObjectID,
    qs.total_physical_reads,
    qs.total_logical_writes,
    qs.total_logical_reads,
    qs.last_execution_time,
    qs.statement_start_offset as StatementStartInObject,
      SUBSTRING (txt.[text], qs.statement_start_offset/2 + 1 ,
     CASE
         WHEN qs.statement_end_offset = -1
         THEN LEN (CONVERT(nvarchar(max), txt.[text]))
         ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1 END)
     AS StatementText,
      qp.query_plan as QueryPlan,
      aqp.query_plan as ActualQueryPlan
FROM sys.dm_exec_query_stats AS qs
INNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = qs.plan_handle
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS txt

OUTER APPLY sys.dm_exec_query_plan (p.plan_handle) AS qp
OUTER APPLY sys.dm_exec_query_plan_stats (p.plan_handle) AS aqp
--tqp is used for filtering on the text version of the query plan
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, qs.statement_start_offset,
qs.statement_end_offset) AS tqp
WHERE txt.dbid = db_id()
ORDER BY qs.total_worker_time + qs.total_elapsed_time DESC;

The preceding code sorts queries by a sum of the CPU time and duration, descending, returning the longest running queries first. You can adjust the ORDER BY and WHERE clauses in this query to find, for example, the most CPU-intensive or most busy execution plans. Keep in mind that the Query Store feature, as detailed later in this chapter, will help you visualize the process of identifying the most expensive and longest running queries in cache.

As you will see after running in the previous query, you can retrieve a wealth of information from these DMOs, including statistics for a statement within an object that generated the query plan. The query plan appears as a blue hyperlink in SSMS’s Results to Grid mode, opening the plan as a new .sqlplan file. You can save and store the .sqlplan file for later analysis. Note too that this query might take quite a long time to execute as it will include a line for every statement in each query.

For more detailed queries, you can add code to search only for queries that have certain details in the plan—for example, looking for plans that have a reason for early termination value. In the execution plan XML, the reason for early termination will show in a node StatementOptmEarlyAbortReason. You can add the search conditions before the ORDER BY in the script, using the following logic:

Click here to view code image

and tqp.query_plan LIKE '%StatementOptmEarlyAbortReason%'

Included in the query is sys.dm_exec_query_plan_stats, which provides the actual plan XML for a given plan_handle.

• More details on sys.dm_exec_query_plan_stats are available at https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-stats-transact-sql.

Permissions required to access cached plan metadata

The only permission needed to run the previous query in SQL Server is the server-level VIEW SERVER STATE permission, which might be appropriate for developers to have access to in a production environment because it does not give them access to any data in user databases.

In Azure SQL Database, because of the differences between the Basic/Standard and Premium tiers, different permissions are needed. In the Basic/Standard tier, you must be the server admin or Azure Active Directory Admin to access objects that would usually require VIEW SERVER STATE. In the Premium tier, you can grant VIEW DATABASE STATE in the intended database in Azure SQL Database to a user who needs permission to view the preceding DMVs.

Force a parallel execution plan

You know how to specify MAXDOP = 1 to force a query not to use parallelism. What about forcing parallelism? You can use a query hint to force a statement to compile with a parallel execution plan. This can be valuable in troubleshooting, or to force a behavior in the Query Optimizer for experimentation, but is not usually a necessary or recommended option for live code.

Appending the following hint to a query will force a parallel execution plan, which you can see using the estimate or actual execution plan output options:

Click here to view code image

… OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

Initial configuration of Query Store

Query Store is identical between the Azure SQL Database and SQL Server in operation, but not in how you activate it. Query Store is enabled automatically on Azure SQL Database and all new databases starting with SQL Server 2022, but it is not automatically on for existing databases that you migrate to SQL Server 2022.

When should you enable Query Store? Enabling Query Store on all databases you have in your environment is a generally acceptable practice, as it will be useful in discovering performance issues in the future when they arise. You can enable Query Store via the database’s Properties dialog box, in which a Query Store page is accessible from the menu on the left, or you can turn it on via T-SQL by using the following command:

Click here to view code image

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

This will enable Query Store with the defaults; you can adjust them using the UI.

Query Store begins collecting data when you activate it. You will not have any historical data when you first enable the feature on an existing database, but you will begin to immediately see data for live database activity. You can then view plans and statistics about the plan in the Query Store reports.

In versions before SQL Server 2019, the default Query Store capture mode setting was ALL, which included all queries that were executed. In SQL Server 2019, this default has been changed to AUTO, which is recommended. The AUTO Query Store capture mode forgets queries that are insignificant in terms of execution duration or frequency. Further, the CUSTOM Query Store capture mode allows for more fine tuning that may become necessary on large and busy databases. While AUTO works fine for most servers, you can use custom capture policies to configure the tradeoff: the amount of history remembered by Query Store versus the amount of storage the Query Store consumes inside the user database.

The Query Store retains data up to two limits: a max size (1,000 MB by default), and a Stale Query Threshold time limit in days (30 by default). If Query Store reaches its max size, it cleans up the oldest data. Because Query Store data is saved in the database, its historical data is not affected by the commands we looked at earlier in this chapter to clear the procedure cache, such as DBCC FREEPROCACHE.

You should almost always keep the size-based cleanup mode set to the default, Auto. If not, when the max size is reached, Query Store will stop collecting data and enter read-only mode, which does not collect new data. If you find that the Query Store is not storing more historical days of data than your stale query threshold setting in days, increase the max size setting.

Troubleshoot with Query Store data

Query Store has several built-in dashboards, shown in Figure 14-8, to help you examine query performance and overall performance over recent history.

You can also write your own reports against the collection of system DMOs that present Query Store data to administrators and developers by using the VIEW DATABASE STATE permission.

• You can view the schema of the well-documented views and their relationships at https://learn.microsoft.com/sql/relational-databases/performance/how-query-store-collects-data.

On many of the dashboards, there is a button with a crosshairs symbol, as shown in Figure 14-9. If a query seems interesting, expensive, or is of high value to the business, you can select this button to view a new window that tracks the query when it’s running as well as various plans identified for that query.

You can review the various plans for the same statement, compare the plans, and if necessary, force a plan that you believe is better than the Query Optimizer will choose into place. Compare the execution of each plan by CPU time, duration, logical reads, logical writes, memory consumption, physical reads, and several other metrics.

Most of all, the Query Store can be valuable by informing you when a query started using a new plan. You can see when a plan was generated and the nature of the plan; however, the cause of the plan’s creation and replacement is not easily deduced, especially when you cannot correlate to a specific DDL operation or system change. Query plans can become invalidated automatically due to large changes in statistics resulting from data inserts or deletes, changes made to other statements in the stored procedure, changes to any of the indexes used by the plan, or manual recompilation due to the RECOMPILE option.

As discussed in the upcoming “Query Store hints” section, forcing a statement (see Figure 14-10) to use a specific execution plan should not be a common activity. If you have access to the source code, work on a code change, only using a forced plan temporarily. For systems where you have no code access, you can use a Query Store hint for specific performance cases, problematic queries demanding unusual plans, and so on. Note that if the forced plan is invalid, such as an index changing or being dropped, SQL Server will move on without the forced plan and without a warning or error, although Query Store will still show that the plan is being forced for that statement. Note that once a plan has been forced for this statement (using the Force Plan button), the plan is displayed with a check mark.

Use Query Store hints

Currently, like plan guides, Query Store hints are driven by T-SQL stored procedures and not accessible from within any GUI (yet). But the identification of queries can occur through the Query Store interface, through a unique key for all queries captured by the Query Store, query_id.

The following steps will detail how to back out of a Query Store hint quickly. Be prepared to observe and rollback any hints.

1. Identify a useful hint for the query, ideally by using a non-production performance testing environment with similar hardware and data scale.

2. Identify the Query Store–assigned query_id for the query. Note that the query_id will be different on different instances of SQL Server.

3. Use sys.sp_query_store_set_hints to apply a hint. Most query hints are supported. For example, to apply the MAXDOP 1 hint to query_id 1234, use the following:

   Click here to view code image

   EXEC sys.sp_query_store_set_hints @query_id= 1234,
   @query_hints = N'OPTION(MAXDOP 1)';

   • For a complete list of supported query hints, review the Microsoft Docs article for sys.sp_query_store_set_hints at https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sys-sp-query-store-set-hints-transact-sql#supported-query-hints.

   You can specify more than one query hint in a Query Store hint, just as you would in the OPTION clause of any T-SQL query. For example, to specify MAXDOP 1 and the pre-SQL Server 2014 cardinality estimator, use this:

   Click here to view code image

   EXEC sys.sp_query_store_set_hints @query_id= 1234,
   @query_hints = N'OPTION(MAXDOP 1,
   USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

   The Query Store hint takes effect immediately and adds three attributes to the execution plan XML: QueryStoreStatementHintId, QueryStoreStatementHintText, and QueryStoreStatementHintSource. If you’re curious, you can review these to see the Query Store hint in action, and prove the hint altered the query without code changes.

4. Observe and confirm that your hint is helping the query’s execution. Confirm the Query Store hint(s) currently in place for your query as follows:

   Click here to view code image

   SELECT * FROM sys.query_store_query_hints
   WHERE query_id = 1234;

5. Remove the Query Store hint when necessary with sys.sp_query_store_clear_hints. (You want to prepare for this ahead of time.)

   Click here to view code image

   EXEC sys.sp_query_store_clear_hints @query_id = 1234;

6. Set yourself a reminder to reevaluate any Query Store hints on a regular basis. Data distributions change and the winds of fate blow. Changes to underlying data and concurrent server workloads might cause Query Store hints to generate suboptimal execution plans in the future.

   If you set a Query Store hint, it will overwrite any existing hint for that query_id. Query Store hints will override other hard-coded statement level hints and plan guides, but you should avoid conflicting instructions as they could be confusing for others in your environment.

Batch mode on rowstore

One of the features added, along with columnstore indexes, in SQL Server 2012 was a type of processing known as batch mode. Columnstore indexes were built to process compressed rowgroups containing millions of rows. A new processing mode was needed when the heuristics told the query processor it would be worthwhile to work on batches of rows at a time.

• For more details on batch and row mode processing, see https://learn.microsoft.com/sql/relational-databases/query-processing-architecture-guide.

Starting with SQL Server 2019 and included in Azure SQL Database, this feature was extended to work for certain types of queries with row store tables and indexes as well as columnstore tables and indexes. A few examples:

• Queries that use large quantities of rows in a table, often in analytical queries touching hundreds of thousands of rows.

• Systems that are CPU bound in nature. (I/O bottlenecks are best handled with a columnstore index.)

The feature is enabled when the compatibility level is at least 150. Though unusual, if you find it is harming performance, you can turn it off using ALTER DATABASE without lowering the compatibility level:

Click here to view code image

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

Instead of changing this setting for the database, you could disallow this feature for a specific query, perhaps one that touches large number of rows. You can use the query hint ALLOW_BATCH_MODE. For example:

Click here to view code image

SELECT …
FROM …
OPTION(USE HINT('ALLOW_BATCH_MODE'));

Cardinality estimation (CE) feedback

Similar to other feedback features, CE feedback allows the Query Optimizer to adapt to changes in query performance based on suboptimal CE. CE estimates the total number of rows in the various stages of a query plan. (Constraints and key relationships between tables can help inform and shortcut estimation, another good reason to put them in place.)

CE feedback is a process by which the Query Optimizer learns by modeling query behavior over time. This feature requires both compatibility level 160 (for SQL Server 2022) and the Query Store to be enabled for the database.

In the past, the cardinality estimator and the database’s compatibility level were closely linked. (Changes in SQL Server 2014 improved the cardinality estimator in most but not all cases, with severe impacts when it missed.) CE feedback does not change the database compatibility level for the query, but makes incremental corrections via the Query Store hint feature.

• For more about Query Store hints, see the “Understand cardinality estimation” section earlier in this chapter.

At the time of this writing, CE feedback is not yet available in Azure SQL Database or Azure SQL Managed Instance, but should be available in the near future.

SQL Server 2022 also added a persistence feature for CE feedback. Persistence helps CE feedback avoid poor adjustment decisions by remembering query information, even if a plan is evicted from cache. Using the Query Store, estimates can be considered over multiple query executions.

Degree of parallelism (DOP) feedback

DOP feedback has perhaps the biggest impact of all the feedback features introduced to make SQL Server more adaptive to observed performance. The ability for the Query Optimizer to analyze and adapt to observed query performance and make changes to parallelism with each query execution is a powerful self-tuning feature.

DOP feedback is introduced with SQL Server 2022, and requires the Query Store feature to be enabled. Further, the DOP_FEEDBACK database scoped configuration can be enabled or disabled if necessary.

Instead of worrying about optimizing the database’s MAXDOP for the most important parts of a workload, DOP feedback automatically adjusts parallelism for repeating queries with the goal of increasing overall concurrency and reducing wait times. Smartly, DOP feedback excludes some wait types that are not relevant to this part of query performance, such as buffer latch, buffer IO, and network IO waits.

DOP feedback adjusts queries automatically by adding the MAXDOP query hint, but never at a value exceeding the database’s MAXDOP setting. You might consider exploring the performance gains with DOP feedback in a different (higher) MAXDOP setting for your server or database in SQL Server 2022.

At the time of this writing, DOP feedback is not yet available in Azure SQL Database or Azure SQL Managed Instance, but should be available in the near future.

SQL Server 2022 also added a persistence feature for DOP feedback. Persistence helps the DOP feedback feature avoid poor adjustment decisions by remembering query information, even if a plan is evicted from cache. Using the Query Store, optimal parallelism can be considered over multiple executions.

Memory grant feedback

When a query executes, it uses some amount of memory. Memory grant feedback lets future executions of the same query know if the memory granted for the execution was too much or too little, so it can adjust future executions. Memory grant feedback was introduced in SQL Server 2017 for batch mode executions, and in SQL Server 2019 for row mode executions.

If for some reason you want to disable this feature in a database, you can do so without lowering the database compatibility level using the following statements:

Click here to view code image

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
-- Azure SQL Database, SQL Server 2019 or later
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
For row mode queries, this feature is controlled using:
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

SQL Server 2022 further enhances memory grant feedback with two additional features: persistence and percentile improvement. Both are intended to help the Query Optimizer avoid expensive memory spills by accurately estimating (or at worst, overestimating) the amount of memory needed for crucial parts of an execution plan.

Memory grant feedback persistence allows SQL Server to remember query memory grant information even if a plan is evicted from cache. Using the Query Store, feedback can be considered over multiple executions. Persistence also applies to CE feedback and DOP feedback, as stated.

Memory grant feedback percentile adjustment allows the memory grant adjustment to examine the recent history of query execution, not just the most recent execution. The complex calculation now includes the 90th percentile of past memory grants over time. The percentile adjustment only applies to memory grant feedback.

When the new Query Store for secondary replicas feature is enabled, memory grant feedback is replica aware and can help primary and secondary replica workloads differently.

Parameter Sensitive Plan optimization

Parameter Sensitive Plan (PSP) optimization, new in SQL Server 2022, addresses the age-old problem of queries getting a suboptimal execution plan when the value of a filter wildly changes the result sets. PSP solves the problem in an intuitive way: by allowing more than one cached execution plan to be saved for a single query.

Imagine a search query for sales, filtered on customer, where customer_id = @customer_id. Customer 1 has been purchasing goods for decades, and has millions of invoice line items. Customer 2 is a new customer with a single invoice. In previous versions of SQL Server, should the execution plan for customer 2 become the execution plan for this search query, performance of the query on customer 1 would be poor. This is because the operators chosen for the execution plan would be unlikely to scale from one row to millions of rows in the result set.

Previous solutions required an understanding of query parameterization and the application of a variety of strategies, including cache preparation, synthetically calling queries with their largest filter parameters using the OPTIMIZE FOR query hint, or using plan guides. Between PSP optimization and Query Store hints, SQL Server 2022 provides multiple superior options to deal with this classic problem. PSP optimization uses a complex set of instructions that require a significant amount of performance data to act and cache a different plan, in an effort to minimize memory utilization associated with unnecessary plan retention.

PSP optimization is part of the database compatibility level 160 feature set, so be sure to update the compatibility level for any databases migrated up to SQL Server 2022. Query Store is not required for PSP optimization, but it is recommended.

If for some reason you need to disable PSP optimization, you can do so without lowering the database compatibility level with the PARAMETER_SENSITIVE_PLAN_OPTIMIZATION database-scoped configuration option.

• For a deep dive on the internals of PSP optimization, see https://learn.microsoft.com/sql/relational-databases/performance/parameter-sensitivity-plan-optimization.

Table variable deferred compilation

Table variable deferred compilation was introduced in SQL Server 2019 to deal with the glaring lack of statistics for a table variable at compile time. Previously, SQL Server defaulted to a guess of one (1) row for the number of rows in the table variable. This provided poorly performing plans if the programmer had stored any significant number of rows in the table variable.

Similarly, an IQP feature introduced in SQL Server 2017 called interleaved execution improved the performance of multi-statement table-valued functions. Interleaved execution let the Query Optimizer execute parts of the query during optimization to get better estimates, because if your multi-statement table-valued function is going to output 100,000 rows, the plan needs to be considerably different.

Instead of using the guess of 1 to define the query plan, table variable deferred compilation waits to complete the actual plan until the table variable has been loaded the first time, and then the rest of the plan is generated.

T-SQL scalar user-defined function (UDF) inlining

A common culprit of poor performance in custom applications is user-defined functions (UDFs). Every programmer who has taken any class in object-oriented programming (OOP) instinctively desires to modularize or de-duplicate their code. So, if you have a scenario in which you want to classify some data (say, something simple like CASE WHEN 1 THEN 'True' ELSE 'False' END), it makes sense from a programmer’s perspective to bundle this up into a coded module (code reuse). However, UDFs can become a bear trap at scale for application performance.

Introduced in SQL Server 2019, scalar UDF inlining alleviates some of the performance hit introduced by UDFs at scale; it’s an automatic, no-code-change-necessary, performance boost. This is a complex fix that substitutes scalar expressions or subqueries in the query during query optimization. Throughout the post-RTM life of SQL Server 2019, cumulative updates added complexity, issue fixes, and restrictions to UDF inlining.

• For details, see https://learn.microsoft.com/sql/relational-databases/user-defined-functions/scalar-udf-inlining.

Example of scalar UDF inlining

To demonstrate UDF inlining, we created the following overly simple UDF in the WideWorldImporters sample database:

Click here to view code image

USE WideWorldImporters;
GO
CREATE SCHEMA Tools;
GO
CREATE FUNCTION Tools.Bit_Translate
(@value bit)
RETURNS varchar(5)
AS
BEGIN
     RETURN (CASE WHEN @value = 1 THEN 'True' ELSE 'False' END);
END;

To demonstrate, execute the function in the same query twice: once in SQL Server 2017 (14.0) database compatibility level, before scalar UDF inlining was introduced, and again with SQL Server 2022 (16.0) database compatibility level behavior.

Click here to view code image

SET STATISTICS TIME ON;
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140; --SQL Server 2017
GO
SELECT Tools.Bit_Translate(IsCompressed) AS CompressedFlag,
CASE WHEN IsCompressed = 1 THEN 'True' ELSE 'False' END AS CompressedFlag_Desc
FROM  Warehouse.VehicleTemperatures;
GO
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 160; -- SQL Server 2022
GO
SELECT Tools.Bit_Translate(IsCompressed) AS CompressedFlag,
CASE WHEN IsCompressed = 1 THEN 'True' ELSE 'False' END
FROM   Warehouse.VehicleTemperatures;

On the 65,998 rows returned in each result set, you will likely not notice a difference in performance. Checking the output from SET STATISTICS TIME ON on this author’s machine, the execution in COMPATIBILITY LEVEL = 160 was only about 75 milliseconds faster on average.

Looking at the actual plan CPU used for the two executions in Figure 14-11, you can see an interesting difference.

The big thing to notice between these two executions is that the compute scalar in query 2 appears as a typical compute scalar operator, for any scalar expression not including a UDF. In query 1, it shows rows passing through, and an amount of time as it calculates the scalar for each row that passes through. Even in this extremely simple case, we saved time because we avoided running the function in a cursor-like loop for every row.

There are limitations to scalar UDF inlining, such as not working when time dependent intrinsic functions like SYSDATETIME() are present. You cannot change security context using EXECUTE AS (only EXECUTE AS CALLER, the default, is allowed). You also cannot benefit from scalar UDF inlining when referencing table variables or table-valued parameters.

• For more details and the complete list of requirements see https://learn.microsoft.com/sql/relational-databases/user-defined-functions/scalar-udf-inlining.

Scalar UDF inlining has immediate value for databases whose programmers have overused scalar UDFs. For many, scalar UDF inlining removes the problematic performance stigma associated with scalar UDFs, opening up more use cases. Formatting functions and translation functions where it might be easier than creating a table are now possible and will perform very well, as opposed to destroying your performance.