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.

0

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.

5
  • 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 – Dharma Apr 5 at 14:45
  • 1
    Yes. Create it as a new index and compare the sizes of the two indexes. – David Browne - Microsoft Apr 5 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 – Dharma Apr 5 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)) – David Browne - Microsoft Apr 6 at 0:13
  • Thanks. Can you give me an example – Dharma Apr 6 at 7:30

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.