Execution Plan for a Currently Running SQL Statement in SQL Server 2000

Question

Is there any way for a DBA to peek in on the execution plan of a long-running query in SQL Server 2000? I know how to get the SQL being run using fn_get_sql(). And yes, theoretically if open a new connection and set the environment flags the same, it should generate the same plan for the SQL. However, I'm in a data warehouse environment and this query has run for 12 hours with a data load in between, so there's no guarantee that the new plan would match the old plan. I just want to know exactly what the server is doing.

And no, I'm certainly not going to kill the currently running statement unless I can see the plan and know for certain that I can do better with index and join hints.

UPDATE: I feel so close, but I still think it can be done. It can definitely be done in 2K5 and later. If you look at the syscacheobjects virtual table, there are object ids for every cached plan. You can call sp_OA* methods on these ids, but without knowledge of the object model (which is proprietary), I can't get anywhere.

Answer 1

No you cannot. The best you can do is run DBCC INPUTBUFFER on query process and see what the last statement being executed was. You can then run this in query analyzer and get an execution plan.