Activity Monitor and Dynamic Management Views
Improvements to My Organization:
After reviewing a client's Microsft SQL environment, I have been able to setup specifics alerts and jobs in SQL Server Management Studio (SSMS) to definitely show them where they are experiencing network latency, disk latency, memory pressure, etc., etc. I have done this type of performance review for over 20 clients in the last 5 years
Room for Improvement:
When reviewing SQL processes it would be great if all of the local processes on the SQL server were marshaled to a remote SSMS client.
Use of Solution:
I have been using Microsoft SQL Server since 6.0
Setting up Microsoft SQL server performance alerts and jobs on a single server or in a Cluster is very straightforward. I typically start off looking at the following:
- Batch Request/Sec
- Buffer cache hit ratio < 96%
- CPU Utilization > 80 % sustained
- Page Life Expectancy (PLE)
- User Connections
- Lock Waits / Sec: _Total
- Process Lock/Block By
My objectives are to provide a baseline to determine database growth pattern to ensure capacity planning, stability, bottlenecks, etc., etc.