RE: BDL Performance on SQL Server

This might help locating them:-

--------
use master

set transaction isolation level read uncommitted

create table #t_locks (
spid smallint,
dbid smallint,
objid int,
indid smallint,
type char(4),
resource char(16),
mode char(8),
status char(5))

insert into #t_locks
exec sp_lock

select t.objid TEMPDB, o.name, t.type, t.mode, s.lastwaittype, s.loginame,
s.hostprocess, s.blocked, count(*)
from #t_locks t, tempdb..sysobjects o, sysprocesses s
where t.dbid = 2
and o.id = t.objid
and t.spid = s.spid
group by t.objid, o.name, t.type, t.mode, s.lastwaittype, s.loginame,
s.hostprocess, s.blocked
order by s.blocked desc, o.name, t.mode

select * from #t_locks
where dbid = 2

drop table #t_locks 
--------

If they are shared locks (read only) you can avoid them by using "set
transaction isolation level read uncommitted" (in an SQL block), as in the
above script.  If there are 1000's of extent locks I guess the temp tables
are pretty large.  Check also that tempdb is large enough and isn't having
trouble growing, and split it across disks if you can.

Tim


-----Original Message-----
From: Richard Turner [mailto:[email protected]] 
Sent: 21 March 2005 15:11
To: 4Js User Group
Subject: [fourjs-users] BDL Performance on SQL Server

Hi all,

We've been trying to tune a few 4GL applications so they can run effectively
against a SQL Server database.  Certain programs seem to cause problems
where they are generating thousands of extent locks on the tempdb database.
When the locks get high enough, the whole system seems to grind to a halt.  

I've been looking at areas in the code where temp tables are used but have
not had success pinpointing the issue.  There are a few places where temp
tables are being used in cursors so maybe this is related??
Would indexes on my temp tables help decrease these extent locks?

Any ideas would be very appreciated.

Thanks,


Richard


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************