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. In Table 14-3 is the list of modes that an index may 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 doing different operations, 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. In Table 14-4, the compatibility of different modes is presented.

To read this table, pick the lock mode in one axis, then an X will be in any compatible column in the other. For example, an update lock is compatible with an intent shared and a shared lock, but not another update lock, or any of the exclusive variants.

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, which will be discussed later in this section).

How to observe locks and blocking

It’s easy to find out live 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 displayed from 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 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;

You can see in the output of this query, the type of resource that is locked, listed in table 14-3 and the modes listed in table 14-4, with a few exceptions. A lock on a schema is mixed with the mode of the lock, for example, you may have an object that is executing, like a stored procedure where the object is locked with executing, with a request mode of Sch-S, which indicates that it is a shared mode, because it is in use. This prevents the schema of the procedure from being changed, but it can be executed by more than one connection.

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

Now, let’s review some example 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 the reason why the NOLOCK query hint appears to make queries perform faster.

Using 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 will discuss later in the 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. For example, 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 READ COMMITTED 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 snap-
shot 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 may not be in READ COMMITTED by default, which is far better for performance.

Using 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 enterprise concurrency. Just as in the previous session, however, we may wish 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:

SELECT col1 FROM dbo.Table (NOLOCK);

Aside from the generally undesirable use of the 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 a use of the SERIALIZABLE isolation level.

In almost every case, table hints should be considered for temporary and/or highly situational purposes. They can make maintenance of these queries problematic in the future. For example, using the INDEX or FORCESEEK table hints 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 the SQL Server documentation at https://docs.microsoft.com/sql/t-sql/queries/hints-transact-sql-table.

Understanding concurrency: two requests updating the same rows

Two users attempting to modify the same resource is possibly the most obvious concurrency issue. As an example, one user wants to add $100 to the 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 level, 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 are not overriding the default isolation level of READ COMMITTED.

All examples will have these two rows simply so it isn’t just a single row, though we will 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, etc. do come into play. These examples illustrate simple, 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 will wait 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.

Understanding concurrency: 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 may actually touch and lock rows in the table even if they don’t make any changes (typically due to poorly written where clauses or lack of indexing causing full table scans.)

Next, 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 are not overriding the default isolation level of READ COMMITTED:

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.RC_Test_Write_V_Read (Type int);
   
   INSERT INTO Demo.RC_Test_Write_V_Read 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_Write_V_Read SET Type = 2
   
   WHERE  Type = 1;

3. Before Transaction 1 commits, Transaction 2 begins and issues a SELECT statement for rows of 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.

Transaction 2 returns 1 row where Type = 2. This is because when Transaction 2 started, it waited for committed data until after Transaction 1 committed.

Understanding concurrency: 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 modification DML), A prime example is the case where you look for the existence of a some data before allowing some other action to occur. Like 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 may be shipped.

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

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.

   --Transaction 2
   
   BEGIN TRANSACTION;
   
   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, which is different than the one row returned earlier, since no committed rows now exist where Type=1. Transaction 1 commits.

   --Transaction 1
   
   COMMIT;

The resulting table now 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 would never have placed any exclusive locks on the data. Transaction 1 suffered from a nonrepeatable read: the same SELECT statement returned different data during the same multistep transaction.

Understanding concurrency: preventing 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 by 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. 1 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.

   --Transaction 2
   
   BEGIN TRANSACTION;
   
   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;

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

Understanding concurrency: experiencing phantom rows

Phantom rows cause issues for transactions when you expect the exact same result back from a query. For example, say you were writing a value to a table that summed 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, nor 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
   
   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 the select was run in Transaction 1.

   --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.

It is important to understand that in some scenarios, a phantom row can have the same effect as a non-repeatable read. For example, consider a query summing the payment amounts for a customer’s invoice. In order to ship a product, the invoice needs to be paid in full. An initial read of the data could show the invoice to be paid. But before the order is processed, the same query could indicate the customer had not been paid because a new row arrives with a reversal activity with a negative amount.

Understanding concurrency: preventing phantom rows

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 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;

1. 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
   
   INSERT INTO Demo.PR_Test_Prevent(Type)
   
   VALUES(1);

2. 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.

3. Transaction 1 executes COMMIT TRANSACTION.

4. Transaction 2 is immediately unblocked and processes its insert. Transaction 2 commits. Query the table again and there are now 2 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 for Type = 1, but they were not inserted until after Transaction 1 had committed.

The case against READ UNCOMMITTED isolation level

This also pertains to using the NOLOCK hint on your queries.

If locks take time, ignoring those locks will make things go faster. This is true, but this logic is like saying: if the weight of airbags, seat belts, bumpers, and collapsible steering wheels brings down gas mileage, remove them…what could happen? Mix a little lie with the truth, it is easier to take. It is true that these things have weight, but they may save your life one day.

Locks are similar in nature. 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 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 held only long enough to perform reads on a row, and then released after resources are read. The basic process:

• Grab a lock on a resource

• Read that resource

• Release the lock on the resource

• Repeat until you are out of resources to read

No one can dirty (modify) the row we are reading because of the lock, but when we are done, we move on and release the lock. 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 table hint NOLOCK or the READ UNCOMMITTED isolation level is that no locks are taken inside the database for reads, save for schema stability locks. (A query using NOLOCK could still be blocked by Data Definition Language [DDL] commands, such as an offline indexing operation.) The basic thinking is that: you turn on READ UNCOMMITTED isolation level for your connection and things will go faster.

This is strategy that many DBA programmers have all 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 may be reading a set of data and see data that doesn’t even meet the constraints of the system. So, a transaction for $1,000,000 may be seen in a query, and then when the transaction is rolled back later in the transaction (perhaps because the payment details for the transaction don’t meet a constraint or trigger requirement), who knows what celebratory alarms may have gone off thinking we have a million dollars in sales today.

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

A query in 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

• Or the query could fail altogether with error “Could not continue scan with NOLOCK due to data movement.” This is a scenario, where since you ignored locks, the structure that was to be scanned is no longer there when you reach it, like in a page split that occurs as you read the page. 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.

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

Click here to view code image

INSERT INTO testnolock1

SELECT * FROM testnolock2 WITH (NOLOCK);

The bottom line is to not use READ COMMITTED isolation level 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 SELECT COUNT(*) FROM dbo.TableName WITH (NOLOCK); can allow you to see the count of rows being inserted into dbo.TableName.) Using it for performance gains is rather short-sighted. However, continue reading for the real way to increase performance without the chance of seeing dirty data as we introduce version-based concurrency techniques in the next section.

Understanding concurrency: accessing data in SNAPSHOT isolation level

The beauty of the SNAPSHOT isolation level is the effect on readers of the data. If you want to go in and query the database, you generally want to see it in a consistent state. And you don’t want to block others. A typical problem comes in when you are writing an operational report, and 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). When using SNAPSHOT isolation for read write processes, it is important to consider the techniques mentioned for optimistic locking in the Inside OUT sidebar in the “Understanding concurrency: two requests updating the same rows” section above.

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 and 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.

   --Transaction 1
   
   BEGIN TRANSACTION;
   
   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 of its data deleted, the results will always be the same for the same query while in the SNAPSHOT level transaction. Once Transaction 2 is committed or rolled back, then 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.

Implementing row-versioned concurrency

You can implement row versioned isolation levels in a database in two different ways. Turning on SNAPSHOT isolation simply allows for the use of SNAPSHOT isolation and begins the process of row versioning. Alternatively, turning on RCSI changes the default isolation level to READ COMMITTED SNAPSHOT. You can implement both or either. 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 turned on, all queries will use RCSI unless overridden.

• 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. The concept of an update conflict will be covered in the next section.

The statements to implement SNAPSHOT isolation in the database are simple enough but is 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.

Here’s how to allow transactions in a database to start transactions in the SNAPSHOT isolation level:

Click here to view code image

ALTER DATABASE databasename SET ALLOW_SNAPSHOT_ISOLATION ON;

After executing only the above statement, all transactions will continue to use the default READ COMMITTED isolation level, but you now can specify the use of 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 cover in the next session, it changes some very important ways we handle executing queries, since 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.

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 turn on RCSI:

Click here to view code image

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

• We discussed memory-optimized tables in greater detail in Chapter 7, “Understanding table features.”

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 utilization in the TempDB, both in activity and space requirements. To avoid autogrowth events, consider increasing 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.

Should the TempDB exhaust all available space on its volume, SQL will be unable to row-version rows for transactions and will terminate them with SQL Server error 3958. SQL Server will also issue errors 3967 and 3966 as the oldest row versions are removed from the TempDB to make room for new row versions needed by newer transactions.

Understanding update operations in SNAPSHOT isolation level

Transactions that read data in SNAPSHOT isolation or RCSI will 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 it before deciding to implement your code in SNAPSHOT isolation level.

One of the keys to understanding how modifying data in the SNAPSHOT isolation level behaves is to understand that 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 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 on 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. The update succeeds.

5. Transaction 2 then immediately fails with SQL 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, and how to prevent it.

In SQL Server, SNAPSHOT isolation uses locks to create blocking but doesn’t block 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 the Msg 3960 that 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.

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

Even though optimistic concurrency of snapshot isolation level increases the potential for update conflicts, you can mitigate these by doing the following if you need 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, it does increase the likelihood of modification conflicts.

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

• Specifying the UPDLOCK table hint can have utility at preventing update conflict errors for long-running SELECT statements. The UPDLOCK table hints 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, and it could in fact create them. Frequent SELECT statements with UPDLOCK could increase the number of update conflicts with updates. Regardless, your application should handle errors and initiate retries when appropriate.

  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.

Understanding reading memory optimized data in other than SNAPSHOT isolation level

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

However, what makes your work more interesting is that when in REPEATABLE READ or SERIALIZABLE isolation levels, 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 in 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 update on 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 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.

Generally speaking, most use of the 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://docs.microsoft.com/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables#conflict-detection-and-retry-logic

Specifying isolation level for memory-optimized tables in queries

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, you can simply query the table as you always have, and it will be accessed in SNAPSHOT isolation level.

If you are in the context of a transaction, it will not automatically default to 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 turning on the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT database option. This promotes access to all memory-optimized tables in the database up to SNAPHOT isolation level if the current isolation level is not REPEATABLE READ or SERIALIZABLE. It will promote the isolation level to SNAPSHOT from isolation levels such as READ UNCOMMITTED and READ COMMITTED. This option is off by default, but you should consider it because you otherwise 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 will not meet the criteria for automatically choosing SNAPSHOT isolation level, you can specify the isolation level using table concurrency hints. (See the section “Using table hints to change isolation” earlier in this chapter). Note that only memory-optimized tables can use this SNAPSHOT table hint, not disk-based tables.

Finally, note that you cannot mix disk-based SNAPSHOT isolation level with 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.

Displaying the estimated execution plan

You can generate the estimated execution plan quickly and view it graphically from within SQL Server Management 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, but SSMS knows that this is a plan, so click the link to open the plan graphically in SSMS. You can save the execution plan as a .sqlplan file by right-clicking in the neutral space of the plan window.

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 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. You can also click an object, and then open the Properties window by pressing F4 or, in the View menu, by clicking Properties Window. 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, Actual Number Of Batches, and Number of Executions.

As an example, consider the following query in the WideWorldImporters database:

Click here to view code image

SELECT *

FROM Sales.Invoices

              JOIN Sales.Customers

                      on Customers.CustomerId = Invoices.CustomerId

WHERE Invoices.InvoiceID like '1%';

In Figure 14-1, we have part of the plan for this query, which looks pretty simple, but the full query plan takes up a good bit more space, due to a security policy (Application.FilterCustomersBySalesTerritoryRole) that implements row-level security that you wouldn’t even notice without looking at the Query Plan.

Displaying 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 turn on the setting. After turning on this setting, when you run a statement, you will see an additional tab appear along with the execution results after the results of 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 can be viewed graphically by clicking on the link. Remember to turn off 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.

You might see that the total rows to be processed does not match total estimated number of rows for that step; rather, the multiple of that step’s estimated number of rows and a preceding step’s estimated number of rows. Back 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.

SELECT Invoices.InvoiceID

FROM Sales.Invoices

WHERE Invoices.InvoiceID like '1%';

In the XML, in the node for the Index Scan, you see:

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="11111" ActualRowsRead="70510"

...

Displaying live query statistics

Live query statistics are an excellent feature that was introduced in SQL Server 2016 Management Studio. You can generate and display a “live” version of the execution plan by using SQL Server Management Studio. You can access live statistics on versions of SQL Server starting with SQL Server 2014.

You turn on the Live Execution Statistics option for a connection via the Query menu of SQL Server Management Studio, as demonstrated in Figure 14-2.

Executing the query we used back in the previous section, you will see something like the following in Figure 14-3.

In Figure 14-3, you can see that 10231 of the estimated 6126 rows have been processed by the Merge Join Operator, and 574 rows had 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 contains more information than the Estimated query plan, such as Actual Number Of Rows and Number Of Executions, but less than the Actual query plan. The Live Query Statistics does not display some data from the Actual Execution Plan, Actual Execution Mode, Number Of Rows Read, and Actual Rebinds.

Returning a live execution plan will noticeably slow down the 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 Activity Monitor, which is accessed by right-clicking on a server in SSMS. Then you can access the live execution plan by right-clicking any query in the Processes or Active Expensive Queries panes.

To understand the details of setting up your server’s Query Profiling Infrastructure, go to https://docs.microsoft.com/sql/relational-databases/performance/query-profiling-infrastructure. Just note that it is not free to add the ability to capture live query execution statistics, so determine if it is worth it first.

Permissions necessary to view execution plans

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

If you wish 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 may not be appropriate in your regularly environment. If that is the case, there are alternatives to providing valuable execution plan data to developers without production access:

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

• Consider also configuring the dynamic data masking feature, which may 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; assign that only to application users.

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

• For more information on dynamic data masking, see Chapter 7.

Providing the ability to see already generated and used execution plans can be done using several tools. SQL Server Extended Events and Profiler have ways to capture execution plans. Activity Monitor, in query reports such as Recent Expensive Queries, will allow you to right click and see the plan if it is still available in cache. Finally, there the 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 use to pass to the sys.dm_exec_query_plan DMV to retrieve the plan. In order to access plans in this manner, the server principal will need the VIEW SERVER STATE permission or be a member of sysadmin.

Finally, you can see execution plans in Query Store, which is covered later in the chapter, and for it you need VIEW DATABASE STATE, or you must be a member of the db_owner fixed role.

Interpreting graphical execution plans

In the next list, we review some of the most common things to look for as you review execution plans in SSMS.

First, start an execution plan. For our purposes, 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%';

Click Ctrl+L, and you will be presented with the following estimated query plan shown in Figure 14-4.

To display details for individual steps, position your pointer over a step in the execution plan. A detailed tooltip style window will appear, much like you can see in Figure 14-5. 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. This is because the index is set up for not only searching for data, but if an index has all the data needed to execute the query, the index “covers” the needs of the query and the table’s data structures are not touched.

You can also click an object, and then open the Properties window by pressing F4 or, in the View menu, clicking Properties Window. You will see the same estimated details in the properties pane. Now, let’s get the Actual Execution Plan, by using Ctrl-M, or one of the other methods discussed earlier. Execute the query, and you will see the actual plan, as seen in Figure 14-6.

The first thing you should notice is that we have a few more details on our query plan. In particular, that 11111 rows were processed, of 6346. You can see that the 6346 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, and when you are tuning larger queries, very large disparities of such guesses need to be investigated.

Open the properties pane and you’ll notice the returned estimate and actual values for some metrics, including Number of Rows, Executions, IO Statistics, etc. Look for differences in estimates that were made, 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 stale, inaccurate index statistics. (We discuss parameter sniffing later in this chapter and discuss index statistics in Chapter 15)

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, they are generated the same way for both 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.

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, drawing 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, as you can see in Figure 14-7.

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 thick pipes are joined or sorted.

Operator cost share isn’t the full story either

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

When optimizing a query, it is usually going to be 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, as there are definitely examples where this will not be the case.

In Figure 14-8, we can see an example of when operator cost might not align with the amount of data. You should investigate performance tuning this execution plan using all the information provided.

Look for Parallel icons

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

This doesn’t mean that multiple sources or pipes are being read in parallel; rather, 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.

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

Cardinality estimation

When a query plan is being generated, one of the most important factors you will deal with is the cardinality estimation. 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 run a store and ship 3 products a week, you may just have a stack of products, and walk 2 miles to the post office and be efficient enough. If you have to ship 300,000 products a day, the net effect of each product being shipped needs to be the same, but the way you will 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 1000 rows, and Y 10, the solution is easy. If they each have a billion, 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'? Then, 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. First: with guesses based on histograms of the data. The table is scanned when creating statistics (for example, by executing UPDATE STATISTICS, or by automatic statistics gathering that happens as data changes, based on the database setting AUTO_UPDATE_STATISTICS). Take the first case where the tables are small. The engine stores a histogram that has something like the following:

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

For many versions of SQL Server, the same cardinality estimation algorithms were pretty much used. However, starting in SQL Server 2014 and continuing in SQL Server 2019, Microsoft has been tweaking the cardinality estimation significantly with pretty much every release. Example changes for recent versions of SQL Server are that the cardinality estimator understands the number of rows may have increased since the samples were taken. Or that certain column values may be related (searching for names of automobiles, like auto maker and model, go together.) While SQL Server 2014 was now 3 releases and 5 years ago, there are still very large amounts of the user base that are on earlier versions (at this year’s PASS Summit, a big focus from Microsoft was to get people to upgrade from SQL Server 2008 and 2008 R2 which are no longer supported.)

The problem with cardinality estimation is that it is a very inexact science. The guesses made are frequently imperfect enough to be bad for performance, sometimes due to the changes made version to version, particularly with very large, very complex queries. In order to control which cardinality estimator is used, there are a few methods. The first is to use the database compatibility level, like this to use the SQL Server 2016 cardinality estimator (and everything else that this limits to 2016 rules too):

Click here to view code image

ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = 130;

To use the SQL Server 7.0 legacy cardinality estimator (with a database set in 120 compatibility level, which was SQL Server 2014), you can use the following database configuration value:

Click here to view code image

ALTER DATABASE SCOPED CONFIGURATION 
    SET LEGACY_CARDINALITY_ESTIMATION = ON;

In compatibility level 130 and higher (SQL Server 2016 SP1), there is a query hint you can use:

Click here to view code image

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

Alternatively, you can use the compatibility level to use the cardinality estimator of that previous version in all your queries. For the most part, we suggest using the latest cardinality estimator possible, and addressing issues in your queries if possible, but realize this is not always a feasible solution.

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

Clearing 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. For example, one common reason 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 command DBCC FREEPROCCACHE 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):

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 those type of objects:

DBCC FREESYSTEMCACHE ('Object Plans');

Note that DBCC FREESYSTEMCACHE is not supported in Azure SQL Database.

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

Click here to view code image

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

Analyzing cached execution plans

You can analyze execution plans in aggregate starting with the dynamic management view sys.dm_exec_cached_plans, 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 dynamic management views. As seen in the code example that follows, you can use the plan_handle to gather information about aggregate plan usage, plan statement text, and to 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 queries can be used to analyze different aspects of cached execution plans. Note that this query may take considerable amount of time as written, so you may wish 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
           CRLF 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;

Note that the preceding query orders 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 hunt, for example, for 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 blue hyperlink in SQL Server Management Studio’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 may 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 some 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. In the execution plan XML, the Reason For Early Termination will show in a node StatementOptmEarlyAbortReason. Simply 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 is new in SQL Server 2019 and will return the actual plan, with the details that are added to the plan after an execution.

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

Forcing a parallel execution plan

Released with SQL Server 2017 (and also implemented in SQL Server 2016 CU2 and later) is a query hint that can 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, 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'));

Using Plan Guides

Plan Guides are often maligned because they are complex to work with, and for good reason, they can be complex to use. There was not a UI for them initially, and the UI that is in SSMS does not really guide you to how they are used. However, once you get the basics down, they help to allow you to optimize queries when you cannot change the text of a query or coded object.

What makes them powerful is that while you can change the entire plan of a query, you can also just influence the plan with a query hint, either in a coded object like a stored procedure, or a textual query that is executes outside of an object. You can also affect how an ad-hoc query parameterizes.

There are three types of Plan Guides we will cover in the following sections: Object Plan Guides, SQL Plan Guides, and Template Plan Guides.

You can create a Plan Guide using the UI in the Programmability section of the database, or with the following stored procedures, which will be demonstrated.

• sp_create_plan_guide – allows you to create the plan guide from the text of the query.

• sp_create_plan_guide_from_handle – allows you to create the plan guide from the plan handle you have retrieved from another DMV or system object.

• sp_control_plan_guide – used to drop, disable, and enable one or all plan guides in a database.

In the following examples, we tweak the plans of the queries with hints, but if you have a complete plan you can use the @hints parameter to include the entire query plan to override. However, we suggest doing this with Query Store generally, unless you are doing very advanced tuning and are taking an existing plan and tweaking it. Doing this is beyond the scope of this book.

CREATE OR ALTER PROCEDURE Purchasing.PurchaseOrders_BySupplierId

@SupplierId int

AS

SELECT PurchaseOrders.PurchaseOrderID,

       PurchaseOrders.SupplierID,

       PurchaseOrders.OrderDate

 FROM   Purchasing.PurchaseOrders

WHERE  PurchaseOrders.SupplierID = @SupplierId;

EXECUTE sp_create_plan_guide

@name = N' SP-Purchasing.PurchaseOrders_BySupplierId_AddOption',

@stmt = N'SELECT PurchaseOrders.PurchaseOrderID,

       PurchaseOrders.SupplierID,

       PurchaseOrders.OrderDate

 FROM   Purchasing.PurchaseOrders

WHERE  PurchaseOrders.SupplierID = @SupplierId',

@type = N'OBJECT',

@module_or_batch = N'Purchasing.PurchaseOrders$BySupplierId',

@params = NULL,  --null for object since it is declare in the parameters

@hints = N'OPTION (OPTIMIZE FOR (@SupplierId = 13))';

EXEC sp_control_plan_guide 'DROP', N' SP-Purchasing.
PurchaseOrders_BySupplierId_AddOption';

EXECUTE sp_executesql @stmt = N'SELECT PurchaseOrders.PurchaseOrderID,

                 PurchaseOrders.SupplierID,

                 PurchaseOrders.OrderDate

FROM   Purchasing.PurchaseOrders

WHERE PurchaseOrders.SupplierID = @SupplierId',

@params = N'@SupplierId int',

@SupplierId = 2;

EXECUTE sp_create_plan_guide

      @name = N'SQL-Purchasing_PurchaseOrders$BySupplierId_NoParallelism',

      @stmt =

'SELECT PurchaseOrders.PurchaseOrderID,

                      PurchaseOrders.SupplierID,

                      PurchaseOrders.OrderDate

FROM   Purchasing.PurchaseOrders

WHERE PurchaseOrders.SupplierID = @SupplierId',

      @type = N'SQL',

      @module_or_batch = NULL,

      @params = '@SupplierId int',

      @hints = N'OPTION (OPTIMIZE FOR (@SupplierId = 2))';

Use WideWorldImporters;

SELECT * FROM Sales.Orders AS o

INNER JOIN Sales.OrderLines AS ol

    ON ol.OrderID = o.OrderID

WHERE o.OrderId = 679;

DECLARE @stmt nvarchar(max);

DECLARE @params nvarchar(max);

EXEC sp_get_query_template

    N'SELECT * FROM Sales.Orders AS o

      INNER JOIN Sales.OrderLines AS ol

           ON ol.OrderID = o.OrderID

      WHERE o.OrderId = 679;  ',

    @stmt OUTPUT,

    @params OUTPUT

EXEC sp_create_plan_guide N'TemplateGuide1',

    @stmt,

    N'TEMPLATE',

    NULL,

    @params,

    N’OPTION(PARAMETERIZATION FORCED)’;

Using the Query Store feature

The Query Store provides a practical history of execution plan performance for a single database, which persists even when the server has been restarted (unlike the plan cache itself, which is cleared, eliminating all of the interesting data that one needs for tuning queries over time.) It can be invaluable for the purposes of investigating and troubleshooting sudden negative changes in performance, by allowing the administrator or developer to identify both high-cost queries and the quality of their execution plans, and especially where the same query has multiple plans, where one performs poorly, and the other well.

The Query Store is most useful for looking back in time toward the history of statement execution. It can also assist in identifying and overriding execution plans by using a feature similar to, but different from the plan guides feature. As discussed in the previous section, Plan Guides are used to add guidance to a query’s optimization, parameterization, or by even replacing the entire plan. The Query Store allows you find plans that are not working well, but only gives you the ability to force an entire plan that worked better from the history it has stored. The Query Store has a major benefit over Plan Guides, in that there is a user interface in SSMS to access it, see the benefits, and find places where you may need to apply a new plan.

You see live Query Store data as it happens from a combination of both memory-optimized and on-disk sources. Query Store minimizes overhead and performance impact by capturing cached plan information to in-memory data structure. The data is “flushed” to disk at an interval defined by Query Store, by a default of 15 minutes. The Disk Flush Interval setting defines how much Query Store data can be lost in the event of an unexpected system shutdown.

USE WideWorldImporters;

GO

SELECT *

FROM

AdventureWorks.[Purchasing].[PurchaseOrders];

The Query Store is a feature that Microsoft delivered to the Azure SQL Database platform first, and then to the SQL Server product. In fact, Query Store is at the heart of the Azure SQL Database Advisor feature that provides automatic query tuning. The Query Store feature’s overhead is quite manageable, tuned to avoid performance hits, and is already in place on millions of databases in Azure SQL Database.

The VIEW DATABASE STATE permission is all that is needed to view the Query Store data.

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

Using query store data in your troubleshooting

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

You can also write your own reports against the collection of system DMO 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://docs.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 depicted in Figure 14-11. If a query seems interesting, expensive, or is of high value to the business, you can click this button to view a new screen 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 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 answered, 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 due to 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 Plan Guides section, forcing a statement (see Figure 14-11) to use a specific execution plan is not a recommended common activity. If you have access to the source code, work on a code change quickly, only using a forced plan temporarily. For systems where you have no code access, you can use a plan guide for specific performance cases, problematic queries demanding unusual plans, etc. 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 one plan has been Forced (using the Force Plan button) for this statement and is displayed with a check mark.

Inside OUT

How should you force a statement to use a certain execution plan?

Your options for forcing a statement to follow a certain execution plan are either plan guides stored procedures (which also allow you to make slight changes to the plan by adding a hint to the plan, rather than replacing the entire plan) or the Query Store interface (and its underlying stored procedures) to force an execution plan.

Both options are advanced options for limited and/or diagnostic use only (and if at all possible, temporary use.) Overriding the query optimizer’s execution plan choice is an advanced performance tuning technique. It is most often necessitated by query parameter sniffing.

It is possible to create competing plan guides or Query Store forced plans. This is certainly not recommended because it could be extremely confusing for the user. If you create competing plan guides or Query Store forced plans, you’ll generally see the Query Store forced plan “win.” (But this is not guaranteed or documented as far as we can determine.)

In case you are troubleshooting competing plan guides and Query Store forced plans, you can view any existing plan guides and forced query plans with the following queries of the system catalog:

Click here to view code image

SELECT * FROM sys.plan_guides;

SELECT *

FROM sys.query_store_query AS qsq

JOIN sys.query_store_plan AS qsp

     ON qsp.query_id = qsq.query_id

WHERE qsp.is_forced_plan = 1;

Finally, without using Query Store or Plan Guides, you can use the USE PLAN query hint to provide the entire XML query plan for any statement execution. This obviously is the least convenient option, and like other approaches that override the query analyzer should be considered an advanced and temporary performance tuning technique.

Adaptive Query Processing

Adaptive Query Processing is a set of features that will allow SQL Server to adapt a given query to new conditions from when the query was originally compiled or last executed. The type of adaptations that can be made currently include:

• Batch Mode Adaptive Joins – In a query that is executing in batch mode (that is, dealing with more than 1 row at a time), the query can use an adaptive join operator that lets the choice of hash or nested loops join be made after one of the inputs has been scanned. So, if it is a small, selective set, the nested loops join may work better, for example. Or if the two input sets are huge, hash match may be the better choice.

• Memory Grant Feedback – When a query executes, it uses some amount of memory to execute. The Memory Grant Feedback feature lets future executions of the same query know if the memory granted for the execution was too much, or too little and let it adjust future executions. This was available on SQL Server 2017 for batch mode executions, and in 2019 it is available for row mode executions.

  This feature can be turned on and off for batch mode queries without breaking compatibility 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
  
  ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

  For row mode queries, this feature is controlled using:

  Click here to view code image

  ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

• Interleaved Execution – Introduced in SQL Server 2017, this feature currently helps with plans that make use of Multi-Statement Table-Valued functions (MSTVF). When a plan is generated that uses as MSTVF in the query, prior to SQL Server 2014, the number or rows returned was estimated as the literal value of 1. Subsequent versions use 100 as the estimate, but neither estimate is very good unless you are actually returning very few rows. Interleaved execution lets the optimizer execute parts of the query during optimization to get better estimates, because if your MSTVF actually is going to output 100000 rows, the plan needs to be considerably different than the 100 row estimate would have provided.

  Interleaved execution can be controlled using the following statements:

  Click here to view code image

  -- SQL Server 2017
  
  ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON; --or OFF
  
  -- Azure SQL Database and SQL Server 2019
  
  ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = OFF; --or ON

Table Variable Deferred Compilation

Much like Interleaved Execution of MSTVFs, Table Variable Deferred Compilation seeks to deal with the lack of statistics for a table variable at compile time, the standard guess was 1 row would the number of rows contained in the table variable. This provided poorly performing plans if the programmer had stored thousands (or more) rows in the table variable.

Table variable deferred compilation, instead of using the guess of 1 to define the query plan, 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. This feature may be one of the best for many DBAs who have had performance problems with very large table variables, despite the fact that they sound like they should be lightweight, highly performant tools for programmers to use.

Batch Mode on Rowstore

One of the features that was added to SQL Server along with columnstore indexes was a type of processing known as Batch Mode. Typical processing in SQL Server had always used a method that sounds terrible. Row by row, now referred to as row mode processing. But columnstore indexes were built to process large amounts of rows so it was updated to, when the heuristics told the query processor it would be worth it, work on batches of rows at a time.

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

In SQL Server 2019, this feature is 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).

• For more details, see the following on Microsoft Docs: https://docs.microsoft.com/sql/relational-databases/performance/intelligent-query-processing/#batch-mode-on-rowstore.

The feature is enabled in SQL Server and Azure SQL DB when the compatibility level is 150, but if you find it is harming performance, you can turn it off using ALTER DATABASE.

Click here to view code image

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

If you want to only then allow this feature to apply to a specific query you may need to execute that touches large number of rows, you can use the query hint ALLOW_BATCH_MODE.

Click here to view code image

SELECT …

FROM …

OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

T-SQL scalar User Defined Functions (UDF) inlining

Of all the features to make a negative impact to performance, T-SQL UDFs have generally been the worst. Every programmer who has taken any class in programming instinctively desires to modularize their code. So if you have a scenario where you want to classify some data (say something simple like CASE WHEN 1 THEN 'True' ELSE 'False' END, it makes sense to bundle this up into a coded module). So, when user defined functions were created, there were shouts of joy. Until performance was taken into consideration.

In WideWorldImporters, we create the following, extremely simply UDF to do exactly what was described.

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;

Execute this function in the following query, once in 150 (SQL Server 2019) compatibility level, and again in 140.

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 = 150; -- SQL Server 2019

 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 that are returned, you will likely not notice a difference in performance. Checking the output from SET STATISTICS TIME ON, on my test computer it was around 450 ms for the SQL Server 2017 compatibility level version, and 500 ms for the SQL Server 2019 compatibility level version.

Looking at the actual plan CPU used for the two executions in Figure 14-13, 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 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 .245 seconds because instead of running the function for every execution as a separate call, it behaved as if you had written the query using the scalar expression alone.

While the function was extremely simple, without accessing tables, this is not limited to simple functions, nor does the function need to avoid accessing data in a table. There are limitations, such as not using time dependent intrinsic functions like SYSDATETIME(), not changing security context using EXECUTE AS (only EXECUTE AS CALLER, the default, is allowed), and not referencing table variables or table-valued parameters.

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

This feature will have immediate value for programmers who completely ignored the common advice to not use scalar UDFs, but for most, it will be more of a change where something you have long wanted to do is now perfectly acceptable. Formatting functions, 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.

Approximate Query Processing

Approximate Query Processing is the primary feature of the Intelligent Query Processing family of changes that does require code changes to work. A very costly query aggregate operation is doing something like counting distinct values. Say you execute a query such as:

SELECT COUNT(DISTINCT(CustomerID))

FROM   Sales.Invoices;

This will give you an exact number of customers that have been invoiced. If this is a very large set (which it is not in this case), then it may be a very costly query to execute, using a lot of memory. To combat this, they have created a new aggregate: APPROX_COUNT_DISTINCT, which cuts out one operation from the plan, and instead of giving you the exact answer, it provides an answer that is close enough for many operations that need to be done for analysis. So the query can be rewritten as:

Click here to view code image

SELECT APPROX_COUNT_DISTINCT(CustomerID)

FROM Sales.Invoices;

If you execute these two queries in the WideWorldImporters database, the plans to do the APPROX_COUNT_DISTINCT will actually show to be a bit more costly at 51 to 49% in the plan, as shown in Figure 14-14.

The first query will return the exact answer 663, and the second a close value (in this case 666 was returned). Execute the queries with SET STATISTICS TIME turned on and you will see an interesting cost savings, even on this smaller set. On the test machine, over several test runs,

• COUNT(DISTINCT())): CPU time = 15 ms, elapsed time = 38 ms

• APPROX_COUNT_DISTINCT: CPU time = 16 ms, elapsed time = 18 ms.

Just as in previous examples, a 20 ms difference seems inconsequential, and it generally is. But when we are working with millions or billions of rows, the amount of memory used by the second query will be far less, and the time savings not inconsequential at all.