Database Reference
In-Depth Information
end - qs.statement_start_offset) / 2) + 1
) as sql
,qp.query_plan
,qs.creation_time
,qs.last_execution_time
from
sys.dm_exec_query_stats qs with (nolock)
cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
outer apply sys.dm_exec_query_plan(qs.plan_handle) qp
where
qs.sql_handle = @H and
qs.statement_start_offset = @S
and qs.statement_end_offset = @E
option (recompile)
Figure 18-6.
Getting information from sys.dm_exec_query_stats
As you know, the execution plan of the blocked query shown in Figure
18-7
has a
Clustered Index Scan
operator.
The query ran at the
READ COMMITTED
transaction isolation level (you can see it in the blocked process report) and,
therefore, acquired shared (S) lock on every row of the table. As a result, it had been blocked because of exclusive (X)
lock on the row held by another session with
DELETE
statement. As we already discussed, you can avoid a
Clustered
Index Scan
by creating an index on the
OrderNum
column in a table.
Figure 18-7.
Execution plan of the blocked process
■
There are a couple of potential problems with the
sys.dm_exec_query_stats
view of which you should
be aware. First, this view provides information about cached plans only, meaning that you would not be able to get the
execution plan if it is not in the cache. Second, there is a chance that you will have more than one cached plan returned.
in some cases, SQl Server keeps the execution statistics even after recompilation occurs, which could produce multiple
rows in the result set. Moreover, you may have multiple cached plans when sessions use different
SET
options. There are
two columns—
creation_time
and
last_execution_time
—that can help pinpoint the right plan.
Note
The SQL trace also has a few shortcomings. Although it could be an appropriate technique for quick
troubleshooting, it is better to build the monitoring solution based on Extended Events and/or Event Notifications
that introduce much less overhead to the server. Another benefit of Event Notifications is that you can develop
code to be executed when the event occurs. This would increase the chance that you capture the right execution
plan, compared to starting analysis later. I have included an example showing how to setup monitoring with Event
Notifications within the companion material.
