none
SQL Server Profiler - High Duration, Low cpu

    Question

  • Hi,

    Does anyone know what could cause high duration and low cpu/reads/writes?

    We have seen system slowness recently. I run profiler, and there are some query duration is over 10,000 but cpu is under 500. There is no blocking/waiting in the SQL Server, and %cup time is only 15.  Batch Request/sec is 800 to 1000. All long duration queries return recordset to the middle tier machine. This is a OLTP system using SQL Server 2008 R2 sp2.

    Thanks for any input.

    Lijun

    Wednesday, November 06, 2013 10:34 PM

Answers

  • If these slow ones are returning data to the middle tier, make sure it's not the middle tier holding things up, look for asynch IO wait states.

    If your server is on a VM, make sure the VM isn't getting paged or swapped out - of course also make sure the Windows server you can see is not doing any paging, any paging and SQL Server just melts down.

    Finally I'd ask how many cores you have and what your global maxdop setting is, sometimes big parallel queries can hog CPUs and keep others from running and it does NOT show up as a block.

    Josh

    Thursday, November 07, 2013 12:29 AM
  • All long duration queries return recordset to the middle tier machine. This is a OLTP system using SQL Server 2008 R2 sp2.

    The duration includes time until the last result buffer is filled.  As Josh alluded, if the mid-tier app is slow in consuming larger results that require multiple buffers, it will be reflected as long-running queries even though the slowness is on the client rather than the SQL Server.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, November 07, 2013 2:46 AM
    Moderator

All replies

  • If these slow ones are returning data to the middle tier, make sure it's not the middle tier holding things up, look for asynch IO wait states.

    If your server is on a VM, make sure the VM isn't getting paged or swapped out - of course also make sure the Windows server you can see is not doing any paging, any paging and SQL Server just melts down.

    Finally I'd ask how many cores you have and what your global maxdop setting is, sometimes big parallel queries can hog CPUs and keep others from running and it does NOT show up as a block.

    Josh

    Thursday, November 07, 2013 12:29 AM
  • All long duration queries return recordset to the middle tier machine. This is a OLTP system using SQL Server 2008 R2 sp2.

    The duration includes time until the last result buffer is filled.  As Josh alluded, if the mid-tier app is slow in consuming larger results that require multiple buffers, it will be reflected as long-running queries even though the slowness is on the client rather than the SQL Server.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, November 07, 2013 2:46 AM
    Moderator
  • During the slow time, some middle tier server system event has warning:

    vent Type:  Warning

    Event Source:     COM+

    Event Category:   (117)

    Description:

    The average call duration has exceeded 10 minutes. If this is not the expected behavior, please see article 910904

    The server has 24 cores and is not on a VM. The maxdop is 0, but there is no parallel queries. The Pages/sec is around 5, Buffer cache hit ration is above 99.99%.

    Thursday, November 07, 2013 3:33 PM
  • The resultsets send back to middle tiers are small set mostly only contain one record.
    Thursday, November 07, 2013 3:35 PM
  • Then perhaps it's not a database issue?

    Did you check the most common wait states, see what that tells you?

    Josh

    Thursday, November 07, 2013 11:57 PM