Possible problem with BLOB insert

[danshelb]

We are implementing an Application (VB) using a SQL 2000 backend. When running under a load of about 500 transactions per minute across multiple stations, we experience locks and timeouts.

Running Profiler we've traced the problem to a BLOB insert the application does on every transaction. The BLOB ranges between 10-200K per, and the only other row in the blob's table is a sequential, indexed integer value.

Stopping and restarting the database does not fix the problem; however reindexing the BLOB's table fixes it immediately, until we ramp up in load again and it dies. I don't have access to the vendor's VB code to see how, exactly, they are inserting the BLOB. Does anyone have any suggestions or ideas that would correct or help further troubleshoot the problem?

Thanks,
Dan

 

[TheBugSlayer]

You should be able to see the INSERT statement in Profiler. I would assume the VB application inserts the data into the BLOB field by chunks and then does an Update on the recordset. They might also be using WRITETEXT in a SQL Server procedure or function.