Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 391

7. The query result execution statistics are displayed as follows:

(121317 row(s) affected)
Table 'Product'. Scan count 2, logical reads 10, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 3, logical reads 1356,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

8. Also, the created plan guides can be disabled or enabled as follows (also, all the

plans can be dropped):

--TO disable or drop the existing plan guides
EXEC sp_control_plan_guide N'DROP ALL';
EXEC sp_control_plan_guide N'DISABLE ALL';

--To Enable the disabled plan guide
EXEC sp_control_plan_guide N'ENABLE ALL';

This completes the set of processes in implementing plan guides to enhance compile-time
and run-time execution plans to help query optimization.

How it works...

Initially, we had generated a query plan and statistics where the query optimizer picked up a
plan for the query text that confirmed a parallelism operation. Then, we created a new plan
guide based on the query text using the parameters @stmt , @type , @hints .

At this point, it is called a plan enumeration step where the query optimizer searches through
the plan execution spare by considering different execution orders for operations such as
scans and joins. At this level, the query optimizer assesses all the available plans in the cache
and produces a suitable plan that is close enough to the optimum plan.

Additionally, without changing the query text, the plan guide is created based on the matching
SQL text and hints that allows the optimizer to preserve the desired query plans for future
reuse. Using the sp_control_plan_guide statement, we can control the usage of plan
guides by dropping them altogether or disabling them temporarily.