Post reply

sp_getschemalock blocking

  • September 17, 2009 at 12:35 pm

    #136052

    We have blocking issues on a production server. So I run a process that pulls all the blocking processes at every 15 second interval. I notice at night we have a lot of processes that run 'sp_getschemalock' blocking some other processes. I could not tell which process runs it. Which table is it trying to get a schema lock on? Does it have something to do with remote query calls?

  • September 17, 2009 at 5:18 pm

    #1055306

    Sorry I can not be any more specific, but if you enter 'sp_getschemalock' in the search window in the upper right hand corner of this forum page you will find some 92 references to this undocumented procedure and how others have handled the problem.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • September 25, 2009 at 2:40 pm

    #1058668

    Thanks for taking the time to reply to my post. I did a search before posting, unfortuantely I only got 4 instead of 90 links back and none of them seemed to answer my question.

    I ran a trace and find sp_getschemalock is called by remote queries. My understanding is that since we have a lot of dynamic queries (inser, select, delete; there is no 'alter table' queries though) run as remote, sp_getschemalock gets schema stability locks on the tables when they are being compiled.

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

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