Composite indexes; performance

Question

Composite indexes; performance

AER

SSCrazy

Points: 2763
More actions
November 8, 2018 at 8:29 am

#368364

Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

AER - Thursday, November 8, 2018 8:29 AM
Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.

Which statement uses which index?
Too many indexes on a table will impact performance.
You may be able to "merge" your indexes into one to support both queries.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

AER - Thursday, November 8, 2018 8:29 AM
Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.

You only need the index with columns 1,2,3 the optimiser is able work out it can to use the leading part of an index.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

It depends. Usually the second index would be sufficient for both queries. However, if it's a requirement that both queries run in the absolute minimum time possible, you may want to keep both in order to minimise page reads for each. Each index has a performance penalty in terms of maintenance - it will have to be updated every time there's an insert, update or delete. There may be reasons the indexes don't get used at all - sargability, or if the query returns so many rows that the query optimizer judges that a table scan would be more efficient. Consider adding the columns in your select list as included columns in the index(es) - then the whole query may be able to be satisfied without requiring a lookup to the clustered index.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jonathan AC Roberts - Thursday, November 8, 2018 8:36 AM
AER - Thursday, November 8, 2018 8:29 AM
Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.
You only need the index with columns 1,2,3 the optimiser is able work out it can to use the leading part of an index.

Yes but the order may be critical, and may also be different for the two queries.
In the last hour I've reversed the order of the two key columns in an index, resulting in a 60-fold performance lift (4 minutes to 4 seconds). The query now reads 20 thousand rows instead of 60 million.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 5

ChrisM@Work - Thursday, November 8, 2018 8:42 AM
Jonathan AC Roberts - Thursday, November 8, 2018 8:36 AM
AER - Thursday, November 8, 2018 8:29 AM
Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.
You only need the index with columns 1,2,3 the optimiser is able work out it can to use the leading part of an index.
Yes but the order may be critical, and may also be different for the two queries.
In the last hour I've reversed the order of the two key columns in an index, resulting in a 60-fold performance lift (4 minutes to 4 seconds). The query now reads 20 thousand rows instead of 60 million.

I was assuming that the order of the columns on the indexes was the same and column 3 was the last column in the index.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jonathan AC Roberts - Thursday, November 8, 2018 8:47 AM
ChrisM@Work - Thursday, November 8, 2018 8:42 AM
Jonathan AC Roberts - Thursday, November 8, 2018 8:36 AM
AER - Thursday, November 8, 2018 8:29 AM
Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.
You only need the index with columns 1,2,3 the optimiser is able work out it can to use the leading part of an index.
Yes but the order may be critical, and may also be different for the two queries.
In the last hour I've reversed the order of the two key columns in an index, resulting in a 60-fold performance lift (4 minutes to 4 seconds). The query now reads 20 thousand rows instead of 60 million.
I was assuming that the order of the columns in the index was the same and column 3 was the last column in the index.

That may well be the case, but at this point in time we don't even know if these indexes are being used, let alone by which query 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 7

ChrisM@Work - Thursday, November 8, 2018 8:50 AM
Jonathan AC Roberts - Thursday, November 8, 2018 8:47 AM
ChrisM@Work - Thursday, November 8, 2018 8:42 AM
Jonathan AC Roberts - Thursday, November 8, 2018 8:36 AM
AER - Thursday, November 8, 2018 8:29 AM
Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.
You only need the index with columns 1,2,3 the optimiser is able work out it can to use the leading part of an index.
Yes but the order may be critical, and may also be different for the two queries.
In the last hour I've reversed the order of the two key columns in an index, resulting in a 60-fold performance lift (4 minutes to 4 seconds). The query now reads 20 thousand rows instead of 60 million.
I was assuming that the order of the columns in the index was the same and column 3 was the last column in the index.
That may well be the case, but at this point in time we don't even know if these indexes are being used, let alone by which query 😉

I was also assuming that the indexes were being used. But that would depend on the query and the values that were specified in the where clause.
Anyway, it's standard practice not to have indexes that are identical in every way with the only difference being an additional column on the end of one of them.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 8

AER - Thursday, November 8, 2018 8:29 AM
Hi everybody,
I have couple quick questions for the DBAs.
We have 2 heavy SELECT statements in 2 stored procedures, that are being called frequently from the application:
The 1st statement has a WHERE clause with columns 1 and 2.
The 2nd statements has a WHERE clause with the same columns plus 3 (i.e. 1,2 and 3).
We also have 2 composite indexes in the table where these columns reside:
The 1st index includes column 1 and 2
Tthe 2nd index includes columns 1,2 and 3
Do we need a 1st index or just the 2nd one will be good enough?
And the more important question: is having both indexes at the same time creates any performance issues?
Thank you.

As Chris has stated, the first step would be to prove that the indexes are actually being used effectively by the two queries. The word "effectively" also includes such things as is a key lookup occurring, is an initial seek followed by an effective range scan occurring, or what?

After that, "It Depends" on things like, which is more important.... performance of inserts and updates or the individual selects? And that's just scratching the surface because there are a whole lot of things that can affect all 3 actions and also affect how busy the log file is during the inserts, updates due to page splits and related index maintenance. Then there's the "problem" of the 3 column index having fewer rows per page than the 2 column index and that will affect memory usage and the performance of any read-aheads for something that's not in memory.

The bottom line is that it's not the simple question that a whole lot of people think it is., especially if the indexes have a lot of rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AER SSCrazy Points: 2763 More actions · Answer 9

Thanks for all your replies.
I know that is not the simple question; that's why I was asking help from DBAs.

I am myself is a production DBA and know what happens in theory but wanted some good practical advice on having 1 or 2 indexes in my case.
So when I mentioned that both queries are heavily used I really meant it. And when I mentioned the columns they are all used in the order that I placed them in my question.

I have scripts to identify the needed indexes, but not sure if there are good scripts out there, that would tell you that any particular index degrades the performance.

That's why I was asking if I need both indexes.

I kind of agree with John's answer.
Any additional thoughts? Maybe anybody can point to the good script that will show the indexes that harm the database performance?

Thank you, again

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Have a look at the index usage stats DMV. Any index that is being written to often and read from rarely is a potential performance harmer. Even then, it's going to depend on, among other things, how much you value read performance over write performance.

John

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 11

AER - Thursday, November 8, 2018 10:05 AM
Thanks for all your replies.
I know that is not the simple question; that's why I was asking help from DBAs.
I am myself is a production DBA and know what happens in theory but wanted some good practical advice on having 1 or 2 indexes in my case.
So when I mentioned that both queries are heavily used I really meant it. And when I mentioned the columns they are all used in the order that I placed them in my question.
I have scripts to identify the needed indexes, but not sure if there are good scripts out there, that would tell you that any particular index degrades the performance.
That's why I was asking if I need both indexes.
I kind of agree with John's answer.
Any additional thoughts? Maybe anybody can point to the good script that will show the indexes that harm the database performance?
Thank you, again

Yes... having scripts to identify the needed indexes sometimes doesn't tell the whole story. What does the ACTUAL EXCECUTION PLAN state for these two queries insofar as whether or not the indexes are being used effectively (that key lookup thing and seek/scan thing again) and what is the actual performance measurements for reads, CPU, writes, and, most importantly, duration?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 12

Jeff Moden - Thursday, November 8, 2018 10:12 AM
AER - Thursday, November 8, 2018 10:05 AM
Thanks for all your replies.
I know that is not the simple question; that's why I was asking help from DBAs.
I am myself is a production DBA and know what happens in theory but wanted some good practical advice on having 1 or 2 indexes in my case.
So when I mentioned that both queries are heavily used I really meant it. And when I mentioned the columns they are all used in the order that I placed them in my question.
I have scripts to identify the needed indexes, but not sure if there are good scripts out there, that would tell you that any particular index degrades the performance.
That's why I was asking if I need both indexes.
I kind of agree with John's answer.
Any additional thoughts? Maybe anybody can point to the good script that will show the indexes that harm the database performance?
Thank you, again
Yes... having scripts to identify the needed indexes sometimes don't tell the whole story. What does the ACTUAL EXCECUTION PLAN state for these two queries insofar as whether or not the indexes are being used effectively (that key lookup thing and seek/scan thing again) and what is the actual performance measurements for reads, CPU, writes, and, most importantly, duration?

You could run some test calls with the index with columns 1,2 existing and after dropping it.
Use this at the top of your code:SET STATISTICS IO, TIME ON
Then look at the results. It may well be that there is not enough of a performance difference to justify two indexes.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 13

Jonathan AC Roberts - Thursday, November 8, 2018 10:15 AM
Jeff Moden - Thursday, November 8, 2018 10:12 AM
AER - Thursday, November 8, 2018 10:05 AM
Thanks for all your replies.
I know that is not the simple question; that's why I was asking help from DBAs.
I am myself is a production DBA and know what happens in theory but wanted some good practical advice on having 1 or 2 indexes in my case.
So when I mentioned that both queries are heavily used I really meant it. And when I mentioned the columns they are all used in the order that I placed them in my question.
I have scripts to identify the needed indexes, but not sure if there are good scripts out there, that would tell you that any particular index degrades the performance.
That's why I was asking if I need both indexes.
I kind of agree with John's answer.
Any additional thoughts? Maybe anybody can point to the good script that will show the indexes that harm the database performance?
Thank you, again
Yes... having scripts to identify the needed indexes sometimes don't tell the whole story. What does the ACTUAL EXCECUTION PLAN state for these two queries insofar as whether or not the indexes are being used effectively (that key lookup thing and seek/scan thing again) and what is the actual performance measurements for reads, CPU, writes, and, most importantly, duration?
You could run some test calls with the index with columns 1,2 existing and after dropping it.
Use this at the top of your code:SET STATISTICS IO, TIME ON
Then look at the results. It may well be that there is not enough of a performance difference to justify two indexes.

Just don't use SET STATISTICS if any scalar or mTVF functions are involved. Either will cause SET STATISTICS to report absolute horror. Please see the following article for demonstrable proof of that.
http://www.sqlservercentral.com/articles/T-SQL/91724/

I'm also not sure how any XML functionality will affect SET STATISTICS because I try to avoid XML like the plague.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 14

Jeff Moden - Thursday, November 8, 2018 10:25 AM
Jonathan AC Roberts - Thursday, November 8, 2018 10:15 AM
Jeff Moden - Thursday, November 8, 2018 10:12 AM
AER - Thursday, November 8, 2018 10:05 AM
Thanks for all your replies.
I know that is not the simple question; that's why I was asking help from DBAs.
I am myself is a production DBA and know what happens in theory but wanted some good practical advice on having 1 or 2 indexes in my case.
So when I mentioned that both queries are heavily used I really meant it. And when I mentioned the columns they are all used in the order that I placed them in my question.
I have scripts to identify the needed indexes, but not sure if there are good scripts out there, that would tell you that any particular index degrades the performance.
That's why I was asking if I need both indexes.
I kind of agree with John's answer.
Any additional thoughts? Maybe anybody can point to the good script that will show the indexes that harm the database performance?
Thank you, again
Yes... having scripts to identify the needed indexes sometimes don't tell the whole story. What does the ACTUAL EXCECUTION PLAN state for these two queries insofar as whether or not the indexes are being used effectively (that key lookup thing and seek/scan thing again) and what is the actual performance measurements for reads, CPU, writes, and, most importantly, duration?
You could run some test calls with the index with columns 1,2 existing and after dropping it.
Use this at the top of your code:SET STATISTICS IO, TIME ON
Then look at the results. It may well be that there is not enough of a performance difference to justify two indexes.
Just don't use SET STATISTICS if any scalar or mTVF functions are involved. Either will cause SET STATISTICS to report absolute horror. Please see the following article for demonstrable proof of that.
http://www.sqlservercentral.com/articles/T-SQL/91724/
I'm also not sure how any XML functionality will affect SET STATISTICS because I try to avoid XML like the plague.

Even if you have a scalar function or mTVF and you are comparing tests both with and without the index you should still see if there is a difference as they both would be slowed by the same amount by the set statistics.