Performance issue with SQL Inserts

[shaimil]

I have a set of jobs that each insert records into a SQL Server 2000 db. I'm finding that some jobs insert at rates of around 800 rows/sec wheras others do as few as 25 rows/sec.

The main differences between the jobs seems to be the number of tables/connections that the jobs make (ie the number of concurrent insert connections)

Jobs with 1 or 2 connections seem to run far quicker than those with 4 and 8 connections.

Does anyone know wether, firstly this is normal and secondly whether there are any tweeks I should be maiking to SQL Server to better manage multiple connections from a single job.

Alternatively, is 8 db connections in a single job just a bad idea.

Any advice is very much appreciated.

Regards
Shay

[elavenil]

I think 25 recs/sec is not normal. You can check few things.

First of all, what stage are you using? Are you using ODBC or any other stage? What is the length of the record and what is the commit interval for each insert job.

I do not understand that you have mentioned no of tables in the job. Are you joining with other tables? What is number of connections? Are you referring no of insert tables in SQL server db? If yes, You need to consider the available resources in the SQL server when you concurrently call the multiple jobs, which do inserting into the tables.

ITWH.

Regards
Saravanan

[satish_valavala]

Hi Shay,
What do you mean "The number of tables/connections that the jobs make".
Suppose if you want to look-up on 10 tables, take one ODBC stage(One instance) and write your SQLs in diffrent SQL tabs of 10 different links to Transformer( ODBC ---->Transformer).
If you elaborate your job designs like how many ODBC stages you are using per job and for what purpose Eg:- Source,Look-up and Target etc., then people can help better.

[shaimil]

Sorry, let me expand.

I have a single input stream inserting into multiple tables on SQL server. The commit level is set to 0 and the record lengths vary depending on the jobs, but all seem to follow the same degradation of performance path.

I have been told that it may be the way the box is configured. It has been set up as Windows 2003 Server using RAID 5. Is it true that RAID 5 could be slowing down the writes.

Thanks
Shay

[Precious]

Palmeal wrote
Raid 5 is not the best storage set up for writing - Raid 0 + 1 would be the best set up. Are your files spread around the disks or are the controllers trying to access the same disks at the same time. Again Raid 0 + 1 will randomise this and reduce disk contention.

The rest of the post is here:
viewtopic.php?t=92464&start=0&postdays= ... erformance

Regards,

[arbert]

Sorry, let me expand.

I have a single input stream inserting into multiple tables on SQL server. The commit level is set to 0 and the record lengths vary depending on the jobs, but all seem to follow the same degradation of performance path.

I have been told that it may be the way the box is configured. It has been set up as Windows 2003 Server using RAID 5. Is it true that RAID 5 could be slowing down the writes.

Thanks
Shay

Raid 5 can make a BIG difference. Also, putting your LDF and MDF files on different drives/raid groups will make a HUGE difference. If they are on the same drives/raid groups, the log and data files will compete for IO.

Also, your commit level shouldn't be 0. This basically creates one large transaction and commits at the end of the entire load--I would set it at least at 1000--the actual number really depends on the width of your row and the number or records.

Brett

[ray.wurlod]

Someone also need to make the point that 800 rows/sec and 25 rows/sec can represent exactly the same performance in terms of volume/time.

800 25-byte rows and 25 800-byte rows contain exactly the same volume of data.

Please don't rely on rows/sec as a measure between jobs unless you are totally certain that row size is identical.