Solved

Find where long running query is executing from

Posted on 2012-03-29
5
239 Views
Last Modified: 2012-04-03
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
Comment
Question by:PACCAST-DEV
  • 3
5 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37782040
Profiler will tell you the application that is executing the query.
0
 

Author Comment

by:PACCAST-DEV
ID: 37782070
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37783399
>> 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
 

Accepted Solution

by:
PACCAST-DEV earned 0 total points
ID: 37784601
I figured the problem out.  Thanks for the help
0
 

Author Closing Comment

by:PACCAST-DEV
ID: 37800057
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question