currently running queries - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

currently running queries

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2012-11-22 : 08:29:02

Hi All,
I just want to know the currently running queries on an instance. clietn dont want to use profiler. please suggest the query.

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-22 : 10:34:53

A very good tool that I use very often is Adam Machanic's sp_whoisactive. It is free and you can download it here: http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-22 : 12:09:36

You mean queries for high CPU consumption or High I/O. You can use performance dashboard or DMV to find.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-11-22 : 14:44:38

For currently executing queries , use this script:http://www.sqlserver-dba.com/2012/09/sql-server-current-queries-executing-on-sql-server.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

srimami
Posting Yak Master

160 Posts

Posted - 2012-11-22 : 23:50:45

Use the following query adding few more parameters

SELECT
d1.session_id,
d3.[text],
d1.login_time,
d1.login_name,
d2.wait_time,
d2.blocking_session_id,
d2.cpu_time,
d1.memory_usage,
d2.total_elapsed_time,
d2.reads,d2.writes,
d2.logical_reads,
d2.sql_handle
FROM sys.dm_exec_sessions d1
JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id
CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-11-28 : 08:24:53

check this

USE MASTER
SELECT TOP 50 t.[text] AS [Batch],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Statement]
, qs.[execution_count] AS [Count]
, qs.[total_worker_time] AS [Tot_CPU], (qs.[total_worker_time] /
qs.[execution_count]) AS [Avg_CPU]
, qs.[total_physical_reads] AS [Tot_Phys_Reads],
(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg_Phys_Reads]
, qs.[total_logical_writes] AS [Tot_Logic_Writes],
(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg_Logic_Writes]
, qs.[total_logical_reads] AS [Tot_Logic_Reads],
(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg_Logic_Reads]
, qs.[total_clr_time] AS [Tot_CLR_Time], (qs.[total_clr_time] /
qs.[execution_count]) AS [Avg_CLR_Time]
, qs.[total_elapsed_time] AS [Tot_Elapsed_Time], (qs.[total_elapsed_time]
/ qs.[execution_count]) AS [Avg_Elapsed_Time]
, qs.[last_execution_time] AS [Last_Exec], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Tot_CPU] DESC
-- ORDER BY [Avg_CPU] DESC
-- ORDER BY [Tot_Phys_Reads] DESC
-- ORDER BY [Tot_Logic_Writes] DESC
ORDER BY [Tot_Logic_Reads] DESC
-- ORDER BY [Avg_Logic_Reads] DESC
-- ORDER BY [Tot_CLR_Time] DESC
-- ORDER BY [Tot_Elapsed_Time] DESC
-- ORDER BY [Count] DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources