Transfer: cloud + community, cloud.tencent.com/developer/n…

51 cto links: the original news.51cto.com/art/201907/…

In general, the SQL query optimizer analyzes many options for a given query, estimates the cost of each option, and selects the option with the lowest cost. If the query optimizer chooses the wrong plan, the performance difference can range from milliseconds to minutes. Fortunately, there are many third-party SQL query optimization tools that automatically optimize each SQL query. These tools greatly simplify the lives of developers and database administrators because they provide the right advice for query tuning and indexing. Now that we know the importance of the SQL query optimizer, here are some good tools.

1.Solarwinds database performance analyzer

Solarwinds Database Performance Analyzer is an advanced tool for monitoring, analyzing, and adjusting the performance of databases and SQL queries. Its outstanding features include:

  • Identify database problems in real time.
  • Track each query in each active session and identify areas that are causing query execution and performance delays.
  • Whether deployed in-house, virtualized or in the cloud, it can be monitored and optimized from a single view.
  • Multiple metrics of response time and server statistics are captured and stored in a data warehouse repository for future analysis.
  • No software or agents are installed on the monitored servers, so less than 1% of the load is placed on the monitored instances.
  • With three or four clicks, you can understand the root cause
  • Relate SQL statements, context, system, storage health, wait type, and response time to get an overall picture of query performance.
  • Proactively monitor the system and identify problems before they begin to affect users.
  • Identifies the SQL block and its corresponding lock.
  • Automatically creates visual reports and sends them via email to the specified address.
  • Initiate alerts via email or SNMP.
  • Perfect integration with Orion platform.
  • Cross-vendor database support from a single interface. Works with different databases such as SQL Server, Oracle, MySQL, MariaDB, AWS Aurora, DB2 and ASE.

2.Redgate SQL Monitor

Redgate SQL Monitor proactively monitors SQL servers and reports problems. Some of Redgate’s key features include:

  • Gives you the option of installing a base monitor for each location. This means that reports from all data centers can be retrieved in one interface without having to install a separate UI at each location. In this way, performance at all locations can be monitored through a single SQL monitoring dashboard, so there is no loss of security.
  • A base monitor can cover more than 200 servers.
  • All alerts are a single extensible object in the inbox, so you can bring all the necessary information together. Plus, it avoids cluttering your inbox.
  • Alert when a crash occurs so that any lost processes can be easily recovered and steps can be taken to reduce future occurrences.
  • Create advanced charts that allow you to quickly browse the status of SQL queries.
  • The query history diagram helps you see the impact of the query. This makes it easy to identify problems early.
  • The blocking process is displayed on the overview page for easy reading.
  • Identify the tools that perform the deployment and display the same tools on the timeline.
  • Details can be loaded in batches through the import-export API.
  • Identify the wrong database deployment.

3.Idera DB Optimizer

Idera DB Optimizer is a comprehensive tool that adjusts poorly performing SQL code and helps identify problems and their root causes early. Idera has the following features:

  • Tuning SQL code on all major RDBMS (such as Oracle, Sybase, DB2, and SQL Server) through a single common interface.
  • Reduces training requirements and simplifies collaboration between different teams within the enterprise.
  • The wizard that comes with the tool automatically suggests solutions to improve optimization.
  • The color-coded index analysis view shows missing indexes and provides suggestions for fixing the same.
  • Its unique Visual SQL Tuning diagram converts text-based SQL code into graphical SQL diagrams. This helps DBAs understand the impact of SQL queries on the database.
  • Provides a graphical representation of wait time analysis to identify poor database performance.
  • Allows information to be shared across processes.
  • Load tests verify the performance of existing and standby SQL queries against the database
  • Helps run multiple queries in parallel.
  • Appropriate SQL rewriting is recommended.

4.EverSQL

EverSQL is an online SQL query optimizer that provides the easiest way to monitor SQL query performance. EverSQL provides the following functions:

  • Easily optimize SQL queries
  • Simple and easy to use
  • Comes with an intuitive interface
  • No download or installation required.
  • Simply upload or enter the query, upload the schema and get optimized query results
  • No database access is required.
  • Support MySQL, MariaDB and PerconaDB databases.
  • Provide the best index suggestions.

5.dbForge Studio

DbForge Studio is a powerful tool designed for integrated development environments. It helps in developing, managing, analyzing, and reporting SQL queries and performance. DbForge Studio has the following features:

  • SQL programming assistance is provided in the form of automatic code completion, SQL statements can be created with just a few keystrokes, SQL formatting can follow generally accepted programming standards, T-SQL analyzers can improve code quality with configurable rules, SQL code snippets can store and reuse repeated code snippets and advanced code navigation.
  • Take full control of your server.
  • Analyze the state of SQL indexes and fix index fragmentation problems.
  • Allows SQL indexes to be visually rebuilt and reorganized immediately.
  • Implement automated unit tests
  • Optimizing slow queries
  • Each query is analyzed and displayed along with its cost. If you are performing a batch query, it is also expressed as a percentage of the total cost of the batch.
  • The Waiting Statistics TAB allows you to easily detect bottlenecks in queries
  • Displays a list of waits and events associated with each query
  • The PLAN tree provides information about the execution of each query, including location, to facilitate optimization.
  • Shows the most expensive operations.
  • Provides input and output statistics for all tables.
  • You can help view the analysis results of multiple queries after each query change.

conclusion

In short, SQL query optimization tools help you choose the least amount of time and resources to execute the right query while providing the best performance. These tools are a great choice for optimizing SQL queries. However, it should be noted that these five tools are all paid tools and all offer free trial versions. Only EverSQL offers a long-term free version with limited features.