SQLServer Plan-Fu

Like google-fu, here is my MS SQL Server plan-fu :D

Finding out the cached plan handle for a query. You need some distinguishing text for the query - which I refer to as the MyMarker. Word of caution - this is computationally intensive so exercise discretion when running it - otherwise you might hear from your friendly neighborhood DBA-man :)

     SELECT cache_plan.plan_handle, cache_plan.objtype, cache_plan.size_in_bytes,
            cache_plan.cacheobjtype, cache_plan.usecounts, sql_text.text
       FROM sys.dm_exec_cached_plans as cache_plan WITH (NOLOCK)
OUTER APPLY sys.dm_exec_sql_text (cache_plan.plan_handle) as sql_text
      WHERE sql_text.text like N'%MyMarker%' AND cache_plan.cacheobjtype='Compiled Plan' AND cache_plan.objtype='Prepared'

To see the query plan using the plan_handle from above

SELECT query_plan
  FROM sys.dm_exec_query_plan (0x06003500F43BCC1940A17B9E010000000000000000000000)

To kick out the bad boy from the cache

DBCC FREEPROCCACHE (0x0600050093BEF30740014500030000000000000000000000)

To see the currently running query for a given spid

DBCC INPUTBUFFER (<spid>)

To see all the connections from a given login (to know the spid)

SELECT * FROM master..sysprocesses WHERE loginame='someuser'