Post reply

Sleeping/Awaiting Command

  • November 25, 2009 at 9:39 am

    #136422

    Hi ALL,

    There are lot of process in our server were the status is sleeping and cmd is Awaiting Command. The reason might be uncompleted transactions. Due to this Im not able to run even a select statement which is itelself getting in to this state. It does work for top 100 or top 1000. When I ran top 10000 process went to sleeping status.

    I ran sp-who , sp_who2. There seems to shared lock held between some of these process. Apart from this no blocking is reported. How can I track down the main process causing this trouble?

    Cheers

  • November 25, 2009 at 12:34 pm

    #1084279

    Try to check the number of open transactions first. Check if there any Long running queries. Run SP_WHO2 Active.

  • November 26, 2009 at 2:02 am

    #1084480

    There are not many long transaction. I have found that this particular table (73 columns) takes long time to retrieve more than 1000 rows. It does quickly retrieve top 10 and top 100 in no seconds. So we need to run and update the column statistics if that improves any performance..

  • November 26, 2009 at 2:50 am

    #1084496

    sqllearner-339367 (11/25/2009)

    There are lot of process in our server were the status is sleeping and cmd is Awaiting Command.

    Sleeping just means connected but not currently running any commands. It doesn't necessarily mean uncompleted transaction. If you check either sys.dm_exec_sessions or sys.dm_exec_requests has a column showing the execution count.

    There seems to shared lock held between some of these process.

    Lock on what? Why do you think these sleeping transactions are a problem if there's no blocking?

    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

  • November 26, 2009 at 2:52 am

    #1084498

    sqllearner-339367 (11/26/2009)

    I have found that this particular table (73 columns) takes long time to retrieve more than 1000 rows. It does quickly retrieve top 10 and top 100 in no seconds.

    Long time to retrieve lots of rows or long time to send them over to the client and display them?

    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

  • November 26, 2009 at 4:14 am

    #1084530

    Hi Gail,

    It take long time to retrieve the rows and display it on query analyser. Application that using this database is reporting performance issue often everyday. I have now setup counters on perfmon to monitor it. Any help is good.

  • November 26, 2009 at 4:20 am

    #1084535

    You need to investigate where the slow down is. Why are you returning thousands of rows to query analyser anyway? Who's reading those thousands of rows?

    For an intro to performance tuning

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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

  • November 26, 2009 at 4:53 am

    #1084546

    Hi Gail,

    Can you give me some direction how to carry out further analysis. I will also look in to the website (I'm already a member) you marked in the signature.

    ta

  • November 26, 2009 at 7:29 am

    #1084593

    sqllearner-339367 (11/26/2009)

    Can you give me some direction how to carry out further analysis.

    Sure, those two articles that i posted the links to are just that.

    As for the long running one, check STATISTICS TIME, the total time vs the display time. See how long the query takes if you insert the results into a tamp table (still runs the query but means the results won't come back). See how long it takes on the server as opposed to when it's run from a remote client.

    You still haven't answered my earlier question. Why are you returning thousands of rows to a query tool? Who's going to read those thousands of rows?

    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

  • November 26, 2009 at 10:00 am

    #1084655

    Hi Gail,

    Thank you.

    My manager found running query on that table is slow he just did try follwing t-sql

    select count (*) from tentable

    select * from tentable.

    It just basically a test query...

  • November 26, 2009 at 12:51 pm

    #1084697

    sqllearner-339367 (11/26/2009)

    select count (*) from tentable

    select * from tentable.

    Those are far from the same query and will execute very differently

    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

Viewing 11 posts - 1 through 10 (of 10 total)

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