Getting long running queries from sql2005

Question

Getting long running queries from sql2005

monster

SSC Enthusiast

Points: 119
More actions
May 14, 2007 at 8:21 am

#117191

Hi,
I need to create a script to get all the long running queries on sql 2005. On 2000 I had a script that asked sysprocesses every 5 seconds for queries that had run for more than 15 seconds. They were stored in a table, which I then used for comparison to see if I got the same spid for the same user with the same query again in my next query. The query is a little imprecise, as I get some registrations that are clearly not correct.
I have to do the same on 2005, but I was hoping there would be a smarter way, perhaps using a dmv or two.
Does anyone know?
Thanks,
Anders

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Anders

The DMV sys.dm_exec_requests is probably what you're looking for. Books Online tells you which new DMVs/metadata views in SQL 2005 map to which system tables in SQL 2000.

John

monster SSC Enthusiast Points: 119 More actions · Answer 2

I did a little research myself.

To get the currently running processes, use:

SELECT

session_id,status,

command,sql_handle,database_id

,(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_requests r

WHERE session_id >= 51

For more detail about was has been running, try:

select

s.session_id, s.login_name, s.host_name, s.status,

s.program_name, s.cpu_time, s.last_request_start_time,

(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS query_text

from sys.dm_exec_sessions s, sys.dm_exec_connections c

where s.session_id = c.session_id and

s.session_id > 50

order by s.last_request_start_time desc

neotokyo SSCertifiable Points: 7239 More actions · Answer 3

excellent script... would be almost perfect if it had

an smtp send statement built in

_________________________

Balmukund SSCertifiable Points: 6648 More actions · Answer 4

Why there is filter for session_id > 50?

SQL Server 2005 does not have same mechanism as SQL 2000 where system SPID are always less than 50.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

Do you mean to say that SQL 2005 have SPIDs always less than 50 no not at all still the system SPID are between the range till 50 and user SPID are greater than 50.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com