Forcing An Adhoc SQL Statement to Recompile

If an adhoc SQL statement (i.e., not a stored procedure or function) is getting a bad execution plan, we can try clearing out the plan from the cache:

  1. Get the execution plan from Solar Winds

  2. Download or open the plan

  3. In the plan XML,find the XML attribute called "QueryHash" and copy its value (it should look like 0x46B6D41E1767DCF2)

  4. Execute the following SQL on the server:


DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL += 'DBCC FREEPROCCACHE (' + convert(varchar(500), s.plan_handle, 1) + '); '

FROM sys.dm_exec_query_stats s WITH (NOLOCK)

WHERE s.query_hash = <paste query hash here without quotes>

EXEC(@SQL)


  1. Check in Solar Winds to see if the code picks up a better plan.

If for some reason that doesn't work, you can also try "invalidating" the current plan by:

  • Issuing sp_recompile against any of the tables/views in the statement (*)

  • Updating statistics (sample no less than 25%) on a smaller table/index within the statement (pick the smallest you can find)

  • Dropping "bad" indexes the plan is using .

(*) -- note -- this can cause blocking. Monitor closely. If you kill this operation, close your SSMS window immediately or execute ROLLBACK TRAN as it could hold open a transaction.

All of those options invalidate the old plan and "roll the dice" hoping to get a better plan next time and should be considered a temporary fix if it helps.