DB LOCKS

Scenario :
Highly transactional DB , with several tables having 100 thousand rows.
Tons of storedprocedures with 1000's of line of code using temp tables and table variables...
Issue :
awefully slow. (vage word, me dont like it either, but fact)
am getting very very intresting locks in the database ,
lot of index locks are being exclusively held on tempdb tables. ?
can you please let me know the reasons for this type of locks ?
the locks as seen in syslockinfo table are like below , dbid 2 is from tempdb.
IDX: 2:544547798:1412364626 [[INDEX_ID]]
IDX: 2:1610018179:0 [[INDEX_ID]]
[BULK-OP-LOG]
Do some of your stored procedures create temp. tables, index them, then
insert/delete/update these temp. tables? If yes, then you will see locks
in temp. DB.

> IDX: 2:544547798:1412364626 [[INDEX_ID]]
> IDX: 2:1610018179:0 [[INDEX_ID]]
if I remember it right, the first number is the file #, 2nd is page #,
and 3rd is row #.
Eric Li
SQL DBA
MCDBA
DallasBlue wrote:
> Scenario :
> Highly transactional DB , with several tables having 100 thousand rows.
> Tons of storedprocedures with 1000's of line of code using temp tables and table variables...
> Issue :
> awefully slow. (vage word, me dont like it either, but fact)
> am getting very very intresting locks in the database ,
> lot of index locks are being exclusively held on tempdb tables. ?
> can you please let me know the reasons for this type of locks ?
> the locks as seen in syslockinfo table are like below , dbid 2 is from tempdb.
> IDX: 2:544547798:1412364626 [[INDEX_ID]]
> IDX: 2:1610018179:0 [[INDEX_ID]]
> [BULK-OP-LOG]
>
|||How to minimise these ?
Am also encountering locks such as
[BULK-OP-LOG]
[BULK-OP-DB]
[Upd-Stat]
What is the best method to avoid these locks which are eventually causing the bottleneck in the db perf ?
"Eric.Li" wrote:

> Do some of your stored procedures create temp. tables, index them, then
> insert/delete/update these temp. tables? If yes, then you will see locks
> in temp. DB.
>
> if I remember it right, the first number is the file #, 2nd is page #,
> and 3rd is row #.
>
> --
> Eric Li
> SQL DBA
> MCDBA
>
> DallasBlue wrote:
>
|||"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:5CC74562-2F1C-4D39-AA96-FB278CE7DAC3@.microsoft.com...
> How to minimise these ?
> Am also encountering locks such as
> [BULK-OP-LOG]
> [BULK-OP-DB]
> [Upd-Stat]
> What is the best method to avoid these locks which are eventually causing
the bottleneck in the db perf ?
>
Busy databases generate a lot of locking activity. What evidence you you
have that these locks are causing your performance problem?
David
|||To minimize it, you have to rewrite some of your stored procedure.
[BULK-OP-LOG] [BULK-OP-DB] locks are used for database backup and log
backup. Do you have any jobs backing up your temp. DB? It's pointless to
backup your temp. DB.
[Upd-Stat] lock is used by update statistics job. Don't understand why
you want to update your temp. DB statistics
Locking won't do you much harm in temp. DB unless some temp. tables are
shared accross differnet sessions. I don't think that's the reason why
your DB is so slow. There are many reasons, did you configure your disk
correctly? spread out your tables? use filegroups? Queries blocking each
other out?
You need a DBA to look at your server to figure out why.
Eric Li
SQL DBA
MCDBA
DallasBlue wrote:
[vbcol=seagreen]
> How to minimise these ?
> Am also encountering locks such as
> [BULK-OP-LOG]
> [BULK-OP-DB]
> [Upd-Stat]
> What is the best method to avoid these locks which are eventually causing the bottleneck in the db perf ?
> "Eric.Li" wrote:
>
|||The database is damn slow. There is no blocking (from sp_who). Server has magnanimous configuration. Its a very very high transactional db.
I have worked on [compile] locks , by using option keep plan on temp tables. And those compile locks are reduced a lot.
Am looking out , if there is any known ways we can avoid below locks like [compile]
IDX: 2:2345 [INDEX_ID]
[BULK-OP-LOG]
[BULK-OP-DB]
[Upd-Stat]
Thanks
"David Browne" wrote:

> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:5CC74562-2F1C-4D39-AA96-FB278CE7DAC3@.microsoft.com...
> the bottleneck in the db perf ?
> Busy databases generate a lot of locking activity. What evidence you you
> have that these locks are causing your performance problem?
> David
>
>
|||Agree with you totally. need to rewrite the procs. The volume of procs are very heavy(1000's of procs, 1000's of lines), business logic in these procs is being written from several years. hope you get the picture of complexity of the procs here.
Create index #temp tables are being used. in lot of procs...
Do you think these would cause [upd-Stat] lock apart from [index] lock ?
Do you think that we should avoid creating the indexes on temptables ?
Thanks!!
"Eric.Li" wrote:

> To minimize it, you have to rewrite some of your stored procedure.
> [BULK-OP-LOG] [BULK-OP-DB] locks are used for database backup and log
> backup. Do you have any jobs backing up your temp. DB? It's pointless to
> backup your temp. DB.
> [Upd-Stat] lock is used by update statistics job. Don't understand why
> you want to update your temp. DB statistics
>
> Locking won't do you much harm in temp. DB unless some temp. tables are
> shared accross differnet sessions. I don't think that's the reason why
> your DB is so slow. There are many reasons, did you configure your disk
> correctly? spread out your tables? use filegroups? Queries blocking each
> other out?
> You need a DBA to look at your server to figure out why.
> --
> Eric Li
> SQL DBA
> MCDBA
> DallasBlue wrote:
>
>
|||NO jobs are running to back up tempdb. no-way. or no other database at the time these locks were seen.
Thanks
"DallasBlue" wrote:
[vbcol=seagreen]
> Agree with you totally. need to rewrite the procs. The volume of procs are very heavy(1000's of procs, 1000's of lines), business logic in these procs is being written from several years. hope you get the picture of complexity of the procs here.
> Create index #temp tables are being used. in lot of procs...
> Do you think these would cause [upd-Stat] lock apart from [index] lock ?
> Do you think that we should avoid creating the indexes on temptables ?
>
> Thanks!!
>
> "Eric.Li" wrote:
|||I understand your frustration, >1000 stored proc. with >1000 lines each
is no fun to maintain, let alone change/enhance.
If your #temp tables are indexed, that explains why you see those
[upd-stat] locks. You may want to turn off auto update/create statistics
If you don't have backup job running, then [BULK-OP-LOG] [BULK-OP-DB]
may be caused by auto grow / shrink. These two locks are on DB level,
turn if off see if it helps
Eric
DallasBlue wrote:
[vbcol=seagreen]
> NO jobs are running to back up tempdb. no-way. or no other database at the time these locks were seen.
> Thanks
> "DallasBlue" wrote:
>
Eric Li
SQL DBA
MCDBA
|||How about creating indexes on the temp tables with fill factor, could this decrease the index reorganization locks and upd-stat locks?
Thanks
"Eric.Li" wrote:

> I understand your frustration, >1000 stored proc. with >1000 lines each
> is no fun to maintain, let alone change/enhance.
> If your #temp tables are indexed, that explains why you see those
> [upd-stat] locks. You may want to turn off auto update/create statistics
> If you don't have backup job running, then [BULK-OP-LOG] [BULK-OP-DB]
> may be caused by auto grow / shrink. These two locks are on DB level,
> turn if off see if it helps
> Eric
> DallasBlue wrote:
>
> --
> Eric Li
> SQL DBA
> MCDBA
>