Timeout on small delete

[DanLynn]

Hi All,

In the database I'm currently working on (which deals with coal mining), a huge performance slowdown suddenly came up. The table that assigns cars to a coal train is taking forever to delete or insert. There are only 10 columns, and a typical train assignment is about 120 cars (rows) to one train. I created a test record which consisted of three cars, and attempted to manually delete from the car table using the query analyzer. My statement was such:

delete from unit_train_car_tracking
where train_id = 'blablablah'
and year_shipped = 2001


If I run select * from instead of delete from it takes less than one second as there are only three rows. However, deleting takes 41 seconds! The table has both a clustered and non-clustered index, both of which I am not too familiar with.

Any ideas on the delay?

Thanks,
Dan Lynn

 

[tlbroadbent]

Are there a delete trigger on the table? If yes, what does the trigger do?
Which version of SQL Server are you running?
Which columns have indexes - clustered and non-clustered?
How many records are on the table? How big are the records? What data types are used in the table?
How much free disk space is available? Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[fluteplr]

Hey Terry, you missed a couple.
Could you be having a lock problem or worse a deadlock?

 

[tlbroadbent]

And very critical points, too. Locking that causes blocking consitions and deadlocks are perhaps the most frequent cause of such slowdowns.

You can check for locking with sp_lock, sp_lock2 (if you have it) or sp_who2. Execute these SPs from query analyzer and search the output for evidence of blocking. You can also use Enterprise Manager and look at the Process Info, Locks / Process ID and Locks / Objects under Management/Current Activity. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous