Transcript
Visual SQL tuning diagrams.
Why tune SQL queries? SQL queries are the primary mechanism for database interaction. SQL query performance is essential for database and application performance.
Why can SQL queries be sub-optimal? Impacted by poor SQL coding choices. Change with evolving applications. Affected by changes in database structure. Suffer from resource contention.
How to improve SQL query performance? Understand how databases store data. Recognize what type of data SQL queries need to access. Locate data quickly by using table indexes correctly. Use more efficient SQL coding constructs.
Five steps for efficient SQL tuning: (1) Identify problematic SQL query. (2) Parse problematic SQL query. (3) Analyze relevant database objects. (4) Display results in relationship diagram. (5) Determine possibilities for optimization.
Components of SQL query analysis: SQL query text. SQL tuning diagram. Index analysis. Table statistics. Column statistics and histograms. Plan guides.
View basic information: Tables and views, table joins, subqueries, relevant columns, relevant keys, and relevant indexes.
View detailed information: Number of table rows. Number of table rows that meet selection criteria. Table join sizes (number of rows that meet selection criteria for both tables). All columns, keys, and indexes. SQL text for objects and table joins. Objects within views. Nested subqueries.
View cardinality: One-to-one, one-to-many, many-to-one, and many-to-many.
Cardinality: Number of rows in one table that match rows in another table. Used to detect and resolve loops.
View additional relationships: Cartesian join, implied Cartesian join, indirect relationship, outer join, unique relationship, NOT EXISTS or NOT IN join, and EXISTS or IN join.
Benefits of SQL tuning diagrams: View SQL query components. Understand efficiency of SQL query paths. Find flaws in database schema designs. Discover missing indexes. Accelerate SQL query troubleshooting.
Topics : Database Diagnostics,SQL Query Performance,
Products : DB Optimizer,SQL Query Tuner,
Why Should You Use Visual SQL Tuning Diagrams
SQL queries are the primary mechanism to interact with databases. Therefore, ensuring that SQL queries perform well is essential. An important method to improve the performance of SQL queries is to use indexes properly to locate data quickly. To create useful indexes, it is essential to understand how databases store data versus what type of data SQL queries need to access.
Visual SQL tuning refers to parsing an SQL query, analyzing the indexes and constraints on the tables in the SQL query, and displaying the SQL query represented as relational models in diagrams. Visual SQL tuning helps see flaws in designs of schemas (such as Cartesians joins, implied Cartesians joins, and many-to-many relationships). The visual SQL tuning diagram also helps to understand more quickly the components of an SQL query, thus accelerating troubleshooting and analysis.
IDERA SQL Query Tuner is an automated SQL query optimization tool that maximizes database and application performance by quickly discovering, diagnosing, and optimizing poor-performing SQL queries. It empowers database administrators and database developers to eliminate performance bottlenecks by graphically profiling critical metrics inside the database, relating resource utilization to specific queries, and helping to tune problematic SQL queries visually. Learn More →
(SQL Query Tuner is an add-on to SQL Diagnostic Manager.)
