Post reply

Queries that are running for long time

  • March 19, 2009 at 12:09 pm

    #202445

    Ideally if we have sql trace or performance monitor running on a server, it impacts the performance to some extent. Is there any other way of monitoring queries which have been running for long taking most of the cpu/memory of the server. This is in sql 2005 and i was wondering if theres any 2005 feature which would help this achieve.

    ANy help will be greatly appreciated

    TIA

  • March 19, 2009 at 12:39 pm

    #963013

    Profiler

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • March 19, 2009 at 12:59 pm

    #963032

    SQL Trace. Much lighter weight than the Profiler GUI. There's a recent article at Simple Talk that talks about using SQL Trace to find badly-performing queries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 19, 2009 at 1:10 pm

    #963051

    Anything other than sql profiler trace/performance monitor as i suppose they do put some load on the server when running. Also just making sure will running a sql trace have some impact on the server??

    any thoughts on this??

  • March 19, 2009 at 1:57 pm

    #963095

    All monitoring tools have some impact. You can't check a server's behaviour without using some resources on it.

    For what it's worth, I've never had a noticeable performance degradation from using SQL Trace.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 19, 2009 at 1:59 pm

    #963097

    There are DMV available in sql 2005. use that and query memory, cpu disk queue length. also sql 2005 give you reports . utilize that it very detailed info init.

    :crazy: :alien:

    Umar Iqbal

  • March 19, 2009 at 2:47 pm

    #963153

    Running this little query in management studio can tell you what statements (including itself) are being processed at the moment.

    SELECT a.session_id, c.dbid,

    SUBSTRING(c.text, (a.statement_start_offset/2)+1,

    ((CASE WHEN a.statement_end_offset < 1

    THEN DATALENGTH(c.text)

    ELSE (a.statement_end_offset -

    a.statement_start_offset)/2

    END)) + 1) as statement_text

    FROM sys.dm_exec_requests a

    CROSS APPLY fn_get_sql (a.sql_handle) c

  • March 19, 2009 at 10:31 pm

    #963304

    Here is an adittion to the suggestions..

    http://msdn.microsoft.com/en-us/library/ms403323(SQL.90).aspx

    Shekhar.. 🙂

    Thanks ,

    Shekhar

Viewing 8 posts - 1 through 7 (of 7 total)

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