locked
Resource intensive processes RRS feed

  • Question

  • Hello all,

    Sys.dm_exec_sessions only shows the resource intensive processes which are active. Is there anyway I can see all the resource intensive processes for the day without using Profiler?

    Thanks.

     

     

     

     

    Monday, April 18, 2011 10:11 PM

Answers

  • Get the sql_handle from select * from sys.dm_exec_requests and use it in another function called sys.dm_exec_sql_text
    select * from sys.dm_exec_sql_text(sql_handle)

    You can basically write a custom script to include the dmvs like sys.dm_exec_requests,dm_exec_connections,sys.dm_exec_sessios,sys.dm_exec_sql_text and various others to get the data and input the data into a temp table . Schedule the polling through SQL agent job

     

    One such sample

    SELECT
      [spid] = r.session_id,
      [database] = DB_NAME(r.database_id),
      r.start_time,
      r.[status],
      r.command,
      *,
      [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
      + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
      t.[text]
    FROM
      sys.dm_exec_requests AS r
    CROSS APPLY
      sys.dm_exec_sql_text(r.[sql_handle]) AS t 

    Thanks, Leks

    • Marked as answer by Ryan_XH Tuesday, April 19, 2011 3:47 PM
    Monday, April 18, 2011 11:27 PM
    Answerer
  • Historical session data is not kept by default.  You need to capture this on a timed basis yourself, or use the the Data Collector that Sethu mentioned.
    • Marked as answer by Ryan_XH Tuesday, April 19, 2011 3:47 PM
    Tuesday, April 19, 2011 3:39 PM

All replies


  • You can use other DMVs to pull information into a custom table and retain that for looking into information. Here are some of the troubleshooting queries from GlennBerry' blog http://sqlserverperformance.wordpress.com/ that can help you in figuring out what you are after.

    You could track and load data for disk activities using dm_io_virtual_file_stats and wait stats using sys.dm_os_wait_stats , sys.dm_os_waiting_tasks etc.


    Thanks, Leks

    Monday, April 18, 2011 10:44 PM
    Answerer
  • Thanks for the reply. Actually I am mainly looking for login information like time, name, cpu time, reads and writes - as you know these information is already given by dm_exec_sessions, but only for active sessions, I am trying to have for both active and inactive sessions for the day. Well I think I may have to create a temporary table to do that as DMVs seem to give only info on active processes.  Is there a way I can also get the query TSQL for a corresponding row in dm_exec_sessions.

    Thanks.

    Monday, April 18, 2011 11:11 PM
  • Get the sql_handle from select * from sys.dm_exec_requests and use it in another function called sys.dm_exec_sql_text
    select * from sys.dm_exec_sql_text(sql_handle)

    You can basically write a custom script to include the dmvs like sys.dm_exec_requests,dm_exec_connections,sys.dm_exec_sessios,sys.dm_exec_sql_text and various others to get the data and input the data into a temp table . Schedule the polling through SQL agent job

     

    One such sample

    SELECT
      [spid] = r.session_id,
      [database] = DB_NAME(r.database_id),
      r.start_time,
      r.[status],
      r.command,
      *,
      [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
      + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
      t.[text]
    FROM
      sys.dm_exec_requests AS r
    CROSS APPLY
      sys.dm_exec_sql_text(r.[sql_handle]) AS t 

    Thanks, Leks

    • Marked as answer by Ryan_XH Tuesday, April 19, 2011 3:47 PM
    Monday, April 18, 2011 11:27 PM
    Answerer
  • You can turn on data collector (available from SQL Server 2008) and after a day, you can take a look at data collector reports. These reports would help you understand your system activity in past 1 day Thanks Sethu Srinivasan [MSFT] SQL Server
    Tuesday, April 19, 2011 12:55 AM
  • I found some scripts online which gives top queries with high i/o or cpu consumption and the associated query statements. But I have not find one that shows the top queriesa along with the associated login names and TSQL statements. Please provide if any of you have it?

    Thanks.

    Tuesday, April 19, 2011 1:30 PM
  • Historical session data is not kept by default.  You need to capture this on a timed basis yourself, or use the the Data Collector that Sethu mentioned.
    • Marked as answer by Ryan_XH Tuesday, April 19, 2011 3:47 PM
    Tuesday, April 19, 2011 3:39 PM