What columns are in that index?

I’ve been helping several customers with tuning lately, which typically means I’m also looking at indexes and asking questions. A pattern I’ve noticed is columns from the clustering key being added to the key definition for the nonclustered index.  This brings up some interesting considerations when it comes to creating your nonclustered indexes.

Review

We’re going to start with a copy of WideWorldImporters, and create a table and indexes from it.
Notice that I’m adding a column to this table named OrderID_and_OrderLineID, which is the OrderID and the OrderLineID values concatenated. In Sales.OrderLines, OrderLine is unique, but OrderID is not. The two of them concatenated are unique, and I made this my primary key, as well as my clustering key (remember, you can have a primary key that’s different than your clustering key, which is a separate topic for another day).

USE [WideWorldImporters]
GO

DROP TABLE IF EXISTS [Sales].[SQLskills_OrderLines];
GO

CREATE TABLE [Sales].[SQLskills_OrderLines](
     [OrderLineID] [int] NOT NULL,
     [OrderID] [int] NOT NULL,
     [OrderID_and_OrderLineID] [bigint] NOT NULL,
     [StockItemID] [int] NOT NULL,
     [Description] [nvarchar](100) NOT NULL,
     [PackageTypeID] [int] NOT NULL,
     [Quantity] [int] NOT NULL,
     [UnitPrice] [decimal](18, 2) NULL,
     [TaxRate] [decimal](18, 3) NOT NULL,
     [PickedQuantity] [int] NOT NULL,
     [PickingCompletedWhen] [datetime2](7) NULL,
     [LastEditedBy] [int] NOT NULL,
     [LastEditedWhen] [datetime2](7) NOT NULL,
     CONSTRAINT [PK_Sales_SQLskills_OrderLines] PRIMARY KEY CLUSTERED
     (
          [OrderID_and_OrderLineID] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
     ) ON [USERDATA];
GO

INSERT INTO [Sales].[SQLskills_OrderLines](
     [OrderLineID]
     ,[OrderID]
     ,[OrderID_and_OrderLineID]
     ,[StockItemID]
     ,[Description]
     ,[PackageTypeID]
     ,[Quantity]
     ,[UnitPrice]
     ,[TaxRate]
     ,[PickedQuantity]
     ,[PickingCompletedWhen]
     ,[LastEditedBy]
     ,[LastEditedWhen]
     )
SELECT
     [OrderLineID]
     ,[OrderID]
     ,CAST(CAST([OrderID] AS VARCHAR(20)) + CAST([OrderLineID] AS VARCHAR(20)) AS BIGINT)
     ,[StockItemID]
     ,[Description]
     ,[PackageTypeID]
     ,[Quantity]
     ,[UnitPrice]
     ,[TaxRate]
     ,[PickedQuantity]
     ,[PickingCompletedWhen]
     ,[LastEditedBy]
     ,[LastEditedWhen]
FROM [Sales].[OrderLines];
GO

We’ll use Kimberly’s version of sp_helpindex, sp_SQLskills_helpindex, to see what’s truly in the index, and where. I much prefer this version to the built-in sp_helpindex, as it includes information about included columns, filters, and tells you what columns are in the tree and leaf levels.

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';
GO
Clustered index for SQLskills_OrderLines

Clustered index for SQLskills_OrderLines

This output is wide, which doesn’t lend itself well to a post, so you may need to zoom in on the image. I also trimmed out the included and filtered columns as neither are relevant in this discussion.

Notice that the only column in columns_in_tree is the concatenated OrderID and OrderLineID, and then all columns are in columns_in_leaf (because the leaf level of the clustered index is all the data).  Now we’ll add a nonclustered index on OrderID:

CREATE NONCLUSTERED INDEX [NCI_OrderID]
     ON [Sales].[SQLskills_OrderLines] ([OrderID])
     ON [USERDATA];
GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';
GO
Nonclustered index for SQLskills_OrderLines

Nonclustered index for SQLskills_OrderLines

In columns_in_tree we have our index key, OrderID, but we also have our clustering key, OrderID_and_OrderLineID, and both are in columns_in_leaf. What happens if we create a nonclustered index on OrderLineID, and make it unique?

CREATE UNIQUE NONCLUSTERED INDEX [NCI_OrderLineID]
     ON [Sales].[SQLskills_OrderLines] ([OrderLineID])
     ON [USERDATA];
GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';
GO
Unique nonclustered index for SQLskills_OrderLines

Unique nonclustered index for SQLskills_OrderLines

In the sp_SQLskills_helpindex output you’ll notice that OrderLineID is the only column in columns_in_tree, and both OrderLineID and OrderID_and_OrderLineID are in columns_in_leaf.

Remember that when you identify a nonclustered index as unique, it will not add the clustering key to the tree level, it just adds it to the leaf level. But if the nonclustered index is not declared as unique, then SQL Server adds the clustering key to the tree level to uniquely identify the row.

If we had created a clustered index that we didn’t also identify as unique, then SQL Server adds a UNIQUIFIER to the clustered index key, which then also gets added to the nonclustered index.

Defining the clustering key as part of the non-clustered key

What I’ve seen lately when looking at indexes is this:

CREATE NONCLUSTERED INDEX [NCI_OrderID_2]     
     ON [Sales].[SQLskills_OrderLines] ([OrderID], [OrderID_and_OrderLineID])
     ON [USERDATA];
GO

This is the same as the NCI_OrderID index I created, but someone has specifically placed the clustering key in the nonclustered index key. I always ask why they added the clustering key. Typically the answer is: I know it’s going to be needed in the query. That’s fine, but understand that it doesn’t matter whether you add the clustering key to the key for a non-unique nonclustered index or not; if you don’t, SQL Server will add it.

We’ve kept it simple so far with a single column for our clustering key. What if we have multiple columns? We’ll change up our table a bit:

USE [WideWorldImporters]
GO

DROP TABLE IF EXISTS [Sales].[SQLskills_OrderLines];
GO

CREATE TABLE [Sales].[SQLskills_OrderLines](
     [OrderLineID] [int] NOT NULL,
     [OrderID] [int] NOT NULL,
     [OrderID_and_OrderLineID] [bigint] NOT NULL,
     [StockItemID] [int] NOT NULL,
     [Description] [nvarchar](100) NOT NULL,
     [PackageTypeID] [int] NOT NULL,
     [Quantity] [int] NOT NULL,
     [UnitPrice] [decimal](18, 2) NULL,
     [TaxRate] [decimal](18, 3) NOT NULL,
     [PickedQuantity] [int] NOT NULL,
     [PickingCompletedWhen] [datetime2](7) NULL,
     [LastEditedBy] [int] NOT NULL,
     [LastEditedWhen] [datetime2](7) NOT NULL,
     CONSTRAINT [PK_Sales_SQLskills_OrderLines] PRIMARY KEY CLUSTERED
     (
     [OrderID] ASC, [OrderLineID] ASC, [StockItemID] ASC, [PackageTypeID] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
     ) ON [USERDATA];
GO

INSERT INTO [Sales].[SQLskills_OrderLines](
     [OrderLineID]
     ,[OrderID]
     ,[OrderID_and_OrderLineID]
     ,[StockItemID]
     ,[Description]
     ,[PackageTypeID]
     ,[Quantity]
     ,[UnitPrice]
     ,[TaxRate]
     ,[PickedQuantity]
     ,[PickingCompletedWhen]
     ,[LastEditedBy]
     ,[LastEditedWhen])
SELECT
     [OrderLineID]
     ,[OrderID]
     ,CAST(CAST([OrderID] AS VARCHAR(20)) + CAST([OrderLineID] AS VARCHAR(20)) AS BIGINT)     
     ,[StockItemID]
     ,[Description]
     ,[PackageTypeID]
     ,[Quantity]
     ,[UnitPrice]
     ,[TaxRate]
     ,[PickedQuantity]
     ,[PickingCompletedWhen]
     ,[LastEditedBy]
     ,[LastEditedWhen]
FROM [Sales].[OrderLines];
GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';
GO
Four column clustered index for SQLskills_OrderLines

Four column clustered index for SQLskills_OrderLines

I don’t generally advocate a wide clustered index, but there are certain scenarios where it’s needed/appropriate. If we take this table and add a nonclustered index, not unique, on one column, then you’ll see all four columns from the clustering key in the tree level:

CREATE NONCLUSTERED INDEX [NCI_Quantity]
     ON [Sales].[SQLskills_OrderLines] ([Quantity])
     ON [USERDATA];
GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';
GO
Nonclustered index (with wide CI) for SQLskills_OrderLines

Nonclustered index (with wide CI) for SQLskills_OrderLines

For a large table, the size of this nonclustered index has the potential to be much, much larger than a table where the clustering key is only a single column.

If we add a unique nonclustered index, then that clustering key is only in the leaf level:

CREATE UNIQUE NONCLUSTERED INDEX [NCI_OrderID_and_OrderLineID]
     ON [Sales].[SQLskills_OrderLines] ([OrderID_and_OrderLineID])
     ON [USERDATA];
GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';
GO
Unique nonclustered index (with wide CI) for SQLskills_OrderLines

Unique nonclustered index (with wide CI) for SQLskills_OrderLines

But then…what happens if we add one or two columns from the clustering key into the nonclustered index key?

CREATE NONCLUSTERED INDEX [NCI_PickingCompletedWhen]
     ON [Sales].[SQLskills_OrderLines] ([PickingCompletedWhen], [OrderID], [OrderLineID])
     ON [USERDATA];
GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';
GO
Nonclustered index with column from clustering key for SQLskills_OrderLines

Nonclustered index with column from clustering key for SQLskills_OrderLines

It doesn’t matter! All the columns from the clustering key are added to the tree level because I did not identify it as unique! If I had defined the nonclustered index as unique, then only those two columns that I specified in the key would be in the tree, and the other two columns from the clustering key would be part of the leaf level.

Conclusion

There are a couple points to remember when designing your nonclustered indexes. First, whenever you know that the column, or combination or columns, makes a unique key for a nonclustered index, I always recommend that you specify UNIQUE when you create the index. That information is great for the optimizer to have when generating query plans. Second, think carefully about the impact of your clustering key on your nonclustered indexes. Any non-unique nonclustered index will be larger (more pages, eventually more levels) because the entire clustering key will be in the tree level. Further, if you happen to have a clustering key with a column (or multiple columns) that are frequently modified, then those modifications have to be propagated to the tree levels of the nonclustered indexes as well.

Happy indexing!

Query Store Best Practices

I’m a huge fan of Query Store, which regular readers may know, but there’s a need to write a bit more about Query Store best practices.  This isn’t a “you must use this feature” post, this is a “here is what you must know if you want to use this feature” post.

I have a lot of content about Query Store, but maybe what’s really important gets lost amongst everything else.  Glenn prompted me to write this, after I worked with two customers last week that ran into issues related to their configuration of Query Store.  Listed below are the things you must know before you enable Query Store.  If you already have Query Store enabled and running, I recommend reviewing the information to ensure you are following best practices.

Settings

  • Review my post on Query Store Settings to understand what different settings exist for Query Store, what values are recommended for each setting, and why.
    • Most important: QUERY_CAPTURE_MODE set to AUTO, MAX_STORAGE_SIZE_MB set to 10GB at the absolute max, something less ideally (you may need to adjust CLEANUP_POLICY to keep less data, depending on your workload).

Trace Flags

  • Review my post on Query Store Trace Flags to understand the two Trace Flags which are Query Store-related, what they do, and why you want to enable them.
    • I definitely recommend both 7752 and 7745.  If you have 7752 enabled and you are trying to make changes to Query Store configuration after a restart, please review my post discussing the Query Store data load.  There is no shortcut to get this to load faster, and no way to kill it.  This is why proper settings are important.

Performance

  • Review my post on Query Store Performance Overhead. If you have a high-volume, ad hoc workload, it is quite possible that you should not enable Query Store.  Jonathan told me about a customer of his that enabled Query Store and within two (2) hours, they had filled up 10GB of Query Store.  We recommended that they not use Query Store at this time.  There are improvements coming in SQL Server 2019 that will hopefully help manage Query Store with this type of workload.

Version

  • I highly recommend that you run SQL Server 2016 SP2 CU7 or SQL Server 2017 CU15 (current CUs at the time of writing, I’ll try to keep this updated). There is a very important fix in CU7 that I strongly suggest you have in place.  I don’t see this fix in a CU for SQL Server 2017 yet, so it’s possible you could run into a problem if you have an ad hoc workload.

Ad hoc?

 

If you still have questions about how to configure Query Store or what to watch out for, please leave a comment!  I’m happy to share as much information and experience as possible.

Troubleshooting a Change in Query Performance

This is tale of troubleshooting…

When you unexpectedly or intermittently encounter a change in query performance, it can be extremely frustrating and troublesome for a DBA or developer. If you’re not using Query Store, a third-party application, or your own method to capture query data, then you probably don’t have information about what query performance looked like when things were good…you just know how it’s running now. I was working with a customer of Tim’s last week that had been chasing this exact problem for, in their words, years. They had recently upgraded to SQL Server 2016, and the problem was still occurring.

First Steps When There’s a Change in Query Performance

If there is a change in query performance, we typically look at differences in the execution statistics, as well as differences in the plan in terms of the shape and operations. The execution statistics confirm that performance is worse; duration is higher, CPU and/or IO is higher. With this client, after we verified the execution data, we used Query Store to compare the plans, and they were different. When I see different plans I immediately check the compiled values (input parameters) for each plan. Typically, I expect the different plans are a result of different values – because the query is sensitive to different input parameters. Interestingly enough, in the case of this customer, the different plans had the exact same parameters. My next step? Statistics.

The customer had told me that the only way they could consistently resolve the issue was to rebuild indexes for a few of the tables that were involved in the query. Rebuilding indexes causes an update to statistics with a FULLSCAN. I asked if they had ever tried just updating statistics with FULLSCAN to fix the issue, and they reported that it caused problems when they did. I tabled that issue, as I had a theory about what was happening and wanted to prove that out before digging into a secondary issue.

Collecting Query-Related Data

I set up an Extended Events session to capture the auto_stats event (which fires when statistics automatically update) and filtered on the objects in question.

I also walked them through the statistics information within SQL Server. This was extremely fun. I love statistics, and I love explaining the output of DBCC SHOW_STATISTICS to people. When I’m teaching, I can typically see the light bulbs come on for people as I talk through the stats header, the density vector, and the histogram (especially the histogram!). On the call, I could hear them say “oh!” and “that makes so much more sense!” as I was talking…so fun. But I digress. So, we looked at the statistics for the tables in question, and we could see that because they had rebuilt indexes earlier in the day to resolve the issue, the sample was 100% (from the FULLSCAN). Query performance was good at this point, so we had our baseline.

Then I showed them how to view the stats header information for all statistics for the tables in question using the sys.dm_db_stats_properties DMV and requested that they query the DMV if they had a change in query performance.  I explained that they should look to see if statistics had updated, and if so, take note of the sample size.

Sidebar

My hypothesis was that stats were getting automatically updated because Auto Update Statistics was enabled for the database, and enough data had changed to fire the update (realize they are on 2016 with 130 compatibility mode, which means the threshold is lower than it used to be…20% + 500 rows). After statistics updated, all queries that used those statistics were recompiled, and because the sample was the default, it wasn’t an accurate representation of the data in the table, and so a different plan was being generated, even though the same input parameters were used.

Back to the story

I took a few moments and explained my hypothesis, and then we looked at the data in one of the tables. We looked at the distribution of data in each of the leading columns in the indexes, and in one of them, there was extreme skew.  If query performance degraded, and statistics had updated with the default sample, I requested that they run UPDATE STATISTICS with FULLSCAN to see if that resolved the issue.

Proof!

Sure enough, within the next 24 hours query performance changed and statistics for one of the indexes had been updated automatically with the default sample. I correlated this finding with the  the Extended Events output as an extra validation step.  When they updated statistics with the FULLSCAN, the “good” query plan was used again, query performance improved, and CPU dropped immediately.

A Better Solution

Now that we knew the issue, we had to decide how to address it. I recommended that they manually update statistics again with the FULLSCAN, but this time with the PERSIST_SAMPLE_PERCENT option set to ON. With this option enabled, statistics will retain the sample rate used with the manual update of statistics, even if it’s an automatic update. In this scenario, we wanted to ensure that statistics would always be updated with a FULLSCAN.

The customer was slightly concerned about the implications of the FULLSCAN update running during the day, because the tables were large and the update would generate overhead. This is absolutely a valid concern for tables that are extremely large.  To address this, we included the NORECOMPUTE option in the UPDATE STATISTICS command for the statistic in question, ensuring that the statistic would not automatically update even if the number of modifications crossed the threshold.

I explained to the customer that they would definitely need to run a SQL Agent job on a regular basis to update statistics for those which used the NORECOMPUTE option so that the optimizer had updated information about the distribution of data. Using NORECOMPUTE is rare, I find, but it definitely has its use cases. It’s essential to have a job to manually update those stats if you use that option for any of your statistics.

The Take Away

I haven’t heard from the customer since we determined the problem and created a solution, and I take that as a good sign. It’s ironic, because so often when I’m talking about changes in query performance, I mention that updating statistics is not the solution. It can just look like the solution because it causes a recompile and for a parameter-sensitive query, it often “fixes” the issue. But it hides the root problem in that scenario, and updating statistics (via the index rebuild) was hiding the root problem here as well. It wasn’t that the query was parameter sensitive, it’s that the optimizer needed a histogram from a FULLSCAN of the column, due to the skew in the data.

Most of the time, the default sample for a stats update is good enough. But when it isn’t, then you either need a FULLSCAN, or if that doesn’t do it, you need to look at filtered statistics across ranges of the data to give the optimizer better information.