?
Solved

Find where long running query is executing from

Posted on 2012-03-29
5
Medium Priority
?
244 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

771 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