Click here to Skip to main content
15,887,776 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
We have run into some trouble with ETL jobs being started (manually and/or in debug) while the integration job is executing. So I have been asked to find out how to have our ETL jobs determine whether or not it is "safe" to run.
Among the ideas put forth is the idea that surely the SQL Server system knows whether or not the Integration procedure is being executed and we can query the various tables and views that are provided.
A quick search turned up a promising query, but it has proven unreliable -- it gives many false negatives, i.e. it doesn't detect that the procedure is running even though it is. Many (but not all) of the false negatives involve the process being in SUSPENDED state, and sometimes it's rebuilding statistics and such.

I will likely wind up simply checking our logs, but if this can be done reliably, that would be best.

I call upon the Great SQL Server Gurus among us:
Who here has a reliable query for determining that a particular procedure is being executed in SQL Server (2014 in particular).

What I have tried:

The query I found (on another site) is basically this:

SQL
SELECT *
FROM [sys].[dm_exec_requests] A
CROSS APPLY [sys].[dm_exec_sql_text] ( A.[sql_handle] ) B
WHERE B.[objectid] = OBJECT_ID ( '[dbo].[uspJobControl]' )


I also tried this:

SQL
SELECT *
FROM [sys].[dm_exec_cached_plans] A
CROSS APPLY [sys].[dm_exec_sql_text] ( A.[plan_handle] ) B
WHERE B.[objectid] = OBJECT_ID ( '[dbo].[uspJobControl]' )


Which at least gives false positives rather than false negatives.

And, for completeness, I have also combined the two:

SQL
SELECT *
FROM [sys].[dm_exec_cached_plans] A
CROSS APPLY [sys].[dm_exec_sql_text] ( A.[plan_handle] ) B
LEFT OUTER JOIN [sys].[dm_exec_requests] C
ON A.[plan_handle] = C.[plan_handle]
WHERE B.[objectid] = OBJECT_ID ( '[dbo].[uspJobControl]' )


Which, of course, gives false negatives.
Posted
Updated 3-Apr-18 17:27pm
Comments
[no name] 3-Apr-18 20:16pm    
SQL profiler will give you correct status to monitor the state of SQL instance but sometimes it will degrade the performance a bit..
Maciej Los 4-Apr-18 6:42am    
Seems, sp_help_job (Transact-SQL) | Microsoft Docs[^] is what you're looking for...
PIEBALDconsult 4-Apr-18 8:52am    
No, that concerns only SQL Server Agent Jobs, not processes in the server, which may be executing via SSMS, SSIS, SQLCMD, ADO.net, whatever.
Maciej Los 4-Apr-18 9:03am    
OK

1 solution

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
SQL
create procedure FindMeToo as
declare @i int;
begin
  while 1=1 begin
     set @i = 1;
     -- This is going to take awhile
  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
SQL
declare @i int
while 1=1 begin
  set @i=1
  exec FindMe
end
go
Now, I query what's happening with
SQL
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
SQL
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.
 
Share this answer
 
v3
Comments
PIEBALDconsult 4-Apr-18 0:30am    
I was hoping you'd have the answer.
Yes, and no. This is a very long-running procedure (hours); not a matter of milliseconds.
Not concerned about parallel executions of the Integration procedure, concerned about an ETL truncating a table which the Integration is trying to read.
We do have a log of all processes in the system, which should be a good enough indicator that Integration is running, and that is my recommendation, but we also want to be sure we investigate this possibility.
Wendelius 4-Apr-18 11:25am    
This is a tricky, but a fun question. Please see the modified answer.
PIEBALDconsult 4-Apr-18 11:48am    
Very likely impossible to do it this way, so I do expect to use another technique.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900