Redunant indexes SQL ServerWhat is the effect of replacing indexes with filtered (non-null value)...
How do you make a gun that shoots melee weapons and/or swords?
Should I use HTTPS on a domain that will only be used for redirection?
Too soon for a plot twist?
Why aren't there more Gauls like Obelix?
Can I challenge the interviewer to give me a proper technical feedback?
Unfamiliar notation in Diabelli's "Duet in D" for piano
How do we objectively assess if a dialogue sounds unnatural or cringy?
In the world of The Matrix, what is "popping"?
How spaceships determine each other's mass in space?
Can a space-faring robot still function over a billion years?
Does the US political system, in principle, allow for a no-party system?
Deal the cards to the players
Should we avoid writing fiction about historical events without extensive research?
newcommand: Using one parameter as the default for the other
Help! My Character is too much for her story!
Generating a list with duplicate entries
Double integral involving the normal CDF
Is "cogitate" an appropriate word for this?
Did Amazon pay $0 in taxes last year?
Was it really inappropriate to write a pull request for the company I interviewed with?
SQL Server: Covering indexes including all columns?
How does a sound wave propagate?
Does restoring a database break external synonyms targeting objects in that database?
PTIJ: Sport in the Torah
Redunant indexes SQL Server
What is the effect of replacing indexes with filtered (non-null value) indexes?Logical reads amount difference with almost identical indexesPurpose of Nonclustered indexes in SQL Server 2012Clustered columnstore indexes and foreign keysSQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexeshow to find overlapping or duplicated indexes? when is it O.K. to keep them?Indexes and Statistics on Temp tablesIs there any reason for an index to contain the primary key?How many B-tree indexes do I need to support lookups by any subset of n columns?Columnstore index on multi-tenant database
I am currently going through all indexes in our data warehouse due to performance tuning. Several of these indexes are not used in the system stats, and I am considering to remove them to reduce overhead during batch.
Some of these does not make sense, due to PK columns being used in the middle of other indexes etc. However, I was wondering how SQL Server handles multi column indexes vs. shorter multi column indexes.
Example:
NONCLUSTERED INDEX Index1 (A, B, C)
NONCLUSTERED INDEX Index2 (A, B)
Would SQL Server be able to use Index1 for the same rows as Index2 making Index2 not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.
Can Index2 be removed and still experience same performance?
As far as I have understood, indexes can also help with sorts in execution plan, would indexes that are to far be used for these aswell?
sql-server t-sql index index-tuning
asked 3 hours ago
CreztianCreztian
183
add a comment |
I am currently going through all indexes in our data warehouse due to performance tuning. Several of these indexes are not used in the system stats, and I am considering to remove them to reduce overhead during batch.
Some of these does not make sense, due to PK columns being used in the middle of other indexes etc. However, I was wondering how SQL Server handles multi column indexes vs. shorter multi column indexes.
Example:
NONCLUSTERED INDEX Index1 (A, B, C)
NONCLUSTERED INDEX Index2 (A, B)
Would SQL Server be able to use Index1 for the same rows as Index2 making Index2 not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.
Can Index2 be removed and still experience same performance?
As far as I have understood, indexes can also help with sorts in execution plan, would indexes that are to far be used for these aswell?
sql-server t-sql index index-tuning
asked 3 hours ago
CreztianCreztian
183
Do the indexes have the same included columns?
– Randi Vertongen
3 hours ago
I wonder in either way, If yes the necessary data would be included at the bottom. If not it would still have the row ID to find t he necessary data. I guess most of my cases does not include the same columns, as many indexes are mostly used to provide data in the 'right order' and not the data itself (big tables with a lot of different use)
– Creztian
3 hours ago
add a comment |
I am currently going through all indexes in our data warehouse due to performance tuning. Several of these indexes are not used in the system stats, and I am considering to remove them to reduce overhead during batch.
Some of these does not make sense, due to PK columns being used in the middle of other indexes etc. However, I was wondering how SQL Server handles multi column indexes vs. shorter multi column indexes.
Example:
NONCLUSTERED INDEX Index1 (A, B, C)
NONCLUSTERED INDEX Index2 (A, B)
Would SQL Server be able to use Index1 for the same rows as Index2 making Index2 not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.
Can Index2 be removed and still experience same performance?
As far as I have understood, indexes can also help with sorts in execution plan, would indexes that are to far be used for these aswell?
sql-server t-sql index index-tuning
asked 3 hours ago
CreztianCreztian
183
I am currently going through all indexes in our data warehouse due to performance tuning. Several of these indexes are not used in the system stats, and I am considering to remove them to reduce overhead during batch.
Some of these does not make sense, due to PK columns being used in the middle of other indexes etc. However, I was wondering how SQL Server handles multi column indexes vs. shorter multi column indexes.
Example:
NONCLUSTERED INDEX Index1 (A, B, C)
NONCLUSTERED INDEX Index2 (A, B)
Would SQL Server be able to use Index1 for the same rows as Index2 making Index2 not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.
Can Index2 be removed and still experience same performance?
As far as I have understood, indexes can also help with sorts in execution plan, would indexes that are to far be used for these aswell?
sql-server t-sql index index-tuning
sql-server t-sql index index-tuning
asked 3 hours ago
CreztianCreztian
183
asked 3 hours ago
CreztianCreztian
183
asked 3 hours ago
CreztianCreztian
183
asked 3 hours ago
CreztianCreztian
183
asked 3 hours ago
CreztianCreztian
183
183
Do the indexes have the same included columns?
– Randi Vertongen
3 hours ago
I wonder in either way, If yes the necessary data would be included at the bottom. If not it would still have the row ID to find t he necessary data. I guess most of my cases does not include the same columns, as many indexes are mostly used to provide data in the 'right order' and not the data itself (big tables with a lot of different use)
– Creztian
3 hours ago
add a comment |
Do the indexes have the same included columns?
– Randi Vertongen
3 hours ago
I wonder in either way, If yes the necessary data would be included at the bottom. If not it would still have the row ID to find t he necessary data. I guess most of my cases does not include the same columns, as many indexes are mostly used to provide data in the 'right order' and not the data itself (big tables with a lot of different use)
– Creztian
3 hours ago
Do the indexes have the same included columns?
– Randi Vertongen
3 hours ago
Do the indexes have the same included columns?
– Randi Vertongen
3 hours ago
I wonder in either way, If yes the necessary data would be included at the bottom. If not it would still have the row ID to find t he necessary data. I guess most of my cases does not include the same columns, as many indexes are mostly used to provide data in the 'right order' and not the data itself (big tables with a lot of different use)
– Creztian
3 hours ago
I wonder in either way, If yes the necessary data would be included at the bottom. If not it would still have the row ID to find t he necessary data. I guess most of my cases does not include the same columns, as many indexes are mostly used to provide data in the 'right order' and not the data itself (big tables with a lot of different use)
– Creztian
3 hours ago
add a comment |
1 Answer
1
active
oldest
votes
In general you should be able to remove Index2 without any major issues. Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two).
If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don't think you would notice any perceptible differences in performance.
answered 3 hours ago
Bert WagnerBert Wagner
2113
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you! What about this case? Would index 2 always be preferred over index1 when only checking colum A due to included columns providing more information at leaf level? (Index2 are tailor made and therefore used, while index1 are never used while it should address general queries for the table) NC Index1 (A, B) NC Index2 (A,C) INCLUDE( X, Y, Z)
– Creztian
2 hours ago
2
Not sure I fully understand, but there's a lot of hypotheticals. Best thing to do would be to code it, try it, and look at the execution plan to see what SQL Server ends up using.
– Bert Wagner
2 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231643%2fredunant-indexes-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In general you should be able to remove Index2 without any major issues. Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two).
If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don't think you would notice any perceptible differences in performance.
answered 3 hours ago
Bert WagnerBert Wagner
2113
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you! What about this case? Would index 2 always be preferred over index1 when only checking colum A due to included columns providing more information at leaf level? (Index2 are tailor made and therefore used, while index1 are never used while it should address general queries for the table) NC Index1 (A, B) NC Index2 (A,C) INCLUDE( X, Y, Z)
– Creztian
2 hours ago
2
Not sure I fully understand, but there's a lot of hypotheticals. Best thing to do would be to code it, try it, and look at the execution plan to see what SQL Server ends up using.
– Bert Wagner
2 hours ago
add a comment |
In general you should be able to remove Index2 without any major issues. Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two).
If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don't think you would notice any perceptible differences in performance.
answered 3 hours ago
Bert WagnerBert Wagner
2113
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you! What about this case? Would index 2 always be preferred over index1 when only checking colum A due to included columns providing more information at leaf level? (Index2 are tailor made and therefore used, while index1 are never used while it should address general queries for the table) NC Index1 (A, B) NC Index2 (A,C) INCLUDE( X, Y, Z)
– Creztian
2 hours ago
2
Not sure I fully understand, but there's a lot of hypotheticals. Best thing to do would be to code it, try it, and look at the execution plan to see what SQL Server ends up using.
– Bert Wagner
2 hours ago
add a comment |
In general you should be able to remove Index2 without any major issues. Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two).
If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don't think you would notice any perceptible differences in performance.
answered 3 hours ago
Bert WagnerBert Wagner
2113
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In general you should be able to remove Index2 without any major issues. Index1 can cover any queries that Index2 would currently be handling (assuming there aren't any included column differences between the two).
If column C in Index1 is large, you might end up reading slightly more data into memory with Index1 for queries that would have used Index2 since that third column is taking up more space on each page, but in most scenarios I don't think you would notice any perceptible differences in performance.
answered 3 hours ago
Bert WagnerBert Wagner
2113
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 hours ago
Bert WagnerBert Wagner
2113
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 hours ago
Bert WagnerBert Wagner
2113
answered 3 hours ago
Bert WagnerBert Wagner
2113
2113
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Bert Wagner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you! What about this case? Would index 2 always be preferred over index1 when only checking colum A due to included columns providing more information at leaf level? (Index2 are tailor made and therefore used, while index1 are never used while it should address general queries for the table) NC Index1 (A, B) NC Index2 (A,C) INCLUDE( X, Y, Z)
– Creztian
2 hours ago
2
Not sure I fully understand, but there's a lot of hypotheticals. Best thing to do would be to code it, try it, and look at the execution plan to see what SQL Server ends up using.
– Bert Wagner
2 hours ago
add a comment |
Thank you! What about this case? Would index 2 always be preferred over index1 when only checking colum A due to included columns providing more information at leaf level? (Index2 are tailor made and therefore used, while index1 are never used while it should address general queries for the table) NC Index1 (A, B) NC Index2 (A,C) INCLUDE( X, Y, Z)
– Creztian
2 hours ago
2
Not sure I fully understand, but there's a lot of hypotheticals. Best thing to do would be to code it, try it, and look at the execution plan to see what SQL Server ends up using.
– Bert Wagner
2 hours ago
Thank you! What about this case? Would index 2 always be preferred over index1 when only checking colum A due to included columns providing more information at leaf level? (Index2 are tailor made and therefore used, while index1 are never used while it should address general queries for the table) NC Index1 (A, B) NC Index2 (A,C) INCLUDE( X, Y, Z)
– Creztian
2 hours ago
Thank you! What about this case? Would index 2 always be preferred over index1 when only checking colum A due to included columns providing more information at leaf level? (Index2 are tailor made and therefore used, while index1 are never used while it should address general queries for the table) NC Index1 (A, B) NC Index2 (A,C) INCLUDE( X, Y, Z)
– Creztian
2 hours ago
2
2
Not sure I fully understand, but there's a lot of hypotheticals. Best thing to do would be to code it, try it, and look at the execution plan to see what SQL Server ends up using.
– Bert Wagner
2 hours ago
Not sure I fully understand, but there's a lot of hypotheticals. Best thing to do would be to code it, try it, and look at the execution plan to see what SQL Server ends up using.
– Bert Wagner
2 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231643%2fredunant-indexes-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do the indexes have the same included columns?
– Randi Vertongen
3 hours ago
I wonder in either way, If yes the necessary data would be included at the bottom. If not it would still have the row ID to find t he necessary data. I guess most of my cases does not include the same columns, as many indexes are mostly used to provide data in the 'right order' and not the data itself (big tables with a lot of different use)
– Creztian
3 hours ago