Solved

Find where long running query is executing from

Posted on 2012-03-29
5
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

726 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