Redundant indexes in SQL Server

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








2















I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing 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 (with overlapping column).



Examples:



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 and Index2 is 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 without impacting performance?

As far as I have understood, indexes can also help with sorts in execution plan. Can Index1 be used for these as well?










share|improve this question
























  • Do the indexes have the same included columns?

    – Randi Vertongen
    Mar 8 at 11:58











  • 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
    Mar 8 at 12:01

















2















I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing 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 (with overlapping column).



Examples:



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 and Index2 is 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 without impacting performance?

As far as I have understood, indexes can also help with sorts in execution plan. Can Index1 be used for these as well?










share|improve this question
























  • Do the indexes have the same included columns?

    – Randi Vertongen
    Mar 8 at 11:58











  • 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
    Mar 8 at 12:01













2












2








2








I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing 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 (with overlapping column).



Examples:



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 and Index2 is 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 without impacting performance?

As far as I have understood, indexes can also help with sorts in execution plan. Can Index1 be used for these as well?










share|improve this question
















I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing 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 (with overlapping column).



Examples:



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 and Index2 is 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 without impacting performance?

As far as I have understood, indexes can also help with sorts in execution plan. Can Index1 be used for these as well?







sql-server t-sql index index-tuning






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 9 at 13:49









Tony Hinkle

3,0751625




3,0751625










asked Mar 8 at 11:40









CreztianCreztian

283




283












  • Do the indexes have the same included columns?

    – Randi Vertongen
    Mar 8 at 11:58











  • 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
    Mar 8 at 12:01

















  • Do the indexes have the same included columns?

    – Randi Vertongen
    Mar 8 at 11:58











  • 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
    Mar 8 at 12:01
















Do the indexes have the same included columns?

– Randi Vertongen
Mar 8 at 11:58





Do the indexes have the same included columns?

– Randi Vertongen
Mar 8 at 11:58













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
Mar 8 at 12:01





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
Mar 8 at 12:01










1 Answer
1






active

oldest

votes


















9














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.






share|improve this answer























  • 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
    Mar 8 at 12:59







  • 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
    Mar 8 at 13:09











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
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231643%2fredundant-indexes-in-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









9














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.






share|improve this answer























  • 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
    Mar 8 at 12:59







  • 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
    Mar 8 at 13:09















9














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.






share|improve this answer























  • 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
    Mar 8 at 12:59







  • 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
    Mar 8 at 13:09













9












9








9







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










answered Mar 8 at 12:02









Bert WagnerBert Wagner

2593




2593












  • 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
    Mar 8 at 12:59







  • 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
    Mar 8 at 13:09

















  • 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
    Mar 8 at 12:59







  • 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
    Mar 8 at 13:09
















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
Mar 8 at 12:59






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
Mar 8 at 12:59





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
Mar 8 at 13:09





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
Mar 8 at 13:09

















draft saved

draft discarded
















































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.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231643%2fredundant-indexes-in-sql-server%23new-answer', 'question_page');

);

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






Popular posts from this blog

How to check contact read email or not when send email to Individual?

Displaying single band from multi-band raster using QGIS

How many registers does an x86_64 CPU actually have?