2

What is the cost of having many columns as included columns in a nonclustred index in SQL Server?

3 Answers 3

7

The more columns you include to index the larger this index becomes. It affects all operations with this index (inserts, updates, selects). It takes more space in buffer pool and when you use index more data needs to be processed (including maintenance tasks like integrity checks and backups). Also when you update column included to this index SQL Server needs to update this index too (it means more reads, writes and even more locks).

7
  • Thanks! Say I have a view and the key column in my index is used in the where clause. I have ten other columns in my select. If I want to make my view fast should I include all other columns as include?
    – xhr489
    Sep 24, 2020 at 22:48
  • Or is I enough to have a couple of them as include?
    – xhr489
    Sep 24, 2020 at 22:49
  • 1
    @David It's a question of pros and cons. I already answered about the cons. Now, what do you get adding these columns to index? If your WHERE clause is already covered by index key then including more columns to the index may help you only to avoid expensive key lookups or clustered index scan in case when you need to select too many rows. But only if you include all required columns (if you add only 2 of 10 then you still need to get other 8). Looking at overall cost of having a lot of included columns (especially if their data types are large like long varchars) key lookups may be not so bad Sep 24, 2020 at 23:03
  • 1
    If your view is returning a LOT of rows, then having them in the include may give you a performance increase (while all the cons listed in the answer will still be true, it may worth it). If you only get some rows, then the lookup operation may not be so bad and then you can go without the include column. Test both option with "set statistics io, time on" so you can compare how many pages had to be read and how long it took and you should have all you need to take that decision. P.s. Very well detailed answer @Nikita ;) Sep 25, 2020 at 15:03
  • 1
    @David it's not so trivial task to measure the "overall cost" of particular index or its difference between two indexes (with and without included columns). Please see Tibor's and KASQLDBA's answers and Dominique's comment. A good point to start is sp_BlitzIndex which can show you index usage and operational stats for existing indexes. Sep 25, 2020 at 15:40
8

Nikita has almost everything covered in her answer.

However the answer is also dependent on how many columns and what type of columns you may decide to add.

If you are adding columns with data types occupying less space may be the overall cost may not look that harmful. Please note I am just saying this because we do not know how many columns you are talking about.

But if there are columns like nvarchar(max) which stores an entire story about something, you can imagine adding those as included column can make things go worse quickly and probably should be avoided.

Now how to perform analysis for such things , comes a handy great tool sp_blitzindex which you can download from brentozar.com.

He has all the great post discussing how to use as a starter and analyze the index usage which will give you the space in addition to overall index utilization which will help you narrow down the search and probably a better answer on what is the cost.

1
  • I appreciate this added context (especially the bit vs varchar(max) part), thank you!
    – Jeff.Clark
    Feb 16 at 16:53
2

"How can I look at the overall cost?"

Above was in the comment and I think the question deserve some explicit attention. But I don't think David will like the "answer"...

What do you mean by cost? Something we can quantify, I take it. Which leads to the question of what we want to measure and what unit? Response time? I/O? CPU? Memory usage? Log records produced? Size of backups? Money?

Imagine we can put a simple answer to above, we then have the next challenge:

Do we have a repeatable load? Without something we can run over and over, how can we measure the cost with vs without these additional columns? That load should reflect your load, or it won't be relevant for your installation.

Then we have the next question. Should that load include everything? Every component of your application that accesses your data? The day-to-day work? The batch operations you run nightly? Reindex? DBCC CHECKDB? Etc.

Say we do indeed have answers to all above, then it is time to pick whatever tool we want to use. This can include performance monitor, Extended Event tracing, sp_blitzindex, various 3rd party applications and scripts. Which to choose is partly dependent on the answers to above questions.

Quite a challenge!

1
  • Thank you, I learned alot from your answer.
    – xhr489
    Sep 25, 2020 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.