SQL Server Performance

Ugly locks

Discussion in 'SQL Server 2008 General Developer Questions' started by zimzum, May 6, 2009.

  1. zimzum New Member

    Hi friends, It's me agains with some performance issues... I wish I had something better to say... but well.
    The question is very simple. I have a software running towards an SQL Server database 24X7. The performance is quite good! You taught me how to improve it!. But lately I have been having some terrible locks. This happens once in 20 days aproximately. There seems to exist some query that runs not very often that locks every single query that tries to run against the DB for a couple of minutes. This locks the entire set of transactions, locking al the requests made to the application. I can see this at the Activity Monitor, where all the connections look like "suspended" .
    If I run the SQL Profiler, it will tell me that all the queries have a great performance and they do. But it seems that once in a while, some runs some runs some bizarre query and it locks everything down.
    So the question is:
    1) Which is the best way to get the best info when this happens?? Maybe something about "Blocked Processes thresholds" ? Maybe If this happens for 30 or 40 seconds I wont get to know, and it might be happening more often than I thought.
    2) Is there a way to tell SQL Server that if this happens , it should inmediately abort the unperformant query?
    Ok... Thanks a lot in advance!
  2. satya Moderator

    Welcome to the forums.
    When it comes to performance optimization, it is not a black magic that can turn out the table.
    I would like to know the application you are mentioning is a third party one or home-built one? If it is a third party one then better to involve the Vendor too, otherwise you could talk to the developers and capture the code that is causing BLOCKING & LOCKING to use INDEX hints that can help the performance gain.
    In this case you need to collect the data to analyze where exactly the process is failing and causing the issues. See this blog http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx that talks about how to carry upon the performance monitoring magic.
    Also see Tony's blog http://sqlblogcasts.com/blogs/tonyr...to-end-how-to-set-it-up-and-make-it-work.aspx about monitoirng the blocked process.
  3. zimzum New Member

    Its a software developed by myself. So I can modify every single query I want. The fact is that I dont want to examinate every single query and make a guess about what might be wrong with it, just to find out that it was something different. Insted, the next time it happens, I want to get the exact info about what cause the blocking...
    Thanks a lot...I'll read your articles!
    Zim
  4. zimzum New Member

    Yes!!!
    The blocked process report made me realize that the isolation level was READ_COMITTED. Then, there seems to be, from now and then, some issues with some Update and everythings that runs after it gets locked for a few minutes.
    That update takes about 5 miliseconds in a normal situation, and the table it uses has all the indexes recommended by the Tuning Advisor. WTF??
    I put the isolation level to READ_UNCOMITTED(it was that way before these issues, and everything was ok) but I want to know what is causing that Update to get mad.
    1) Can you recommend any good blog that explains how to interpret the XML generated by the blocked process report?
    2) Is there some report that saves some info when the execution of some query takes more than a given threshold?
    Thanks a lot!!! You really really helped me!
  5. zimzum New Member

    Hey...I just realized that the isolation level managed with SET ISOLATION LEVEL is volatile. I mean ,if I run a SET ISOLATION LEVEL READ UNCOMMITTED in one script, I open another sessions and run "DBCC useroptions", the ISOLATION LEVEL goes back to its default!
    WTF!?!?
  6. RickNZ New Member

    The set isolation level command sets the per connection isolation level, not per server or per login.
    If you can catch the system when it's behaving poorly, you might be able to exec sp_lock to see the status of all current locks.
    Are you seeing deadlocks, or just blocking?

Share This Page