Performance counter quest

Hi,
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech.
I am running Performance monitor from another machine which is windows 2000 terminal , to monitor the above mentioned sqlserver. The output of the csv file showing total processor time > 300. I dont understand why it is showing instead of values < 100. I
n that machine i can see .NET CLR counters also. Is this behaviour because of .NET?
(PDH-CSV 4.0) (Pacific Daylight Time)(420)\\AB01\Process(_Total)\% Privileged Time\\AB01\Process(_Total)\% Processor Time
4/13/04 5:00 AM349.7912317399.6868476
4/13/04 5:00 AM350399.8958333
4/13/04 5:00 AM399.7918835399.8959417
4/13/04 5:01 AM343.8541667399.6875
How can i take the processor time to calculate cpu usage? Any help appreciated
thanks
kris
Look at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/ar...LCounters.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance counter quest

Hi
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech
I am running Performance monitor from another machine which is windows 2000 terminal , to monitor the above mentioned sqlserver. The output of the csv file showing total processor time > 300. I dont understand why it is showing instead of values < 100. In that machine i can see .NET CLR counters also. Is this behaviour because of .NET
(PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\% Privileged Time \\AB01\Process(_Total)\% Processor Tim
4/13/04 5:00 AM 349.7912317 399.686847
4/13/04 5:00 AM 350 399.895833
4/13/04 5:00 AM 399.7918835 399.895941
4/13/04 5:01 AM 343.8541667 399.687
How can i take the processor time to calculate cpu usage? Any help appreciate
thank
kriLook at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/articles/SQLCounters.aspx
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance counter quest

Hi,
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech.
I am running Performance monitor from another machine which is Windows 2000
terminal , to monitor the above mentioned sqlserver. The output of the csv f
ile showing total processor time > 300. I dont understand why it is showing
instead of values < 100. I
n that machine i can see .NET CLR counters also. Is this behaviour because o
f .NET?
(PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\% Privileg
ed Time \\AB01\Process(_Total)\% Processor Time
4/13/04 5:00 AM 349.7912317 399.6868476
4/13/04 5:00 AM 350 399.8958333
4/13/04 5:00 AM 399.7918835 399.8959417
4/13/04 5:01 AM 343.8541667 399.6875
How can i take the processor time to calculate cpu usage? Any help appreciat
ed
thanks
krisLook at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/a...QLCounters.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan:).
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003, using
>> 4
>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
>>Did you update the stats after the restore on the dev machine? If not the
>>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003,
>> using
>> 4
>>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

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

Performance between Server and PC

Hi All,
Recently we have an application running for 9 hours+ on an HP DL760 G2 with
8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
tested with the same database and application on a normal pc which only have
2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
took about 5 hours to complete. Both server and PC is using Windows 2003
Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
According to the client, their normal operation is working fine. The issue
only happen on that application. But it is weird that the same application
running on normal PC is faster than the server. We just reformat that server,
but the result still took 9hours + to complete.
does anyone know what happen? I have checked all the counter, everything
seem works fine. But I found that the process is pretty fast for 1st 4 hours
then the performance will start degrade.
I also enabled AWE on that server with setting the max memory to 9.6GB RAM
(80% of 12GB). did I leave out something?
I have also tested on HP ML530 G2, Dual Processors and 2GB RAM with RAID 5
(3 HDD 10K RPM). The application still take about 9+ hours to complete.
Any advice is much appreciated. Thanks.
--
Regards,
VenedictVenedict wrote:
> Hi All,
> Recently we have an application running for 9 hours+ on an HP DL760 G2 with
> 8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
> tested with the same database and application on a normal pc which only have
> 2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
> took about 5 hours to complete. Both server and PC is using Windows 2003
> Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
> Any advice is much appreciated. Thanks.
When you say "application", what are you referring to? You make
references to the application "completing", which leads me to believe
this is a report or stored procedure.
Have you looked at sysprocesses while this "application" is running? My
hunch is that query parallelism is causing this. If you look at
sysprocesses, you'll likely see several threads with the same spid, some
with a waittype of CXPACKET. You might want to experiment with
different degrees of parallelism.

Performance between Server and PC

Venedict wrote:
> Hi All,
> Recently we have an application running for 9 hours+ on an HP DL760 G2 wit
h
> 8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
> tested with the same database and application on a normal pc which only ha
ve
> 2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it onl
y
> took about 5 hours to complete. Both server and PC is using Windows 2003
> Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
> Any advice is much appreciated. Thanks.
When you say "application", what are you referring to? You make
references to the application "completing", which leads me to believe
this is a report or stored procedure.
Have you looked at sysprocesses while this "application" is running? My
hunch is that query parallelism is causing this. If you look at
sysprocesses, you'll likely see several threads with the same spid, some
with a waittype of CXPACKET. You might want to experiment with
different degrees of parallelism.Hi All,
Recently we have an application running for 9 hours+ on an HP DL760 G2 with
8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
tested with the same database and application on a normal pc which only have
2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
took about 5 hours to complete. Both server and PC is using Windows 2003
Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
According to the client, their normal operation is working fine. The issue
only happen on that application. But it is weird that the same application
running on normal PC is faster than the server. We just reformat that server
,
but the result still took 9hours + to complete.
does anyone know what happen? I have checked all the counter, everything
seem works fine. But I found that the process is pretty fast for 1st 4 hours
then the performance will start degrade.
I also enabled AWE on that server with setting the max memory to 9.6GB RAM
(80% of 12GB). did I leave out something?
I have also tested on HP ML530 G2, Dual Processors and 2GB RAM with RAID 5
(3 HDD 10K RPM). The application still take about 9+ hours to complete.
Any advice is much appreciated. Thanks.
--
Regards,
Venedict|||Venedict wrote:
> Hi All,
> Recently we have an application running for 9 hours+ on an HP DL760 G2 wit
h
> 8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
> tested with the same database and application on a normal pc which only ha
ve
> 2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it onl
y
> took about 5 hours to complete. Both server and PC is using Windows 2003
> Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
> Any advice is much appreciated. Thanks.
When you say "application", what are you referring to? You make
references to the application "completing", which leads me to believe
this is a report or stored procedure.
Have you looked at sysprocesses while this "application" is running? My
hunch is that query parallelism is causing this. If you look at
sysprocesses, you'll likely see several threads with the same spid, some
with a waittype of CXPACKET. You might want to experiment with
different degrees of parallelism.

Performance

I have a SQL server 7.0 which is installed on a dual
processors machine (NT4.0), when I try to do a query its
fairly slow, I have checked CPU utilization and memory
they are ok, is there anyway to find out what is wrong
here?
Also is there any tool I can check SQL server performance?
thanks in advance-RobCheck the query plan, also use profiler to see
the statistics of the query, such as reads, writes, etc.
>--Original Message--
>I have a SQL server 7.0 which is installed on a dual
>processors machine (NT4.0), when I try to do a query its
>fairly slow, I have checked CPU utilization and memory
>they are ok, is there anyway to find out what is wrong
>here?
>Also is there any tool I can check SQL server performance?
>thanks in advance-Rob
>.
>|||I am new this area, what is the query plan and how can I
use the profiler.
Thanks-rob
>--Original Message--
>Check the query plan, also use profiler to see
>the statistics of the query, such as reads, writes, etc.
>>--Original Message--
>>I have a SQL server 7.0 which is installed on a dual
>>processors machine (NT4.0), when I try to do a query its
>>fairly slow, I have checked CPU utilization and memory
>>they are ok, is there anyway to find out what is wrong
>>here?
>>Also is there any tool I can check SQL server
performance?
>>thanks in advance-Rob
>>.
>.
>|||Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
newsgroup post.
The query plan will show you problems like Table Scans that can be solved
with more selective where clauses and/or applying indexes to table in
question.
There is obviously much more to it than that, but there is a start for you.
Also, visit www.sql-server-performance.com
Cheers,
Greg Jackson
Portland, OR|||Hi Rob,
You may also want to refer to these articles that may help you troubleshoot performance issues.
224587 HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/?id=224587
This step-by-step article describes how to troubleshoot SQL Server performance issues. Troubleshooting
performance issues involves the use of a series of steps to isolate and determine the cause of an application
slowdown.
~~~
243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
This article describes how to handle a performance issue that applications may experience in conjunction with
Microsoft SQL Server: slow performance of a specific query or group of queries.
~~~
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
This article describes how to troubleshoot the slow performance of many concurrent ad-hoc queries.
~~~
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453
This article focuses on how to monitor SQL Server to capture pertinent system information and how to analyze
that information to successfully resolve blocking issues.
~~~
Sincerely,
William Wang
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Jaxon" <GregoryAJackson@.hotmail.com>
> References: <045401c3a960$5eccf1f0$a301280a@.phx.gbl> <037501c3a964$e45d7660$a401280a@.phx.gbl>
<05b301c3a966$30440bb0$a101280a@.phx.gbl>
> Subject: Re: Performance
> Date: Wed, 12 Nov 2003 16:31:22 -0800
> Lines: 19
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> Message-ID: <OKLL41XqDHA.2964@.tk2msftngp13.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: 66.203.99.51
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316205
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Lookup Graphical Query Plan in Books on Line. Too hard to explain in a
> newsgroup post.
> The query plan will show you problems like Table Scans that can be solved
> with more selective where clauses and/or applying indexes to table in
> question.
> There is obviously much more to it than that, but there is a start for you.
> Also, visit www.sql-server-performance.com
>
> Cheers,
>
> Greg Jackson
> Portland, OR
>
>

performance

hi all
how can I increase the speed of an SQL Server damaticly ?
configuration:
W2k, SQL Server 2000, 2 processors (1.4GHz)
table:
1000000 records
queryresult:
450000 records
time:
11s
i tried to set indexes on fields where i'm search for. but
there was not a lot of speed available. for my oppinion
it's a very poor value for a sql server to find 450000
records!!!
any hints to optimize the query or the server ?
thankx a lot
tomWhere do you issue that query from? You still have to move those 450,000
rows from the server to your client and that is going to take some time.
Even on my local machine, if I do SELECT * FROM table, where that table has
300,000 rows and a lot of columns it takes 40 seconds for the results to
show in Query Analyzer, but SELECT COUNT(*) is sub second, even when I make
sure that it uses a table scan by including a where clause on a non indexed
column that returns all rows.
--
Jacco Schalkwijk
SQL Server MVP
"thomas.marti@.netrics.ch" <anonymous@.discussions.microsoft.com> wrote in
message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
> hi all
> how can I increase the speed of an SQL Server damaticly ?
> configuration:
> W2k, SQL Server 2000, 2 processors (1.4GHz)
> table:
> 1000000 records
> queryresult:
> 450000 records
> time:
> 11s
> i tried to set indexes on fields where i'm search for. but
> there was not a lot of speed available. for my oppinion
> it's a very poor value for a sql server to find 450000
> records!!!
> any hints to optimize the query or the server ?
> thankx a lot
> tom|||it's on the same machine. so i think that it cannot be so
slow isnt't it?
but how do other people work with lots of records? one
million records is not really a big amount for a
database!!!
>--Original Message--
>Where do you issue that query from? You still have to
move those 450,000
>rows from the server to your client and that is going to
take some time.
>Even on my local machine, if I do SELECT * FROM table,
where that table has
>300,000 rows and a lot of columns it takes 40 seconds for
the results to
>show in Query Analyzer, but SELECT COUNT(*) is sub
second, even when I make
>sure that it uses a table scan by including a where
clause on a non indexed
>column that returns all rows.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"thomas.marti@.netrics.ch"
<anonymous@.discussions.microsoft.com> wrote in
>message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
>> hi all
>> how can I increase the speed of an SQL Server
damaticly ?
>> configuration:
>> W2k, SQL Server 2000, 2 processors (1.4GHz)
>> table:
>> 1000000 records
>> queryresult:
>> 450000 records
>> time:
>> 11s
>> i tried to set indexes on fields where i'm search for.
but
>> there was not a lot of speed available. for my oppinion
>> it's a very poor value for a sql server to find 450000
>> records!!!
>> any hints to optimize the query or the server ?
>> thankx a lot
>> tom
>
>.
>|||One million rows is not a lot for a database, but one million (or 450,000)
rows is a whole lot to return to your user. They are not going to look
through 450,000 rows just to find something.
As I said before the bottleneck is with getting the results to the client,
not with getting the rows from the server.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:093b01c3c0b9$50a63b20$a401280a@.phx.gbl...
> it's on the same machine. so i think that it cannot be so
> slow isnt't it?
> but how do other people work with lots of records? one
> million records is not really a big amount for a
> database!!!
> >--Original Message--
> >Where do you issue that query from? You still have to
> move those 450,000
> >rows from the server to your client and that is going to
> take some time.
> >
> >Even on my local machine, if I do SELECT * FROM table,
> where that table has
> >300,000 rows and a lot of columns it takes 40 seconds for
> the results to
> >show in Query Analyzer, but SELECT COUNT(*) is sub
> second, even when I make
> >sure that it uses a table scan by including a where
> clause on a non indexed
> >column that returns all rows.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"thomas.marti@.netrics.ch"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
> >> hi all
> >> how can I increase the speed of an SQL Server
> damaticly ?
> >>
> >> configuration:
> >> W2k, SQL Server 2000, 2 processors (1.4GHz)
> >>
> >> table:
> >> 1000000 records
> >>
> >> queryresult:
> >> 450000 records
> >>
> >> time:
> >> 11s
> >>
> >> i tried to set indexes on fields where i'm search for.
> but
> >> there was not a lot of speed available. for my oppinion
> >> it's a very poor value for a sql server to find 450000
> >> records!!!
> >> any hints to optimize the query or the server ?
> >>
> >> thankx a lot
> >> tom
> >
> >
> >.
> >