none
Activity Monitor - Batch Requests/sec

    Question

  • Hi,

    Am I correct in saying that the Batch Requests/sec should be high on a busy server?

    How do I find what queries are causing problems with server performance?

    Thanks!

    Friday, November 01, 2013 1:53 PM

Answers

  • How do I find what queries are causing problems with server performance?

    >>with performance interms of what, is that CPU,Memory and I/O is high or users facing slowness?

    yes on an busy peak server it gives that number of sql statments that being executed per second  according to how it is going on under the DB,but we cannot be relay on only on this because it depends on how database designed interms of used right index used or ad-hoc queries passed or how the configuration settings made on the sql server instance with memory & other.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    Friday, November 01, 2013 2:03 PM

All replies


  • How do I find what queries are causing problems with server performance?

    Thanks!

    Hello,

    http://technet.microsoft.com/en-us/library/ms177500(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Friday, November 01, 2013 1:59 PM
  • How do I find what queries are causing problems with server performance?

    >>with performance interms of what, is that CPU,Memory and I/O is high or users facing slowness?

    yes on an busy peak server it gives that number of sql statments that being executed per second  according to how it is going on under the DB,but we cannot be relay on only on this because it depends on how database designed interms of used right index used or ad-hoc queries passed or how the configuration settings made on the sql server instance with memory & other.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    Friday, November 01, 2013 2:03 PM
  • I would start by looking at the plan cache for long running queries, also look at queries with excessive IO weighting.

    A profiler trace and extended events will also help you.

    Use this to look for queries with the longest runtime.

    SELECT top 20
    SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,
    ( ( CASE statement_end_offset
    WHEN -1 THEN DATALENGTH(t.text)
    ELSE s.statement_end_offset
    END - s.statement_start_offset ) / 2 ) + 1)
    AS statement_text,
    text,
    objtype,
    cacheobjtype,
    usecounts,
    last_execution_time,
    total_worker_time,
    total_worker_time / execution_count AS [Avg CPU Time],
    execution_count ,
    qp.query_plan
    FROM sys.dm_exec_query_stats AS s
    inner join sys.dm_exec_cached_plans cp
    on s.plan_handle = cp.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
    CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp
    order by total_worker_time desc
    

    There are more columns to investigate in the above DMV's - Change the order by clause to whatever suits your needs 
    Friday, November 01, 2013 2:04 PM
  • >How do I find what queries are causing problems with server performance?

    The problem may be caused by out-of-date/missing indexes & statistics.

    Before hunting for queries, make sure that the database is properly maintained.

    System optimization:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Friday, November 01, 2013 2:04 PM
  • see also-

    Checklist for Analyzing Slow-Running Queries
    http://technet.microsoft.com/en-us/library/ms177500%28v=sql.105%29.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, November 01, 2013 2:13 PM