Performance is always at the front of most DBA’s concerns -- mainly because the users make it that way. The DBA is always going to hear about it if the server is performing slowly. Hitting and maintaining your performance goals is essential to keeping your end users and management happy. However, keeping your SQL Server instances running well isn’t something you can look at once and then forget about it. Databases are constantly growing. Your end user workloads are not always predictable and they often change over time. Business demands and information requirements are continually changing. Plus, your application’s data access patterns can change every time your applications are updated. These sorts of factors make SQL Server performance a moving target.
The Importance of Benchmarking
Benchmarking your SQL Server systems can help you stay on top of your changing SQL Server environment. Not everyone does benchmarking but they should. Benchmarking is an essential tool for on-going monitoring as well as troubleshooting and capacity planning. While it takes a bit of time and effort regularly running benchmarks on your SQL Server instances enables you to record your system’s normal runtime characteristics which can help you to quickly identify anomalies when you run into performance issues.
Key Performance Metrics
Your primary tool for running benchmarks is Performance Monitor (perfmon.exe). Performance Monitor enables you to track both Windows Server and SQL Server performance metrics.
There are a lot of different performance counters for both Windows Server and SQL Server. Some key performance metrics that you should track for Windows Server include:
Processor: %Processor Time -- Tracks the time your server’s CPU usage
Available Mbytes – Tracks your system’s available memory
Network Interface:Bytes Total/sec – Tracks your total network utilization
Some key performance metrics that you should consider tracking for SQL Server include:
SQLServer:SQL Statistics:Batch Requests/sec – Tracks how busy your system is
SQLServer:Buffer Manager:Buffer Cache Hit Ratio – Tracks the percent of queries that are satisfied by the Buffer Pool cache
SQLServer:Access Methods: Full Scans/sec – Tracks how many full table scans are being performed
Learn more at IT/Dev Connections 2015!
Hopefully this gives you an idea of some of the key points that you should look at to hit your SQL Server performance goals. At this year’s IT/Dev Connections conference at the ARIA Resort in Las Vegas Ben Debow and David Klee will be doing a deep dive into SQL Server benchmarking in their session Benchmarking your SQL Server Environment. You can use the following discount code to get a cool $100 off your registration fee.
Discount code: ITDCOTEY15 ($100 off)
Register here: IT/Dev Connections 2015