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 Tuning Help needed

This is a Sql 2000 server running on Win 2k. This is a 1
CPU box connected to a SAN.
I ran a profiler trace for 3 hours and collected all the
data. Some of the queries show a large amount of reads.
When I run those queries individually the # of reads is
fairly smaller. For example the trace file shows 36503
reads and the individual query plan shows 91 CPU reads.
Whats going on ? Is the profiler tracking all I/O activity
at that time ? I mean, is it tracking all tempdb usage and
any other I/O activity at this time. What would explain
this large drop in I/O ?
THanks"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
I'm new to SQL Server so take what I say with a grain of salt.. But does
the profiler keep track of how many executions for each SQL statement?
Executed once the number of reads may be small but if it was executed 1000
times during that timeframe I would expect the number to be large.|||i assume you ran the query with the same parameters?
if so, then this large a difference in reads might
indicate the trace captured that query with a different
plan. if the choice of parameters can results in large
differences in rows involved, then depending on what
parameters were used when the query was last compiled,
different executes could get different plans.
a smaller difference on the order of several hundred or
even more than 1k might indicate compile or recompile.
>--Original Message--
>This is a Sql 2000 server running on Win 2k. This is a 1
>CPU box connected to a SAN.
>I ran a profiler trace for 3 hours and collected all the
>data. Some of the queries show a large amount of reads.
>When I run those queries individually the # of reads is
>fairly smaller. For example the trace file shows 36503
>reads and the individual query plan shows 91 CPU reads.
>Whats going on ? Is the profiler tracking all I/O
activity
>at that time ? I mean, is it tracking all tempdb usage
and
>any other I/O activity at this time. What would explain
>this large drop in I/O ?
>THanks
>.
>|||Jack,
are you comparing Profiler output to Query Analyzer query plan?
If so... the difference is "normal".
Trust the Profiler, if it shows 36000 reads, that's really happening
in the DB.
peksi
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THanks|||Hi Jack
The profiler shows what's happening at run time, which can be different from
what happens when you run the query for a few reasons, eg:
(a) The level & type of SQL Server activity is different when you run the
query from when the same query was captured by the profiler.
(b) The security context from which you ran the query may be different from
the query picked up by profiler.
(c) General resource levels on the server may be different.
Capturing the execution plans & comparing them may reveal some explanation
of what accounts for the different levels of reads, but this only takes you
part way to resolving the actual problem.
HTH
Regards,
Greg Linwood
SQL Server MVP
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THankssql

Performance Related Question

I am doing a performance counter for the CPU usage and
recording the output to a log file.
What i want to know is a way where I can say that the max
CPU usage say 80% occured for x amount of time, the
perfmon does not tell me how long was the maximum time
was for.
so i want something like CPU usage max value 80% was for
9 seconds.
Any ideasapok
In the Performance Monitor you can set up a range of intervals , i mean from
9am to 11am and see how CPU behaved since.
Also, If I rememeber well there are some classes in .NET framework you can
use to get the info from PM.
"apok" <anonymous@.discussions.microsoft.com> wrote in message
news:15aa01c535b7$e327f0a0$a501280a@.phx.gbl...
> I am doing a performance counter for the CPU usage and
> recording the output to a log file.
> What i want to know is a way where I can say that the max
> CPU usage say 80% occured for x amount of time, the
> perfmon does not tell me how long was the maximum time
> was for.
> so i want something like CPU usage max value 80% was for
> 9 seconds.
> Any ideas

Performance Related Question

I am doing a performance counter for the CPU usage and
recording the output to a log file.
What i want to know is a way where I can say that the max
CPU usage say 80% occured for x amount of time, the
perfmon does not tell me how long was the maximum time
was for.
so i want something like CPU usage max value 80% was for
9 seconds.
Any ideasapok
In the Performance Monitor you can set up a range of intervals , i mean from
9am to 11am and see how CPU behaved since.
Also, If I rememeber well there are some classes in .NET framework you can
use to get the info from PM.
"apok" <anonymous@.discussions.microsoft.com> wrote in message
news:15aa01c535b7$e327f0a0$a501280a@.phx.gbl...
> I am doing a performance counter for the CPU usage and
> recording the output to a log file.
> What i want to know is a way where I can say that the max
> CPU usage say 80% occured for x amount of time, the
> perfmon does not tell me how long was the maximum time
> was for.
> so i want something like CPU usage max value 80% was for
> 9 seconds.
> Any ideassql

Performance Related Question

I am doing a performance counter for the CPU usage and
recording the output to a log file.
What i want to know is a way where I can say that the max
CPU usage say 80% occured for x amount of time, the
perfmon does not tell me how long was the maximum time
was for.
so i want something like CPU usage max value 80% was for
9 seconds.
Any ideas
apok
In the Performance Monitor you can set up a range of intervals , i mean from
9am to 11am and see how CPU behaved since.
Also, If I rememeber well there are some classes in .NET framework you can
use to get the info from PM.
"apok" <anonymous@.discussions.microsoft.com> wrote in message
news:15aa01c535b7$e327f0a0$a501280a@.phx.gbl...
> I am doing a performance counter for the CPU usage and
> recording the output to a log file.
> What i want to know is a way where I can say that the max
> CPU usage say 80% occured for x amount of time, the
> perfmon does not tell me how long was the maximum time
> was for.
> so i want something like CPU usage max value 80% was for
> 9 seconds.
> Any ideas

Performance Questions - disks

I recently started collecting historical performance statistics from our SQL
servers. I am gathering information from the big four counters, CPU, RAM,
Disk, Network. In addition, I am watching CPU and RAM for the sqlservr.exe
process.
On one server, %disk time is rather high and so is disk queue length. CPU
is around 50%. This seems like an obvious disk bottleneck. In a
multi-processor system, is %disk time based off a 100% max for total CPU or
200% for each indivual processor? Also, can defragmenting the hard drive
reduce the disk queue length?
In our current server implementations, we have maintenance plans that backup
our databases's to files on the same disks as our sql databases's. These
plans run nightly, so there is a lot of disk I/O on these disks. In
addition, I suspect that they are highly fragmented due to all the writing
backup files and deleting old backup files. We also are running around
5-10% of free disk space on this particular server. Since the file system
was formatted with 64 Kbps clusters, I can not defragment the file system.
Questions:
1) Do other admins deploy scheduled defragmentation policies on live sql
volumes? Such as Diskeeper?
2) I read that there are mixed opinions on using 64 Kbps NTFS volumes for
your live SQL DB's.
3) If you are going to backup the DB's to file, I assume the best procedure
for disk utilization is as follows:
SYS - System & SQL Log
DATA - SQL DB
PAGEFILE - Pagefile & SQL backup files
Hi
You ask alot of questions here, but a starter would be to read
SQL Server 2000 Performance Turning Technical Reference ISBN 0-7356-1270-6
http://www.sql-server-performance.co...ing_review.asp
and visit:
http://msdn.microsoft.com/library/de...izing_5lt1.asp
http://www.sql-server-performance.com/
John
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jgrv$429$1@.grandcanyon.binc.net...
> I recently started collecting historical performance statistics from our
SQL
> servers. I am gathering information from the big four counters, CPU, RAM,
> Disk, Network. In addition, I am watching CPU and RAM for the
sqlservr.exe
> process.
> On one server, %disk time is rather high and so is disk queue length. CPU
> is around 50%. This seems like an obvious disk bottleneck. In a
> multi-processor system, is %disk time based off a 100% max for total CPU
or
> 200% for each indivual processor? Also, can defragmenting the hard drive
> reduce the disk queue length?
> In our current server implementations, we have maintenance plans that
backup
> our databases's to files on the same disks as our sql databases's. These
> plans run nightly, so there is a lot of disk I/O on these disks. In
> addition, I suspect that they are highly fragmented due to all the writing
> backup files and deleting old backup files. We also are running around
> 5-10% of free disk space on this particular server. Since the file system
> was formatted with 64 Kbps clusters, I can not defragment the file system.
> Questions:
> 1) Do other admins deploy scheduled defragmentation policies on live sql
> volumes? Such as Diskeeper?
> 2) I read that there are mixed opinions on using 64 Kbps NTFS volumes for
> your live SQL DB's.
> 3) If you are going to backup the DB's to file, I assume the best
procedure
> for disk utilization is as follows:
> SYS - System & SQL Log
> DATA - SQL DB
> PAGEFILE - Pagefile & SQL backup files
>

Performance Questions - disks

I recently started collecting historical performance statistics from our SQL
servers. I am gathering information from the big four counters, CPU, RAM,
Disk, Network. In addition, I am watching CPU and RAM for the sqlservr.exe
process.
On one server, %disk time is rather high and so is disk queue length. CPU
is around 50%. This seems like an obvious disk bottleneck. In a
multi-processor system, is %disk time based off a 100% max for total CPU or
200% for each indivual processor? Also, can defragmenting the hard drive
reduce the disk queue length?
In our current server implementations, we have maintenance plans that backup
our databases's to files on the same disks as our sql databases's. These
plans run nightly, so there is a lot of disk I/O on these disks. In
addition, I suspect that they are highly fragmented due to all the writing
backup files and deleting old backup files. We also are running around
5-10% of free disk space on this particular server. Since the file system
was formatted with 64 Kbps clusters, I can not defragment the file system.
Questions:
1) Do other admins deploy scheduled defragmentation policies on live sql
volumes? Such as Diskeeper?
2) I read that there are mixed opinions on using 64 Kbps NTFS volumes for
your live SQL DB's.
3) If you are going to backup the DB's to file, I assume the best procedure
for disk utilization is as follows:
SYS - System & SQL Log
DATA - SQL DB
PAGEFILE - Pagefile & SQL backup filesHi
You ask alot of questions here, but a starter would be to read
SQL Server 2000 Performance Turning Technical Reference ISBN 0-7356-1270-6
http://www.sql-server-performance.com/sql_server_2000_performance_tuning_review.asp
and visit:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agoptimizing_5lt1.asp
http://www.sql-server-performance.com/
John
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jgrv$429$1@.grandcanyon.binc.net...
> I recently started collecting historical performance statistics from our
SQL
> servers. I am gathering information from the big four counters, CPU, RAM,
> Disk, Network. In addition, I am watching CPU and RAM for the
sqlservr.exe
> process.
> On one server, %disk time is rather high and so is disk queue length. CPU
> is around 50%. This seems like an obvious disk bottleneck. In a
> multi-processor system, is %disk time based off a 100% max for total CPU
or
> 200% for each indivual processor? Also, can defragmenting the hard drive
> reduce the disk queue length?
> In our current server implementations, we have maintenance plans that
backup
> our databases's to files on the same disks as our sql databases's. These
> plans run nightly, so there is a lot of disk I/O on these disks. In
> addition, I suspect that they are highly fragmented due to all the writing
> backup files and deleting old backup files. We also are running around
> 5-10% of free disk space on this particular server. Since the file system
> was formatted with 64 Kbps clusters, I can not defragment the file system.
> Questions:
> 1) Do other admins deploy scheduled defragmentation policies on live sql
> volumes? Such as Diskeeper?
> 2) I read that there are mixed opinions on using 64 Kbps NTFS volumes for
> your live SQL DB's.
> 3) If you are going to backup the DB's to file, I assume the best
procedure
> for disk utilization is as follows:
> SYS - System & SQL Log
> DATA - SQL DB
> PAGEFILE - Pagefile & SQL backup files
>

Performance Questions - disks

I recently started collecting historical performance statistics from our SQL
servers. I am gathering information from the big four counters, CPU, RAM,
Disk, Network. In addition, I am watching CPU and RAM for the sqlservr.exe
process.
On one server, %disk time is rather high and so is disk queue length. CPU
is around 50%. This seems like an obvious disk bottleneck. In a
multi-processor system, is %disk time based off a 100% max for total CPU or
200% for each indivual processor? Also, can defragmenting the hard drive
reduce the disk queue length?
In our current server implementations, we have maintenance plans that backup
our databases's to files on the same disks as our sql databases's. These
plans run nightly, so there is a lot of disk I/O on these disks. In
addition, I suspect that they are highly fragmented due to all the writing
backup files and deleting old backup files. We also are running around
5-10% of free disk space on this particular server. Since the file system
was formatted with 64 Kbps clusters, I can not defragment the file system.
Questions:
1) Do other admins deploy scheduled defragmentation policies on live sql
volumes? Such as Diskeeper?
2) I read that there are mixed opinions on using 64 Kbps NTFS volumes for
your live SQL DB's.
3) If you are going to backup the DB's to file, I assume the best procedure
for disk utilization is as follows:
SYS - System & SQL Log
DATA - SQL DB
PAGEFILE - Pagefile & SQL backup filesHi
You ask alot of questions here, but a starter would be to read
SQL Server 2000 Performance Turning Technical Reference ISBN 0-7356-1270-6
http://www.sql-server-performance.c...>
w.as
p
and visit:
http://msdn.microsoft.com/library/d...
zing_5lt1.asp
http://www.sql-server-performance.com/
John
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jgrv$429$1@.grandcanyon.binc.net...
> I recently started collecting historical performance statistics from our
SQL
> servers. I am gathering information from the big four counters, CPU, RAM,
> Disk, Network. In addition, I am watching CPU and RAM for the
sqlservr.exe
> process.
> On one server, %disk time is rather high and so is disk queue length. CPU
> is around 50%. This seems like an obvious disk bottleneck. In a
> multi-processor system, is %disk time based off a 100% max for total CPU
or
> 200% for each indivual processor? Also, can defragmenting the hard drive
> reduce the disk queue length?
> In our current server implementations, we have maintenance plans that
backup
> our databases's to files on the same disks as our sql databases's. These
> plans run nightly, so there is a lot of disk I/O on these disks. In
> addition, I suspect that they are highly fragmented due to all the writing
> backup files and deleting old backup files. We also are running around
> 5-10% of free disk space on this particular server. Since the file system
> was formatted with 64 Kbps clusters, I can not defragment the file system.
> Questions:
> 1) Do other admins deploy scheduled defragmentation policies on live sql
> volumes? Such as Diskeeper?
> 2) I read that there are mixed opinions on using 64 Kbps NTFS volumes for
> your live SQL DB's.
> 3) If you are going to backup the DB's to file, I assume the best
procedure
> for disk utilization is as follows:
> SYS - System & SQL Log
> DATA - SQL DB
> PAGEFILE - Pagefile & SQL backup files
>

Performance Question

Hello,
The database we have is pretty CPU intensive.We have a trace running on the
server 24*7 although it doesn't have lot of events in trace and we run this
through stored procedures and not through GUI.Does anybody think trace is
going to affect the performance and the drive the CPU little more up.
Thanks
Yes, but it is impossible to say how much. Key for tracing without affecting performance too much is to use a
server side trace (define through stored procedures not GUI), have the output file local to the SQL Server
(not a network drive) and minimize the events.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:8D5D15B2-D664-489A-AD46-56C353BF5F2E@.microsoft.com...
> Hello,
> The database we have is pretty CPU intensive.We have a trace running on the
> server 24*7 although it doesn't have lot of events in trace and we run this
> through stored procedures and not through GUI.Does anybody think trace is
> going to affect the performance and the drive the CPU little more up.
> Thanks

Performance Question

I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
processor. Would my performance be better served by adding the 2nd CPU or
upgrading the current CPU to a 3.2GHz?
Any thoughts are welcome,
ScottGenerally I would go with a multi proc environment as you could
simultaneously serve mulitple requests faster although individual request ma
y
be served faster by increasing the clock speed of the single processor.
Licensing would also be a factor in my decision.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Scott Cadreau" wrote:

> I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
> processor. Would my performance be better served by adding the 2nd CPU or
> upgrading the current CPU to a 3.2GHz?
> Any thoughts are welcome,
> Scott
>
>

Performance Question

Hello,
The database we have is pretty CPU intensive.We have a trace running on the
server 24*7 although it doesn't have lot of events in trace and we run this
through stored procedures and not through GUI.Does anybody think trace is
going to affect the performance and the drive the CPU little more up.
ThanksYes, but it is impossible to say how much. Key for tracing without affecting
performance too much is to use a
server side trace (define through stored procedures not GUI), have the outpu
t file local to the SQL Server
(not a network drive) and minimize the events.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:8D5D15B2-D664-489A-AD46-56C353BF5F2E@.microsoft.com...
> Hello,
> The database we have is pretty CPU intensive.We have a trace running on th
e
> server 24*7 although it doesn't have lot of events in trace and we run th
is
> through stored procedures and not through GUI.Does anybody think trace is
> going to affect the performance and the drive the CPU little more up.
> Thanks

Performance Problem

We have a web server and a database application on the backhand.
the use case is we wanna use minumum CPU resources.
So to achieve that we are considering two options
1.to put all the data into one 500 GB disk
2.to put data into 10 harddisk size of 50 GB
which one more performant ?Option 2 should be the fastest (especially when using RAID 1+0).