0

I m planning to add a key column index in one of the existing non clustered index in a big table which have millions of rows rather than create a new non clustered index. Is that any way for me to know how much the extra space consumed by the index by adding a new key column in existing non clustered index and is that there is any data/index ratio best practices, as I know its better not to have more than 5 index per table.

Just to be clear, I need to explain to storage team extra space that consumed by index after add the new column , then only I can add the column to existing index.

1
  • 2
    I think David's answer probably sums it up pretty good. I'm just curious, why do you think that 5 is a magic number for too many indexes? For a given table, two might be too many and 10 might not be enough. Sure, as few as possible, but the right index, even if it's #6, can be worth many time it's storage and maintenance costs. Apr 21, 2021 at 10:19

3 Answers 3

2
+50

It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what effect it has there.

You can estimate though. If the added column is 8 bytes long (a datetime column for instance) and there are 100M rows, then you can expect it to add approximately 800,000,000 bytes to the index's leaf pages. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data using SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

This only accounts for the extra data added to the leaf pages in the index, but it should be accurate enough as an estimate as that will be by far the largest factor. There will be a little extra space taken by non-leaf pages too.

Also the above does not take compression into account, if that is enabled for your index. Compressed data can be much more difficult to model, so in that case you are back to testing by creating the index on realistic data as the only really accurate way to go.

Without knowing your table/index definitions it is not possible to give a more precise answer.

2

Is that any way for me to know how much the extra space consumed by the index by adding a new key column

Create a new index and check its size:

select schema_name(t.schema_id) schema_name,
       t.name table_name, 
       i.name index_name, 
       ps.used_page_count / 8. / 1024 size_mb,
       row_count,
       row_count / nullif(ps.used_page_count,0) rows_per_page
from sys.tables t
join sys.indexes i
  on t.object_id = i.object_id
join sys.dm_db_partition_stats ps
  on i.object_id = ps.object_id
 and i.index_id = ps.index_id
 order by table_name, index_name

is that there is any data/index ratio best practices, as I know its better not to have more than 5 index per table

Start with indexes for your candidate keys and foreign keys, then add additional indexes as necessary for query performance, monitoring both missing indexes and unused indexes.

6
  • Hi David, maybe my explanation not clear, I want to know extra space that will be consumed by adding a new column in existing index before add a new column to index . Is there any calculation we can do
    – Mario
    Apr 5, 2021 at 14:45
  • 1
    Yes. Create it as a new index and compare the sizes of the two indexes. Apr 5, 2021 at 14:46
  • yes we can compare after create a new index but is there any way i can know it by doing any calculation before add the column so that i can explain to storage team space that might require
    – Mario
    Apr 5, 2021 at 14:50
  • The new value will be stored on every index leaf level row, and every non-leaf row. So something like SizeOfColumn*(NumberOfRows + log(NumberOfRows)) Apr 6, 2021 at 0:13
  • Thanks. Can you give me an example
    – Mario
    Apr 6, 2021 at 7:30
0

If you can't create a new index or alter the index in a lower environment, then you can try taking a representative sample, say 100,000 and dumping them into a new table that you then create your index on this smaller sample. If your primary concern is space, then use the smallest representative sample that you feel comfortable with. Keep in mind: the smaller the sample, the poorer your final estimate will be.

Once you have the size of the index in your representative table created then you can ballpark the size for the real table by multiplying by the number of rows in the real table and divide my the number of rows in your representative sample table.

But again, creating the index on a full sized table in a lower environment would be the best solution. Keep in mind that the space required will also change as the index becomes fragmented over time when updates, inserts, and deletes occur.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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