Performance

Recently useres have been complaing of slow reaponse times
from my SQL servers.
In some cases it has been down to high load, but in a lot
of cases it is due to low newtwork bandwidth.
I wrote a simple stored procedure that records how long it
takes to make 10 entries into a table. As the SP is
executed on the server, it is not effected by general
network traffic so i can easily and quickly see how the
server is performing by comparing the result to a
benchmark set when i know the server was running well.
Although this method is not ideal, it does give me a quick
referance and a quick answer to users, are there any
standard benchmarks for sql server, or any simple
comparisons that i can make to ensure performance is alway
peak.I do a number of things.
1. Every day from 10am to 11am (start of peak traffic) I run a "Worst
Offender" profile to capture our slowest Sproc Performance. I trend graph
these over time to indicate if we are heading into troubled waters.
2. PerfMon Counters (standard stuff) and trend those over time
IO - Average Disk Queue is the one I really watch here
CPU - CPU Utilization
RAM - Included here is Buffer Cache Hit Ratio
3. SQL Counters (Again Trend over time) - Page Splits, Full Scans,
DeadLocks, SP Recompiles, and others
4. I also check fragmentation levels at least once per week and defrag
indexes\tables as needed
5. furthermore, we have a monitor that pings our back end svcs and makes
requests. We track how long it takes to get a response back (again over time
trend patterns).
Hope this makes sense, if you need more specifics, I can point you to tons
of articles, etc.
Most of which are easily found on SQL-Server-Performance.com (I spend LOTS
of time at this site)
cheers
Greg Jackson
PDX, Oregon