Performance and Stress tests

Hi out there
I am trying to create a uniform test-environment which I can move from one
hw platform to another. I have found ostress.exe and read80trace.exe as a
usefull tool for this. Are there some out there which has written some either
stress-scripts for these tools to stress a sql-database, so that I can see
how to do it (sorry to admit it but I am a wizard into sql)
regards /thomas iwang
Hi
MS has some tools, follow the link from:
http://msmvps.com/epprecht/archive/2.../24/10591.aspx
Regards
Mike
"Thomas Iwang" wrote:

> Hi out there
> I am trying to create a uniform test-environment which I can move from one
> hw platform to another. I have found ostress.exe and read80trace.exe as a
> usefull tool for this. Are there some out there which has written some either
> stress-scripts for these tools to stress a sql-database, so that I can see
> how to do it (sorry to admit it but I am a wizard into sql)
> regards /thomas iwang
|||Hi,
If you want to stress io subsystem sqliostress is a nice stress tool. First
you don't need to install sql server to use sqliostress, and if you have
problems with your io subsytem you can catch them before going into
production. (We catch some serious problems with the fibre channel adapter
cards etc.. thanks to sqliostress)
If you like to stress test your application, we use ACT which is available
with Visual Studio if i'm not wrong..
And there used to be a tool in resource kit which is called Database Hammer..
Hope this helps..
"Thomas Iwang" wrote:

> Hi out there
> I am trying to create a uniform test-environment which I can move from one
> hw platform to another. I have found ostress.exe and read80trace.exe as a
> usefull tool for this. Are there some out there which has written some either
> stress-scripts for these tools to stress a sql-database, so that I can see
> how to do it (sorry to admit it but I am a wizard into sql)
> regards /thomas iwang

Performance and Stress tests

Hi out there
I am trying to create a uniform test-environment which I can move from one
hw platform to another. I have found ostress.exe and read80trace.exe as a
usefull tool for this. Are there some out there which has written some eithe
r
stress-scripts for these tools to stress a sql-database, so that I can see
how to do it (sorry to admit it but I am a wizard into sql)
regards /thomas iwangHi
MS has some tools, follow the link from:
http://msmvps.com/epprecht/archive/...7/24/10591.aspx
Regards
Mike
"Thomas Iwang" wrote:

> Hi out there
> I am trying to create a uniform test-environment which I can move from one
> hw platform to another. I have found ostress.exe and read80trace.exe as a
> usefull tool for this. Are there some out there which has written some eit
her
> stress-scripts for these tools to stress a sql-database, so that I can see
> how to do it (sorry to admit it but I am a wizard into sql)
> regards /thomas iwang|||Hi,
If you want to stress io subsystem sqliostress is a nice stress tool. First
you don't need to install sql server to use sqliostress, and if you have
problems with your io subsytem you can catch them before going into
production. (We catch some serious problems with the fibre channel adapter
cards etc.. thanks to sqliostress)
If you like to stress test your application, we use ACT which is available
with Visual Studio if i'm not wrong..
And there used to be a tool in resource kit which is called Database Hammer.
.
Hope this helps..
"Thomas Iwang" wrote:

> Hi out there
> I am trying to create a uniform test-environment which I can move from one
> hw platform to another. I have found ostress.exe and read80trace.exe as a
> usefull tool for this. Are there some out there which has written some eit
her
> stress-scripts for these tools to stress a sql-database, so that I can see
> how to do it (sorry to admit it but I am a wizard into sql)
> regards /thomas iwang

Performance and Stress tests

Hi out there
I am trying to create a uniform test-environment which I can move from one
hw platform to another. I have found ostress.exe and read80trace.exe as a
usefull tool for this. Are there some out there which has written some either
stress-scripts for these tools to stress a sql-database, so that I can see
how to do it (sorry to admit it but I am a wizard into sql)
regards /thomas iwangHi
MS has some tools, follow the link from:
http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx
Regards
Mike
"Thomas Iwang" wrote:
> Hi out there
> I am trying to create a uniform test-environment which I can move from one
> hw platform to another. I have found ostress.exe and read80trace.exe as a
> usefull tool for this. Are there some out there which has written some either
> stress-scripts for these tools to stress a sql-database, so that I can see
> how to do it (sorry to admit it but I am a wizard into sql)
> regards /thomas iwang|||Hi,
If you want to stress io subsystem sqliostress is a nice stress tool. First
you don't need to install sql server to use sqliostress, and if you have
problems with your io subsytem you can catch them before going into
production. (We catch some serious problems with the fibre channel adapter
cards etc.. thanks to sqliostress)
If you like to stress test your application, we use ACT which is available
with Visual Studio if i'm not wrong..
And there used to be a tool in resource kit which is called Database Hammer..
Hope this helps..
"Thomas Iwang" wrote:
> Hi out there
> I am trying to create a uniform test-environment which I can move from one
> hw platform to another. I have found ostress.exe and read80trace.exe as a
> usefull tool for this. Are there some out there which has written some either
> stress-scripts for these tools to stress a sql-database, so that I can see
> how to do it (sorry to admit it but I am a wizard into sql)
> regards /thomas iwang

Performance & Timeout during auto grow in SQL 2000

We had a downtime in our Stress environement during Database Automatic Data
file grow.
The database was preallocate to 20Gb with auto increment 10%.
It seems the reallocation of the 2 Gb take minutes.
We are trying to get Best Practices in SQL 2000 to see what is recommended.
- Having a Dedicated DBA to check DB space available?
- Use or Not Auto Grow? use % versus fixed size?
The idea here is to get business justification and/or use case to help us
deciding Dedicated DBA versus specific tools & technologie.
Hi
Auto grow should only be a last resort. Create the DB big enough, and then
check on it daily/weekly. You can setup an alert to fire an e-mail off if the
free space gets too low. If you have the space, grow the Db to 30Gb when the
server is not too busy.
Regards
Mike
"Fabrice PIERRE" wrote:

> We had a downtime in our Stress environement during Database Automatic Data
> file grow.
> The database was preallocate to 20Gb with auto increment 10%.
> It seems the reallocation of the 2 Gb take minutes.
> We are trying to get Best Practices in SQL 2000 to see what is recommended.
> - Having a Dedicated DBA to check DB space available?
> - Use or Not Auto Grow? use % versus fixed size?
> The idea here is to get business justification and/or use case to help us
> deciding Dedicated DBA versus specific tools & technologie.
|||Hi ,Mike
I'd go with creating a database big enough with auto-grow feature.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ACB025A7-3ACE-4E3D-9FD2-6695A1C3AAB1@.microsoft.com...
> Hi
> Auto grow should only be a last resort. Create the DB big enough, and then
> check on it daily/weekly. You can setup an alert to fire an e-mail off if
the
> free space gets too low. If you have the space, grow the Db to 30Gb when
the[vbcol=seagreen]
> server is not too busy.
> Regards
> Mike
> "Fabrice PIERRE" wrote:
Data[vbcol=seagreen]
recommended.[vbcol=seagreen]
us[vbcol=seagreen]

Performance & Timeout during auto grow in SQL 2000

We had a downtime in our Stress environement during Database Automatic Data
file grow.
The database was preallocate to 20Gb with auto increment 10%.
It seems the reallocation of the 2 Gb take minutes.
We are trying to get Best Practices in SQL 2000 to see what is recommended.
- Having a Dedicated DBA to check DB space available?
- Use or Not Auto Grow? use % versus fixed size?
The idea here is to get business justification and/or use case to help us
deciding Dedicated DBA versus specific tools & technologie.Hi
Auto grow should only be a last resort. Create the DB big enough, and then
check on it daily/weekly. You can setup an alert to fire an e-mail off if the
free space gets too low. If you have the space, grow the Db to 30Gb when the
server is not too busy.
Regards
Mike
"Fabrice PIERRE" wrote:
> We had a downtime in our Stress environement during Database Automatic Data
> file grow.
> The database was preallocate to 20Gb with auto increment 10%.
> It seems the reallocation of the 2 Gb take minutes.
> We are trying to get Best Practices in SQL 2000 to see what is recommended.
> - Having a Dedicated DBA to check DB space available?
> - Use or Not Auto Grow? use % versus fixed size?
> The idea here is to get business justification and/or use case to help us
> deciding Dedicated DBA versus specific tools & technologie.|||Hi ,Mike
I'd go with creating a database big enough with auto-grow feature.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ACB025A7-3ACE-4E3D-9FD2-6695A1C3AAB1@.microsoft.com...
> Hi
> Auto grow should only be a last resort. Create the DB big enough, and then
> check on it daily/weekly. You can setup an alert to fire an e-mail off if
the
> free space gets too low. If you have the space, grow the Db to 30Gb when
the
> server is not too busy.
> Regards
> Mike
> "Fabrice PIERRE" wrote:
> > We had a downtime in our Stress environement during Database Automatic
Data
> > file grow.
> > The database was preallocate to 20Gb with auto increment 10%.
> > It seems the reallocation of the 2 Gb take minutes.
> > We are trying to get Best Practices in SQL 2000 to see what is
recommended.
> > - Having a Dedicated DBA to check DB space available?
> > - Use or Not Auto Grow? use % versus fixed size?
> >
> > The idea here is to get business justification and/or use case to help
us
> > deciding Dedicated DBA versus specific tools & technologie.

Performance

I have an application that allows for searches, but it appears when I stress
test it with 50+ users the CPU utilization on the MS SQL server machine is
hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
that is the solution here.
What are some methods to scale this thing out? Replicate the database and
spread out the searches to multiple identical databases?It's pretty hard to say what the problem is without seeing exactly what you
are doing. But more than likely your queries and / or indexes are not
optimized. Try tuning them first.
--
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
> It's pretty hard to say what the problem is without seeing exactly what
you
> are doing. But more than likely your queries and / or indexes are not
> optimized. Try tuning them first.
Let's assume that it is optimized. Where to next?|||You did not specify any details so my recommendations:
- Check your DB and log files are on seperate arrays. (Raid 1 for Log, Raid
5 for db (Raid 10 is better))
- Did you enable /3GB switch, if possible.
- Use SQL Profiler, Index Tuning Wizard to find out problematic queries.
- Check your DB and Log file auto-growth, auto-shrink settings.
"Shabam" wrote:
> I have an application that allows for searches, but it appears when I stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>
>|||> - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
Raid
> 5 for db (Raid 10 is better))
Strange, but I was told that Raid 1 is better for db as it performs best,
and that Raid 5 is bad for performance? This db will be writing quite a
bit.
> - Did you enable /3GB switch, if possible.
Where do I do this?
Ok, beyond the turning, optimizing, etc... what options are there next if I
outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
what?|||If we are to assume that the code and DB model are optimal, and that we
should focus on answering your question only. Your specific question is how
does SQL scale OUT an OLTP. Scale out is generally a matter of adding more
SQL servers and distributing (usually via code but distributed partitions is
also an option) your database and/or functions across them. Sounds like
your asking about creating a reporting ODS to pull the searches off to
another server. By focusing only on scale out solutions you are confining
the answers to expensive solutions.
Lets start with the basics.
What other applications are running on the server if any?
Is this SQL 2000?
Is this SQL Standard or Enterprise edition?
What's driving your high CPU?
How many recompiles and procedure cache misses are occurring?
Does your code have an abundance of non set oriented logic?
Is this code owned by someone else and you don't have the right to change
it?
The more information you can provide the better advice the group can give.
Ray
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:2dadnQoD5ty8u6jfRVn-3A@.adelphia.com...
>I have an application that allows for searches, but it appears when I
>stress
> test it with 50+ users the CPU utilization on the MS SQL server machine is
> hitting 100%. The machine is on RAID SCSI, running on 4GB of memory and
> dual AMD Opteron cpu's. I can get a more powerful machine, but not sure
> if
> that is the solution here.
> What are some methods to scale this thing out? Replicate the database and
> spread out the searches to multiple identical databases?
>|||> > - Did you enable /3GB switch, if possible.
> Where do I do this?
From BOOT.INI, but you did not give any details about which edition of SQL
Server you are using and the OS.
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
>
You can search RAID5 vs RAID 10 on internet for more information. Raid1 is
better for Log because log writes are sequential. Raid 10 is better for db
files because you spread io across multiple disks and it did not need to deal
with parity as Raid 5 does.
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it?
My point is to not use autogrowth-auto shrink which may cause problems.
"Shabam" wrote:
> > - Check your DB and log files are on seperate arrays. (Raid 1 for Log,
> Raid
> > 5 for db (Raid 10 is better))
> Strange, but I was told that Raid 1 is better for db as it performs best,
> and that Raid 5 is bad for performance? This db will be writing quite a
> bit.
> > - Did you enable /3GB switch, if possible.
> Where do I do this?
> Ok, beyond the turning, optimizing, etc... what options are there next if I
> outgrow it? Sure I can upgrade the server, but that gets to a limit. Then
> what?
>
>|||Run Profiler and find out what it is that takes time and then try to
optimize those parts.
Regards
Steen
Shabam wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly
>> what you are doing. But more than likely your queries and / or
>> indexes are not optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23BuYhsJKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Run Profiler and find out what it is that takes time and then try to
> optimize those parts.
Another dead end...|||Have a look here:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:H8qdnR8CepdmpqjfRVn-qQ@.adelphia.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9eeM7FKFHA.572@.tk2msftngp13.phx.gbl...
>> It's pretty hard to say what the problem is without seeing exactly what
> you
>> are doing. But more than likely your queries and / or indexes are not
>> optimized. Try tuning them first.
> Let's assume that it is optimized. Where to next?
>