Post reply

How to determine hung process?

  • December 19, 2011 at 8:23 am

    #247843

    Seasons' Greetings All,

    I have a SQL Server installed on these end-user stations that have one app running against the database. Lately, the users are perofrming this one type of function from the app that hangs the SS. As in, I can start SSMS but any normal query (i.e. 4 rows) I run will not return. It sits there and counts away the seconds as if the server is under heavy burden. If I restart SQL Server Service, I can run the normal queries, but that function from the app will hang it up again. How can I determine what process is hanging up the system so badly?

    TIA

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • December 19, 2011 at 8:34 am

    #1423136

    Have you looked at the wait type and resource? (of both that function and any other query you run)

    Is the CPU bottlenecked?

    Is that functionality written optimally? Are there good indexes?

    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

  • December 19, 2011 at 8:42 am

    #1423139

    are you seeing any blocked processes ?

    java[/url]

  • December 19, 2011 at 9:10 am

    #1423158

    GilaMonster (12/19/2011)

    Have you looked at the wait type and resource? (of both that function and any other query you run)

    Is the CPU bottlenecked?

    Is that functionality written optimally? Are there good indexes?

    Going through your list of questions helped me find the culprit. I was paying all of my attention to the database side since I couldn't get a response from it. In trying to debug the convoluted front-end code to answer your last question, I found that it opened a database transaction and then caused an error which never appeared. Thus, an open trans sitting out there.

    Thanks for the poke.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • December 19, 2011 at 9:39 am

    #1423175

    Cool. So all sorted?

    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

  • December 19, 2011 at 10:19 am

    #1423205

    Yep, all good. Thanks.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Viewing 6 posts - 1 through 5 (of 5 total)

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