Re: How to prevent a lock escalation to a table level ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Nags (nags_at_DontSpamMe.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 12:27:10 -0400

Got it. We are experimenting one table at a time.

Thank You,

-Nags

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:ODQGkJmJEHA.4052@TK2MSFTNGP11.phx.gbl...
> I'd use it surgically - not globally.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> .
> "Nags" <nags@DontSpamMe.com> wrote in message
> news:%23sEGrFlJEHA.1392@TK2MSFTNGP09.phx.gbl...
> Cool. This worked.
>
> We tested it for couple of days and it did not cause any issue.
>
> We do have a lot of issues with lock escalation, and we are not sure
whether
> we want to enable it at the server level.
>
>
> -Nags
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:#cJH0i9IEHA.3308@tk2msftngp13.phx.gbl...
> > Check out KB article 323630. Basically, we have a job that starts when
> SQL
> > Server Agent starts and does this:
> >
> > begin tran
> >
> > SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
> > LOOP:
> > WAITFOR DELAY '01:00:00'
> > GOTO LOOP
> >
> > --
> > Tom
> >
> > ---------------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "Nags" <nags@DontSpamMe.com> wrote in message
> > news:OHv27U9IEHA.3528@TK2MSFTNGP09.phx.gbl...
> > We have a typical issue with one of our SQL Queries. When the query
runs
> > because of the amount of data it touches the lock is escalated from a
page
> > level to a table level. Although it is a share lock, it prevents all
> > inserts and updates into the table. The query runs for 1 hour or 2
hours
> > sometimes depending on the volume of data it processes. Is there a way
we
> > can prevent the locking to be escalated to a table level ?
> >
> > sp_indexoption helps preventing row level or page level locking, but it
> does
> > not prevent it from escalating to table level.
> >
> > -Nags
> >
> >
>
>

Relevant Pages