Resource locks

hi,
I have a T-sql as part of ETL. The code selects all data
from table x (with 3 million rows) and populates table y
(which already has 1.5 million rows). There are no where
conditions no calculations - simple insert into table x
(col1,col2,...coln) select col1,col2...coln from table y.
When I run sp_lock I see a lot locks of type EXT and mode
X. Can I used hint TABLOCKX.
I noticed that if I used hint TABLOCKX for the table
inserted into then number of locks reduces drastically. If
I use hint TABLOCKX in the table selected from there is no
impact.
Anybody encountered similar issues? Any input will be
useful..
Thx,
DeepaThat means you are getting extent locks which are OK for this type operation
but if you don't have other users accessing it would be best to use a table
level lock but don't need to use TABLOCKX. Instead try TABLOCK but if you
have users in the table you are selecting from they may prevent this from
happeing.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:01cc01c3b51f$f2276eb0$a301280a@.phx.gbl...
> hi,
> I have a T-sql as part of ETL. The code selects all data
> from table x (with 3 million rows) and populates table y
> (which already has 1.5 million rows). There are no where
> conditions no calculations - simple insert into table x
> (col1,col2,...coln) select col1,col2...coln from table y.
> When I run sp_lock I see a lot locks of type EXT and mode
> X. Can I used hint TABLOCKX.
> I noticed that if I used hint TABLOCKX for the table
> inserted into then number of locks reduces drastically. If
> I use hint TABLOCKX in the table selected from there is no
> impact.
> Anybody encountered similar issues? Any input will be
> useful..
> Thx,
> Deepa
>|||Thank you Andrew. I am new to sqlserver and your help is
appreciated.
Now, this table has six indexes and dropped them before
the insert and recreate them after.
What I notice is for a brief moment the number of locks
jumps to 30,000+ before reducing to around 200 incase of
tablock and 4000 when used without tablock. Why is there a
spike initially.
Also I read that each lock resource uses 96k. My box has
2GB memory which means I can have upto (2*1024*1024*1024)/
(96*1024) which comes to about 21800 then how can the lock
resource grow to 30,000?
i encounter resource lock issues only at the time that the
o.s backup is happening. My systems people are not able to
advice me. Would you know if an o.s. backup would be heavy
on memory. I'm not a windows person either...
Many thanks,
Deepa|||Its 96 bytes Deepa and not 96KB :-)
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Yes as Hassan pointsout it is 96 bytes and not KB so you ae not as short on
memory as you think. The reason you see it spike initialy is that sql sever
starts out with row or page locks and will escalate to a table lock if it
can after a while. You say it is an OS backup, are you sure they aren't
using a sql plug-in to do sql backups as well? If you are doing a sql
backup it will take locks when it reads the data. If it is strictly an OS
backup they should eliminate the sql erver files from the backup as they are
useless from a sql server point of view and only can cause issues when
accessing the sql files.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Thank you both. Yes it is 96 bytes and not 96kb.. my bad!
I have been doing tests with drop index / populate /
recreate index and testing is in progress but this is
likely to resolve the error.
Its a standard o.s backup. The files being backed up are
the backup files created by my maintenance plans - no the
datafiles.
many thanks for the response.
>--Original Message--
>Yes as Hassan pointsout it is 96 bytes and not KB so you
ae not as short on
>memory as you think. The reason you see it spike
initialy is that sql sever
>starts out with row or page locks and will escalate to a
table lock if it
>can after a while. You say it is an OS backup, are you
sure they aren't
>using a sql plug-in to do sql backups as well? If you
are doing a sql
>backup it will take locks when it reads the data. If it
is strictly an OS
>backup they should eliminate the sql erver files from the
backup as they are
>useless from a sql server point of view and only can
cause issues when
>accessing the sql files.
>--
>Andrew J. Kelly SQL MVP
>
>"Deepa" <anonymous@.discussions.microsoft.com> wrote in
message
>news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
>> Thank you Andrew. I am new to sqlserver and your help is
>> appreciated.
>> Now, this table has six indexes and dropped them before
>> the insert and recreate them after.
>> What I notice is for a brief moment the number of locks
>> jumps to 30,000+ before reducing to around 200 incase of
>> tablock and 4000 when used without tablock. Why is
there a
>> spike initially.
>> Also I read that each lock resource uses 96k. My box has
>> 2GB memory which means I can have upto
(2*1024*1024*1024)/
>> (96*1024) which comes to about 21800 then how can the
lock
>> resource grow to 30,000?
>> i encounter resource lock issues only at the time that
the
>> o.s backup is happening. My systems people are not able
to
>> advice me. Would you know if an o.s. backup would be
heavy
>> on memory. I'm not a windows person either...
>> Many thanks,
>> Deepa
>>
>
>.
>