© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
G. FritcheySQL Server 2022 Query Performance Tuninghttps://doi.org/10.1007/978-1-4842-8891-7_15

15. Reduce Query Resource Use

Grant Fritchey1  
(1)
Grafton, MA, USA
 
The previous chapter was focused on ensuring that your queries were able to take advantage of the indexes in your database. In this chapter, we’ll be focusing on ensuring that we reduce the resource use of our queries. There are mechanisms with T-SQL that are more likely to use up memory, CPU, and I/O. Focusing on writing our queries in such a way as to reduce resource use not only helps individual queries but reduces the overall load on the system. We’ll examine a number of different techniques focused around the following topics:

  • Query designs that reduce resource use

  • Mechanisms to enhance the use of the plan cache

  • Reducing network overhead where possible

  • Techniques to reduce the transaction cost of a query

Avoiding Resource-Intensive Queries

There are multiple ways to write almost any query. Many of those approaches will function just fine in terms of returning the appropriate data. However, some of those approaches are more likely to impact your system resources than others. Here are a number of mechanisms that will help reduce the resource use of your queries:

  • Use the appropriate data types.

  • Test EXISTS over COUNT(*) to verify data existence.

  • Favor UNION ALL over UNION.

  • Ensure indexes are used in aggregation and sort operations.

  • Be cautious with local variables in batch queries.

  • Stored procedure names actually matter.

Use Appropriate Data Types

SQL Server supports a very large number of different data types. Also, SQL Server will automatically convert from one data type to another, depending of course on the data type. When this happens, it’s called an implicit conversion. This is a situation where SQL Server helps and makes your life easier, but at the cost of performance issues. To maintain your performance, use parameters, variables, and constants that are the same data type as the column you’re comparing.

To see the negative effects of an implicit conversion in action, let’s take a look at Listing 15-1.
DROP TABLE IF EXISTS dbo.Test1;
CREATE TABLE dbo.Test1
(
    Id INT IDENTITY(1, 1),
    MyKey VARCHAR(50),
    MyValue VARCHAR(50)
);
CREATE UNIQUE CLUSTERED INDEX Test1PrimaryKey ON dbo.Test1 (ID ASC);
CREATE UNIQUE NONCLUSTERED INDEX TestIndex ON dbo.Test1 (MyKey);
WITH Tally
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
    FROM MASTER.dbo.syscolumns AS A
        CROSS JOIN MASTER.dbo.syscolumns AS B)
INSERT INTO dbo.Test1
(
    MyKey,
    MyValue
)
SELECT TOP 10000
       'UniqueKey' + CAST(Tally.num AS VARCHAR),
       'Description'
FROM Tally;
SELECT t.MyValue
FROM dbo.Test1 AS t
WHERE t.MyKey = 'UniqueKey333';
SELECT t.MyValue
FROM dbo.Test1 AS t
WHERE t.MyKey = N'UniqueKey333';
Listing 15-1

Creating a test table with data and run queries against it

Listing 15-1 creates a table, two indexes, and loads it all with data. Then, two queries are run, one of which will cause an implicit conversion. This is a fairly benign-looking conversion too. I’m simply taking a string from an NVARCHAR to a VARCHAR. You would think that wouldn’t affect much. However, let’s see the execution plans in Figure 15-1.

A diagram for query 1 has a query cost of 12% and begins from a non-clustered index seek at 50% cost and clustered key lookup at 50%, to nested loop, inner join at 0%, and select at 0%. A diagram for query 2 has a query cost of 88% and begins from a non clustered index scan at 81%.

Figure 15-1

Two execution plans showing implicit conversion at work

While the plan shapes are somewhat similar, two things should stand out. First, in the second plan, we can see a warning indicator on the first operator of the plan, the SELECT on the left. Second, again, in the second plan, we see that an Index Scan of the nonclustered index was done instead of the seek in the first plan. All of this was brought about by the need to convert the data type of the query from the NVARCHAR to VARCHAR because I set the hard-coded value through the use of “N” in front of the string definition at the end of Listing 15-1.

The warning indicator is telling that there is something you should examine inside the execution plan. It may be nothing you need to worry about, or it could be vital to help improve performance. Just looking at the differences in estimates between these two plans, you can tell that this is likely a piece of vital information. Looking at the properties of the SELECT operator, we can see the warning itself:
Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[t].[MyKey],0)=[@1]) may affect "SeekPlan" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[t].[MyKey],0)=N'UniqueKey333') may affect "SeekPlan" in query plan choice

Because of the conversion, even though the result sets are identical, performance went from 137mcs on average to 5,927mcs. The reads went from 4 to 38.

Conversions are always done to the comparison value, whether it’s a variable, parameter, or hard-coded, not to the column. The comparison process has to use the values stored with the table or index, so the conversion is done to the other value.

As you can, while the implicit conversion process occurs simply, and in the background, it can be extremely problematic for performance. The best solution is to always match your hard-coded values, variables, and parameters to the column data type.

Test EXISTS over COUNT(*) to Verify Data Existence

A very common approach to validate that data exists in the table is to run a query similar to that in Listing 15-2.
DECLARE @n INT;
SELECT @n = COUNT(*)
FROM Sales.SalesOrderDetail AS sod
WHERE sod.OrderQty = 1;
IF @n > 0
    PRINT 'Record Exists';
Listing 15-2

Validating the existence of data through COUNT(*)

To satisfy the WHERE clause and the COUNT operation, a complete scan for all values matching the WHERE clause must be performed. However, in most circumstances, this kind of validation is more easily done through the EXISTS operation shown in Listing 15-3.
IF EXISTS
(
    SELECT sod.OrderQty
    FROM Sales.SalesOrderDetail AS sod
    WHERE sod.OrderQty = 1
)
    PRINT 'Record Exists';
Listing 15-3

Validating data existence using EXISTS

Figure 15-2 shows the execution plans for both listings as well as the performance metrics:
COUNT Reads: 1,248
Duration: 12ms
EXISTS Reads: 29
Duration: 1.2ms

A diagram for query 1 has a cost of 100% and begins from a clustered index scan at 91% cost, to stream aggregate at 9%, and to compute scaler and select at 0% each. A diagram for query 2 has a cost of 0% and begins from constant and clustered index scans at 100% each.

Figure 15-2

Different execution plans between COUNT and EXISTS

While this may look like an obvious win, negative results, where the entire data set has to be scanned, may run the same or even slower. This is why I say test this approach in your systems. Clearly going from 1,248 reads down to 29 is a win.

Worth noting, the optimizer is suggesting a different index for the first query from Listing 15-3. Following that suggestion could enhance a little, but not as much as using EXISTS in this case.

Favor UNION ALL Over UNION

The UNION clause allows you to combine multiple result sets into a single result set. However, UNION is an aggregate operation. Listing 15-4 shows an example of combining results.
SELECT sod.ProductID,
       sod.SalesOrderID
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 934
UNION
SELECT sod.ProductID,
       sod.SalesOrderID
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 932
UNION
SELECT sod.ProductID,
       sod.SalesOrderID
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 708;
Listing 15-4

Combining result sets through UNION

This query results in the execution plan shown in Figure 15-3.

A diagram has 3 non-clustered index seeks with costs of 12%, 12%, and 26%, and values 935 of 935, 926 of 926, and 3007 of 3007 given, respectively. Then 3 stream aggregates at 2%, 2%, and 7%, and values 935 of 905, 926 of 896, and 3007 of 2725, respectively.

Figure 15-3

Execution plan from a UNION query

In this case, the optimizer has chosen to satisfy the query through the use of the Stream Aggregate operations. You’ll note that each of the three data sets is retrieved through an Index Seek, but then they are aggregated, in order to eliminate duplicates, in the Stream Aggregate operations. The results of the aggregation are then combined through the two Merge Join operations.

However, in this case, each of our three queries is actually already unique. If you’re in that situation, or in a situation where the data doesn’t have to be unique, then using the UNION ALL clause can dramatically improve performance. Figure 15-4 shows the results of changing Listing 15-3 to UNION ALL.

A diagram begins with 3 non-clustered index seeks on the right with costs of 24%, 24%, and 50%, and values 935 of 935, 926 of 926, and 3007 of 3007 given, respectively. Then concatenation on the left with values of 2% cost, 0.001 seconds.

Figure 15-4

An execution plan for UNION ALL

The aggregation operations are gone as are the joins. Instead, we’re left with just the data access through the Index Seek operations and then a combination of the data sets through the Concatenation operator. The plan is simpler, and the performance went from 5.1ms to 3.2ms. While it’s not a huge gain in this case, it may be as data sets grow. Interestingly, the reads stayed the same at 20 for both queries. The additional work was primarily taken up through CPU.

Ensure Indexes Are Used for Aggregate and Sort Operations

The most optimal way to improve the performance of aggregate functions such as MIN or MAX is to take advantage of columnstore indexes. However, even traditional rowstore indexes can provide some performance improvements for aggregation queries. The lack of indexes supporting a query inevitably leads to table or index scans. For example, Listing 15-5 has a simple aggregate.
SELECT MIN(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod;
Listing 15-5

Looking for MIN UnitPrice

Running this query results in the execution plan in Figure 15-5 and the following performance metrics:
Reads: 1,248
Duration: 59.4ms

A diagram begins on the right with a clustered index scan with values of 94% cost, 0.037 seconds, and 121317 of 121317, given. From there it goes to a stream aggregate on the left with values of 6% cost, 0.057 seconds, and 1 of 1, and to select at 0%.

Figure 15-5

No supporting index for the aggregate query

To return a single value, the minimum UnitPrice, 1,248 reads were necessary as well as performing the aggregation operation through the Stream Aggregate operator. You can even see this visually as a thick arrow comes out of the Clustered Index Scan and only a very thin arrow from the Stream Aggregate.In order to attempt to improve performance, I’ll add the index shown in Listing 15-6.
CREATE INDEX TestIndex ON Sales.SalesOrderDetail (UnitPrice ASC);
Listing 15-6

Adding an index on the UnitPrice column

Rerunning the original query from Listing 15-5 results in the following performance metrics and the execution plan in Figure 15-6:
Reads: 3
Duration: 402mcs

A diagram begins on the right with a non-clustered index scan with values of, 100% cost, 0.000 seconds, and 1 of 1, given. From there it goes to the top on the left with values of 0%, 0.000 seconds, and 1 of 1, stream aggregate with values of 0% cost, 0.000 seconds.

Figure 15-6

An index supports the query

While the plan looks more complex since it has more operations, the performance metrics speak for themselves. The index is scanned, but it’s a limited scan of a single row. The Top operator is to ensure that only one value is returned. The Stream Aggregate is merely a formality, validating the results.

You’ll find similar behaviors in ORDER BY statements and indexes. It’s all about having the data in a particular order to support the needs of the query. Overall, for analytical queries, you’re still going to see superior performance enhancements from columnstore indexes, but you can see some from rowstore indexes as well. Before we proceed, Listing 15-7 will remove the test index.
DROP INDEX IF EXISTS TestIndex ON dbo.Test1;
Listing 15-7

Removing the test index

Be Cautious with Local Variables in a Batch Query

Running multiple statements through a batch process is extremely common. Just as common is to use local variables to pass values between the statements in the batch. However, local variables in filter criteria can, at times, lead to poor choices by the optimizer. To keep things simple, instead of a large batch, we’ll just look at the behavior of a single statement. The principles are the same in a larger set of queries. Listing 15-8 shows a query using a local variable.
DECLARE @Id INT = 67260;
SELECT p.Name,
       p.ProductNumber,
       th.ReferenceOrderID
FROM Production.Product AS p
    JOIN Production.TransactionHistory AS th
        ON th.ProductID = p.ProductID
WHERE th.ReferenceOrderID = @Id;
Listing 15-8

Using a local variable to pass values

Figure 15-7 is the resulting execution plan.

A diagram begins on the right with a non-clustered index scan and clustered key lookup with values of 19% cost, 0.005 seconds, 48 of 3, 41%, 0.004 seconds, and 48 of 3 given, respectively.

Figure 15-7

Execution plan with a local variable

We’re filtering with the local variable on the ReferenceOrderID column of the TransactionHistory table. An Index Seek against a nonclustered index was used to satisfy the query. However, that means that additional values have to be pulled from the clustered index using the Key Lookup operation and a Nested Loops join. The rest of the data for the query comes from the Clustered Index Seek against the Product table and another Nested Loops join to pull them together.

If we modify the code to use a hard-coded value, we’d see something like Listing 15-9.
SELECT p.Name,
       p.ProductNumber,
       th.ReferenceOrderID
FROM Production.Product AS p
    JOIN Production.TransactionHistory AS th
        ON th.ProductID = p.ProductID
WHERE th.ReferenceOrderID = 67260;
Listing 15-9

Replacing the local variable

With the query changed, we see a new execution plan in Figure 15-8.

A diagram begins on the right with a non-clustered index scan and clustered key lookup with values of, 2% cost, 0.000 seconds, 48 of 8, and 81%, cost, 0.000 seconds, 48 of 48, given, respectively. Then nested loops, inner join at 0% cost, to a clustered index scan at 7%.

Figure 15-8

The execution plan changes with a hard-coded value

While we’re retrieving the exact same data set, the optimizer has made new choices in how best to satisfy the query. Some of the plan shape is the same. The data access against the TransactionHistory table is still through the nonclustered Index Seek and the Key Lookup. However, the rest of the plan is different. A Merge Join is used, as well as an Index Scan against the Product table’s clustered index. If you look at the properties of the Scan, you’ll find it’s an ordered scan, satisfying the need for the Merge Join to have ordered data. However, the other additional operator is the Sort, needed to get the data from the TransactionHistory table into order as well.

Things really get interesting when we look at the performance metrics. First, our original query from Listing 15-8:
Reads: 242
Duration: 408mcs
Now the metrics from Listing 15-9:
Reads: 170
Duration: 747mcs

Listing 15-8 is considerably faster. However, Listing 15-9 uses fewer resources in terms of reads, 170 vs. 242. The key difference here is in the row estimates. If you look at Figures 15-7 and 15-8, you’ll notice that the number of rows estimated in Figure 15-7 is 3 while the number in Figure 15-8 is 48. You’ll also notice that in both cases, the actual number of rows returned as 48.

The real value for the row estimate is in the properties of the execution plan from Figure 15-7, 3.05628, rounded to the value we see of 3. The reason such an inaccurate estimate was used is because the value of a variable is not known to the optimizer at compile time. Because of this, the optimizer uses the density graph from the statistics, 2.694111E-05, and the number of rows, 113,443, and multiplies the two together, arriving at a row estimate of 3.05628.

The difference with the hard-coded value is that the optimizer can use the histogram to decide how many rows will be returned by the value used, 67620. That value from the histogram is 48, the actual number returned. The optimizer decided that performing 48 Index Seek operations through the Nested Loops join would be more expensive than doing an ordered scan of the index and sorting the results of the initial data from the TransactionHistory table.

However, in this case, the optimizer arrived at a plan that used fewer resources (Figure 15-8) but actually ran slower. This is one of those points where we have to start making choices when attempting to tune queries. If all we care about is speed of execution, in this case, using the local variable is faster. However, if our system was under load and potentially suffering from I/O issues, there’s a very good chance that the hard-coded value will perform better, since it performs fewer reads and will thus be in less conflict on the resources it uses.

I’m not advocating for hard-coded values, however. That leads to all sorts of code maintenance issues. Instead, you would want to explore the possibility of making the variable into a parameter so it can be sampled by the optimizer at compile time through parameter sniffing, although, again, that opens up issues as we explored in Chapter 13.

Sadly, there’s not always going to be a single, right answer when it comes to query tuning. However, gaining the knowledge of how, and more importantly, why, your queries are satisfied by the optimizer can help you make better choices.

Stored Procedure Names Actually Matter

Believe it or not, how you choose to name your stored procedures can have some impact on performance, albeit a very small one. A common approach to naming objects is to use an abbreviation, like “sp” for “stored procedure”. Another common approach is to then separate this lead from the object name with an underscore, “sp_”. This is so common as a naming standard, Microsoft was actually there first. This is how system stored procedures are designated. SQL Server then assumes that a stored procedure with that prefix is in the master database. In fact, the following are the search criteria:

  • The master database

  • The current database based on qualifiers (database name and/or owner)

  • In the current database using “dbo” as the schema if none is provided

The performance hit from this is tiny, almost to the point of not being able to measure it. However, on very active systems, you’re adding overhead that you simply don’t need to add. Another issue comes when you create a procedure in your local database, with the same name as a system procedure. Attempting to execute the query, especially if it has a different set of parameters, will simply cause an error.

Reducing Network Overhead Where Possible

It’s a rare process, report, analysis, or application that is running locally with the database. The network is always involved in moving the data around from SQL Server. While it’s important to optimize your queries, you also have to think through how you work in batches of queries. A couple of approaches can help to reduce some network overhead:

  • Execute multiple queries in sets.

  • Use SET NOCOUNT.

To fully understand what I mean, we’ll drill down on these approaches.

Execute Multiple Queries in Sets

Everything is subject to testing, but generally, it’s a good approach to put sets of queries together in a batch or stored procedure. Rather than repeatedly connecting to the server, initiating a round trip across the network, call once and get the work done, returning what’s needed. You may have to ensure that your code can deal with multiple result sets. You also may need to ensure that you can consume JSON or other mechanisms of moving sets of data around, not simply single-row inserts or updates.

Use SET NOCOUNT

After every query in a batch or stored procedure, SQL Server will, by default, report the number of rows affected:

(<Number> row(s) affected)

That information is transmitted across the network for every single statement. As you can imagine, while tiny, it accumulates to excessive network overhead. To change this behavior, use the T-SQL statement in Listing 15-10.
SET NOCOUNT ON;
Listing 15-10

Using the SET NOTCOUNT command

You can also set the NOCOUNT to OFF if you choose. Using this statement at the start of a set of queries or in a batch will not cause recompiles. It simply stops the reporting of those row counts. It’s a small thing, but it’s a good coding practice.

Techniques to Reduce Transaction Cost of a Query

Transactions are a fundamental part of how SQL Server protects the data in your system. Every action query—INSERT, UPDATE, DELETE, or MERGE—is performed as an atomic action in order to preserve the data in a consistent state, meaning data changes are successfully committed. This behavior cannot be disabled.

If the transition from one consistent state to another requires multiple database queries, then atomicity across the multiple queries should be maintained using explicitly defined database transactions. The old and new states of every atomic action are maintained in the transaction log (on the disk) to ensure durability, which guarantees that the outcome of an atomic action won’t be lost once it completes successfully. An atomic action during its execution is isolated from other database actions using database locks.

Based on the characteristics of a transaction, here are two broad recommendations to reduce the cost of the transaction:

  • Reduce logging overhead.

  • Reduce lock overhead.

Reduce Logging Overhead

Your procedure or batch might consist of a series of action queries. If you attempt to maintain atomicity for each query separately, you get a lot more writes to the transaction log, possibly adding to contention there. To see this in action, let’s consider Listing 15-11.
DROP TABLE IF EXISTS dbo.Test1;
CREATE TABLE dbo.Test1
(
    C1 TINYINT
);
GO
DBCC SQLPERF(LOGSPACE);
--Insert 10000 rows
DECLARE @Count INT = 1;
WHILE @Count <= 10000
BEGIN
    INSERT INTO dbo.Test1
    (
        C1
    )
    VALUES
    (@Count % 256);
    SET @Count = @Count + 1;
END;
DBCC SQLPERF(LOGSPACE);
Listing 15-11

Inserting 10,000 rows of data

The DBCC command, SQLPERF, is a really simple way to look at the amount of log space consumed. At the start of the operation, I have a small log in AdventureWorks, about 72MB in size and only 4% full. Running Listing 15-11 takes 18 seconds and fills the log to about 58%. For a very small system like this, that’s a pretty massive impact.

One way to immediately change the behavior would be wrap the code in a transaction like what you see here in Listing 15-12.
DECLARE @Count INT = 1;
DBCC SQLPERF(LOGSPACE);
BEGIN TRANSACTION;
WHILE @Count <= 10000
BEGIN
    INSERT INTO dbo.Test1
    (
        C1
    )
    VALUES
    (@Count % 256);
    SET @Count = @Count + 1;
END;
COMMIT;
DBCC SQLPERF(LOGSPACE);
Listing 15-12

Adding a transaction

Now, instead of 10,000 individual transactions, I have one. The percentage of the log used didn’t change at all. The execution time went to one second. That alone is a win. However, we can improve this even more. The WHILE loop is a poor approach to set-based operations (and we’ll be covering row-by-row processing in Chapter 18 in a lot more detail). Changing to a set-based operation like Listing 15-13 should have an even greater impact.
DBCC SQLPERF(LOGSPACE);
BEGIN TRANSACTION;
WITH Tally
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
    FROM master.dbo.syscolumns AS A
        CROSS JOIN master.dbo.syscolumns AS B)
INSERT INTO dbo.Test1
(
    C1
)
SELECT TOP 1000
       (Tally.num % 256)
FROM Tally;
COMMIT;
DBCC SQLPERF(LOGSPACE);
Listing 15-13

Eliminating the WHILE loop

As with the simple addition of the transaction in Listing 15-12, we now have a single statement, so the percentage used of the log didn’t shift at all. And the performance improved even more, dropping down to about 310ms. We’ve gone from using 54% of the log to just almost nothing and 18 seconds to 310ms. It is possible to use fewer log resources.

However, a caution. Putting more work into a transaction can lead to a longer running transaction overall in some circumstances. That can lead to resource contention and blocking (which we’ll cover in Chapter 16). Also, longer transactions can add to the recovery time during a restore (although Accelerated Database Recovery certainly helps with this; for more, read the Microsoft documentation). As always, testing to validate how things work on your system is the best way to go.

Reduce Lock Overhead

By default, all T-SQL statements use some type of locking to isolate their work from that of other statements. Lock management adds performance overhead to a query. Another way to improve the performance of a query is reduce the number of locks necessary to satisfy a given query. Further, reducing the locking of one query improves the performance of other queries because they are then waiting less for those resources.

This is a very large topic which we’re going to drill down on in the next chapter. However, when talking about resource contention, I want to add a little detail on locking, here, in this chapter.

By default, SQL Server can use a row-level lock. However, if the query is working on a larger number of rows, row locks on each individual row add significant overhead to the lock-management process. SQL Server will automatically attempt to manage the lock granularity by moving to a page level or even a table-level lock. This process is dynamic and automatic and should generally be left to SQL Server to take care of. However, you can get directly involved by providing a lock hint as shown in Listing 15-14.
SELECT * FROM <TableName> WITH(PAGLOCK);  --Use page level lock
Listing 15-14

Code to supply a lock hint

The example in Listing 15-14 would supply a page level lock.

By default, SQL Server uses less intrusive locks for SELECT statements besides those for INSERT, UPDATE, and DELETE statements. This allows the SELECT statements to read data that isn’t being modified. In some cases, the data may be quite static, and it doesn’t go through much modification. In such cases, you can reduce the lock overhead of the SELECT statements in one of the following ways:

  • Mark the database as READONLY.

  • Use snapshot isolation.

  • Prevent SELECT statements from requesting a lock.

Mark the Database As READONLY

This requires a situation where the database will not receive updates while it is set using the code from Listing 15-15.
ALTER DATABASE <DatabaseName> SET READ_ONLY;
Listing 15-15

Changing the database to be READ_ONLY

This allows users to retrieve data from the database, but it prevents them from modifying the data. The setting takes effect immediately. If occasional modifications to the database are required, then it may be temporarily converted to READWRITE mode in Listing 15-16.
ALTER DATABASE <DatabaseName> SET READ_WRITE
Listing 15-16

Changing the database back to read/write

Use Snapshot Isolation

SQL Server provides a mechanism to put versions of data into tempdb as updates are occurring, radically reducing locking overhead and blocking for read operations. You can change the isolation level of the database by using an ALTER statement like Listing 15-17.
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON;
Listing 15-17

Changing the database isolation level

This will add overhead to the tempdb (again, except when using Accelerated Database Recovery). You can also set the isolation level in the connection string.

Prevent SELECT Statements from Requesting a Lock

I’m honestly hesitant to even mention the use of NOLOCK or READ UNCOMMITTED isolation levels. These are seen by many as the magic “run faster” switch in SQL Server. It is not. The use of NOLOCK prevents the SELECT statement from requesting any lock except the shared lock. NOLOCK is applicable to SELECT statements only. Although the NOLOCK hint can’t be used directly on the tables referred to in the action queries (INSERT, UPDATE, and DELETE), it may be used on the data retrieval part of the action queries, as shown in Listing 15-18.
DELETE Sales.SalesOrderDetail
FROM Sales.SalesOrderDetail AS sod WITH (NOLOCK)
    JOIN Production.Product AS p WITH (NOLOCK)
        ON sod.ProductID = p.ProductID
           AND p.ProductID = 0;
Listing 15-18

Using the NOLOCK with a DELETE statement

You must know that the use of NOLOCK leads to dirty reads. Dirty reads can cause duplicate rows or missing rows. Therefore, NOLOCK should be considered only as a last resort to control locking. In fact, this is considered to be quite dangerous and will lead to improper results in queries. The best approach is to mark the database as read-only or use one of the snapshot isolation levels.

If you made any of the example changes to the database from this section, I recommend restoring from a backup.

Summary

Tuning queries is not simply about picking the right index and then ensuring that the code uses that index. As you can see, ensuring that you minimize the resources used by a query is also a big part of query tuning. Experimenting with different logical approaches to a given query can result in drastic performance enhancements, or simply reduce the resource use of a bottlenecked resource. When resources are in use, you will begin to experience performance hits caused by blocking on those resources. In the next chapter, we’ll examine how blocking affects SQL Server query performance.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.253.62