Performance Problem on Delete insert update : SSIS :solved

I had a SSIS package which runs multiple times


SSSI has :



Step 1:Execute SQL task:Delete from ABC table with some condition .
Step 2:Execute SQL task:Delete from XYZ table with some condition .

Then Step 3: Data flow task which has text file has Source and having various conditions and  inserting to ABC and XYZ tables

Then Step4:
Stored procedure which  Insert into another 10 tables  selecting data from ABC and XYZ based on conditions .


Here the stored procedure was taking 1 hr to complete , and some times it used to timeout calling from C# .....



So i looked at each individual Delete, Select, Inserts happening on SSIS ....
and i found there are few indexes on tables XYZ and ABC and there were lot of scans , user updates happening on indexes  and  these deletes , inserts happening longer time coz it has to delete/ insert/ update in data pages and index pages .
So
1.i dropped indexes before Delete , insert ( before step 1, 2,3 ) and
2. created the indexes, update statistics( which are not created by indexes)  before step 4 ...

This time SSIS package took 2 mins to run and i monitored for 1 week it is executing in 2mins  ...
Below Script helped me fining usage of indexes :
SELECT
    ObjectName      = object_schema_name(idx.object_id) + '.' + object_name(idx.object_id)
    ,IndexName      = idx.name
    ,IndexType      = CASE
                        WHEN is_unique = 1 THEN 'UNIQUE '
                        ELSE '' END + idx.type_desc
    ,User_Seeks     = us.user_seeks
    ,User_Scans     = us.user_scans
    ,User_Lookups   = us.user_lookups
    ,User_Updates   = us.user_updates
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats us
    ON idx.object_id = us.object_id
    AND idx.index_id = us.index_id
    AND us.database_id = db_id()
WHERE object_schema_name(idx.object_id) != 'sys'
ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC



 Naresh ...11/26/13