SQL Server Performance

How can we see long running queries with Tsql...

Discussion in 'General DBA Questions' started by jagpalsingh4, Jun 9, 2008.

  1. jagpalsingh4 New Member

    Hi,
    Can anybody tell how can we see long running queries with tsql i have query for sql 2005 but it doesnt work with 2000.
    so plz help me

    Regards
    Jagpal Singh
  2. Luis Martin Moderator

    This will help you with cpu.
    SELECT * FROM master..sysprocesses WHERE status = 'runnable' ORDER BY cpu desc
    DBCC INPUTBUFFER (53)

    DECLARE @handle binary(20)

    SELECT @handle = sql_handle FROM master..sysprocesses WHERE spid = 53

    SELECT [text] FROM ::fn_get_sql(@handle)
  3. jagpalsingh4 New Member

    Hi,
    Really thaxx for ur help but i want to see like which is long running query .like we can see in sql 2005 which queries running long bcuase is sql 2005 there is dmv of query status but i dont know how can we see in sql 2000 .
    Thaxx
    Regards
    jagpal singh
  4. MohammedU New Member

    Only option would be SQL Server profiler/trace to get the list of long running procedures/queries. Save the profile data into files then export them into a table to get the info by quering it...
    http://vyaskn.tripod.com/analyzing_profiler_output.htm
    Note: You see the information in profiler only when it is completed but not when it is in progress.
  5. Luis Martin Moderator

    Profiler does not help you?

Share This Page