Batch Optimizer is taking a long time to run and to abort. (44547)

I entered a job and ran it in Batch Optimizer. It is taking hours to run a fairly simple query. Why is this? If I try to abort it also take a long time.

Batch Optimizer is heavily dependent on the option settings. The time it takes to run a job depends on how many query, how complex the query is, and what kind of optimization and execution setting are set for the SQL Optimizer.

When choosing to abort a job, SQL Optimizer will send out an abort command but can only stop a job at a safe stopping point to avoid errors or changes to the database (depending on what process the application is on when the abort command is given). It may need to wait for the current process to finish before aborting the whole job and this can take time.

Preferences may need to be adjusted. Go to Options in the upper right corner and review execution preferences under Batch Optimizer. Execution preferences determine what alternates created from the optimization process will be executed for run time stats info. The execution step in the batch optimizer could take a long time depending on how many alternate are created and how many fit the execution criteria and will be executed accordingly.

It may also be necessary to lower the optimization intelligence level. By default SQL Optimizer is set to predefined intelligence level 4. Lowering the level (min 1) in Options will run the query though a less intensive optimization process. Raising the intelligence level (max 10) will mean a more intensive optimization process that could return more alternate scenarios and take longer to optimize.

Test the individual queries in Tuning Lab first and adjust preferences accordingly. The Tuning Lab will only let you run one query at a time, but it is a more step by step process that allows for more control over east step of the process.

Further details about the different tools and options in the application can be found in the Help files and tutorials.