-
Title
I have SQL Tuning and am trying to Tune a query, but it is taking hours to run. -
Description
I have SQL Tuning and am trying to Tune a query, but it is taking hours to run.
-
Cause
SQL Tuner/Optimizer is an extensive tool. It can review and general hundreds of alternative scenarios for your query. This can take a long time depending on option settings.
-
Resolution
Please adjust preferences/settings for the different steps in the Tuner.
RESOLUTION 1:
If the Optimization step of SQL Tuning is taking a long time to return alternative scenarios...
Please adjust defaultOptimization Intelligencelevel. By default it is set at 4. If this takes to long, test the process at level 0 or 1.
There is also the option of using custom intelligence setting which gives you more flexibility to define what you consider optimized.
RESOLUTION 2:
If the Execution step of SQL Tuning is taking a long time to run the alternatives and give statistics...
The optimization may give hundreds of alternative scenarios. Some may not be better then the original and sometimes you may not what to execute all alternatives. You can check off which alternatives to execute and collect stats on. When you execute, click on the black drop down arrow on the Execute icon, and you can choose to Execute Selected. -
Additional Information
If there are still problems test in the execution step, test how fast the alternative runs in an Editor, outside of Tuning. If it runs for a long time, Tuning will take as long to run.
If using Optimizer v7 and up, there is a feature called Batch Optimizer. Previous versions, before v7, do not have this feature. Keep in mind that if you are running Batch Optimizer, it combines the scanning of source code, the optimization of SQL statement, and testing of the SQL alternatives with the original SQL into one process. This means when you choose to run it, it will run every step and every alternative based off of preferences settings. This may take a long time if there are many alternatives generated.
If using Batch Optimizer seems to be taking too long, you may want to Tune the query in SQL Tuner first and adjust setting to have a more reasonable return and run time.