Performance tuning issues

I have built a solution which runs for two hours on a server with 4CPU 2GHz each and 2GB of RAM on windows 2000 server (CPU utilization almost 70% and almost out of RAM). I moved the two source databases and the solution to a new box runing 8 xeon's at 3GHz each and 16GB of RAM running widows 2003 server 32bit and it still runs for 2 hours (CPU utilization 10% and ample RAM left).

I was expecting it to run much faster. So I started exploring the performance tuning features in SSIS and started tweaking the following:

Control Flow:

DefaultBufferMaxRows: Based on row size and buffer size, calculated the max rows.

DefaultBufferSize: Set this to max 100MB

DataFlow Destination:

Rows Per Batch: Set this to equal to the numbe of rows expected from the source.

Maximum Insert Commit Size: Set this to zero since memory was not an issue

I took the recommendations from other threads on similar issues here including the excellent recommendations at http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx but now the job is running for 6 hours.

Can anyone explain what I am doing wrong here? I have tried each of the above one by one and all together. No matter what combination I try it does not work any faster and both source and destination database are on the same server. Even selects from the same database also slowed down from 10 minutes to one hour.

Any assistance is appreciated, I need to get this job run in an hour.

Thanks!

- Philips.

How complex is your solution? I would recommend you to look at a lower grain;take a look a the log execution and compare it againt previous logs in the old server to see if you can identify a specifc part of the process as the bottleneck...|||

I'd also recommend watching the OVAL webcast that I talk about here:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

-Jamie

|||Did you setup the server to access more than 4gb of memory?

You need to configure Windows and then SQL server.|||Yes, SQL server is using around 14GB of memory and awe is turned on. Thanks!|||

It is a financial warehouse job which collects data from an ERP system loads a staging area and then the datamart. It also creates aggreagate tables. There are around 8 packages called from the master package.

My problem is I cannot find any way of using those four performance tuning settings accurately.

Any changes I make to the default setting is slowing the job down.

|||

Thanks! I went through it.

I am at a point where I am willing to drop all the control flow task and use plain old insert into statements and use the power of the DBMS engine than try to get the SSIS engine to do it. Unless I figure out what is wrong and why.

|||

Philips-HCR wrote:

Thanks! I went through it.

I am at a point where I am willing to drop all the control flow task and use plain old insert into statements and use the power of the DBMS engine than try to get the SSIS engine to do it. Unless I figure out what is wrong and why.

There's nothing wrong with doing that. The use of SSIS does not dictate that you should use data-flows to move your data about. If SQL is an option then invariably it will be the best option. it depends on your requirements and your preferences. You can issue SQL from an Execute SQL Task and still leverage all the other good stuff in SSIS like logging, workflow, portability etc... if you so wish.

-Jamie

Performance of extended stored procedures in SQL Server 2000

What is the overhead of using extended stored procedures?

I created a table with 500,000 rows.
1) I ran a select on two columns and it runs in about 5 seconds.
2) I ran a select on one column and called an UDF (it returns a
constant string) and it takes 10 seconds.
3) I ran a select on one column and called a UDF that calls an extended
stored procedure that returns a string and it takes 65 seconds.
I also tried running test 3 with 4 concurrent clients and each client
takes about 120 seconds.(smauldin@.ingrian.com) writes:
> What is the overhead of using extended stored procedures?
> I created a table with 500,000 rows.
> 1) I ran a select on two columns and it runs in about 5 seconds.
> 2) I ran a select on one column and called an UDF (it returns a
> constant string) and it takes 10 seconds.
> 3) I ran a select on one column and called a UDF that calls an extended
> stored procedure that returns a string and it takes 65 seconds.
> I also tried running test 3 with 4 concurrent clients and each client
> takes about 120 seconds.

The overhead is apparently significant in this case. And it may well be
typical. By calling an extended stored procedure, you are completely
serializing the processing. If you rewrote the query as a cursor and
called the XP in the extednded stored procedure, you would probably
see a higher value than 65, but not that much higher.

Thus the overhead is not so much in the XP itself, as in the way
it affects the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance Monitoring

Hi All
I am running web applications over multiple database and web servers. Each
web application runs on a Web Server and connects to a seperate Database
Server. As more web applications are created, we have to purchase more web
and database servers as the systems slow down. The problem is that we don't
really know if we need more database or more web servers. Does anyone know a
good tool for monitoring web AND database servers to help us to decide when
to buy new servers?
We are running windows 2003 (IIS 6) and SQL Server 2000.
Thanx in advance
Joe
Well Profiler & Perfmon should allow you to see how SQL Server is
performing. IF you want 3rd party tools you might want to look at Quest for
SQL Server. Veritas has a product line called i3 that will monitor all
levels of the app. From the web server to the db and give you plenty of
reports etc. to show where problems may be occurring. But they do cost a
few dollars.
Andrew J. Kelly SQL MVP
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am running web applications over multiple database and web servers. Each
> web application runs on a Web Server and connects to a seperate Database
> Server. As more web applications are created, we have to purchase more web
> and database servers as the systems slow down. The problem is that we
> don't really know if we need more database or more web servers. Does
> anyone know a good tool for monitoring web AND database servers to help us
> to decide when to buy new servers?
> We are running windows 2003 (IIS 6) and SQL Server 2000.
> Thanx in advance
> Joe
>
|||I work for a company that might be able to help you or is partners with
companies who could. The website is www.gomez.com. Check it out. Just
thought it might help.
|||Cheers, I didn't think about built in SQL Server Tools. I shall try those
first.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ulh9B8sjFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Well Profiler & Perfmon should allow you to see how SQL Server is
> performing. IF you want 3rd party tools you might want to look at Quest
> for SQL Server. Veritas has a product line called i3 that will monitor
> all levels of the app. From the web server to the db and give you plenty
> of reports etc. to show where problems may be occurring. But they do cost
> a few dollars.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
> news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
>
|||Check for http://www.agileinfollc.com DataStudio, it has a good performance
monitoring facility under Performance node.
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am running web applications over multiple database and web servers. Each
> web application runs on a Web Server and connects to a seperate Database
> Server. As more web applications are created, we have to purchase more web
> and database servers as the systems slow down. The problem is that we
> don't really know if we need more database or more web servers. Does
> anyone know a good tool for monitoring web AND database servers to help us
> to decide when to buy new servers?
> We are running windows 2003 (IIS 6) and SQL Server 2000.
> Thanx in advance
> Joe
>