• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Find where long running query is executing from

If I execute the query below the object name is NULL.  If I search sys.sql_modules where definition like and put the query string in place with wildcards I get nothing.

I cannot find what stored procedure/function the long running query is executing from.  All I know is the source of the connection is our utility server which means the query is called from our ERP application.  I have read that this could be an adhoc query so would profiler give me some clues as so where this is originating?   Thanks

SELECT TOP 10
   ProcedureName    = t.text,
   ObjectName          = OBJECT_NAME(t.objectid) ,
   ExecutionCount   = s.execution_count,
   AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
   AvgWorkerTime    = s.total_worker_time / s.execution_count,
   TotalWorkerTime  = s.total_worker_time,
   MaxLogicalReads  = s.max_logical_reads,
   MaxPhysicalReads = s.max_physical_reads,
   MaxLogicalWrites = s.max_logical_writes,
   CreationDateTime = s.creation_time,
   CallsPerSecond   = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
   CROSS APPLY sys.dm_exec_sql_text( s.sql_handle )  t
ORDER BY
   s.max_physical_reads DESC
0
PACCAST-DEV
Asked:
PACCAST-DEV
  • 3
1 Solution
 
Lee SavidgeCommented:
Profiler will tell you the application that is executing the query.
0
 
PACCAST-DEVAuthor Commented:
I know the application it is coming from.  The query above gives me the actual query text as the procedure name but when I search for the query string in sys.sql_modules I get nothing back so is it an adhoc that someone could be writing the query and just executing on demand?
0
 
Scott PletcherSenior DBACommented:
>> OBJECT_NAME(t.objectid) , <<

You must be in the same database for this to work properly.

(You can get the corresponding db_id from sys.dm_exec_sql_text()).

Verify that you are in the correct db upon which the query is based.

And of course keep in mind that a single query can use objects from multiple databases.
0
 
PACCAST-DEVAuthor Commented:
I figured the problem out.  Thanks for the help
0
 
PACCAST-DEVAuthor Commented:
I had to use the Vendor software to find the form that was causing the query to execute but I received good suggestions here.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now