Applying the best results

After getting the results for all queries being analyzed, use the fix action to apply the best settings to the queries in the database system. For example:

QPTune  -S host:port/database -A fix -i best.xml 
         -v -g

The -i option specifies the queries and their best plans resulting from the comparison.

The -g option, when used with the fix action, applies the default goal. The default goal is the best optgoal setting that most queries used as the best plan using QPTune’s fix action. This option only generates plans for queries that do not currently use the server’s default optimization goal.

The example fix action above produces this output:

Query Plan(s) fixed on "Wed Sep 17 17:44:09 PDT 2008"
--------------------------------------------------------------
Fixed 2 queries using mode "custom_1" with following optimizer settings": '(use optgoal allrows_mix) (use merge_join off) (use opttimeoutlimit 15)'

Fixed 4 query using mode "allrows_mix" 
	
Apply “sp_configure optimization_goal, 0, allrows_mix” as the default optgoal
Details of statement(s) fixed:
---------------------------------------
Query: 'select count(T.title_id) from authors A, titleauthor T where A.au_id = T.au_id ' 
Fixed using: 'custom_1'
[INFO] Fix Statement = create plan 'select count(T.title_id) from authors A, titleauthor T where A.au_id = T.au_id' '(use optgoal allrows_mix) (use merge_join off) (use opttimeoutlimit 15)'
Query: 'select * from titleauthors where au_id  > 20 and title_id < 100' 
Fixed using: 'custom_1'
[INFO] Fix Statement = create plan 'select * from titleauthors where au_id  > 20 and title_id < 100' '(use optgoal allrows_mix) (use merge_join off) (use opttimeoutlimit 15)'

QPTune then creates an optimized query plan which is saved in the sysqueryplans system table in the current database. When a query with matching SQL is encountered, this optimized plan is used. Incoming SQL and the SQL of the persistent plan are said to match when a checksum type of hash on the two SQL statements matches. If literal parameterization is enabled explicitly, the two statements may differ only in the static values of search arguments such as:

where CustomerID = “12345”

In this case, the value “12345” is replaced by a placeholder variable, so the hash value is the same, regardless of the search value.

If the application changes the SQL in any manner, such as adding a new predicate, there is no longer a match to a persistent plan and the optimizer creates a query plan according to the current configuration and available statistics.