client/server performance problems

We have 1000 users on a client/server CRM application that we've written in
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
Doug
My guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
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
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>