Data is everything and, consequently, SQL is everywhere. The Structured Query Language has become the most used query language and it is supported by most modern relational database management systems. In fact, its use is so prevalent that we often refer to database servers as SQL servers.
The language defines how queries can be formed to extract from a database the precise data which is required. Given the multiplicity of modern databases with dozens of joined tables, SQL queries can be quite complex. With added complexity comes reduced performance. SQL query optimization allows one to fine-tune queries so that they return the right data as quickly as possible. Optimizing SQL queries can be done manually, but tools exist to assist in optimizing them. We’re about to review some of the best tools we’ve found.
Since we want everyone to be on the same page as we review the best products, we’ll start off by discussing the what and the shy of SQL query optimization. Then, we’ll discuss the main features of SQL query optimization tools. Different tools offer different features, as you’re about to see. We’ll do our best to sort out the essential features that you should be looking for when picking the best tool for your specific needs. And finally, we’ll review the best tools we could find.
Query Optimization in SQL Servers
Simply put, SQL query optimization is the act of analyzing SQL queries and determining the most efficient execution mechanism. It is often a trial-and-error process where different queries are tested to see which one offers the best performance, while still returning the sought after data. Query optimizers are sometimes built into database management systems but external, third-party tools are often thought to offer better performance results. A typical query optimizer will generate one or more query plans for each query, each of which is a mechanism used to run the query. The performance (i.e. execution time) of each is measured and the most efficient query plan is selected and used to run the query. While some SQL query optimization tools are simpler helper applications that require much human assistance, the best systems do most of their work in the background.
Here’s a quick example of query optimization. If a user runs a query that selects approximately half of a table’s data at a time when the server is heavily tasked with multiple simultaneous connections. In such a situation, the query optimizer could decide to use a query plan that uses the table indexes to satisfy the query, based on limited resources. Doing so can reduce the query’s drain on server resources. If the user ran the same query at another time when more server resources were available, the query optimizer could have determined that resource limitation was not an issue and not use table indexes, loading the full table in memory.
Main Characteristics Of SQL Query Optimization Tools
As we’ve determined, the main function of a SQL Query Optimizer tool is to try to determine the best way to run the query by analyzing different query plans. All of the tools reviewed here will at least to that but some offer much more functionality. Let’s have a look at some of the main features commonly found in SQL query optimization tools.
Basic SQL Tuning
This is the core functionality of SQL Query Optimization tools. It is the process of re-writing SQL statements differently with the goal of improving the performance of the query while still getting the same resulting data set. The tool works by measuring the query execution time of the various versions of the query (query plans) and picking the one that executes the fastest.
Database Engine Support
Most tools will at least support the most common database engines such as Microsoft SQL, Oracle, MySQL, PostgreSQL, and MariaDB. Others will support a wider selection and others yet will only support one. It is important to pick a tool which can support all the engines you’re using,
Cloud-based Database Support
Some SQL query optimization tools are able to analyze and optimize cloud-hosted databases such as Microsoft SQL Azure or AWS RDS. Cloud support varies greatly from tool to tool. Again, it’s important to pick one that supports what you have. Note that most tools supporting cloud databases will also support on-premises ones.
Best SQL Query Optimization Tools And Software
The time has finally come to reveal—and review—the best SQL query optimization tools and software we could find. Our list has many different types of tools, each bringing its own twist to optimization. Our main inclusion criteria were that each product at least helped manually optimize SQL queries—most do it automatically—and that they worked as advertised. We wouldn’t want to have you waste time on products that won’t do what they should.
Our first product is from SolarWinds, a company that has enjoyed an excellent reputation for making some of the best network and system management tools for most of it twenty-ish years of existence. Its flagship product called the SolarWinds Network Performance Monitor is viewed by many as one of the very best network bandwidth monitoring software. Some of SolarWinds’ fame also comes from the many free tools it offers. They are smaller tools, each addressing a specific task of network administrators. Two excellent examples of these tools are the Advanced Subnet Calculator and the Kiwi Syslog Server.
For SQL Query optimization, what you need, though, is the SolarWinds Database Performance Analyzer (DPA), a database management tool built for SQL query performance monitoring, analysis, and tuning. It monitors and analyzes your SQL database instances and, using a Response Time Analysis method which puts the primary focus on the time between a query requests and the corresponding response from the database, analyzes wait types and events to pinpoint the bottlenecks of databases.
- FREE TRIAL: SolarWinds Database Performance Analyzer
- Download Link: https://www.solarwinds.com/database-performance-analyzer
The tool, which is also great for troubleshooting features an easy to use interface that will assist you in finding issues quickly and easily. The main screen will let you view database instances, wait times, query advice, CPU (with warning and critical alerts), memory, disk and sessions.
When drilling down, the trend dashboard of a specific database instance shows you in a graphical format the total wait times (or your choice of average or typical day wait times) for users over the course of a month. On that graph, each colour represents an individual SQL statement, giving you a visually appealing representation of which statement takes the longest to run.
While this product is not a true SQL query optimization tools, some of its functions can help you with that. For instance, it can monitor and measure query execution time and it will also provide some tuning advice although it tends to be related to the database configuration rather than the query code.
The price for the SolarWinds Database Performance Analyzer starts at $1 995 and varies according to the number and type of database instances to monitor. Should you want to give the product a test run before purchasing it, a fully functional 14-day trial version is available.
2. SQL Query Tuner for SQL Diagnostic Manager
The SQL Diagnostic Manager is one of the top database monitoring tools available. But when complementing it with the SQL query tuner, what you get is one of the best SQL query optimization tools. Its benefits are multiple. It identifies problematic SQL queries via database profiling of wait time analysis. It can also give you automatically generated tuning recommendations with the SQL query tuning wizard. The tool will verify SQL queries performance through load testing in simulated production environments.
The SQL Query Tuner will quickly and easily identify SQL queries that cause poor database performance via database profiling and display a graphical visualization of wait time analysis. It will monitor an entire data source within a configurable span of time with continuous profiling. As aresult, you’ll gain a better understanding of how SQL Server plans to execute SQL queries and the various performance costs.
This tool will present automatically generated suggested solutions with the SQL query tuning wizard which provide an essential context for tuning SQL queries. It features color-coded index analysis of used, not used, and missing indexes with recommendations for optimum performance. You can generate possible cases and find the best alternative to a given SQL statement by including SQL query rewrites and hint injections.
The SQL Query tuner features unique visual SQL query tuning diagrams which let you understand the impact of SQL statements on the database using the diagrams instead of complicated execution plans. Indexes and constraints on tables and views are displayed with table statistics, and the joins used in a SQL statement (such as Cartesian joins, implied Cartesian joins, and many-to-many relationships) with the diagrams.
Verify the performance of existing and alternative SQL queries against the database via load testing. Configure SQL queries to run multiple times in parallel and to observe how they respond to simulated production environments without the risk of actually testing in production. Using this tool, you can tune poorly performing SQL queries for SQL Server from an intuitive interface.
The SQL Query Tuner is an add-on to the SQL Diagnostic Manager. It you don’t already own that product, both can be purchased together as the SQL Diagnostic Manager Pro for $2 156 per license a free, no-credit-card-required 14-day trial is also available from the product’s vendor
3. SQL Server Management Studio
If your database server is a Microsoft SQL Server, perhaps Microsoft’s own tool, SQL Server Management Studio (SSMS), is all you need. As you may know, the tool—which was first launched first launched with Microsoft SQL Server 2005—is used for configuring, managing, and administering all components within Microsoft SQL Server. It includes both script editors and graphical tools which work with objects and features of the serve. What you may not know is that one of SSMS’s tools, the SQL Server Database Engine Tuning Advisor (DTA) can be used for SQL query optimization.
The DTA’s primary purpose is analyzing databases and giving recommendations. According to Microsoft’s official documentation, the tool can help you troubleshoot the performance of a specific problem query, tune a large set of queries across one or more databases, perform an exploratory what-if analysis of potential physical design changes, and manage storage space.
One of the best things about SQL Server Management Studio is its price. This is a free tool from Microsoft which you can use for managing not only SQL Server but also Azure SQL Database. It might not be the most user-friendly tool out there but, given its price, it might be worth looking at.
EverSQL is interesting as it is one of the few online SQL optimization tools. Administrators can use this tool to get query tuning recommendations and the indexing improvement suggestions. Database support in EverSQL is somewhat limited. It does, however, support most popular paid and free and open-source database engines including MSSQL, Oracle, MySQL, MariaDB, and PerconaDB. In fact, since this is an offline tool that has no connection to your database, it could, theoretically, optimize and SQL query but it will do a better job if it knows what type of database engine will process the optimized query.
Using the tool is super simple. You connect to the site, select your database type and version, and paste the SQL query code. To get indexing recommendations and to allow the tool to apply advanced query transformations, you can optionally upload you database’s schema structure. You click “Next” and within seconds, you get the optimized version of your query along with some indexing recommendations.
EverSQL does not only do query optimizations, though. A few other free and useful online tools are also offered. There is, for instance, a SQL Query Syntax Check & Validator, a Query Minifier for SQL Statements, and a Query Formatter.
The Pricing structure of EverSQL is simple. You have a choice of a free plan which gives you one free query optimization per day for 30 days. This is the trial plan. Next, you have the Basic plan at $29/month which includes 10 monthly optimizations and the Plus plan at $135/month with 30 optimizations per month and live chat support. Customized plans can also be arranged to meet your specific needs.
5. SentryOne Plan Explorer
Plan Explorer is a free tool from SentryOne. It is one of the best SQL query analysis and optimization tools. This tool can help you quickly get to the root of the toughest SQL Server query problems. It is made of several modules. The Index Analysis uses scoring algorithms to help you determine the best index to support a given query. You can easily view recommended indexes, create and modify indexes, and detect and update old statistics.
The statistics analysis module shows you the impact of compiled and runtime query parameters on performance. This lets you quickly spot data skew that can contribute to parameter sniffing issues, visualize potential ascending key problems, and identify opportunities for filtered indexes.
The Query Performance Profiling and Playback module will replay queries and access their live performance statistics. Using this tool, you can play back the query profile to see exactly which operators are adding the most load on system resources. There’s no need to run the query again.
While most SQL query optimizers use estimated plan cost, Plan Explorer can display the actual observed costs. And you can see the cost difference by toggling the view between Estimated and Actual. There are more features to this free tool than we have time to present them. But since it’sa free tool, perhaps you’ll want to give it a try and see for yourself.
6. dbForge Studio for SQL Server
Devart’s dbForge Studio for SQL server is an all-in-one SQL server GUI tool which can be used for SQL Server management, administration, development, data reporting, analysis, optimization, and more. SQL developers and database administrators can use the GUI tool to speed up almost any complex database tasks such as designing databases, writing SQL code, comparing databases, synchronizing schemas and data, generating meaningful test data, among others.
dbForge Studio for SQL server includes the SQL Query Plan Tool, a dedicated query optimization tool. Its Execution plan diagram feature feature helps visualize and tune query execution plan by pinpointing the slow-executing nodes. The tool’s Wait Stats tab lets you easily detect potential bottlenecks in your query by displaying a list of events and waits associated with them. Yo may also use the tool’s plan tree to get information on how the SQL Server executes a SELECT statement. It will show you where adding an index to a table or optimizing table joining, for example, could increase performance.
The tool, which is also known as T-SQL Query Profiler, is built into the Standard, Profesional, and Enterprise versions of dbForge Studio for SQL server which are priced at $249.95, $499.95, and $699.95, respectively. A free 30-day trial of the whole product—not just the query optimization tool—is available.