Database Reference
In-Depth Information
Identifying Bad Parameter Sniffing
This will be an intermittent problem. You'll sometimes get one plan that works well enough and no one complains,
and you'll sometimes get another, and suddenly the phone is ringing off the hook with complaints about the speed
of the system. Therefore, the problem is difficult to track down. The trick is in identifying that you are getting two (or
sometimes more) execution plans for a given parameterized query. When you start getting these intermittent changes
in performance, you must capture the query plans involved. One method for doing this would be pull the estimated
plans directly out of cache using the
sys.dm_exec_query_plan
DMO like this:
SELECT deps.execution_count,
deps.total_elapsed_time,
deps.total_logical_reads,
deps.total_logical_writes,
deqp.query_plan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
WHERE deps.object_id = OBJECT_ID('AdventureWorks2012.dbo.AddressByCity');
This query is using the
sys.dm_exec_procedure_stats
DMO to retrieve information about the procedure in
cache and the query plan. The results when run within SSMS will include a column for
query_plan
that is clickable.
Clicking it will open a graphical plan even though what is retrieved is XML. To save the plan for later comparison, just
right-click the plan itself and select Save Execution Plan As from the context menu. You can then keep this plan in
order to compare it to a later plan. What you're going to look at is in the properties of the first operator, in this case the
SELECT
operator. There you'll find the Parameter List item that will show the values that were used when the plan was
compiled by the optimizer, as shown in Figure
16-5
.
Figure 16-5.
Parameter values used to compile the query plan
You can then use this value to look at your statistics to understand why you're seeing a plan that is different from
what you expected. In this case, if I run the following query, I can check out the histogram to see where values like
London
would likely be stored and how many rows I can expect:
DBCC SHOW_STATISTICS('Person.Address','_WA_Sys_00000004_164452B1');
Figure
16-6
shows the applicable part of the histogram.
