Not sure if I understand your question correctly but you're trying to find out if a procedure is running in order prevent something else (same procedure perhaps) from running, is that correct?
If that's the problem I don't think that checking if something is in execution is the correct way. The check show you the situation only at the moment when the check is run. Few milliseconds later the situation might be different.
If you're trying to prevent parallel executions, who not use a small table with a single row. The first one updates the table in a transaction leaving an exclusive lock on the row and when another process tries to do the same update, it's put on hold until the lock is released.
There are of course multiple ways of preventing simultaneous operations but that one is quite simple.
ADDITION:
---------
I think one major problem is that
dm_exec_requests
shows what is happening now. If the procedure is calling other procedures then the system view may show different procedure than expected. It's not showing the outermost procedure name but the innermost.
I did a small test as follows, first two procedures
create procedure FindMeToo as
declare @i int;
begin
while 1=1 begin
set @i = 1;
end;
end;
go
create procedure FindMe as
declare @i int;
begin
while 1=1 begin
exec FindMeToo
end;
end;
go
Then if I call the
FindMe
which is the one I would like to know if it is running or not
declare @i int
while 1=1 begin
set @i=1
exec FindMe
end
go
Now, I query what's happening with
SELECT *
FROM [sys].[dm_exec_requests] A
CROSS APPLY [sys].[dm_exec_sql_text] ( A.[sql_handle] ) B
I get a row where the
objectid
is
711673583
. If I query what it is
select * from sysobjects where id =711673583
I get
name id xtype uid
--------- --------- ----- ---
FindMeToo 711673583 P 1
So execution is in
FindMeToo
even though
FindMe
is where the story started.
What comes to cached plans, I wouldn't look there. If the server is running well and you have sufficient amount of memory, a plan for majority of statements is found in cache, running or not.
This gets really interesting when parallelization is taken into account, you might get a lot of running calls which all are originated from the same top-level call but do a different parts of job.
I would still recommend considering a "Blocking lock" -approach if you want to prevent a procedure to run while another operation is running. As far as I can see, this would be foolproof and easy to implement.