Index Usage

Question

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
SELECTi.[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?