Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70

    Unanswered: Application Freezes

    Its similar to an interview question.

    When user is trying to run a report (User activity) application freezes (CPU goes to 100%), the same report which worked fine till last last thursday.

    Major activities taken recently are just the indexes rebuild,job added 3 weeks back(followed by dbcc updateusage and update statistics).
    Today got one more ticket saying one of the other report is giving the same problem, which is working fine for weeks data but not for months.

    Same report is running fine in local server(Restored copy).

    Any help on this is highly appreciated.
    Pavan.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    which CPU is at 100. Client or server.

    always check for bloacking first. sp_who2.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Client CPU is 100%, checked the blocking from sp_who2, but found nothing, the Suspended query is blocked but just for few sec's.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what are you using for a reporting tool?

    did you run sp_who2 during the freezing?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    We use crystal reports for reporting, but for this specific report, we donot use any report, its just a simple fetch from the database
    I have checked sp_who2 during freezing also and it shows nothing.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    crystal for web?

    how long does the query take when executed by itself?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Its not a web application, VB application.
    First run takes 40+ sec's and the next runs take 30+ sec's
    But the same query when executed in dev(local) server takes 10+ sec's

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    30 to forty seconds is a long time. Have you looked at your execution plan for expensive scans? Can you post some SQL and some sample data?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    72% of the cost it takes for a tbl called item_process_history and the cost is a 2 digit number, I can see from the server that the CPU it takes is between 30-75%, and there are always 250+sessions active at any time.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    is that a table or index scan? which column on that table are you joining, sorting or searching on? Is there an index on that column?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Its doing Clustered index scan, we are quering using datetime_stamp column, and that column is part of the clustered index.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •