SQL Server Performance

Do Indexes help data loading performance?

Discussion in 'Performance Tuning for DBAs' started by csdunaway, Sep 26, 2005.

  1. csdunaway New Member

    I have an application that loads thousands of records an hour into a SQL Server database. The server is running very slow. The hard drives are being maxxed out at 100% Disk Time.

    Here is my question: will poor indexes cause hard disk thrashing for a INSERT stament?
  2. joechang New Member

    indexes do add overhead to write operations.
    are you doing inserts, bulk inserts or bcp?

    figure each index adds ~25% overhead to an insert, but probably more to bulk inserts, especially if you are otherwise meeting the condition for a bulk logged op
  3. csdunaway New Member

    The application is using INSERT statements only.
  4. joechang New Member

    do you have separate physical disk drives for data & logs?
    how many inserts /sec are you doing?
    are you inserting one row at a time?
  5. csdunaway New Member

    The drive system is setup as follows:

    RAID 1 Drive C & Drive E
    RAID 5 Drive D

    So, Drives C and E share the smae spindles.

    Database files are on D, Transaction Log files are on E.
    I do not have a number yet for inserts/sec.
    Data IS insterted one row at a time.
  6. joechang New Member

    the % disk time is a meaningless counter for DBs,
    log the write/sec, avg disk write queue len, and avg sec/write for each of RAID arrays separately,
    it is important to do this, and not use the total disk counters
  7. mmarovic Active Member

    On top of what Joe said, bad choice for clustered index and inappropriate fill-factor can significantly slow down insert performance.
  8. derrickleggett New Member

    I'm guessing your disk queue lengths are pretty interesting. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Also, look at your memory usage. What do the cache hit counters look like. When you look at disk queue lengths, tell us what each disk is at (not the total) and what is on that disk (tempdb, data files, log files, OS).<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  9. FrankKalis Moderator

    What system is this?
    Do you load data while users query the table? Or is it possible to load data first and the build indexes after the load is done.
    Any specific reason why you use a simple INSERT?

    Frank Kalis
    Microsoft SQL Server MVP
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  10. csdunaway New Member

    There is a third-party application that laods the data. It is loaded in batches every few minutes throughout the day.
  11. FrankKalis Moderator

    So, no chance to change the app and its behaviour? And the table needs to be available for queries all the time, too, I guess?

    Frank Kalis
    Microsoft SQL Server MVP
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. csdunaway New Member

    Yes, that is my problem. I just need to tune this database for both queries and high volume inserts. many tables already have over 50 million records in them.

    Here are some performance counters I see from the local drives:

    RAID 0: (Drives C and E) RAID 5 (Drive D)
    %Disk Time 0.000 6582
    Avg Disk Queue Length 0.000 45.765
    Avg. Disk Read Queue Length 0.000 45.765
    Current Disk Queue Length 0.000 51

    %Processor Time 23.851

    The OS, Swap files are on drive C
    The Transaction Log files are on E
    The Database files are on D
  13. FrankKalis Moderator

    Sorry, that's an area now where the hardware freaks come into play. I can only sit back and follow the further discussion.
    Only one comment: I assume your data reside on D:. RAID 5 isn't the "best" level to achieve good write performance. Might also make up a point in a solution.

    Frank Kalis
    Microsoft SQL Server MVP
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  14. joechang New Member

    i would guess from the partial list of performance counters that your issues is in reading from data, ie, you need more disk drives for the data.
    need to know the other disk counters to make a better assessment
  15. derrickleggett New Member

    What was the Avg. Disk Write Queue Length during that time? Did you capture it? How many disks are in that RAID five configuration? Unless you have like 18000000 disks in it, that queue length is horrible. Can you post the table CREATE statement with indexes included? Have you checked for duplicate indexes?


    When life gives you a lemon, fire the DBA.
  16. yodarules New Member

    As already suggested by many, you might want to verify if your application actually uses all the indexes on this table. I have a similar problem and infact our application writes millions of rows in a hour. Its a pretty big table in column numbers and size as you can imagine. So irrespective of how much fill factor you try you can only reduce the problem to an extent. We verified the use of the indexes and many were not actually used or only used very rarely. The performance benefit that we get in elimination these indexes is greater than any help the indexes were actually providing in terms of querying.

    So try to eliminate these indexes that are not being used and ofcourse provide a fill factor for the ones being used. Ofcourse fillfactor would only make sense if you are actually reindexing. Also make sure that you have choosen the clustered index on the right column.

Share This Page