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

I don't think you can do such a thing, it needs to be submitted to the server with the original query:

http://msdn.microsoft.com/en-us/library/aa178303(SQL.80).aspx .

You could load up the query and get the estimated execution plan.

Answer 2

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.

Answer 3

Maybe...

Run profiler, and expand the "performance" events node. Choose one of the SHOWPLAN options.

Hopefully, you will be able to trap the end of execution. I know you can log query plans, but I don't know if it works in this case.

I don't have SQL 2k profiler, only 2k5, to test something or see the options.