Solved

Find where long running query is executing from

Posted on 2012-03-29
5
238 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:ScottPletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now