The importance of SQL Server Performance Baselines
No one loves to hear they have performance problems, especially when it comes to their production SQL Server databases. If a database performance problem should occur knowing where to start troubleshooting can be difficult. Once a SQL Server performance abnormality is found, how do you know if it’s the root cause or something that’s been there for a much longer time. The answer is without SQL server performance baselines, you can never know for sure.
Let’s say you found an issue with the storage servicing your MS SQL Server database files where latency was 300ms, or the same speed as a 1.44 MB floppy drive. This is a significant concern and should be addressed immediately; but, is it the root cause of your issue that started this morning? With previously established SQL Server performance baselines, you may see that latency has always been this high, or even higher, which would indicate that storage is not the root cause for today’s issue. Don’t get me wrong. Storage latency of 300+ ms is a major concern and should be addressed. What I’m saying is that it’s likely not the root cause based on our SQL Server baseline analysis. Ruling out disk latency as the immediate concern, you continue to compare SQL Server baseline numbers and find that latch waits are much higher. Focused on the emergent issue, you work on the resolution for the likely cause of today’s problem.
Now imagine that no SQL Server baselines existed. You’d need to start by resolving the disk latency which you found first. Next you find an issue with blocking and resolve this via code. Then the next issue and the next. The time it could take to resolve the issue may be much longer than when using SQL Server baselines for confirmation.
Now that we understand the importance of a baseline, we should discuss what to collect and over the next few weeks we’ll discuss each area of the server and instance so you understand what to look for and when to look for it. Here’s a high-level list of the items we’ll be discussing in this series.
Microsoft SQL Server Baselines – Metrics that matter
SQL Server O/S & Hardware baseline metrics SQL Server database baseline metrics
-Physical Disk -Access Methods
-Logical Disk -Buffer Manager
-Memory -Buffer Node
-Network -General Statistics