how can I find current query or procedur executed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how can I find current query or procedur executed

Author

Topic

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-25 : 20:03:07

I need a script to find current query or procedure executed and
other details time,number of execution.
any graphical help in sql ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-25 : 20:03:57

You'll need to run a trace for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-26 : 10:33:06

SELECT deqs.last_execution_time AS [Time],
dest.TEXT AS [Query],
*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

--------------------------
http://connectsql.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-26 : 12:40:54

That won't get you number of times something is executed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 12:55:27

I would allow only sproc access, and then log each call

\but that's just me

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-26 : 13:13:41

quote:
That won't get you number of times something is executed.

Then what does the execution_count column represent?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-04-26 : 13:15:21

Here's a good one:

SELECT r.session_id, r.status, r.start_time, r.command, s.text, 
     SUBSTRING(text, (statement_start_offset/2) + 1,      
     ((CASE statement_end_offset           WHEN -1 THEN DATALENGTH(text)          ELSE statement_end_offset END
               - statement_start_offset)/2) + 1) AS statement_text,  
  blocking_session_ID, *
FROM 
  sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
order by 
  R.Start_Time

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-26 : 13:45:14

quote:
Originally posted by robvolk

quote:
That won't get you number of times something is executed.
Then what does the execution_count column represent?

My point, which obviously I wasn't clear about, is that it won't provide the full picture. It'll only provide the information for what's available in cache. Not everything will be in cache. Only a trace will provide the full picture.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 14:16:47

quote:
Originally posted by robvolk

quote:
That won't get you number of times something is executed.
Then what does the execution_count column represent?

quote:

execution_count
bigint
Number of times that the plan has been executed since it was last compiled.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources