DataBase performance suggestion required

[pratapsriram]

Dear DataStagers,

I have a simple job which is doing a Extract and Load. The stages in the job are DRS -> Transformer -> SQL Server Enterprise. The performance is not that great. I see a extract row count/sec of 4000 initially and then drops down to 200 after a while. Also the SQL server is so overloaded that others cannot log into it as well. So in these circumstances what are the Database tuning or performance improvements that we can suggest to the DBA? In this case the DBA is saying that he cannot do anything other than increasing the Memory resources of the Database server.

Any suggestions are welcome.

Thanks,
MS

[antonyraj.deva]

If the Database Server is of low configuration, then the only other option in your hands that you can try is to make the job run in parallel with appropriate partitioning methods so that the run time can be minimised.

-Tony

[Ultramundane]

If the source and target are the same database or database server, you could try splitting the job into two jobs (an extract job and a load job) and see if the two jobs complete faster than one due to saturation of the database server.

[cppwiz]

What type of RAID is being used (RAID-5)? How much data is being updated (megabytes)?

Have you looked at separating the inserts from the updates in your job? What about dropping non-clustered indexes at the beginning of your job and adding them back when it finishes?

We get between 500 to 5000 rows per second on our DataStage 8.1 jobs running on an AIX server updating SQL2008 through the ODBC Connector stage. I would think the native Windows stage would be faster than that.

To allow the users to login, SQL2008 has a Resource Governor that can be used to throttle the performance of the ETL process to free up resources for user activity.

[pratapsriram]

Yes the SQL Server is of low configuration. It is a virtual server and having only 2 nodes with 2 GB of Memory. The DBA increased the CPU on that virtual server and added 2 more GB on that(Windows server 2003 - Standard edition). There are no updates, the job just loads the staging table. And the target also does not have any keys. The Database servers are both virtual for the source and target.

This raises a question: Is it advisable to use a virtual server as a DB for Windows Server? Also why do small organizations(3000 employees in size) prefer Windows platform for DataStage 8.1? Just wondering.