If you’ve got performance troubles with an application that stores data in SQL Server, and especially if it’s a home-grown application (not a store-bought app), you can get dramatic performance improvements simply by focusing on some basic indexing techniques. These tips and tricks pay off more than pouring money into hardware that might look good sitting in the datacenter, but doesn’t really make the application significantly faster.
When I go into a shop to speed up an application I’ve never seen before, two of my favorite quick-hits are from the index performance tuning queries from SQLServerPedia:
- Find unused indexes – these are indexes the SQL Server engine says it’s not using. Unused indexes incur a speed penalty because SQL Server still has to add/update the indexes as records change, so they make writes slower.
- Find missing indexes – these are indexes SQL Server wishes it had available.
I’m not going to cover those in detail this week because I’ve already recorded tutorial videos over at SQLServerPedia for those, but I do want to focus on something these queries won’t pick up. Sometimes a table has two nearly-identical indexes, and they’re both being used for reads. Take these two:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType] ON [dbo].[MyTable] ([RunID] ASC, [SiteID] ASC, [DataSource] ASC, [OutputType] ASC, [PeriodType] ASC ) CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_QuotaItemDriverID] ON [dbo].[MyTable] ([RunID] ASC, [SiteID] ASC, [DataSource] ASC, [OutputType] ASC, [PeriodType] ASC, [QuotaItemDriverID] ASC, ) |
They’re two different indexes, and they’re both getting used – but does that mean we need them both?
They’re very nearly identical – but the second index has one extra field. When the SQL Server engine gets a query that needs RunID, SiteID, DataSource, OutputType, and PeriodType – but not QuotaItemDriverID – then it will use the first index. When it gets a query that needs all six fields, then it’ll use the second index.
In cases like this, I prefer to drop that first index and let the slightly bigger index pick up the slack. Reading a slightly larger index will take slightly more time: if a query didn’t need that QuotaItemDriverID field, it still has to pull it off the disk in order to perform the query. However, dropping the index pays off during inserts/updates/deletes, because it’s one less index SQL Server has to manage. It also makes the database smaller, thereby making database maintenance tasks smaller/faster.
If:
- I have two indexes with the exact same fields in the same order, but
- One has 1-2 extra fields, and
- There aren’t include fields, or the include fields are the same
Then I’ll drop the shorter index with extreme prejudice.
When Indexes Have Include Fields
If they have “include” fields, then I’ll merge the include fields between the two indexes to make one index to serve both needs. Say we have these two indexes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_Includes] ON [dbo].[MyTable] ([RunID] ASC, [SiteID] ASC, [DataSource] ASC, [OutputType] ASC, [PeriodType] ASC ) INCLUDE ( [YTDRevenue], [MTDRevenue] ) CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_QuotaItemDriverID_Includes] ON [dbo].[MyTable]([RunID] ASC, [SiteID] ASC, [DataSource] ASC, [OutputType] ASC, [PeriodType] ASC, [QuotaItemDriverID] ASC, ) INCLUDE ( [SalespersonID], [MTDRevenue] ) |
The first index includes the YTDRevenue field, but the second index doesn’t. If I just drop the first index, then queries that needed that field won’t get the full speed benefits from the second index. To merge the two indexes, I need to drop both indexes and recreate the second one with the YTDRevenue field included, like this:
|
1 2 3 4 5 6 7 |
CREATE NONCLUSTERED INDEX [IX_RunID_SiteID_DataSource_OutputType_PeriodType_QuotaItemDriverID_Includes] ON [dbo].[MyTable]([RunID] ASC, [SiteID] ASC, [DataSource] ASC, [OutputType] ASC, [PeriodType] ASC, [QuotaItemDriverID] ASC, ) INCLUDE ( [SalespersonID], [MTDRevenue], [YTDRevenue] ) |
In this example, I tacked the YTDRevenue field on to the end of the include field list. The order of the included fields doesn’t matter, since SQL Server doesn’t sort by those.
Things to Watch Out For
In my examples, I kept things simple by omitting all of the extra indexing options like partitioning and sorting in TempDB. When doing index tuning in real life, though, you’ll want to check those options to make sure they’re consistent from index to index.
Field order matters in indexes; if two indexes have the same fields but in different order, that doesn’t mean you can drop one of them.
Ideally, after making index changes, we would restart the SQL Server instance to reset the DMV counters that monitor index use. In reality, though, that’s not so easy to pull off, so we need to log our changes to understand what the changes have been. After making index changes, log the changes somewhere. I keep the output of the index performance tuning DMV queries in Excel spreadsheets because it’s easier to email those back and forth from machine to machine, especially when I’m consulting. The next time you do index performance tuning on the same database, you can use the historical spreadsheets to determine whether or not your changes worked the way you’d planned.
Learn More About SQL Server Index Tuning
I really like Grant Fritchey’s book SQL Server 2008 Query Performance Tuning Distilled, and I wrote a book review about it. I can’t recommend it highly enough, and I’d start there.
If you don’t have the patience to wait for a book, here’s a few more blog posts about performance tuning:
Performance Tuning with Perfmon – how to set up Perfmon, what SQL Server Perfmon counters to track what the indicators mean.
Data Mining Your SQL Server Perfmon Counters – want to take your Performance Monitor statistics to a new level? I wrote an article on SQLServerPedia explaining how to use Microsoft’s free Table Analysis Tools for the Cloud plugins to dive deeply into your data.
Primary Keys and Indexes – I explain the concepts behind keys and indexes using phone books as an example. Indexes have huge impacts on performance, and if you master these you can make your server go a whole lot faster without spending more money.
SQL Server 2005 Setup Checklist – some simple configuration tweaks can get you 20-30% performance increases right from the start without spending any extra money.
21 Comments. Leave new
That van needs a Decepticon logo on the gril. ;->
Saw transformers over the weekend it it just needs it.
Hi! If the indexes contain the same fields but in different order, ¿Is posible to drop any if i change the order of the fields in my selects?
Thanks
I’m not sure what you mean – can you elaborate?
Yes, sorry.
After run the db engine tunning advisor, it proposes to me 6 or 7 index to create in one table, but the fields are the same in 4 or 3 of them in diferent order.
I’m a developer and the tunning is new for me. My questions is: if i modify my querys, changing the order of the fields in the “joins” and “wheres” could i minimize the number of indexes?
Thanks in advance
Ah, great question. The order does indeed matter. It’s too big of a subject matter to cover here, but I highly recommend picking up Grant Fritchey’s book SQL Server 2008 Query Performance Tuning Distilled. I wrote a review of it here:
https://www.brentozar.com/archive/2009/04/book-review-sql-server-2008-query-performance-tuning-distilled/
The short story is that no, the order of the fields in the joins and the wheres isn’t going to matter as much as some other things you can do to improve query tuning performance.
Thanks Brent!
Based on your video in sqlserverpedia on removing indexes, is it a good idea to remove a index with reads_per_write greater than 1. What is your suggestion?
Thanks.
Reg84 – I talk about that in more detail in the video itself. Generally, no, I wouldn’t remove an index if it’s being used, but there’s always exceptions. For example, if it’s narrower than a wider index, and the wider one is being heavily used, it might make sense to remove the narrower one even if it’s being used. Every case is different, though, and it’s beyond what I could cover in a blog comment.
Brent,
Why don’t you include the field [RunID] in your new created index. Instead of going to the real data to find out if the [RunID] is the right one. This means less I/O’s. Or depends on the usage, you can add the [RunID] instead of [QuotaItemDriverID] in the indexed fields and add [QuotaItemDriverID] in the include fileds. This realy depends on the usage of the indexes.
Thanks,
Jacob
He does include RunID, it’s just a bit hidden, the second index and the final replacement one both have RunID at the end of the first line, so it’s missing a carriage return before it basically. You just need to scroll to the right to see it.
wich publishing house?the book
SQL Server 2008 Query Performance Tuning Distilled
Apress?
Yep!
Hi Team,
The link “Find unused indexes” is redirecting somewhere else. Please fix the same. I need a script which tells me the indexes are unused in the system.
Hi! Unfortunately SQLServerPedia has gone offline, so it’ll take us a while to repoint all of our links from there to somewhere else. Instead, check out https://www.brentozar.com/blitzindex/ – it does that, plus way more. Enjoy!
What if I have these two indexes:
CREATE INDEX [IDX_POLICY_APPLICATION_02]
ON [dbo].[POLICY_APPLICATION] ( prim_owner_third_id, app_status_cd )
INCLUDE ( contract_bu_cd, contract_main_id, contract_sub_id, contract_version_id);
CREATE INDEX [IDX_POLICY_APPLICATION_06]
ON [dbo].[POLICY_APPLICATION] ( prim_owner_third_id )
INCLUDE ( app_status_cd, application_id, contract_bu_cd,
contract_main_id, contract_sub_id, contract_version_id);
can I combine them to have:
CREATE INDEX idx_POLICY_APPLICATION__prim_owner_third_id__app_status_cd__INCLUDE
ON dbo.POLICY_APPLICATION (prim_owner_third_id, app_status_cd)
INCLUDE (application_id, contract_bu_cd, contract_main_id,
contract_sub_id, contract_version_id)
I am not sure how it would handle this situation since “app_status_cd” is in the includes in one index and is part of the index in the other. Since some situations only need prim_owner_third_id in the index, but may depend on having app_status_cd available, will this function similarly?
Hey there– yes, you can combine the two indexes in this way. I do recommend checking first to make sure both are being used and that you really need both of them.
The only downside of moving app_status_cd into the key is that it makes your key larger, which then makes the upper levels of the index larger. It’s worth thinking about the size of the tables and key in cases where you have a very large table– but since you already have one index with both of those columns in the key I’m guessing that’s not a problem in this case.
Well, application_id is actually the primary key, so it would be included anyways. Basically, I would just be dropping the second index. Based on what you are saying, I would think there is very little disadvantage. Thanks for your help.
The _index performance tuning DMV queries_ link redirects to a generic SQL Server Community wiki. The URL to the DMV queries seems to have changed to http://www.toadworld.com/platforms/sql-server/w/wiki/10061.index-related-dmv-queries – Great article!
It looks like you could redirect at least some of your SqlServerPedia links to archive.org. It shouldn’t take to long to do, but I don’t know if that is a desired option for you.
Unfortunately the Videos are flash videos which are no longer supported.
https://web.archive.org/web/20130507082717/http://sqlserverpedia.com/wiki/Index_Related_DMV_Queries
https://web.archive.org/web/20130507090210/http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use
https://web.archive.org/web/20130507084725/http://sqlserverpedia.com/wiki/Find_Missing_Indexes
Mark – yeah, it’s not high on my radar right now. Just only so many hours in the day.