Percentage of total: users

Showing posts with label users. Show all posts

Monday, March 26, 2012

Performance depending on user rights?

Hello,
I am in the world of SQL Server. Just installed it (SQL Server 2000)
from cd, added some users, created an ODBC datasource from my client
and am ready to roll. And it works!
When I perform an query from the Query Analyzer some queries are very
fast, while the same query from my client (Access) take much longer. I
noticed in the Profiler differences between the execution paths. I
just can't find out what causes these performance problems.
Someone told me it could have something to do with the user. When I
use Query analyser I am on the server, with admin rights. ODBC uses a
user with as little rights as possible. Can this be the problem?
Hope someone can help me.
Thankz,
PeterDoes the SQL statement look the same (the one from QA and Access)?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"P. Bikkel" <bikkelp@.hotmail.com> wrote in message
news:f3f99039.0310230343.7f809590@.posting.google.com...
> Hello,
> I am in the world of SQL Server. Just installed it (SQL Server 2000)
> from cd, added some users, created an ODBC datasource from my client
> and am ready to roll. And it works!
> When I perform an query from the Query Analyzer some queries are very
> fast, while the same query from my client (Access) take much longer. I
> noticed in the Profiler differences between the execution paths. I
> just can't find out what causes these performance problems.
> Someone told me it could have something to do with the user. When I
> use Query analyser I am on the server, with admin rights. ODBC uses a
> user with as little rights as possible. Can this be the problem?
> Hope someone can help me.
> Thankz,
> Peter|||Yes, the SQL statement is the same.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uAOiBNWmDHA.1884@.TK2MSFTNGP09.phx.gbl>...
> Does the SQL statement look the same (the one from QA and Access)?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "P. Bikkel" <bikkelp@.hotmail.com> wrote in message
> news:f3f99039.0310230343.7f809590@.posting.google.com...
> > Hello,
> >
> > I am in the world of SQL Server. Just installed it (SQL Server 2000)
> > from cd, added some users, created an ODBC datasource from my client
> > and am ready to roll. And it works!
> >
> > When I perform an query from the Query Analyzer some queries are very
> > fast, while the same query from my client (Access) take much longer. I
> > noticed in the Profiler differences between the execution paths. I
> > just can't find out what causes these performance problems.
> >
> > Someone told me it could have something to do with the user. When I
> > use Query analyser I am on the server, with admin rights. ODBC uses a
> > user with as little rights as possible. Can this be the problem?
> >
> > Hope someone can help me.
> >
> > Thankz,
> >
> > Peter|||Could possibly be different SET settings between the QA and ODBC connection...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"P. Bikkel" <bikkelp@.hotmail.com> wrote in message
news:f3f99039.0310250327.4779d0bf@.posting.google.com...
> Yes, the SQL statement is the same.
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message
news:<uAOiBNWmDHA.1884@.TK2MSFTNGP09.phx.gbl>...
> > Does the SQL statement look the same (the one from QA and Access)?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "P. Bikkel" <bikkelp@.hotmail.com> wrote in message
> > news:f3f99039.0310230343.7f809590@.posting.google.com...
> > > Hello,
> > >
> > > I am in the world of SQL Server. Just installed it (SQL Server 2000)
> > > from cd, added some users, created an ODBC datasource from my client
> > > and am ready to roll. And it works!
> > >
> > > When I perform an query from the Query Analyzer some queries are very
> > > fast, while the same query from my client (Access) take much longer. I
> > > noticed in the Profiler differences between the execution paths. I
> > > just can't find out what causes these performance problems.
> > >
> > > Someone told me it could have something to do with the user. When I
> > > use Query analyser I am on the server, with admin rights. ODBC uses a
> > > user with as little rights as possible. Can this be the problem?
> > >
> > > Hope someone can help me.
> > >
> > > Thankz,
> > >
> > > Peter|||On 23 Oct 2003 04:43:04 -0700, bikkelp@.hotmail.com (P. Bikkel) wrote:
>I am in the world of SQL Server. Just installed it (SQL Server 2000)
>from cd, added some users, created an ODBC datasource from my client
>and am ready to roll. And it works!
I love it when a plan comes together!
>When I perform an query from the Query Analyzer some queries are very
>fast, while the same query from my client (Access) take much longer. I
>noticed in the Profiler differences between the execution paths. I
>just can't find out what causes these performance problems.
>Someone told me it could have something to do with the user. When I
>use Query analyser I am on the server, with admin rights. ODBC uses a
>user with as little rights as possible. Can this be the problem?
Well, how fast is fast, and how much longer is much longer?
If the query returns a lot of records, it could be something as simple
as the network transfer time. What if you run Query Analyzer on the
client, or Access on the server?
Joshua Sternsql

Wednesday, March 21, 2012

performance bottleneck?

how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi
"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegr oups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:

>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi

performance bottleneck?

how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi

performance bottleneck?

how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:

>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi

Monday, March 12, 2012

Performance and Detection

I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !
Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:

> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>
|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ
|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com
|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.

> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !

Performance and Detection

I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application whic
h
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the serve
r
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:

> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.

> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !

Performance and Detection

I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
--
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> >I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> > Server has a Memory of 512 K. There are different users using different
> > applications like access, Crystal reports and other general application
> > which
> > accesses these database in Production enviornment.
> >
> > Problem:
> > Sometime we experience slowdowns and then after about 5 MIN
> > everything starts moving again. Sometimes I have to disconnect users from
> > the SQL SERVER to make sure everything is working again. Reboot of the
> > server
> > also helps and will work as normal. But time and again we experience
> > slowdowns.
> > Number of users accessing this server is about 35. Not sure how do I fix
> > this problem. How do I know who is the culprit and whats locking it up.
> > Sp_WHO and SP_lock2 doesn't tell anything.
> >
> > Please advice.
> >
> > Jazzman !
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !

performance and #of users

If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:Originally posted by ricka49
If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:

SQL Server 7.0 and 2000 use approximately 50 Kb of server RAM per connection. Note that a user can have multiple connections, even with connection pooling. A good rule of thumb here is one connection per 100 records returned, so 50Kb of server memory per 100 records.

I cannot site an article on this. I got this information from a MS developer when I was looking at licensing options a couple of years back.|||You can check the number of users logged in from Enterprise Manager. You can also run sp_who and/or sp_who2. There is another place to check on user connections (and also connection memory)::

Look at the sysperfinfo table on master:

SELECT * FROM master..sysperfinfo
WHERE
(object_name = 'SQLServer:General Statistics' and counter_name = 'User Connections') OR
(object_name = 'SQLServer:Memory Manager' and counter_name = 'Connection Memory (KB)')

I make it a general habit to snapshot this data to a history table on an hourly basis. It can help me sometimes track usage and performance issues.

Regards,

Hugh Scott

Originally posted by ricka49
If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:

Performance affecting users only

Hi,
I have mssql 2000 std sp4 running on w2k3 std sp2. intermittently users
experience slowness in accessing the database but users with elevated access
such as domain admins never experience such slowness when everybody else is
sufferring.
Please help i am going insane.
Thanks
PabloAre the two groups connecting using different networks?
"pfrank61" <pfrank61@.discussions.microsoft.com> wrote in message
news:FAF8FA18-A4E9-42CC-8FA5-4EC4FD77B08B@.microsoft.com...
> Hi,
> I have mssql 2000 std sp4 running on w2k3 std sp2. intermittently users
> experience slowness in accessing the database but users with elevated
> access
> such as domain admins never experience such slowness when everybody else
> is
> sufferring.
> Please help i am going insane.
> Thanks
> Pablo|||No Jay, they are on the same network and even sometimes I ask them to login
into the same computer.
I must also add that when slowness happens and I reboot the SQL server
everything normalizes again.
Thanks
Pablo
"Jay" wrote:
> Are the two groups connecting using different networks?
> "pfrank61" <pfrank61@.discussions.microsoft.com> wrote in message
> news:FAF8FA18-A4E9-42CC-8FA5-4EC4FD77B08B@.microsoft.com...
> > Hi,
> > I have mssql 2000 std sp4 running on w2k3 std sp2. intermittently users
> > experience slowness in accessing the database but users with elevated
> > access
> > such as domain admins never experience such slowness when everybody else
> > is
> > sufferring.
> >
> > Please help i am going insane.
> >
> > Thanks
> > Pablo
>
>|||Hi Pablo
Ive got a similar situation.
SP calls from IIS (logged in to sql server with a certain sql server login)
time outs while same call with same parameters (captured from profiler) run
from Query Analyzer (logged in with another account, higher permission level)
have normal response times.
After restart of the sql server service boot situations (IIS and QA) works
fine.
Did you find out anymore on this?
Any other who can direct me regarding this?
SQL Server 2000, IIS 6. Memory, CPU etc looks fine.
Thanks!
/Tore
"pfrank61" wrote:
> No Jay, they are on the same network and even sometimes I ask them to login
> into the same computer.
> I must also add that when slowness happens and I reboot the SQL server
> everything normalizes again.
> Thanks
> Pablo
> "Jay" wrote:
> > Are the two groups connecting using different networks?
> >
> > "pfrank61" <pfrank61@.discussions.microsoft.com> wrote in message
> > news:FAF8FA18-A4E9-42CC-8FA5-4EC4FD77B08B@.microsoft.com...
> > > Hi,
> > > I have mssql 2000 std sp4 running on w2k3 std sp2. intermittently users
> > > experience slowness in accessing the database but users with elevated
> > > access
> > > such as domain admins never experience such slowness when everybody else
> > > is
> > > sufferring.
> > >
> > > Please help i am going insane.
> > >
> > > Thanks
> > > Pablo
> >
> >
> >|||Hi Tore,
My situation hasn't change and I am still rebooting the server evertime it
happens.\
Hello Guys we can't be the only guys experiencing this, Please help us out
with suggestions.
Thanks
Paul
"Tore" wrote:
> Hi Pablo
> Ive got a similar situation.
> SP calls from IIS (logged in to sql server with a certain sql server login)
> time outs while same call with same parameters (captured from profiler) run
> from Query Analyzer (logged in with another account, higher permission level)
> have normal response times.
> After restart of the sql server service boot situations (IIS and QA) works
> fine.
> Did you find out anymore on this?
> Any other who can direct me regarding this?
> SQL Server 2000, IIS 6. Memory, CPU etc looks fine.
> Thanks!
> /Tore
>
> "pfrank61" wrote:
> > No Jay, they are on the same network and even sometimes I ask them to login
> > into the same computer.
> > I must also add that when slowness happens and I reboot the SQL server
> > everything normalizes again.
> >
> > Thanks
> > Pablo
> >
> > "Jay" wrote:
> >
> > > Are the two groups connecting using different networks?
> > >
> > > "pfrank61" <pfrank61@.discussions.microsoft.com> wrote in message
> > > news:FAF8FA18-A4E9-42CC-8FA5-4EC4FD77B08B@.microsoft.com...
> > > > Hi,
> > > > I have mssql 2000 std sp4 running on w2k3 std sp2. intermittently users
> > > > experience slowness in accessing the database but users with elevated
> > > > access
> > > > such as domain admins never experience such slowness when everybody else
> > > > is
> > > > sufferring.
> > > >
> > > > Please help i am going insane.
> > > >
> > > > Thanks
> > > > Pablo
> > >
> > >
> > >

Friday, March 9, 2012

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.

> Raid
> 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:

> Raid
> 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.
>
> 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...
> 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/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_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...
> you
> Let's assume that it is optimized. Where to next?
>

Wednesday, March 7, 2012

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 stre
ss
> 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 i
f
> 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.

> Raid
> 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 dea
l
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:

> Raid
> 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.
>
> 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...
> 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/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_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...
> you
> Let's assume that it is optimized. Where to next?
>

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?
>

Monday, March 26, 2012

Performance depending on user rights?

Wednesday, March 21, 2012

performance bottleneck?

performance bottleneck?

performance bottleneck?

Monday, March 12, 2012

Performance and Detection

Performance and Detection

Performance and Detection

performance and #of users

Performance affecting users only

Performance affecting users only

Performance affecting users only

Friday, March 9, 2012

Performance

Wednesday, March 7, 2012

Performance

Performance

Percentage of total

Blog Archive

About Me