I’m a lazy person. I just want to do as little work as possible. I don’t want to think too much when I work. Yes, I know that sounds very terrible and will probably disqualifies me as a SQL Server consultant, but in today’s blog posting I want to show you how you can delegate the working and thinking process to the Query Optimizer when you want to create an indexing strategy for a specific query. Sounds interesting? If yes, then enter my world of index tuning

The problematic query

Let’s have a look at the following query:

As you can see here I use a local variable in combination with an inequality predicate to retrieve some records from the table Sales.SalesOrderDetail. When you run that query and you look at the execution plan, you can see some serious problems with it.

• SQL Server has to scan the complete Clustered Index of the table Sales.SalesOrderDetail, because there is no supporting Non-Clustered Index. The query needs 1382 logical reads for this scan, and the elapsed time is around 800ms.
• The Query Optimizer introduced an explicit Filter operator in the query plan, which does a row-by-row comparison to check for qualifying rows (ProductID < @i)
• Because of the ORDER BY CarrierTrackingNumber, an explicit Sort operator is introduced in the execution plan.
• The Sort operator is spilled over to TempDb, because of the inaccurate Cardinality Estimation. With an inequality predicate in combination with local variables, SQL Server estimates hard-coded 30% of the rows from the base cardinality of the table. In our case the estimation is 36395 rows (121317 * 30%). In reality the query returns 120621 rows, which means that the Sort operator has to spill over to TempDb because the requested memory grant is just too small.

And now I ask you – how can you improve that query? What are your suggestions? Just take a break and think for a few minutes. How can you improve that query without changing the query itself?

Let’s tune the query!

Of course we have to work on our indexing strategy to make an improvement. Without a supporting Non-Clustered Index that’s the only plan that the Query Optimizer can use to run our query. But what is a good Non-Clustered Index for this specific query? Normally I will always start thinking about possible Non-Clustered Indexes by looking at the search predicate. In our case the search predicate is as follows:

WHERE ProductID < @i

We request rows filtered on the column ProductID. Therefore we want to create a supporting Non-Clustered Index on that column. So let’s create that index.

After the creation of the Non-Clustered Index we have to test our change, so we execute our original query from the first listing again. And guess what? The Query Optimizer is not using the Non-Clustered Index that we just created! We have created a supporting Non-Clustered Index on the search predicate, and the Query Optimizer is not referencing it? Normally people are already out of luck at this point. But we can hint the Query Optimizer to use the Non-Clustered Index to get a better understanding of *why* the Query Optimizer hasn’t chosen the index automatically:

When you now look at the execution plan, you can see the following beast – a parallel plan!

The query takes 370244 logical reads! And the elapsed time is almost the same as previously with around 800ms. What the heck is going on here? When you look in more detail at the execution plan, you can see that the Query Optimizer has introduced a Bookmark Lookup, because the previous created Non-Clustered Index is not a Covering Non-Clustered Index for this query. The query is over the so-called Tipping Point, because we are retrieving almost all rows with our current search predicate. Therefore it doesn’t make sense to use the Non-Clustered Index in combination with a very expensive Bookmark Lookup.

Instead of thinking why the Query Optimizer hasn’t chosen the previous created Non-Clustered Index, we have just delegated that thinking process to the Query Optimizer itself, and have asked it through the query hint, why that Non-Clustered Index wasn’t chosen automatically. As I said at the beginning: I don’t want to think too much ☺.

To solve that problem with the Non-Clustered Index we have to include the additional requested columns from the SELECT list in the leaf level of the Non-Clustered Index. You can look again at the Bookmark Lookup to see which columns are currently missing in the leaf level:

• CarrierTrackingNumber
• OrderQty
• UnitPrice
• UnitDiscountPrice

Let’s recreate that Non-Clustered Index:

We have made another change, so we have to test our change again by running our query. But this time we run the query without the query hint, because the Query Optimizer should now choose the Non-Clustered Index automatically. And guess what? The index is now chosen when you look at the execution plan.

SQL Server now performs a Seek operation on the Non-Clustered Index, but we still have an explicit Sort operator in the execution plan. And because of the 30% hard-coded Cardinality Estimation the Sort operator still spills over to TempDb. Ouch! Our logical reads have dropped down to 757, but the elapsed time is still at around 800ms. What do you do now?

We can now try to include the column CarrierTrackingNumber at first in the navigation structure of the Non-Clustered Index. This is the column on which SQL Server performs the Sort operation. When we have that column first in the Non-Clustered Index, we have a physical presorting of our data by that column, and therefore the explicit Sort operator should go away. And as a positive side-effect there is nothing to spill over to TempDb. And no operator in the execution plan cares now about the wrong Cardinality Estimation. So let’s try that assumption by recreating the Non-Clustered Index again:

As you can see from the index definition, we have now physically presorted our data by the columns CarrierTrackingNumber and ProductID. When you now rerun the query, and when you have a look at the execution plan, you can see that the explit Sort operator has gone away, and that SQL Server scans the complete leaf level of the Non-Clustered Index (with a residual predicate for the search predicate).

That plan isn’t that bad! We just need 764 logical reads, and the elapsed time for this query is now down to 600ms. That’s a 25% improvement compared with previously! BUT: the Query Optimizer suggests to us a better Non-Clustered Index through the *great* (?) feature of the Missing Index Recommendations! Because we trust the Query Optimizer blindly, we create that recommended Non-Clustered Index:

When you now rerun the original query you will see amazing things: the Query Optimizer uses *OUR* previously created Non-Clustered Index, and the Missing Index Recommendation has gone away! You have just created an Index that is never used by SQL Server – except for INSERT, UPDATE, and DELETE statements where SQL Server has to maintain your Non-Clustered Index. You just have created *pure* overhead for your database. But on the other hand you have satisfied the Query Optimizer by eliminating the Missing Index Recommendation. But that’s *NOT* the goal: the goal is to create indexes that are *ALSO* used.

Conclusion: never, ever trust the Query Optimizer!

Summary

Today’s blog posting was a little bit controversial, but I wanted to show you how the Query Optimizer can help you when you work on your indexing strategy, and how the Query Optimizer can fool you when you work on your indexing strategy. Therefore it is very, vey important that you just make minor adjustments, and that you immediately test your change by running your query again. And when you use a Missing Index Recommendation from the Query Optimizer, please think whether the recommendation is a good one. As I have said – I don’t want to think. Ouch…

Thanks for your time,

-TheLazyPersonNamedKlaus

In today’s blog posting I will talk about how you can have Lock Escalations in the isolation level SERIALIZABLE, and how they can be avoided. I have already blogged back in February 2014 about the basic concept of Lock Escalations and why they are needed in SQL Server. So please go back to this blog posting to get a basic understanding of this very important concept.

The Isolation Level SERIALIZABLE

The isolation level SERIALIZABLE is used to prevent so-called Phantom records. To prevent them, SQL Server uses a Key-Range Locking technique. Let’s have a look at the following SELECT statement:

This statement requests all records where the column StateProvinceID is between 10 and 12. If you run that statement in the isolation level SERIALIZABLE, the range between these IDs is locked to protect it from data modifications:

• You can’t INSERT new records into the protected range
• You can’t DELETE existing records from the protected range
• You can’t move existing records into the protected range by an UPDATE statement

Changes outside this range are permitted, because SQL Server has only locked that particular range.

Lock Escalations

The most important thing about the key range locking technique is that you need a supporting Non-Clustered Index on your search predicate. In our case this is the column StateProvinceID. If you have no supporting index defined on that, the Query Optimizer has to choose a Clustered Index Scan/Table Scan operator in the execution plan. This means that you have to scan your complete table (with a residual predicate) to find matching rows.

And when you run your SELECT statement in the isolation level SERIALIZABLE, you will trigger a Lock Escalation when you acquire more than 5000 locks during the scan. The following listing demonstrates how you trigger the Lock Escalation when there is no supporting Non-Clustered Index.

Now let’s create a supporting Non-Clustered Index.

When you look at the execution plan now, you can see that the Query Optimizer references this newly created index in combination with a Bookmark Lookup.

When you now run the SELECT statement again in the isolation level SERIALIZABLE, you will not trigger the lock escalation anymore, because you have physically only read the 20 requested rows.

Summary

The isolation level SERIALIZABLE is the most restrictive one, and prevents phantom records. Internally SQL Server uses a key range locking technique to keep a range of requested rows stable. The most important thing to remember here is that you need a supporting Non-Clustered Index on your search predicate. Otherwise you need to scan your complete table, and you will trigger a Lock Escalation if you have read more than 5000 rows.

Thanks for your time,

-Klaus

I’m very happy to announce today that I will be back with my already famous SQL Server Performance Tuning Workshop in London this year! This 5-day long workshop is scheduled from June 1 to June 5 in Central London. Besides London I also run this workshop in Utrecht/Netherlands from April 13 – 17. There are plenty of choices if you want to attend this workshop. If you are interested in attending the workshop you should be very fast, because I also offer a super-early bird price until January 31, 2015.

I don’t want to talk too much about the workshop content from my side, because Wim, Carla, and Filip who have attended this workshop last year in Antwerpen/Belgium were kind enough to provide you some feedback about the workshop. So let’s sit back, and enjoy listening to them.

Wim from Heverlee/Belgium

“The SQL Server Performance Tuning & Troubleshooting Workshop exceeded the high expectations I already had. All the separate bits and pieces of SQL knowledge we gathered through the years, were properly tied together by Klaus. He provided us with all the necessary knowledge to tackle the performance issues we will surely encounter in our day to day business. Highly recommended!”

Carla from Heverlee/Belgium

“The workshop exceeded my expectations! The complete workshop was well structured, all topics were explained very clear with a lot of excellent prepared and documented demos. I definitely will use the knowledge and documentation that Klaus shared with us during the workshop as starting point and guide for my future work.”

Filip from Beerse/Belgium

“This training on SQL Server performance tuning was extremely interesting. Klaus taught me lot of things I was not aware of. I especially loved the tips he gave on common mistakes and wrong concepts about SQL Server. Such information, coming from an independent expert (“expert” is still very understimated when talking about Klaus’ knowledge), is a lot more valuable than spending hours reading and googling. This training has a high return on investment and anyone who is seriously working with SQL Server should attend this workshop.”

Thanks for your time, and I already look forward to see you soon in London!

-Klaus

In today’s blog posting I want to talk about 2 kinds of deadlocks that can occur when you run transactions in the Transaction Isolation Level Repeatable Read. When you set your transaction to use the isolation level Repeatable Read, SQL Server holds acquired Shared Locks for reading data until the end of the transaction (COMMIT or ROLLBACK). When you afterwards try to change the read data (through an UPDATE statement), it can cause different types of deadlocks if the same transaction runs concurrently multiple times. Let’s have a more detailed look at 2 different kinds of such deadlocks.

Cycle Deadlock

A Cycle Deadlock is one of the most common deadlocks – you just access resources (like various tables) in a different order, and at the end every query waits for another one. With the isolation level Repeatable Read it is even possible to cause a cycle deadlock when you just work with one table across multiple transactions. Let’s have a look at the T-SQL code of the first transaction.

And here is the T-SQL code of the second transaction.

As you can see from both listings, 2 data ranges are read and finally updated. If both transactions run at the same time, a Cycle Deadlock can occur, because the data ranges are accessed in the wrong order.

When SQL Server starts to run the UPDATE statement, the necessary Update Lock (U) can’t be acquired, because the Update Lock isn’t compatible with the already granted Shared Lock from the different session. Finally both UPDATE statements are waiting because of the Shared Lock acquired in the other session – a classical Cycle Deadlock just with one table! In this case you have to rewrite your code to get resolve this specific deadlock – just access your data ranges in the same order.

Read/Update Deadlock

A second type of deadlock can occur with the isolation level Repeatable Read if you read data with the intention to update it later. Let’s have a look at the T-SQL code of a simple transaction.

To cause this type of deadlock you just need to run the transaction across multiple sessions. You even don’t need to access different data ranges as you can see from the code. Let’s try to explain what happens here. When this transaction runs across multiple sessions concurrently, all sessions can acquire the Shared Locks for reading the data.

Because you hold the Shared Locks until the end of the transaction (COMMIT or ROLLBACK) in Repeatable Read, the following UPDATE statement can’t acquire the necessary Update Locks, because they are already blocked by the Shared Locks acquired in the different session. Deadlock!

This type of deadlock can be avoided by using a hint in the SELECT statement to acquire an Update Lock in advance.

Therefore just one SELECT statement can acquire the necessary Update Locks at the beginning (the Update Lock is not compatible with the Update Lock itself), continue with the UPDATE statement, and finally release the acquired locks. Afterwards the second transaction can continue with its SELECT and UPDATE statement.

You have to use the same technique here that SQL Server uses internally in an UPDATE execution plan: when you read data with the intention to update it later, you have to acquire an Update Lock during the reading phase to prevent this type of deadlock in the isolation level Repeatable Read.

Summary

As you have seen in this blog posting, it is very easy to cause various types of deadlocks if you use the isolation level Repeatable Read. Therefore you have to be very careful when you write transactions in this specific isolation level.

Thanks for your time,

-Klaus

Today I’m very happy and proud to announce the SQLpassion workshop schedule around Europe for 2015. The following locations are currently scheduled and planned:

• SQL Server Query Tuning Workshop in Vienna/Austria (March 16 – 19, 2015)
• SQL Server Performance Tuning Workshop in Utrecht/Netherlands (April 13 – 17, 2015)
• SQL Server Query Tuning Workshop in Zurich/Switzerland (April 20 – 23, 2015)
• SQL Server Query Tuning Workshop in Frankfurt/Germany (May 4 – 7, 2015)
• SQL Server Performance Tuning Workshop in London/United Kingdom (June 1 – 5, 2015)

I’m really happy to finally also include a workshop in Frankfurt/Germany in the schedule, because I got over and over again requests for that. I can listen . In addition to the current scheduled dates I also work to plan additional workshops at the following locations:

• SQL Server Query Tuning Workshop in Antwerpen/Belgium
• SQL Server Query Tuning Workshop in Stockholm/Sweden

If you are interested in attending a workshop in one of these locations, please get in touch with me.

You can also learn from past attendees how it feels when you get the SQLpassion in your veins.

Thanks for your time,

-Klaus

In today’s blog posting I want to demonstrate how missing indexes on tables can cause deadlocks in SQL Server. For the preparation of the test scenario the following code creates 2 tables and populates both tables with 4 records.

Before I show you the actual deadlock repro, the following code shows a simple UPDATE statement that updates a specific row in the first table.

Because we have no index defined on Column2, the Query Optimizer has to choose a Table Scan operator in the execution plan to find the qualifying rows for our UPDATE statement:

This means that we have to scan the complete heap table to find the one row we want to update. In that case SQL Server locks the first row in the table with an Exclusive Lock. When you run in a different session a SELECT statement where you reference another “later occurring” row in the heap table, the Table Scan operator will block, because first you have to read all the “earlier occurring” rows of the heap table to actually get to the row that you have logically requested in your query.

A Table Scan by default means that you have to scan the whole table, and therefore you have to acquire a Shared Lock on every record – even on records that you have not logically requested. This situation can lead now to a deadlock situation if you access the 2 tables in the 2 different sessions in a different order, and when you try to write and read from both tables. The following code shows the transaction from the first query:

And the following listing shows the code from the 2nd transaction:

As you can see from both transactions, the two tables are accessed in a different order. If the timing is now right, running both transactions at the same time can lead to a deadlock situation. Imagine the following execution sequence:

1. The first transaction runs the UPDATE statement on Table1.
2. The second transaction runs the UPDATE statement on Table2.
3. The first transaction runs the SELECT statement on Table2. This SELECT statement will block, because the Table Scan operator wants to acquire a Shared Lock on a row that is already exclusively locked by the second transaction.
4. The second transaction runs the SELECT statement on Table1. This SELECT statement will block, because the Table Scan operator wants to acquire a Shared Lock on a row that is already exclusively locked by the first transaction.

The following picture illustrates this deadlock situation.

Both transaction are now blocking each other, therefore you cause a deadlock in SQL Server. In that case the Deadlock Monitor background process kicks in, and performs a rollback of the cheapest transaction (based on the number of bytes that the transaction has written to the Transaction Log).

You can resolve this deadlock very easily by providing an index for the Column2 in both tables. In that case SQL Server can perform a Seek operation to find the qualifying rows, and can therefore bypass already locked rows in the leaf level of the index when you perform the SELECT statement:

The following picture illustrates how the locking situation looks now:

With a Seek operation you can just bypass locked rows in the leaf level of the index, and you can avoid the deadlock that we have been talking about. Therefore it’s always very important that you have a detailed look at your indexing strategy when you see deadlock situations in your database. Indexing is one of the most important things in SQL Server – always remember that!

Thanks for your time,

-Klaus

In my last week’s blog posting I talked about how the use of temp tables can cause recompilations in SQL Server. As mentioned at the very end of the blog posting, I want to concentrate today a little bit more on Table Variables, which can avoid the expensive overhead of recompilations. Let’s have a more detailed look at them.

Table Variables

A table variable is always scoped to a batch submitted to SQL Server. When you want to reference a table variable outside of a batch, you will get back an error message. That’s the first big difference compared to temp tables. The following code shows how you can create and use table variables – again in the context of a simple stored procedure.

The nice thing about table variables is that they don’t cause any recompilations. When you execute the stored procedure and when you monitor recompilations with the Extended Event session from last week, you will see that no recompilations are happening.

Why is this possible with table variables? First of all a table variable is just a variable – as the name implies. This means that you don’t change your database schema when you define your table variable. Therefore recompilations based on database schema changes can be avoided. And in addition a table variable has no statistics. Therefore statistics don’t have to be maintained, and the second cause of recompilations just disappears.

Both things sounds great at first, but we will find huge drawbacks when we look further. Let’s have a look at them. A table variable is just a variable – almost! A table variable is still persisted in TempDb! Yes, you read correctly: when you work with a table variable, physical I/O in TempDb is involved. This is very easy to prove with the Dynamic Management View sys.dm_db_session_space_usage, which tracks TempDb usage on a session level. Let’s have a look at the following code:

As you can see from the output from the DMV, the table variable needs 5 pages in TempDb, which were allocated. Because the table variable is already out of scope, these 5 pages are already marked for deallocation. Be aware of that side-effect!

Table variables also have no statistics. Therefore no recompilations happen here. But as a side-effect the Query Optimizer always just estimates 1 row. And that can be very, very bad. Imagine you make a join from your table variable to another table in your database. In that case the Query Optimizer will introduce the Nested Loop Join operator in the execution plan, and will reference the table variable as the outer table, because of the estimation of 1 row. If you return in reality 10000 or even more rows, the whole execution plan isn’t really optimal anymore. Let’s have a look at the following example:

When you have a more detailed look at the Clustered Index Scan operator, you can see that the cardinality estimation is here just 1 row and is nowhere near the actual number of rows returned by the operator.

You can fix that wrong cardinality estimation by using a Statement-Level Recompilation that was introduced back with SQL Server 2005.

But this approach is a little bit counter-productive, because you again introduce a recompilation overhead that you wanted to avoid with the use of table variables…

Summary

With table variables you can avoid the overhead of recompilations in SQL Server – with some side-effects. The largest one is the wrong cardinality estimation of just 1 row. Therefore table variables only make sense when you deal with a small amount of rows, because then the wrong cardinality estimation doesn’t really matter and doesn’t affect your performance. But with a larger set of rows it would hurt your performance because of the inefficient execution plan that is generated.

As a general rule-of-thumb you should use temp tables for a large amount of data, and table variables with a small amount of data. But you really have to benchmark it for your own workload, to decide when a temp table and when a table variable makes sense.

Thanks for your time,

-Klaus

In this and next week I want to talk about a very important performance tuning topic in SQL Server: Recompilations. As you will see they can happen when you execute very simple stored procedures. Today I want to lay out the foundation of recompilations in SQL Server, why they have to happen, and next week I will show you how you can avoid them by rewriting your stored procedures in a different way.

Recompilations – what?

Before I want to talk about the specifics of recompilations in SQL Server, I want you to have a more detailed look at the following simple stored procedure.

As you can see from the code, there is nothing special about it. In the first step a temp table is created. Afterwards some records are inserted into the Temp Table, and finally the inserted data is retrieved with a simple SELECT statement. Code that you have already written hundreds – maybe thousand times in SQL Server. Let’s create in the first step an Extended Event session that tracks recompilations. The following code shows the definition of this event session.

When you now execute the stored procedure you will see that that 2 Recompilations have happened during the runtime.

A Recompilation means that SQL Server recompiled the execution plan during the execution itself. This introduces additional CPU overhead that will finally decrease the throughput of your workload. But for now the real question is why these recompilations have happened?

SQL Server mainly performs recompilations under the following 2 circumstances:

• Schema Changes
• Statistic Updates

And because of these 2 circumstances we have triggered the recompilations in the previous executed stored procedure. When you look again at the stored procedure definition, you can see that we have created in the first step a temp table. When you create a temp table in TempDb, you change your database schema, and because of this reason the first recompilation has happened.

And after the creation of the temp table, you have inserted some rows into it. Therefore SQL Server had to update the underlying statistics object of the Clustered Index of the temp table – the Clustered Index was created through the PRIMARY KEY constraint by SQL Server. A simple stored procedure caused 2 recompilations in SQL Server – wow!

Summary

Temp tables can have a huge performance overhead caused by recompilations. On the other hand temp tables have accurate statistics that helps the Query Optimizer to produce better performing execution plans. Therefore temp tables only make sense when you deal with a larger set of data. For a smaller set of data the CPU overhead caused by the recompilations would be larger as the performance gain through the statistics.

In next weeks blog posting I will introduce Table Variables to you, and here we will see how they can avoid the nasty side-effects of recompilations – and what other performance problems they can introduce. Stay tuned.

Thanks for your time,

-Klaus

The last month was really time consuming for me: I was running my brand-new SQL Server Query Tuning Workshop 3 times across Europe: Zurich/Switzerland, London/United Kingdom, and finally in Vienna/Austria. The workshop runs for 4 days, and I teach almost everything you need to know to write better and faster performing SQL queries for SQL Server.

I don’t want to talk too much about the workshop itself, because Arun, Ina, Bernd, and Thomas were kindly enough to provide some feedback about the workshop from their perspective as an attendee. So let’s sit back, and enjoy listening to them.

Arun from London/United Kingdom

“My day-to-day writing of SQL has gone up a level – I am able to finely tune individual statements or underlying database objects to get the kind of query plan I want. Moreover I know now what sort of plan I am looking for. I have been already able to use this knowledge to address performance issues that came up this week in capacity testing.”

Ina from Oberhaching/Germany

“Anyone faced with the task to implement queries on complex ERP data structures – for reporting, providing views, or for statistical analysis – comes quickly to its limits without knowledge of the operation of SQL Server. The workshop provides an excellent insight into the technical world of SQL Server, explained with many examples of queries and their optimization.”

Bernd from Monheim/Germany

“After attending the 4 days long workshop you know very exactly how SQL Server executed SQL queries on the logical and physical level through execution plans. In addition you will know how you can improve queries by applying the right indexing strategy. This in-deep knowledge was very well explained and presented by Klaus.” 

Thomas from St. Margrethen/Switzerland

“I was lucky enough to attend the 4 day long SQL Server Query Tuning Workshop from Klaus in Zurich. He has presented in a very easy way the performance related internals of SQL Server that will help you to write better performing SQL queries. Based on that knowledge I finally know now why sometimes queries are fast, and sometimes are slow. It was an amazing workshop, which makes the day-to-day work with SQL Server now very easy.”

Wow, that’s really amazing! It makes me really proud to see the SQLpassion in their faces, and how the workshop already helped them in their day-to-day life with SQL Server. If you also want to feel the SQLpassion in your veins when working with SQL Server, you can signup for the last SQL Server Query Tuning Workshop in 2014 in Utrecht/Netherlands from November 17 – 21.

If you attend this week the SQLPASS Summit in Seattle, make sure to say hi to me. I try to be as much time as possible at the Community Zone.

Nice greetings from the always rainy Seattle

-Klaus