Index Usage

Question

Post reply

Index Usage

Anam Verma

SSCertifiable

Points: 5365
More actions
January 20, 2010 at 7:57 pm

#140723

i found a query on internet to find the index disk space used for a particular table. the query is

SELECT i.[object_id] --, i.[name] , i.index_id , p.partition_number , p.rows as [#Records] , a.total_pages * 8 as [Reserved(kb)] , a.used_pages * 8 as [Used(kb)] FROM sys.indexes as i inner join sys.partitions as p ON i.object_id = p.object_id and i.index_id = p.index_id inner join sys.allocation_units as a ON p.partition_id = a.container_id WHERE i.[object_id] = object_id('dbo.test')
ORDER BY p.partition_number

The query above generated the following results

object_id index_id partition_number #Records Reserved(kb) Used(kb) ----------- ----------- ---------------- -------------------- -------------------- -------------------- 286624064 1 1 5696529 1960872 1960592 286624064 1 1 5696529 0 0 286624064 1 1 5696529 0 0 286624064 2 1 5696529 104488 104416 286624064 3 1 5696529 107752 107672 286624064 4 1 5696529 104488 104416 286624064 5 1 5696529 115112 115088 286624064 6 1 5696529 300200 300152 286624064 7 1 5696529 411048 409424 286624064 8 1 5696529 378600 378504 286624064 9 1 5696529 132136 132104 286624064 11 1 5696529 108944 108856 (12 row(s) affected)

I know that having too many indexes on a table is not a good approach but i'll leave this question of having too many indexes at the moment as i don't know why the previous DBA had created these.

I am doing data migration and inserting 5,696,529 records to this table and the good thing is that i can disable/delete all these indexes except clustered index which is on primary key before data insertion.

My questions:

1. Which is better approach: disable and rebuild or drop and create?

2. How big the impact will be on the performance if i don't drop or disable these indexes considering the number of records i am going to insert? Will it reduce the execution time many folds?