SQL Server Performance

Exclusive lock on Stored Procedure

Discussion in 'Performance Tuning for DBAs' started by LanLan, Jun 5, 2006.

  1. LanLan New Member

    When we use profiler trace the performance, we find one store procedure requires exclusive table lock. Does this sound right ?
  2. Luis Martin Moderator

    Exclusive table lock.

    Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

    You have to ask to developer teem if that is neccesary.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. LanLan New Member

    This SP has three updating statements inside. The trace show each updating requires either Clustered or non-clustered index locks. What confuese me is that there is a table lock on objectID 1838629593, which is this SP. I don't understand why table lock is required for this sp ? Is it possible that SP requires re-compile ?
  4. mmarovic Active Member

    Yes it is. Do you use temporary tables inside sp?
  5. satya Moderator

    Run SP_LOCK during this SP execution to see what tables are locked.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  6. LanLan New Member

    There is no tempdb inside the SP.
  7. Luis Martin Moderator

    Like Satya said, us SP_LOCK to find out tables locked.
    Also use sp_who2 to see users locked.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  8. mmarovic Active Member

  9. FrankKalis Moderator

  10. mmarovic Active Member

    There is no Czech version of that article. Overall I am not delighted with IT articles translations both in Czech and Serbian/Croatian. Actually I think Serbian translations are worst, but that's probably because I am Serb living in Czech Republic, so Serbian is my native language. As you can see I prefer articles in English.
  11. FrankKalis Moderator

    &lt;grin&gt; So do I. The German translation are *really* bad. Not even my 6 years old son would speak and write like this. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  12. mmarovic Active Member

    How about your translations? I know you translated at least one long article from English to German. I guess it was a hell of work. To be fair I know I would have terrific problems translating them to Serbian. I prefer to leave as much English terms as possible.
  13. FrankKalis Moderator

    Yes, it's really a lot of work, but it pays, since many German users suprisingly do not seem to understand that much English to be able to understand the original article. Right now I'm in the process of updating my translation of Erlands "Dynamic SQL" article. He changed a lot of things when updating it to SQL Server 2005. So, basically it's a whole new article.

    Generally I do not translate common technical terms like "clustered index". The German version of "Inside SQL Server 2000" uses "Gruppierter Index" for that. When I first read this, I had to guess what kind of index that would be. I don't think one can or should translate such terms as it only confuses people.



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  14. Adriaan New Member

    I am Dutch myself and I don't know the Dutch translation of most computer terms. We recently bought a PC with Dutch language Windows, and I find myself staring at menus wondering what is what.<br /><br />We have French and German clients with IT staff who don't understand English at all, but there seems to be an improvement over the last few years with natural selection starting to take its toll on older staff.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Oddly enough, Italians seem to be more open-minded about English ...
  15. FrankKalis Moderator

    We're hijacking this thread... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Personally I blame a lot of this on the German faible to translate each and everything which is shown on TV into German. I really like the "Dutch" habit to leave it as it is and display these subtitles (are they called so?).<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  16. mmarovic Active Member

Share This Page