FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> -----Original Message-----
> From: Steven Campbell [mailto:scampbel AT jamedia.com]
> Sent: October 24, 2001 10:09 AM
> Subject: [freetds] Locking & SQL Server 7.0
> 
> Hello. My company has a SQL Server 7.0 SP3 servers, Windows NT 4.0
> servers running ColdFusion 4.0 that use M$'s ODBC drivers et 
> al to drive a ColdFusion application. 
> 
> I have another process running on OpenBSD 2.9, FreeTDS 0.52, Apache
> 1.3.20 and PHP 4.0.6 that generates PDF files that outside users (in
> front of our firewall) use to download Clipping Labels to PDF 
> files.  When outside users are using the OpenBSD boxen to
> generate PDF files, the stored procedures and queries on the 
> ColdFusion equipment stall for up to two minutes. 
> 
> I have attempted to solve the problems by feeding the PHP script
> locking hints like so:
> 
> $query = "UPDATE Clipping_Label WITH(UPDLOCK, REPEATABLEREAD) SET
> Fax_Status = 'P' WHERE Label_ID IN ( $LABEL_ID )";
> 
> This has no effect on the performance of the ColdFusion stuff. 

Hi Steven, 

Yup, you have a contention issue.  The transport layers don't matter.  

Do I understand correctly that the PDF generating system issues that (or
any) update statement?  If so, it's a great way to block other processes,
especially if the table is large.  How many rows are we talking about here?


If Lable_ID isn't the first member of the clustered index on Clipping_Label,
there's a chance the update statement is doing a table scan.  

My favorite technique, in addition to Brian's suggestions (including update
statistics, just to be on the safe side) is the SQL Server Profiler.  You
can watch each statement and see its I/O stats and duration.  If a box is
acting weird, I just set it to watch all queries for all users for a little
while and scan the results for resource hogs.  It will be pretty easy to see
who's clogging up the works.  

HTH.

--jkl