SQL Server performance tuning best practices for DBAs
Tuning database performance is a complex process, but consultant Joey D'Antoni details a list of SQL Server performance tuning best practices that can make it easier.
SQL Server performance tuning is a complex topic due to how layered it is. While performance problems can often be traced back to data storage issues, the performance tuning process is a "holistic effort" that should be examined from all sides of a SQL Server environment, according to database consultant Joey D'Antoni.
For starters, query tuning is not the same thing as performance tuning overall, D'Antoni said in a recent webinar on SQL Server performance tuning best practices. It's important that queries run quickly and efficiently, but equating the two can lead to a misdiagnosis of performance issues in a SQL Server system, he cautioned.
Database administrators (DBAs) still need to understand the impact that queries have on overall database server performance, though -- and vice versa. "You can have all the query optimization in the world, but if you're using junk hardware, it doesn't matter," said D'Antoni, who is a principal consultant at Denny Cherry & Associates Consulting in Oceanside, Calif. On the other hand, sometimes issues that look like hardware problems are instead directly related to badly written queries, he added.
Establish a performance baseline
A performance baseline is the most important thing to measure for properly tuning a database, D'Antoni advised. And because having a baseline is integral to SQL Server performance tuning, DBAs should establish one early on, he said. Once you've done so, you can determine problem areas by comparing the baseline statistics to SQL Server's current performance.
CPU utilization isn't necessarily the key aspect of a performance baseline, but it is the largest factor and must be included, according to D'Antoni. Determining how the CPUs in a system are being utilized will show how busy your server is at any given time and, when checked against the baseline level, indicate which areas should be altered or fixed. For example, he said high CPU utilization is frequently caused by bad code or bad queries.
Other metrics that should go into the performance baseline include I/O latency and how long it takes to run important reports or administrative commands like checkdb, D'Antoni said. While it's possible to create baselines yourself manually, he recommended using a performance monitoring tool to speed the process. Cloud portals are also good sources of baseline information on database servers that run in the Azure, AWS or Google clouds.
Database performance tuning to-do items
Know your SQL Server system resources
D'Antoni said SQL Server performance tuning best practices also include knowing the limits of your system resources -- or, as he put it, "knowing what your computer is made of." In particular, system memory is finite and must be divided up among the different programs that need to run on a server. Data is pulled into RAM or the SQL Server buffer pool for processing, and SQL Server also uses memory to join queries together, rebuild indexes and sort data.
It's important to understand what waits matter and what waits are benign.
Joey D'Antoniprincipal consultant, Denny Cherry & Associates Consulting
To avoid performance problems, be sure not to overallocate the available memory in a SQL Server system, D'Antoni said. He warned that if there isn't enough memory for workloads, you'll wind up putting stress on the storage devices in the system, potentially slowing down I/O throughput. Your network is another limited resource to take into account when tuning SQL Server performance, he added.
In addition to checking things like CPU utilization, I/O latency and memory usage, D'Antoni advised SQL Server DBAs to use the wait statistics that database systems collect.
SQL Server tracks and logs what each thread is waiting on whenever queries and other jobs are temporarily blocked from running. Aggregating these statistics can help you analyze and prevent performance bottlenecks. "It's important to understand what waits matter and what waits are benign," D'Antoni said.
Performance tuning questions to ask
In the webinar, which was hosted by database tools vendor Idera, D'Antoni said you also must ask -- and answer -- the following questions as part of the performance tuning process:
Is anything else running on my server?
Is an antivirus program unintentionally hindering the progress of processing jobs?
Microsoft ships SQL Server and all related products in the same installation file. As a result, users can easily install technologies like SQL Server Analysis Services, Integration Services and Reporting Services on the same server as their SQL Server database engine.
This isn't inherently a bad thing, in D'Antoni's view, but it takes memory and other system resources away from SQL Server itself. "In an ideal world, we'd run each component on its own individual server," he said. Unfortunately, licensing issues make that difficult, if not impossible. DBAs need to plan accordingly when working to tune SQL Server's performance, he added.
While antivirus software is designed to help organizations avoid data breaches and other security problems, it can also slow down SQL Server performance, D'Antoni said. He advised that you make sure an antivirus program isn't scanning MDF, LDF or NDF files in a SQL Server database in order to reduce the amount of I/O operations being processed on the server.
If you run SQL Server in VMware or another virtualized environment, your VM host and hypervisor configuration can negatively affect system performance, too, D'Antoni cautioned. In the case of VMware, DBAs should have access to vCenter Server, the virtualization platform's server management software, so they can see how the hypervisor is set up and what resources are allocated to it. "If you're running VMware, you want to have insight into vCenter, even if it's read-only," he said.
