Tip # 4 – Not having a Baseline
Top 10 Tips for SQL Server Performance and Resiliency
This article is part 4 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is related to the most common errors I see and is not all inclusive. If you like this post check out the other tips already posted:
Most common mistake #4: Not having a Baseline
Baselines can come in many different forms, however none of them are overly complex to understand. The core principal of a baseline is having a known set of metrics which can be used to make a comparison. A common and effective trouble shooting technique when something goes wrong is review what has changed. However, if you don’t have a starting point showing you what the system was like before something changed, finding what has changed is much more difficult.
Performance Baseline
I am often reviewing systems after they have started to exhibit performance issues. My goal with these type of engagements is to return the server to normal or better than normal performance as quickly as I can. Without a performance baseline of how the system runs under normal circumstances, leaves whoever is troubleshooting the performance issues without a valuable tool.
At the basic of level, SQL Server performance can be grouped into 3 categories where a performance issue may lie; hardware, operating system and SQL Server. Consider having a performance monitor counter run that includes some basic counter that could be relevant to each section.
Memory\Available Bytes
Memory\Pages/sec
PhysicalDisk(*)\Avg. Disk sec/Read
PhysicalDisk(*)\Avg. Disk sec/Write
PhysicalDisk(*)\Current Disk Queue Length
PhysicalDisk(*)\Avg. Disk Queue Length
Processor(_Total)\% Processor Time
SQLServer:Access Methods\Full Scans/sec
SQLServer:Access Methods\Index Searches/sec
SQLServer:Buffer Manager\Page life expectancy
SQLServer:General Statistics\User Connections
SQLServer:Latches\Average Latch Wait Time (ms)
SQLServer:Locks(_Total)\Lock Wait Time (ms)
SQLServer:Locks(_Total)\Lock Waits/sec
SQLServer:Locks(_Total)\Average Wait Time (ms)
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
SQLServer:Plan Cache(_Total)\Cache Hit Ratio
SQLServer:SQL Statistics\Batch Requests/sec
SQLServer:SQL Statistics\SQL Compilations/sec
SQLServer:SQL Statistics\SQL Re-Compilations/sec
System\Processor Queue Length
System\Context Switches/sec
When you leave tasks to be run manually, such as taking a baseline each month, there is a risk of someone forgetting or the task being put aside for higher priorities. Consider scheduling your performance monitor data collections.
Configuration Baseline
The baseline you have shouldn’t stop at the performance monitor. The configuration of your SQL Server with a document explaining some of the non-standard configurations should be done as well.
Recently I was working on a performance problem for a client who has had their SQL Server online for years. The server had been slowing down over time and the client needed the performance to be better. When we were looking at the database configuration, we noticed the compatibility level on the database was set to SQL Server 2000 even though the SQL Server instance was 2008. Everyone who worked in the IT department when the server was configured had moved on a few years prior. The server and a number of settings configured that were not default, some of the settings were not best practice. The only way we could determine if the setting was correct or if it was just something someone overlooked was to make the change back to the default and complete regression testing to see the impact. This added a lot of time and delay to correcting the overall performance issue. A configuration baseline would have saved a lot of time and money. I recommend doing a health check\baseline on your SQL Servers once a year.
Top 10 Tips for SQL Server Performance and Resiliency
Chris Shaw's Weblog 
[…] Not having a Baseline […]