Based upon the query or stored procedure execution the plan will be stored in the cache, but it may not be in readable format as it is stored in Hexadecimal when you simply query SYSPROCESSES table. So in order to extract the query plan that is in cache, you can retrieve the SQL text of the query and the query execution plan in XML showplan format at any time. Using the DMV sys.dm_exec_sql_text you can obtain the XML showplan and to obtain query paln you can use another DMV sys.dm_exec_query_plan...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2007/10/04/2324.aspx