SQL Server Performance

Dropping indexes before bulk insert

Discussion in 'SQL Server 2005 General DBA Questions' started by najeed_dba, Aug 16, 2007.

  1. najeed_dba New Member

    Hi all,
    Need some advice.
    I have a huge table 170 Gb of size. On that table we have 10 indexes of around 12 GB in size.
    The application is designed such that it bulk inserts the file in to sql server. But , often we are getting time outs and some latching isssues ( as can be seen in activity monitor).
    So, will this be a good idea of dropping those indexes and then recreating them again for better performance.
    So whats ur adivce on this.
    Thanks.
    SQL server 2005 std Edtion SP1 ; 4Gb RAM, Win 2k3 OS Standard Editon.
    //N
    najeed_dba, Aug 16, 2007
    #2

  2. satya Moderator

    If you have such bulk inserts then why don't you take advantage of using BULK LOGGED recovery with necessary backup schedules.
    If you drop and recreate then it is nothing but wasting resources, it may beuseful if the database server is not accessed 24/7 hours.
    satya, Aug 16, 2007
    #3

  3. najeed_dba New Member

    Database is in SIMPLE recovery mode and the database is not accessed 24x7, its not OLTP.
    So, i need your suggestion now satya,
    Like i am thinking to drop and rebuild the indexes only for that huge tabe, not for the entire database. I was thinking like this because , however like every 2 weeks we build indexes. So, this could solve the problem of that file bulk inserting into that huge mega table.
    Thanks for quick response .
    //N.
    najeed_dba, Aug 16, 2007
    #4

  4. satya Moderator

    No worries as per your explanation, but also ensure to perform intermittent CHECKPOINT even though it is in SIMPLE recovery model. Ensure to maintain upto date backups before &after process.
    satya, Aug 16, 2007
    #5

Share This Page