To write optimal SQL queries that perform well, you need to know how the query optimizer works.

Generally speaking, SQL Query Optimizers analyze many options for a given query, estimate the cost of each of these options and finally, pick the ones with the lowest cost. This should help you appreciate the importance of a query optimizer.

If a query optimizer chooses the wrong plan, the difference in performance can be from a few milliseconds to even a few minutes. This also explains why this optimizer has the biggest impact on the performance of a SQL server component.

Fortunately, there are many third-party SQL query optimizer tools available today that optimize every SQL query automatically.

Here is our list of SQL Query Optimizer tools:

1. SolarWinds Database Performance Analyzer – FREE TRIAL An SQL optimization tool that is able to spot inefficiencies in resource access caused by SQL query clause ordering. Installs on Windows Server. Start a 14-day free trial.
2. Site24x7 SQL Server Monitoring – FREE TRIAL A cloud-based monitor for networks, servers, and applications that creates an application stack linking together interfaces, databases, and the underlying server resources. Access a 30-day free trial.
3. ManageEngine Applications Manager – FREE TRIAL A monitoring system for a range of services and applications, including databases and the SQL queries that run on them. Installs on Windows Server and Linux. Get a 30-day free trial.
4. Redgate SQL Monitor A query optimizer for SQL that uses its own blacklist of serious query construction mistakes.  Runs on Windows Server.
5. Idera DB Optimizer A query optimizer for SQL Server, Oracle, and Sybase that spots inefficient queries and recommends fixes. Installs on Windows and Windows Server.
6. EverSQL A Web-based SQL optimizer that automatically rewrites queries to improve database performance.
7. Devart dbForge Studio for SQL Server An SQL query builder with an auto-complete system for typing in queries and code explorer. Runs on Windows and Windows Server.

These tools greatly ease the work of developers and database administrators, as they provide the right query tuning suggestions and indexing recommendations.

Here’s the Top SQL Query Optimizer Tools

Here is a detailed look into each of these tools.

1. SolarWinds Database Performance Analyzer – FREE TRIAL

Database Performance Analyzer from SolarWinds is an advanced tool for monitoring, analysis and tuning of database and SQL query performance.

Features: Its salient features include,

• Identifies database problems in real time.
• Tracks every query in every active session and identifies those areas that cause delays in query execution and performance.
• Monitors and optimizes SQL server from a single view, regardless of whether it is on-premises, virtualized or in the cloud.
• Captures multiple metrics of response time and server statistics and stores them in data warehouse repository for future analysis.
• Installs no software or agent on the monitored server, and hence places less than 1% of load on the monitored instance.
• Takes you to the root cause in three to four clicks
• Correlates SQL statements, context, system, storage health, wait type and response times to give you a complete picture of a query’s performance.
• Proactively monitors the system and identifies problems before it starts affecting users.
• Identifies SQL blocks and its corresponding locks.
• Automatically creates graphical reports and even emails them to specified addresses.
• Alerts proactively through email or SNMP traps.
• Integrates well with Orion platform.
• Provides cross-vendor database support from a single interface.
• Works well on different databases such as SQL Server, Oracle, MySQL, MariaDB, Aurora, DB2 and ASE.

Pricing: Starts at $1,995.

Download: You can download a fully functional free 14-day trial from: https://www.solarwinds.com/database-performance-analyzer/registration

2. Site24x7 SQL Server Monitoring – FREE TRIAL

Site24x7 is a cloud-based monitoring system that covers networks, servers, and applications. The service includes database performance monitoring and SQL analysis utilities.

Features: Some of the key features of the SQL analyzer in Site24x7 are:

• Hosting included with the software
• The ability to monitor Windows Server and Linux
• An application stack feature that links together interfaces, databases, and other services, and the underlying resources of the hosting server.
• The tracking of key database transaction processing metrics
• Tracking of temporary table creations
• Recording of read and write activity
• Linking of transaction activity to server processes
• Alerts for when system resources are running low
• A web-based console that is accessed through any browser
• Secure connections for data transfers between the on-premises data collector and the Site24x7 servers.

Pricing: Site24x7 Infrastructure costs $9 per month when paid for annually.

Download: Get more information and access to a 30-day free trial at:

https://www.site24x7.com/sql-server-monitoring.html

3. ManageEngine Applications Manager – FREE TRIAL

ManageEngine Applications Manager monitors the performance of applications and services and also the hosts that all of that software runs on. This remit includes the monitoring of databases and SQL query execution.

Features: Some of the important features of the Applications Manager are:

• Monitors on-premises, remote, and cloud-based databases.
• Aggregates all of the metrics for all database instances of each DBMS and include a drill-down path to get data on each individual instance.
• Automatically discovers, categorizes, all of your databases and starts monitoring them without any human intervention. Settings can be adjusted manually.
• A troubleshooting assistant that includes code-level guidance.
• Monitors database instance factors, including CPU and memory utilization, buffer cache, resource pools, connection statistics, and user sessions.
• The live monitor examines query performance, deadlock issues, plus system and user errors.
• Maps each query execution with resource usage.
• The monitor includes database structure analysis with recommendations for improved indexing and table relationships.
• Supervises replication and backup processes.
• Deploys machine learning to recommend resource allocations for database instances.
• Regularly scheduled performance reports.

Pricing: There is a Free edition of Applications Manager. That version is limited to monitoring five application statuses. The Professional edition can monitor up to 500 Applications for $945 per year. The Enterprise edition can monitor up to 10,000 application statuses at a price of $9,595.

Download: You can download a 30-day free trial at  https://www.manageengine.com/products/applications_manager/

4. Redgate SQL Monitor

Redgate‘s SQL Monitor proactively monitors your SQL server and reports problems, before they turn into full-fledged disasters.

Features: Some of the important features in Redgate are:

• Gives you the option to install one base monitor per location. This means, you can get the reports of all your datacenters in one single interface without having to install a separate UI in each location. With this, you can monitor the performance of all locations through a single SQL monitor dashboard, so there is no security compromises.
• One base monitor can cover more than 200 servers.
• All alerts are single expandable object in your inbox, so you have all the necessary information clustered together. In addition, it avoid cluttering your inbox as well.
• Alerts deadlocks as they happen, to make it easy to recover any lost processes and to put measures to reduce their future occurrences.
• Creates advanced graphs that give you a quick glance into the state of SQL queries.
• Query History Graph helps to see the impact of queries. This makes it easy to identify problems at the earliest.
• Blocking processes are displayed in an overview page for easy readability.
• Identifies the tool that carried out deployment and displays the same on your timeline.
• You can bulk load details through the import-export API.
• Identifies bad database deployments.

Pricing: The pricing depends on the number of servers on which you want to install this tool. The cost is $1,495 per server for up to four servers and $1,271 per server for five to nine servers. If you want to install more than ten servers, contact the sales team for a personalized quote. They can be reached at https://www.red-gate.com/products/dba/sql-monitor/#contact

Download: You can download a 14-day free trial at https://www.red-gate.com/dynamic/products/dba/sql-monitor/download

5. Idera DB Optimizer

Idera DB Optimizer is a comprehensive tool that tunes poorly performing SQL code and helps you identify problems and their root causes at the earliest.

Features: Idera comes with the following features.

• Tunes SQL code across all major RDBMS such as Oracle, Sybase, DB2 and SQL Server through a single common interface.
• Reduces training needs and streamlines collaboration among different teams within an organization.
• The wizard that comes with this tool automatically suggests solutions to improve optimization.
• Color-coded index analysis view shows the missing indexes and offers recommendations to fix the same.
• Its unique Visual SQL Tuning diagram turns text-based SQL code into graphical SQL diagrams. This helps DBAs to understand the impact of SQL queries on the database.
• Gives a graphical representation of wait time analysis to identify poor database performance.
• Allows information to be shared across process.
• Load testing verifies performance of existing and alternative SQL queries against the database
• Helps to run multiple queries in parallel to stimulate the environment you want.
• Appropriate SQL rewrites are suggested.

Pricing: Starts at $429 per user or workstation. This cost includes one year maintenance as well.

Download: You can download a fully-functional 14 day free trial from https://idera.com/dboptimizer-sql-database-optimization/freetrialsubscriptionform

6. EverSQL

EverSQL is an online SQL query optimizer that provides the easiest way to monitor the performance of SQL queries.

Features: EverSQL comes with the below-mentioned features.

• Optimizes SQL queries easily
• Simple to use .
• Comes with an intuitive interface
• No downloads or installations required.
• Simply upload or type a query, upload the schema structure and get optimized query results
• Doesn’t require any access to the database.
• Supports MySQL, MariaDB and PerconaDB databases.
• Provides the best indexing recommendations.

Pricing: The cost depends on the number of credits you buy. There are four plans, namely, Free, Basic, Plus and Pro.

Free

• FREE to use
• Two credits are given
• Automatic query optimization
• Provides indexing recommendations
• Email support is available

Basic

• Costs $29 a month
• 10 credits are available every month
• Automatic query optimization
• Provides indexing recommendations
• Gives query history
• Email support is available

Plus

• Costs $135 a month
• 50 monthly credits are available.
• Optimizes from slow log
• Automatic query optimization
• Provides indexing recommendations
• Email and live chat support is available

Pro

• Costs $749 a month
• 300 monthly credits are available.
• Optimizes from slow log
• Automatic query optimization
• Provides indexing recommendations
• Email and live chat support is available

Download: This tool can be accessed online at https://www.eversql.com/

7. dbForge Studio

dbForge Studio is a powerful tool designed for an integrated development environment. It helps to develop, manage, administer, analyze and report SQL Server queries and performance.

Features: dbForge Studio has the following features

• Provides SQL coding assistance in the form of auto code completion to create SQL statements with just a few keystrokes, SQL formatting to follow a commonly accepted coding standard, T-SQL analyzer to improve your code quality with configurable rules, SQL snippets to store and reuse repeated code snippets and advanced code navigation.
• Gives complete control over your server.
• Analyzes the status of SQL indexes and fixes issues with index fragmentation.
• Allows to instantly rebuild and reorganize SQL indexes in a visual manner.
• Implements automated unit testing
• Optimizes slow queries
• Each query is analyzed and displayed along with its cost. It is also represented as a percentage of the total cost of the batch, if you’re executing batch queries.
• Wait Stats tab allows you to easily detect bottlenecks in your query
• Displays a list of waits and events associated with each query
• PLAN tree gives information on the execution of every query, including positions, to make optimization easy.
• Displays the most expensive operations.
• Provides the input output statistics for all tables.
• Helps you see profiling results for multiple queries, after you change the query every time.

Pricing: Comes in three flavors – Standard, Professional and Enterprise.

Standard

• Costs $249.95
• Basic Code Completion
• Visual Query Builder
• Query Profiler
• T-SQL Debugger
• Data Export & Import

Professional

• Costs $499.95
• All features of Standard is included
• Advanced Code completion
• T-SQL Code Analyzer
• Data Compare & Sync
• Schema Compare & Sync
• Test Data Generation
• Command-Line Automation

Enterprise

• Costs $699.95
• All features of Professional edition
• Source Control
• T-SQL Unit Test
• DB Documentation
• Script Folder Diff

Download: Download a free trial at https://www.devart.com/dbforge/sql/studio/download.html

Conclusion

In short, SQL Query Optimization software and tools help you to choose the right queries that take minimal time and resources to execute and at the same time, gives you the best performance. The above mentioned tools are great choices to optimize your SQL queries.