Performance

I am trying to improve performance on my SQL 2000
Server. In my performance monitoring, my % processor
time hovers around the 35% to 40% mark. The memory seems
to be sufficient to support our needs, but users are
complaining that the system is slow.
I can identify which databases are being used that causes
the slow response time. I would like to add some indexs
on a few tables. Can I add indexes while users are in
the system?
Any other suggestions to decrease the % processor time,
besides upgrading the processors on the server?
Thank you,
JLFlemingHi
%30-40 avg processor time did not show that you have a bottleneck with
processor.
It seems like you have slow running queries or locking-blocking issues..
I think it would be better for you to analyze you're queries with sql
profiler. And start to optimize or re-write them.
Creating index on tables will block users to access to that tables until
index creation is complete.
And my suggestion is to decide which index to create according to your
queries.
You can use SQL Profiler to capture the trace's and Index Tuning Wizard to
analyze them. (You better use ITW against the db on the test server using the
trace file that you capture from production)
Regards..
"JLFleming" wrote:
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming
>|||You might want to have a looka t these below. Low processor utilization can
mean several things but one of the most common is that they are waiting on
Disk I/O to complete. If you have a disk bottleneck the processors can't do
much. The same is true for blocking. Have you run sp_who2 to see if anyone
is being blocked?
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
"JLFleming" <anonymous@.discussions.microsoft.com> wrote in message
news:066e01c50eac$063c8340$a401280a@.phx.gbl...
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming|||I have very little to add to what these guys have said,
however you may also need to look at Disk Raiding and
Network speed.
Other than that is your system used as a data input tool,
reporting tool or a bit of both.
Peter
peternolan67REMOVETHIS@.hotmail.com
"It's true hard work never killed anybody, but I figure,
why take the chance?"
Ronald Reagan
>--Original Message--
>I am trying to improve performance on my SQL 2000
>Server. In my performance monitoring, my % processor
>time hovers around the 35% to 40% mark. The memory seems
>to be sufficient to support our needs, but users are
>complaining that the system is slow.
>I can identify which databases are being used that causes
>the slow response time. I would like to add some indexs
>on a few tables. Can I add indexes while users are in
>the system?
>Any other suggestions to decrease the % processor time,
>besides upgrading the processors on the server?
>Thank you,
>JLFleming
>.
>|||Our system is mostly used as a data input tool. We do
some reporting, but not much.
>--Original Message--
>I have very little to add to what these guys have said,
>however you may also need to look at Disk Raiding and
>Network speed.
>Other than that is your system used as a data input
tool,
>reporting tool or a bit of both.
>Peter
>peternolan67REMOVETHIS@.hotmail.com
>"It's true hard work never killed anybody, but I figure,
>why take the chance?"
>Ronald Reagan
>
>>--Original Message--
>>I am trying to improve performance on my SQL 2000
>>Server. In my performance monitoring, my % processor
>>time hovers around the 35% to 40% mark. The memory
seems
>>to be sufficient to support our needs, but users are
>>complaining that the system is slow.
>>I can identify which databases are being used that
causes
>>the slow response time. I would like to add some
indexs
>>on a few tables. Can I add indexes while users are in
>>the system?
>>Any other suggestions to decrease the % processor time,
>>besides upgrading the processors on the server?
>>Thank you,
>>JLFleming
>>.
>.
>