Gail Shaw’s SQL Server Howlers

For the latest in our series of SQL Server Howlers, we asked Gail Shaw which common SQL Server mistakes and misunderstandings lead to tearful DBAs and plaintive cries for help on the forums.

I spend a lot of time on the SQLServerCentral, and other SQL community forums, trying to help SQL Server users who find themselves in difficulties. Over time, I’ve seen certain misunderstandings and mistakes occur with alarming regularity. Here I pick just four of my “favorite” SQL Server howlers, covering misunderstanding of partitioning, misguided index strategies, abuse of hints, and the never-ending quest for “go-faster” server settings.

Missing the Point of Partitioning

“I partitioned my table, but my queries aren’t any faster…!”

Partitioning is a feature that’s mostly intended to aid in maintenance on larger tables and to offer fast ways to load and remove large amounts of data from a table. Partitioning can enhance query performance, but there is no guarantee.

Partitioning and Query Performance

Let’s look at a textbook example, the Orders and OrderDetails tables for a hypothetical internet vendor.

Listing 1: The Orders and OrderDetails tables

This is simple enough; in the script, PartitionTableDefinition.sql (available for download at the bottom of the article), I create two copies of those tables, one to be partitioned and one to be left un-partitioned.

I’m going to partition the table on the OrderDate column, which is a fairly logical and normal choice for this kind of table, given that many queries will be likely filtering by date, and has the advantage that the column is reasonably narrow, unchanging and ascending, all desirable attributes for the clustered index.

Listing 2 shows the partition schema and function. We create seven partitions (Partition1 for any dates earlier than 2011/11/01, Partition2 for 2011/11/01 up to 2011/11/30, and so on), all of which, in this example, I’m mapping into the PRIMARY filegroup, simply because I don’t have extra drives to split them across. However, for reasons we’ll discuss later, this is unlikely to have too much of an impact in this case.

Listing 2: Creating the partition function and partition scheme

I use the same partition schema and function for both tables, which is the reason why the OrderDetails table has an OrderDate column in it, a column that shouldn’t really be in there as it is an attribute of the order, not the order line items. Still, if I’m going to partition both by date, they both need the date column in them. With our partitioning in place, Listing 3 creates the necessary indexes for each of our tables.

Listing 3: Primary keys and Indexes

The next step is to populate each table. In the provided script, I load 500,000 rows per month for 6 months into the Orders table (for a total of 3 million rows) and 2,000,000 rows per month for 6 months into the OrderDetails table (for a total of 12 million rows).

We’re now ready to try some simple queries to see how the two tables (partitioned vs. un-partitioned) perform. The unwary developer tends to expect a performance improvement in the partitioned queries that is proportionate to the portion of the table that the queries can ignore. For example, if the table has 6 months of data and is partitioned by date and then we query and filter for only the one month of data, then SQL can ignore 5/6 of the data in the table and hence they expect that the query should be of the order of 6 times faster.

Listing 4: Simple queries to test partitioning performance boost

I ran each test several times, and ignored the results from the first execution (because of the cost of compiling the query and caching the data). This is the reason why the fact that the files were all in the same filegroup, on the same drive, did not affect these results; all of the reads are logical reads, reads done from memory, not from disk. On larger, less-used tables, if multiple partitions were going to be read and the data was not in memory, then having the tables partitioned and the partitions on different IO paths might provide a performance benefit.

I aggregated the results from a Profiler trace, and Table 1 shows the average CPU, and average number of reads, for each pair of queries against the partitioned and un-partitioned tables.

Query Average CPU Average Reads
Query 1 Partitioned 2302 73337
Query 1 Un-partitioned 2704 75565
Query 2 Partitioned 992 12121
Query 2 Un-partitioned 844 12209
Query 3 Partitioned 272 50323
Query 3 Un-partitioned 232 63952
Query 4 Partitioned 438 8645
Query 4 Un-partitioned 369 8747

Table 1: Comparing query performance for partitioned and un-partitioned tables

For every query, we can see that the ones against the partitioned tables show a slightly lower number of reads, the reduction ranging from 0.7% up to 20%. The latter value is good, but it’s not phenomenal. If I were performance tuning a problematic query, I certainly wouldn’t consider a 20% reduction in reads a success. The effect on the CPU time is minimal, and in three cases, the average CPU time increases for the partitioned table. In other words, just by partitioning these two tables, we have not achieved a good improvement in the performance of these queries.

Of course, I’m not implying that partitioning never improves query performance. It certainly can do so, but it requires specific query forms, and a lot of the benefit comes from parallelizing over the partitions or, if the query is IO-bound, from additional IO bandwidth, arising from having partitions that are accessed in parallel on separate IO channels.

However it is not, for reasons that relate to index architecture, a general-purpose tool that will guarantee an easy, immediate and significant increase in query performance. Figure 1 offers a simplified depiction of index architecture for an un-partitioned table with the clustered index on a date column (a real index would have a lot more pages at the leaf level).

1539-Picture%201-27c05c05-80bd-43f1-a372

Figure 1: Un-partitioned table, 3-level deep clustered index

Let’s say that we filter for one month of data (and assume that the data is evenly distributed by month). SQL locates this data by navigating down the b-tree from the root page to the start of the range and then reads along the leaf level to the end of the range, as shown in Figure 2.

1539-Picture%202-8b21306e-d15e-4475-85a7

Figure 2: Navigating the un-partitioned clustered index to find the required rows

That takes five page reads: the root, one intermediate page and the three leaf pages (again, there would be far more in a real index). Now let’s compare that against a table that’s partitioned by month into six partitions, as shown in Figure 3.

1539-Picture%201-936ed592-8661-4a3f-ab90

Figure 3: A table partitioned into six partitions

A partitioned table is really a collection of tables (as many as there are partitions) that behave as a single table. Each partition has its own smaller b-tree for the clustered index on that partition. Any partition-aligned non-clustered indexes are also essentially multiple smaller non-clustered indexes, one on each partition. In order to locate one month of data, the partition function tells SQL what partition holds the requested data, then it’s just a seek down the b-tree of the partition that contains the data, as shown in Figure 4.

1539-Picture%202-e634f4fe-1890-41e5-a40d

Figure 4: Navigating the partitioned clustered index to find the required rows

We haven’t avoided the need to scan the table, because with a clustered index on the date column we would never have scanned the table anyway.

So what’s the point of partitioning at all?

Partitioning and the Sliding Window Technique

The main benefits of partitioning are in maintenance and data loads. With partitioning, we can add data to, or remove it from, a huge table very easily, just by switching partitions in or out. This is termed “sliding window”, and at a high-level it works as shown in the following series of figures.

1539-Partitioning%20Start-f684e7b5-0bd6-

Figure 5: A partitioned Transactions table and Staging table

What we see, in Figure 5, is a table storing transactions (for example sales transactions). The table is partitioned by month and data is loaded into it at the end of each month. The Staging table has exactly the same indexes as the partitioned table, and all indexes on the partitioned table are partition-aligned (created on the same partition scheme).

To load the latest month of data into that table and remove the oldest, we can load the Staging table with the most recent data (if it’s not already loaded, say by the application) and split the most recent partition in the sales table, so that there is an empty partition at the ‘end’ of the table, as shown in Figure 6.

1539-Partitioning%20Split-62a3afba-83c9-

Figure 6: Load the Staging table with new data; split the most recent partition in the main table

Next, with a purely metadata operation, we can switch the Staging table with the empty partition, and the data in the Staging table is now in the main table. Since a metadata operation does not require moving the actual, rows, just changing some pointers in the system catalogs and allocation structures, this is both exceedingly fast and logs very little. It also does not require the long-duration exclusive locks that inserting into the table would require.

1539-Partitioning%20SwitchIn-eb69decd-28

Figure 7: Switch the Staging table with the empty partition

In order to remove the oldest data from the table, we simply reverse the process. We switch the oldest partition with the empty Staging table, as shown in Figure 8.

1539-Partitioning%20SwitchOut-91e61cff-c

Figure 8: Switch the oldest partition with the Staging table

Then, we merge the oldest partition with the next oldest and truncate the Staging table.

1539-Partitioning%20Merge-bd8a1006-1803-

Figure 9: Merge in the empty partition, truncate the Staging table

Using this sliding window technique, we can add a million or so rows to the table, and remove another million or so rows, in milliseconds with minimal impact on the transaction log and without needing long-duration locks on the main table that could block other queries.

Using the Orders table from our previous example, our sliding window scheme would look something like as shown in Listing 5.

Listing 5: Implementing a sliding window for the Orders table

In terms of easing maintenance, we can rebuild individual partitions (offline only) without affecting the rest of the table. The different partitions can even have different compression settings, so we can tailor the setting as appropriate for the various usage patterns, typically with the older data getting more compression than the current data.

In addition, we can map partitions containing older, less-frequently used data to filegroup placed on slower storage, because it is not as important as the latest data, which would likely be in a partition on fast storage.

In summary, partitioning is mostly for improved maintenance, fast loads, fast deletes and the ability to spread a table across multiple filegroups; it is not primarily for query performance.

Poor Index Design

“I have indexes on all the columns in the WHERE clause, but SQL is still doing a scan…!”

This is a fun one…there are a couple of reasons why a query may scan indexes, or the table, even when there is an index that appears to support the particular query.

The most common causes are as follows:

  • Index is neither sufficiently selective nor covering
  • Index columns are in the wrong order to support the query
  • Query predicate is not SARGable

I’ll briefly touch on each of these.

Index is insufficiently selective or not covering

For SQL Server to use an index for the evaluation of a query, one of two criteria needs to be true: the query needs to retrieve a small portion of the table, or the index needs to be a covering index.

A covering index is one where all the columns needed by the query are somewhere in the index. See http://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/ for further details on covering indexes.

When I say small portion of the table, I’m not talking half or a quarter or even 10%; the usual point where SQL Server will consider that a query is returning too much data to use a non-covering index is about 0.5% of the table. As a rough estimate, the cut-off point is the point where the number of rows returned exceeds 30% of the total number of pages in the table.

The reason for this cut-off is the expense of performing key lookups to retrieve the data not contained in the index, as shown in Figure 10.

1539-a1d068e9-a1ef-41ed-b036-6a7f1f43119

Figure 10: The costly Key Lookup

Let’s take an example query against a made-up table, and assume we have a non-clustered index on the Location column only.

Listing 6: Find customers located in “Twee Buffels”

Now it’s unlikely that many people live in “Twee Buffels”, so SQL Server can perform a seek operation on the index and fetch the rows that match that location.

Since the index is just on the Location column, all that SQL will be able to get back from the index is the Location column, and a pointer to the row in the base table (the pointer will be either the clustered index key or a physical RID). Having retrieved the rows from the index, SQL will then do lookups to the base table to fetch the rest of the required columns. SQL Server performs these lookups one row at a time.

If we assume that the query returns three rows, SQL has to do four separate data access operations to run that query, one seek on the non-clustered index and then three lookups to the base table, one lookup for each row returned by the seek. It’s easy to see that these lookups become very expensive when a query returns many rows.

If we reran the same query with a location that returns 100,000 rows that would be 100,001 separate data accesses, each reading one or more pages (each lookup does a number of reads equal to depth of the clustered index tree, if the table has a clustered index, or one read if the base table is a heap).

It’s for this reason that SQL Server will choose not to do key lookups if the query returns a large number of rows; instead, it will perform a single scan of the whole table, which can very easily be more efficient in terms of IO than many, many, many single-row lookups

Another common cause of SQL Server choosing to scan is a query that contains multiple predicates, when no single index exists that has all the columns necessary to evaluate the WHERE clause. For example, an index on (FirstName, Surname), would fully support any query with a WHERE clause of FirstName = @FirstName AND Surname = @Surname. However, if there was one index on FirstName only, and a second separate index on Surname, then SQL Server can use neither one efficiently. It may choose to seek one index, do lookups for the other columns and then do a secondary filter; it may choose to seek both indexes and perform an index intersection, or it may give up and scan the table.

So, if a query isn’t using the index that it should be, check whether the index is covering, check how many rows it retrieves and remember that placing an index on every column of a table is not a good indexing strategy, and is unlikely to be useful for many queries.

Index key columns are in the wrong order to support the query

When we create a table, the order in which the columns are specified is irrelevant. However, when we create a clustered or non-clustered index on that table, the order in which the index key columns are specified is critically important, because it defines the sort order of the index.

For a query to be able to seek on an index, the query predicate must be a left-based subset of the index key. Let’s return to our previous made-up example.

Listing 7: Find customers located in Teyateyaneng

Let’s imagine, this time, that there is an index on the columns (Surname, Location) and no other indexes that contain the Location column. Figure 11 paints a massively-simplified picture of how a portion of that index would look.

1539-9e363e0d-7a3e-4a40-b70e-46da3cd517c

Figure 11: Simplified depiction of the index on (Surname, Location)

As we can see, there is no way for SQL Server to perform a seek operation on that index, to retrieve rows by location, since the rows are not ordered by location. Rows are ordered by surname first, and rows for a specific location are scattered through the index. Hence, if we only have a query predicate on Location and an index that has Location as a second (or third, or fourth, or…) column, then SQL Server can only use that index for a scan, not a seek.

For further coverage of this topic, see the following two blog posts:

Query predicate is not SARGable

SARG is one of those annoying, made-up terms that tell us just about nothing. It’s an abbreviation of Search Argument and it simply means that a particular query predicate can be used for an index seek operation (which is a lovely recursive definition).

As a very high-level summary, a query predicate is SARGable if it is of the form:

That looks complex but, basically, it means that the column must be compared directly to an expression (function, parameter, variable or constant).

Let’s consider three example predicates:

  • WHERE TransactionDate = ‘2012/01/01’
  • WHERE TransactionDate > DATEADD(mm,-1,@SomeDate)
  • WHERE DATEADD(dd,-1, TransactionDate) > @SomeOtherDate

Of those three, the first two are SARGable. The first example directly compares the TransactionDate column to a constant, and the second to a function.

In the third example, there is a function on the TransactionDate column; this makes the predicate non-SARGable and will result in an index scan rather than a seek (providing there’s no other SARGable predicates in the query).

To summarize, just having an index on a column is no guarantee that queries filtering on that column can use the index. The columns returned and the exact form of the predicates in the WHERE clause can both influence whether an index is useful for a query or not.

Looking for Go-faster Hints

“What hint do I add to make queries faster?”

Why not try, OPTION(GoFasterPrettyPlease)? Just kidding! There’s no hint that universally makes queries faster. Think about it, if there really were an option that made 100% of queries faster, why wouldn’t it be the default?

Many people seem to regard the NOLOCK hint (a.k.a. the Read Uncommitted isolation level), in particular, as some of “query-go-faster” switch; it is not. Rather it is an isolation level hint that allows SQL Server to ignore locks and potentially return inconsistent data (quickly). There are places and times where use of NOLOCK is tolerable, but never use it as a default hint on all queries.

If blocking is a problem, consider use of the row-versioning isolation levels, either Snapshot isolation or read committed snapshot isolation, rather than the read uncommitted isolation level, as the row-versioning isolation levels, which allow read queries to avoid use of locks and still ensure that they fetch the correct data.

The MAXDOP and RECOMPILE hints are not go-faster switches either. There are situations where they are fine, and situations where they are not (and that’s far too complex to get into in this article), but the main point is they should always be used only after careful testing.

Some hints are less “intrusive” than others and you should always try to use the least intrusive hint that achieves the desired outcome. The OPTIMIZE FOR… hints, for example, can be very useful for fixing bad parameter sniffing issues and are far less intrusive than index or join hints.

Some hints are misunderstood; for example, many people seem to think that the USE PLAN hint bypasses the optimizer, forcing use of a particular execution plan. While it’s true that the hint specifies an execution plan for the query to use, it does not bypass the optimizer and does not remove the compilation overhead. The optimizer still has to optimize (guided by the plan specified in the hint) to ensure that the plan specified is in fact a valid plan for that query. It can in some cases increase the compilation time.

In short, use hints sparingly. Reserve their use for just those times when the optimizer is not picking a desired plan, and you know exactly what the hint does, why it is used and why it is necessary, and you have tested extensively, and you are prepared to continue to test to ensure that the hint remains the best option.

Looking for Go-faster Server Configuration settings

“My server’s slow. What configuration settings do I change to make it fast?”

Again, if there were a setting that always improved query performance, it would be the default. Nevertheless, some people seem to treat certain settings as if they were a go-faster switch. The result of messing with these settings is a server with settings that differ from those generally recommended, and that may well run slower than it would with the default settings.

Personally, there are only three settings that I would generally recommend changing from their default values. Firstly, don’t leave Max server memory at the default setting, as it’s likely to result in OS memory pressure (http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx).

Secondly, I feel that cost threshold for parallelism is too low by default. I’ve seen recommendations to bump it to certain fixed values (I’ve seen 10, 25 and 50), but I prefer to do an analysis of the queries and pick a value tailored for the environment.

Thirdly, the value of MAXDOP may need changing from 0, but certainly not to 1, except in some specific situations, and only after extensive workload analysis has proved that this is the way to go.

Deciding the value to which it should be set is rather more complex, than just ‘use this value’. For more details on this topic, see chapter 3 of Troubleshooting SQL Server (the eBook is available as a free download), as well as Adam Machanic’s Parallelism series.

That is about it in term of settings you can and should tweak without really good reasons. Any time I do a server audit for a client I find out which server settings they have changed. If I find that settings like ‘boost priority’ or ‘lightweight pooling’ or the query memory settings (min memory per query (KB)) changed I know I’m going to find huge problems with the server because someone’s been fiddling with things best left unchanged.

In short, if you don’t know why a setting should be changed, don’t change it.

Summary

That’s just a short list, and unfortunately, I see these day after day on the forums and elsewhere. I guess the best summary would be: don’t change or implement anything without research and without knowing what it’s going to affect. Sure, that’s more work than just implementing something blindly, but it’s far more likely to achieve the desired effect.