Sometimes for testing purposes (or an emergency) one might find it useful to remove query plan for a single stored procedure. This way you won't have to disrupt the entire instance (and possibly anger your fellow developers!). I often forget that DBCC FREEPROCCACHE accepts a plan handle as a parameter value. There are a few ways to identify the plan handle, usually it involves the sys.dm_exec_cached_plans with a cross apply on sys.dm_exec_sql_text and sys.dm_exec_query_plan.
I know, it sounds like it could be a desert. But it's poison! You ever have one of those Saturday morning emergencies and come face to face with a wait type that you're completely unfamiliar with? I recently got contacted that a highly transactional SQL Server was performing slowly. I noticed some memory pressure, but when I dove into wait types, I found that many of the transactions were waiting on the RESOURCE_SEMAPHORES wait. What is it? Basically it's when a query is wai