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
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
Lee
Profiler will tell you the application that is executing the query.
ASKER
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?
>> 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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to use the Vendor software to find the form that was causing the query to execute but I received good suggestions here.