Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 392

Besides, the plan guides behavior has significant changes between SQL Server 2005
and SQL Server 2008 (higher) versions. Within SQL Server 2005, when the plan guide is
applied as a hint—which results in a no plan error 8722 —it will return to the client application
and the query will not be executed. However, for SQL Server 2008, if the plan results in a
no-plan error then the error is caught internally and the corresponding query is re-optimized
without the hints. This behavior can be captured using the traditional tools, SQL profiler, or
SYSMON (perfmon).
To capture this behavior, SQL Profiler (server-side trace) is useful by
bagging the PlanGuideUnsuccessful trace event. To capture
the information using the Perfmon tool, use the SQL Server: SQL
Statistics\Misguided plan executions/sec counter.
There's more...
If the plan guide cannot be honoured, the query optimizer compiles a different plan at compile
time and no error is returned.
If we upgrade the SQL Server instance from 2005 version to SQL Server 2008 R2 then
we should perform the plan guide validation by using sys.fn_validate_plan_guide
function, which requires the plan guide to be passed as a parameter. This function is useful to
see any errors associated because of the hints ( including USE PLAN hint ) that were applied.
The sys.sp_create_plan_guide_from_handle system stored procedure will enable
the user to create a plan using USE PLAN hint on a statement based on the current cached
plan. This feature is highly useful to make it possible to freeze plans for the complete
database or system.
Also, it is not possible to script out the plan guides as the hints will behave the same way as
an inline table or view, such as using INDEX or FORCESEEK table hints.
Configuring and managing storage systems
for optimized defragmentation processes
The database fragmentation can be addressed by using native DBCC statements, and
when it comes to physical storage system fragmentation then professional grade disk
defragmentation software is essential. There is not much SQL Server-related technical details
provided in this recipe, and most of the time the storage level tasks may not be a direct-
responsibility for a DBA, where storage level administrators will manage the disk subsystem.
In this recipe, we will go through the best practice process in configuring and managing the
storage system for optimized defragmentation methods.