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.
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.
MemoryAvailable Bytes
MemoryPages/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 MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec
SQLServer:Buffer ManagerPage life expectancy
SQLServer:General StatisticsUser Connections
SQLServer:LatchesAverage 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 ManagerMemory Grants Pending
SQLServer:Memory ManagerTarget Server Memory (KB)
SQLServer:Memory ManagerTotal Server Memory (KB)
SQLServer:Plan Cache(_Total)Cache Hit Ratio
SQLServer:SQL StatisticsBatch Requests/sec
SQLServer:SQL StatisticsSQL Compilations/sec
SQLServer:SQL StatisticsSQL Re-Compilations/sec
SystemProcessor Queue Length
SystemContext 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 checkbaseline on your SQL Servers once a year.
If you have questions on getting a baseline on your server or need assistance with your baseline or SQL Server in general, reach out to us! Myself and XTIVIA can assist you with adding resiliency for your business. Please don’t miss my other blogs regarding this topic. Contact Us
Top 10 Tips for SQL Server Performance and Resiliency