October 25, 2002 at 3:56 pm
Greetings: I have a SQL7.0 running and from times to times it get slow response time. I have run profiler to detect what is causing and found nothing. When it get slow I have found a strange behavier on tempdb database. I have found in this database a large number of SPID with the same value and in each of this SPID, locktype have EXT value and mode is U. With SPID value I have try to find what sql statement is executing but found nothing. When I kill this SPID then it would return to normal response time. Can anyone advise how can I identify and solve this strange behavier?
October 27, 2002 at 1:14 pm
running
dbcc inputbuffer(spid)
will return the first portion of what is being executed.
Also run profiler and capture the Completed events and look for the one with the correct spid.
The problem looks like a task that is creating a temporary table populating it, this is resulting in lots of locks.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 28, 2002 at 10:36 am
Thank you for your reply Mr. Sabin, but when execute your command DBCC INPUTBUFFER(spid)
it return null values. So I cannot identify
source problem... ANy other idea??
October 28, 2002 at 11:48 am
Do you have join sentences executing...remeber sql use tempdb to execute joins. Maybe you should check how the queries are builded and maybe consider checking indexes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply