A main focus for database administrators (DBAs) is to ensure server environments are optimized and performance is at its peak. Whether you’re a DBA starting in a new role and are evaluating an existing environment for the first time, or you’re a senior database professional with the ongoing task of maintaining optimal performance, following key SQL Server best practices for installing, configuring, and ensuring new instances of SQL Server are consistently deployed is all in a day’s work.
However, over time, skew can occur and configuration changes happen that may cause possible performance degradation. Use this daily SQL Server best practice checklist for DBAs to keep an eye on and stay ahead of common SQL Server performance issues—some day-to-day activities you may already know and some not-so-common tasks to check..
Power Plan
The Windows power plan lets an administrator choose a balance between high performance and saving energy. Your power plan is one of the easiest things to change and has a huge impact on performance. If the power plan isn’t set correctly, the server is only using 70% of the CPU for your SQL Server workloads. This option should be audited on every server, whether it runs on bare metal or a virtual host. This applies to both hardware and software.
For hardware, you need to take this a step further and adjust your power management on the hardware in the main system BIOS setting, which allows the processors to manage their own power states.
All SQL Servers (software) should be run on a server with the power plan set to “High Performance.” By default, Windows Server sets the “Balanced” power plan, which enables energy conservation by scaling the processor performance based on current CPU utilization. In the latest generation CPUs, the clock is throttled down to save power (Processor P-state) and increases only when CPU utilization reaches a certain point.
If a server requires ultra-low latency, invariant CPU frequency, or the very highest performance levels, such as a database server like SQL Server, it’s helpful for the processors to refrain from switching to lower-performance states. The High-Performance power plan provides this assurance and caps the minimum processor performance state at 100 percent.
Virtual Log Files (VLFs)
Every transaction log is composed of smaller segments called virtual log files. Every time a growth event occurs, a new virtual log file is created at the end of the transaction log file (Figure 1).
Since having many VLFs at one time can slow things down, you should check the number routinely for each of the databases within the instance, including Tempdb as it can have significant performance impact. Reducing the creation of VLFs also helps to improve startup time when the database has to go through recovery.
Figure 1: Relationship of virtual log files to the life of the transaction log
What causes high numbers of virtual log files? As transactions force the transaction log to grow, inappropriate log file sizing or auto-growth settings can cause a high number of VLFs to be created. Each growth event adds VLFs to the log file. The more often you grow in conjunction with smaller growth segments, the more VLFs your transaction log will have.
To reduce the number of VLFs, the transaction log file should be shrunk until there are only two VLFs, then grown in 8000 MB chunks back to the current size. The VLF counts should ideally be under 100. A higher number indicates a problem you should address.
Keeping the high counts of VLFs at bay starts with the proper auto-growth settings. Revisiting these settings to ensure the growth rates are appropriate for your log file is essential to reducing VLF counts. All databases should grow based on a fixed-size MB increment, not a percentage.
Services
It’s common to inherit a SQL Server instance with all SQL Services installed. This happens when SQL Server is installed via a GUI, and every available option is checked, then Next is continually clicked until the install. If this is your environment, take a moment to evaluate and decide which services are required (Figure 2).
Figure 2: Partial list of available services
To preserve good performance, it’s important to run only the necessary services, as each of these services can consume server resources. Sharing resources reduces those available to the SQL Server engine. All services not associated with the engine should be run on a separate server.
Sometimes, due to licensing concerns, this isn’t a viable solution. However, be sure you’re knowledgeable about how each configuration setting, such as memory, may need to be adjusted when other services share these resources.
The max memory set inside SQL Server doesn’t impact other services like SSIS, SSAS, or SSRS. Those services take their memory from the operating system allocation, which in turn can take memory from the SQL Server engine. Be sure to allocate enough to each service. (You may end up spending more in RAM than in licensing, especially if you can run the standard edition for BI tools like SSRS or SSIS.)
Take a moment and look to see what services you’re running versus what you’re using. If you find SSIS, for example, is running, and you’re not using it in your environment, turn off the service. Take the time to set it to DISABLED and not just STOP the service. Stopping the service isn’t enough if it’s set to start automatically. When the server is rebooted, the service will diligently start, and you don’t want that. Disable the service to prevent this from occurring.
Indexes
Lastly and most importantly are indexes. Indexes can be incredibly beneficial to your database performance; however, they come with a cost—indexes both consume storage space and affect performance during inserts and updates. Therefore, an important part of your index maintenance is to periodically check whether your indexes are being used. Many times, indexes are created in the belief they are needed, but in fact are never used. You can reduce I/O overhead on inserts and updates by removing unnecessary indexes.
If you’re writing to your index and never or infrequently reading (seeks and scans) from it, you may want to consider disabling and then dropping it. It takes a lot of resources to update, store, and maintain indexes, so avoid wasting resources. Also, the index usage statistics available in dynamic management views (DMVs) are only as good as your last SQL Server service restart.
Tool Up to Sync Up
This blog covers some daily DBA activities to help ensure best practices are followed when properly installing and running SQL Server. Given its importance to an organization, it’s critical for this core database to run quickly and efficiently. If you’re not using database performance management tools as a DBA, you could also be missing some deviations from best practices. SolarWinds® SQL Sentry® and SolarWinds Database Performance Analyzer (DPA) can help you monitor for performance regressions over time and more easily check for best practice deviations.
Get started with a free trial of SQL Sentry and DPA to see for yourself what they can do.
