Do Indexes help data loading performance?

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?

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

The application is using INSERT statements only.

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?

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.

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

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

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.

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

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

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

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?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

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.