Post reply

Avoid Blocking!!

  • October 2, 2006 at 12:42 pm

    #170302

    hi all,

    I just wonat to find out that how can i avoid having block on system?

    I tried alert but would like to avoid better way and also want to reduce the blocking on the DB at least?

    suggestion please..

    thanks

  • October 2, 2006 at 3:08 pm

    #663727

    Well, that is kind of a broad question.  There are many factors that can add to blocking.  For starters, keep your transactions as short as possible and make sure your T-SQL code is optimized and you are using indexes properly.  Are you having a specific problem?  If so, post your problem in more detail including T-SQL code and table DDL. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • October 2, 2006 at 3:10 pm

    #663729

    readers block writers and slow system makes transactions slower thus increasing blocking.

    Quick fix = add more memory as this will cache more data thus making transactions quicker ( reduces disk i/o, scans etc. etc. )

    If your readers are not critical use nolock hints in the selects to stop shared locks.

    Difficult to say without seeing your system but factors that impact locking include:-

    Poor Code

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 2, 2006 at 3:10 pm

    #663728

    readers block writers and slow system makes transactions slower thus increasing blocking.

    Quick fix = add more memory as this will cache more data thus making transactions quicker ( reduces disk i/o, scans etc. etc. )

    If your readers are not critical use nolock hints in the selects to stop shared locks.

    Difficult to say without seeing your system but factors that impact locking include:-

    Poor Code

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 2, 2006 at 3:17 pm

    #663731

    sorry - wrong key!!

    • Triggers - guaranteed to cause problems
    • Dynamic sql
    • embedded sql
    • implicit transactions poorly formed
    • temp tables within transactions
    • stored proc recompilation - procs starting sp_
    • linked sevrer queries
    • cross database queries
    • insufficient server resource - check context switches for procs, page life expectancy for memory
    • slow disk subsystem - check i/o completion times ( over 6 ms worry )
    • SANs
    • parallelism - usually poor sql
    • inefficient / inappropriate indexes - for updates especially
    • poor table design
    • poor n'tier - be amazed how many middle tier problems there are
    • I've usually found most problems are in code

    You might want to monitor the blocking of course - to identify what it is - I use procs which write this type of data into a table - there should be suitable offerings on this site or on microsoft kb.

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

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

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