Investigating a (suddenly) slow running query...

Some Questions / lines of enquiry ...

What else was going on on the server at that time?

• Is blocking occuring?
• Is another process monopolising resources (memory, cpu, i/o)

What has changed?

•  Any code changes
•  Any configuration change
•  Any change in data volume (influx of new data)

Is all maintenance happening? (it won't take long to check)

• Are statistics up to date
• How is index fragmentation
• Are backups occurring (remember full backups control log fie growth)

Can the query be run manually?

- Look at Execution plan

Is it a stored procedure?

- Look at Execution plan for parameter differences (parameter sniffing)

Parameter Sniffing

A short reminder, again to get this fresh in my mind....

Parameter Sniffing is when a previously compiled execution plan is reused with different parameter values. This is by design and is generally good as we are saving time by not compiling a new plan. Occasionally though it can lead to suboptimal choices when the procedure is run again, with new parameter values.

If a procedure returns only 1 row with 1 parameter value and 1 million with a different parameter value then the optimal plans for retrieving those rows may be very different.
Here the retrieval of a million rows wold be done using the plan generated for 1 row.

2 ways around parameter sniffing are forcing a recompile (WITH RECOMPILE) in the procedure definition or using the OPTIMIZE FOR query hint.

Simple Talk : Parameter Sniffing
Brent Ozar : Parameter Sniffing
MSDN : Parameter Sniffing & Workarounds

Plan Cache Pollution

They say if you can't explain something easily, you don't understand it well enough. For that reason, I'm revisiting some things I take for granted.

The Plan Cache is where SQL Server stores exection plans.

If queries are not parameterized, the plan cache fills with lots of similar queries of hard coded values.

SELECT mycolumns FROM mytable WHERE myid = 1
SELECT mycolumns FROM mytable WHERE myid = 2
SELECT mycolumns FROM mytable WHERE myid = 3
SELECT mycolumns FROM mytable WHERE myid = 4

If queries are parameterized (or parameterization is forced on at a database level) then 1 entry will be in the cache instead.

SELECT mycolumns FROM mytable WHERE myid = n

Correctly parameterized queries will prevent wasting cpu (generating plans) & memory (storing them). Allowing the cache to fill with simiar plans in this way is Plan Cache Pollution.

You can examine the plan cache with the sys.dm_exec_cached_plans DMO.

Links -
Tamarick Hill : sys.dm_exec_cached_plans
Klaus Aschenbrenner : Plan Cache Pollution