Post reply

TIME OUT EXPIRED

  • January 23, 2013 at 12:43 pm

    #269313

    WHEN SO MANY USERS HIT A TABLE CONCURRENTLY IN MSSQL 2008 R2 ERRORS OCCURED

    TIME OUT EXPIRED OR ELASPED TIME OUT SERVER IS NOT RESPONDING SO MANY TIMES THE QUERIES ARE

    AFTER RESTART THE SERVICES OF SQL PROBLEM IS RESOLVED AFTER SOME TIME ONECE AGAIN OCCURED

    WHEN WE RUN THIS QUERY FROM QUERY ANALYZER SOME TIME EXECUTE PROPERLY BUT SOME TIME ERROR OCCURED DEAD LOCK

    Select * from CONTENT where BASKETID = & PARAM_BASKET_ID & ORDER BY fbkdate , FLD_CONTENT_ID

    AND

    " Select ColNameS from IndexCardNme (nolock) where Sno =@DocumentNo

    HOW WE RESOLVED THIS PROBLEM

  • January 23, 2013 at 1:17 pm

    #1580253

    :doze: CAPS LOCK is not your friend. Periods are.

    jackwala (1/23/2013)

    WHEN SO MANY USERS HIT A TABLE CONCURRENTLY IN MSSQL 2008 R2 ERRORS OCCURED TIME OUT EXPIRED OR ELASPED TIME OUT SERVER IS NOT RESPONDING

    # of users are not the direct problem. The problem is most likely in your locking mechanics and how often you're writing on the table vs. reads.

    SO MANY TIMES THE QUERIES ARE AFTER RESTART THE SERVICES OF SQL PROBLEM IS RESOLVED AFTER SOME TIME ONECE AGAIN OCCURED WHEN WE RUN THIS QUERY FROM QUERY ANALYZER SOME TIME EXECUTE PROPERLY BUT SOME TIME ERROR OCCURED DEAD LOCK

    So, restart (and resetting the existing users) clears the issue for a bit and you're seeing the same problem from SSMS as from the application tier. This is useful.

    The two queries you listed are most likely victims of some other process that's trying to lock the entire table out.

    I'd start with researching deadlocks. You'll also want to turn on the trace flag 1222 for the deadlock graphs in your error logs.

    I'd start with looking for long running queries in your system, particularly ones that use UPDATE, INSERT, or DELETE. These take exclusive locks on things and block other processes.

    As a starting step, fire up one of the queries in SSMS that you know will run long. Go to sys.sysprocesses, lookup your spid, and look for blocked. Start running the blocking chain backwards until you find the 'head' of the block. Figure out what's running the heavy locking on your system and then optimize that.

    Rinse and repeat until you find things that just can't be cleaned up further, then investigate alternative methods of accessing that data, like snapshot isolation, iterative updates, etc.

    This is a deep problem. I'm not sure if you're just frustrated right now or in over your head, but you need to approach this systematically. There's no quick fix, and will take a lot of work from what I've extrapolated (and assumed) from what you've provided so far.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 2 posts - 1 through 2 (of 2 total)

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