sqlxpress visual query tuner copyright © 2014 merlon software corporation
TRANSCRIPT
![Page 1: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/1.jpg)
SQLXPress Visual Query Tuner
Copyright © 2014 Merlon Software Corporation
![Page 2: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/2.jpg)
Agenda
• Introduction to Visual Query Tuner (VQT)• How to generate a plan• How to read a plan• Measuring query performance• Influencing plans• Comparing performance• Auto-tuner
![Page 3: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/3.jpg)
Introduction
• VQT is a Windows® application• Connects to SQLXPress server process running on a NonStop
system• Supports both MX and MP queries
MX version 2.3.4 or later MP version 350 or later
![Page 4: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/4.jpg)
Introduction
• VQT uses a NonStop SQL database to store query details Each query has a name and plain language description
• Keeps a history of ”snapshots” for each query• Each snapshot contains:
Query text Plain language description of snapshot Params, defines, and CONTROL statements Execution plan Execution statistics
![Page 5: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/5.jpg)
Introduction
• Why use VQT? Learn how query is executed Get performance characteristics of query Identify problems in a plan Try remedial changes, for example:
• Update statistics• Database changes (create index)• Query changes (predicate re-write)• Use of SQL CONTROL statements (CQD, CQS)
Compare performance after remediation
![Page 6: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/6.jpg)
How to generate a plan
• Use the Query tab to enter query detail
Color-coded syntax Auto-completion Query text will most
likely be pasted from clipboard
• Click Take Snapshot to save query, generate plan, and collect execution statistics
![Page 7: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/7.jpg)
How to generate a plan
• The first Take Snapshot command prompts for a query name• Subsequent Take Snapshot commands for the same query use the
same name Use Save Snapshot As to save under a new name Use New command to start working on a new query
• Each snapshot is identified by the query name and the time the snapshot was taken
![Page 8: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/8.jpg)
How to generate a plan
• Snapshot Management Take snapshot command adds a new snapshot Delete snapshot command deletes current snapshot
• Also has delete snapshots dialog to delete multiple snapshots with one command
Easy to navigate snapshots from toolbar Snapshot retention – automatically delete oldest snapshot when retention
limit exceeded Use any snapshot as staring point for new query “experiment”
![Page 9: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/9.jpg)
How to read a plan
• Plans are displayed in the Execution Plan tab• Plans displayed in three different ways:
Summary – key attributes of each operator Details – plan outline, plus complete details on each operator Plan Diagram – a truly visual plan depiction
![Page 10: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/10.jpg)
How to read a plan
• Plan Summary (MX)• Lists each operator• Key operator attributes• Similar to HP’s VQP
![Page 11: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/11.jpg)
How to read a plan
• Plan Summary (MP)• Lists each operation• Shows index and table
selectivity
![Page 12: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/12.jpg)
How to read a plan
• Plan Details (MX)• Tree view on left
provides plan outline• Complete plan text on
right shows operator details
• Click a node in the outline to show details for selected operator
• Similar to HP’s VQP, but viewing operator details is much easier
![Page 13: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/13.jpg)
How to read a plan
• Plan Details (MP)• Outline shows steps and
operations• Click on node in outline
to show details of step / operation
![Page 14: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/14.jpg)
How to read a plan
• Plan Diagram (MX)• Plan displayed as a tree• Much more intuitive
layout than traditional tree view
• There is a lot of information in the diagram
![Page 15: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/15.jpg)
How to read an MX plan diagram
• Plan fragments are color-coded• Data flows between fragments shown as heavy dotted line• Data flows within a fragment shown as lighter solid lines• Full table / index scan operators have heavy red borders• Operators that run in parallel are “stacked”• Estimated row count displayed above each operator• Operator details displayed in popup window
![Page 16: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/16.jpg)
How to read a plan
• Operator details can be displayed in a popup window
• Move mouse over an operator to display details
• Move mouse away, popup is dismissed
![Page 17: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/17.jpg)
How to read a plan
• Plan Diagram (MP)• Plan displayed as a tree• Both steps and operations
are represented by nodes• Similar to MX diagram
![Page 18: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/18.jpg)
How to read a plan
• Various ways to mitigate problems displaying large plans Scroll and zoom Fit to page Left to right – may be better than top to bottom on wide-screen monitors Simplified MX plan diagrams
![Page 19: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/19.jpg)
How to read a plan
• Simplified Plan Tree (MX)
• Suppress display of: ESP Exchange Split Top Partition Access
• Fewer nodes to display• Diagram visually
indicates fragment boundaries and parallel operators
![Page 20: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/20.jpg)
Measuring query performance
• Take Snapshot command automatically runs query to gather execution statistics
Can be turned off in Options
• Use Run Query command to explicitly run the query without creating a new snapshot
Use to collect multiple samples of execution statistics Avoid cache bias by running query more than once
• Run Query does not display query results Rows are discarded as soon as they are received by the server process
• Execution Statistics tab shows results from Run Query
![Page 21: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/21.jpg)
Measuring query performance
• Execution Statistics• Shows compile time and
execution time• Per-table statistics, like
those shown by MXCI / SQLCI
• Shows min, max, avg values for multiple samples
![Page 22: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/22.jpg)
Influencing plans
• There are various techniques to try and make query run faster Update Statistics Adjust access paths
• For example, add or modify an index Use SQL Control statements
• For example, force join order, join method, parallelism Adjust the query text
• Rewrite the query while still returning the same set of rows
![Page 23: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/23.jpg)
Influencing plans - Update Statistics
• Use View Table Statistics command
• Current statistics are displayed for tables in the query
• From here you can update statistics
![Page 24: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/24.jpg)
Influencing plans – Access Paths
• Use View Access Paths command
• Access paths for tables in query are displayed
• From here you can create or drop indexes
• Click Object Properties to view properties (including DDL)
![Page 25: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/25.jpg)
Influencing plans – SQL Control Statements
• For MX: CONTROL QUERY DEFAULT CONTROL QUERY SHAPE CONTROL TABLE
• For MP: CONTROL QUERY CONTROL EXECUTOR CONTROL TABLE
• Specify CONTROL statements on Query tab• Stored in database as part of query snapshot
![Page 26: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/26.jpg)
Influencing plans example - MX CQD
• Example of CQD• Click Edit to set CQD
values
![Page 27: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/27.jpg)
Influencing plans example - MX CQD
• Edit CQD dialog• Click on link to get popup help
on a CQD attribute• Many attributes have pick-list
of valid values• Values that are Not Specified
will take default value
![Page 28: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/28.jpg)
Influencing plans example - MX CQS
• CQS tab• Enter CQS statement• Click Show to issue
SHOWSHAPE to get current shape which you can then edit
Drag divider up to make more room when editing shape
![Page 29: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/29.jpg)
Influencing plans example – MP
• Control Executor / Query tab
Parallel Plans MDAM Interactive Access
• Join Control tab Specifies join sequence
and join method
• Control Table tab Additional control table
statements
![Page 30: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/30.jpg)
Influencing plans – adjust query text
• Change the query text in a way that helps the optimizer produce a better plan, but returns the same rows as before
• Avoid using columns as function parameters (including CAST) to allow use of an index
• Add additional redundant predicates to avoid full table scans• Don’t aggregate data in the application, use the SQL aggregate
functions• Only select the columns you actually need
![Page 31: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/31.jpg)
Influencing plans – example use of functions
• Suppose ORDERPLACED is a TIMESTAMP column• To query yesterday’s orders:
Instead of:CAST (O.ORDERPLACED AS DATE) = CURRENT_DATE - INTERVAL '1' DAY
Use:O. ORDERPLACED >= CAST(CURRENT_DATE - INTERVAL '1' DAY AS TIMESTAMP) AND O. ORDERPLACED < CAST(CURRENT_DATE AS TIMESTAMP)
• If there is an index on ORDERPLACED this will avoid a full table scan
![Page 32: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/32.jpg)
Influencing plans – example redundant predicates• Instead of:
select * from dept d where d.deptnum in (1000, 3000, 4000)
• Use:select * from dept d where d.deptnum in (1000, 3000, 4000) and d. deptnum >= 1000 and d. deptnum <= 4000
• This will avoid a full table scan• There are more examples of query rewrite in the VQT User
Guide
![Page 33: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/33.jpg)
Comparing performance
• After you make adjustments to a query, you should determine if the performance has improved
Take a new snapshot Compare the performance with previous snapshots
• Use the View Performance Charts command to compare execution statistics
![Page 34: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/34.jpg)
Comparing performance
• Chart shows stats for all snapshots
• Various Data Values: Response Times Records Read / Used Number of Messages Message Bytes
• Show sample: Minimum Maximum Average
![Page 35: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/35.jpg)
Comparing performance
• This is example uses line chart
• Shows records read and records used
![Page 36: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/36.jpg)
The Auto-tuner
• Try other remediation techniques first• Auto-tuner only works on SELECT statements• May not work on some queries
Publish / subscribe Queries using complex views
• Multi-step wizard• Allows user to take snapshot based on recommended query and
SQL control statements
![Page 37: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/37.jpg)
The Auto-tuner
• Goals Get a robust execution plan Get good (but not necessarily the best) performance
• How it works Analyzes query text Determines available access paths Collects metrics on joins and predicates
• Can use queries against tables, or ask the user Generates recommendations:
• May include create / modify index recommendations• Re-written query text• SQL CONTROL statements
![Page 38: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/38.jpg)
The Auto-tuner
• For robustness: Use nested joins
• Scale well with increased table sizes• Lower memory requirements compared to hash and merge joins• Don’t require temporary disk space for hash or sort
• For performance Determine best join order
• Drive query from table with best filter• For the rest of the join order
– Prefer joins to master tables over detail tables– Prefer joins to well filtered tables– Prefer joins with lower join ratios
![Page 39: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/39.jpg)
The Auto-tuner
• For performance: Support driver table filter with an index Support joins to detail tables with index on foreign key Prohibition on hash joins may be relaxed in some circumstances
![Page 40: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/40.jpg)
The Auto-tuner
• Recommendations report• Includes query re-write
and SQL CONTROL statements
![Page 41: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/41.jpg)
The Auto-tuner
• Post analysis actions• Select a recommendation
and click Go to implement
![Page 42: SQLXPress Visual Query Tuner Copyright © 2014 Merlon Software Corporation](https://reader033.vdocuments.us/reader033/viewer/2022051116/56649e945503460f94b990f1/html5/thumbnails/42.jpg)