Tuning SQL Server With Aireforge Studio Advise
Configuring SQL Server to run at peak performance is a skill that can take years to learn. But when you need to configure and maintain tens, hundreds or even thousands of instances, it's a task that can overwhelm even the most seasoned DBA.
The Advise module for Aireforge Studio helps to combats this. Advise analyzes your SQL Server instances and provides suggestions for optimization and best practices. Here, we'll take a look at some of its key features and how it can improve the performance, security and stability of your SQL Server estate.
Advise offers support for all versions of SQL Server, as well as Azure SQL Database and Azure Managed Instances. It combines expert database analysis with a focus on ease of use. At Aireforge, our extensive database management experience underpins Advise's recommendations. We've taken fundamental operational checks, along with best practices from our experience working on mission critical and high-transaction databases, and packaged them into dynamically generated scripts you can run on your own estate. The tool provides a reliable and time-efficient alternative to writing and maintaining your own diagnostic scripts.
Feature Highlights
Advise supplies over 50 different warnings and suggestions. Let's take a look at five key areas.
1. Index Management
Poor indexing can severely hit performance. A lack of useful indexes can lead to excessive scanning of indexes, tables and heaps, potentially crippling the I/O subsystem. Inefficient or fragmented indexes will also result in excessive I/O and CPU usage, as fragmented indexes will use a greater number of pages, missing columns will require another table hit / lookup and missing indexes could result in inefficient query plans and / or expensive table scans.
It's critical to ensure your tables are indexed properly so that data can be quickly accessed. At the other extreme, duplicate indexes or overlapping indexes require more resources to maintain the duplicated data, increasing the amount of processing needed when data is inserted or modified. Duplicate indexes can also result in sub-optimal execution plans as more indexes means more options for the optimizer and a greater risk of timing out before a "good enough" plan is found.
Getting index management right will result in major performance improvements and Advise will suggest changes to speed up your queries and free up resources by removing duplicates and merging overlapping indexes.
Advise detects duplicate indexes, where the same columns are indexed in the same order, giving you the dynamically generated script to drop the duplicate. It also highlights superseded indexes. This is where index A and index B are indexed on identical columns, but B has additional columns. Where A and B have 'included columns', Advise may suggest a creating a third index to replace A and B. Data from the missing index statistics are also factored into these checks, suggesting additions to existing indexes where possible or completely new indexes if required.
Advise also detects disabled, unused or hypothetical indexes, that are consuming resources but simply aren't used. Dropping these kinds of indexes are quick wins that can save huge amounts of storage and again, give the optimizer an easier time.
Screenshot 1: comparing indexes in Aireforge Studio.*
Screenshot 2: choosing analysis options in Aireforge Studio.
2. Configure SQL Server's Automatic Behavior and Settings
Many of the default settings for SQL Server haven't been updated in years, despite the increased speed of new hardware. Outdated default settings can cause poor performance and operational issues, so Advise looks at the SQL Server settings and suggests adjustments. This includes:
- Recommending min/max memory allowance.
- Recommended trace flags.
- Operating system best practices.
Advise also ensures that non-default settings have not been used that could impact the performance, stability and integrity of a server. Including:
- Checking whether auto shrink and auto close are enabled (both of which can cause massive performance issues if misconfigured).
- Ensuring auto statistics is enabled to support the optimizer in creating more efficient query plans.
3. Optimize Parallelism Settings
One of the jobs of the SQL Server query optimizer is planning how to run queries on multiple threads. Advise will recommend an optimal parallelism cost threshold. This is the threshold that SQL Server uses to determine when to run a query using multiple threads. A low setting can cause the optimizer to waste time planning multi-threaded queries. A high one can lead to all queries only ever using a single thread. Choosing the optimal cost threshold for parallelism is a delicate balance and Advise helps you tune it correctly.
4. Database File Management
Running out of space when writing to the database file can cause a major service outage. Catching this issue early is critical so Advise warns if a database file is nearly full. Advise will also make best practice suggestions such as an optimum file count per CPU core. It also alerts you if data files in the same group have differing sizes, or if a data file has excessive free space. These checks are applied to all databases, including tempDB.
5. Database Monitoring and Log Analysis
Advise reviews the SQL Server logs to check for possible problems, or opportunities for improvement. This includes alerts if an I/O request takes longer than 15 seconds to complete or if the server is constrained by low memory. These alerts can be the difference between catching performance issues before anyone notices and unplanned downtime that disrupts your entire user base. Advise Makes Tuning Simple
Advise has many other checks in addition to the ones mentioned above, but common to all of them is that as well as dynamically generated SQL, you're given human-readable information to understand what the suggested action is, how to take it, and why it's being shown to you at all. This way, you can not only optimize your database, but have confidence while doing it.
Every check has come from our experience working on real production databases, and we routinely use it in our consulting work in high-performance and high-transaction enterprise solutions. Advise is written by busy DBAs, for busy DBAs. For more information on Aireforge Studio, visit our homepage.