Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: MS SQL performance from 10" to 3 minutes

    Hello all !

    I am runing from .NET application an SQL Query
    it normally return the rows in 10 seconds
    but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)

    with exactly the same datas in database

    what can be the reasons ?

    thank you

  2. #2
    Join Date
    Sep 2006
    Posts
    30

    MS Sql Performance

    check whether the session is getting expired or not if not kill it.

  3. #3
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    other reason could be lock put on the table during the transaction which may keep the DB server busy.
    Also check if some other query seeks a large resultset from DB.
    In GOD we believe. Everything else we Test!

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    ppavan21 if I kill the session and a user is logged-in he will be thrown, I cannot do it , or do you see a solution ?

    wash : is there a way to unlock ? ot what can I do ?

    on 5 rows it takes normally less than one second, sometimes it can turn a few minutes and crash with exactly the sames datas

    thank you

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the reasons for this can vary widely.

    things to check...

    1. open up the task manager to see if it is the sqlserver process consuming resources. Are you running anything on the machine? IIS? exchange?

    2. run sp_who\sp_who2\sp_lock to look for blocking\resource intensive operations or excessive locking.

    3. Open up the performance monitor and make sure you disk que length is under 3.

    4. Have you looked at the execution plan of the query that varies in execution time? Are there any table\index scan as opposed to index seeks in the plan? If the query can return vastly varying amounts of data, have you tried adding WITH RECOMPILE to the query? Have you recompiled the stored procedure lately? Are the indexes that the query is using heavily fragmented?

    That should keep you busy.
    “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.

  6. #6
    Join Date
    Dec 2005
    Posts
    266
    RECOMPILE ? i didn't know it was even possible
    how do yo do it ?

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    recompiling is sometimes beneficial if there has been a large amount of data added to your database recently which can have the effect of making your execution plan out of date.

    see sp_recompile in Books Online.
    “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.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sean,

    I believe you assume That this is a sproc

    I got Money that it's not
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    oh probably not. dude can probably use a little BOL reading anyways.
    “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.

Posting Permissions

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