I'm currently experiencing a strange issue that my understanding of SQL Server doesn't quite mesh with. We use SQL as our file storage for our internal storage service, and our database has about half a million rows in it. Most of the files (86%) are 1mb or under, but even on fresh copies of our database where we simply populate the table with data for the purposes of a test, it appears that rows with large amounts of data stored in a BLOB frequently cause timeouts when our SQL Server is under load.

My understanding of how SQL Server deletes rows is that it's a garbage collection process, i.e. the row is marked as a ghost and the row is later deleted by the ghost cleanup process after the changes are copied to the transaction log. This suggests to me that regardless of the size of the data in the blob, row deletion should be close to instantaneous. However when deleting these rows we are definitely experiencing large numbers of timeouts and astoundingly low performance.

In our test data set, its files over 30mb that cause this issue. This is an edge case, we don't frequently encounter these, and even though we're looking into SQL filestream as a solution to some of our problems, we're trying to narrow down where these issues are originating from.

We ARE performing our deletes inside of a transaction. We're also performing updates to metadata such as file size stats, but these exist in a separate table away from the file data itself. Hierarchy data is stored in the table that contains the file information.

Really, in the end it's not so much what we're doing around the deletes that matters, we just can't find any references to low delete performance on rows that contain a large amount of data in a BLOB. We are trying to determine if this is even an avenue worth exploring, or if it has to be one of our processes around the delete that's causing the issue.

Are there any situations in which this could occur? Is it common for a database server to come to the point of complete timeouts when many of these deletes are occurring simultaneously? Is there a way to combat this issue if it exists?

(cross-posted from StackOverflow )


Can you define a "time out"? How long does the command take to complete?

SQL Server logs the delete into the transaction log, then when the database checkpoints the delete is actually removed from the data file. When you run the delete, the pages are loaded into memory (if they aren't already there) and the delete is written to the transaction log.

When you delete data from the table how many records are you trying to delete at once?

| improve this answer | |
  • We're trying to delete single rows. My understanding was that the rows and pages were marked as deallocated and later moved into the transaction log upon commit/checkpoint, but up until that point the 'ghost' records were used as the log in the case of a delete. – Max Pollack Feb 22 '11 at 13:29
  • Nope, transaction log is done first for all transactions. Upon checkpoint those changes are then flushed from the transaction log to the data file(s). – mrdenny Feb 22 '11 at 21:52

Are there any foreign keys referencing this table, or does the table itself have any foreign keys? I've seen delete performance go down the toilet if there's a foreign key with no index on the column, particularly when you've got that many rows.

Maybe try including the execution plan while performing a delete, and see if there are any unexpected steps (e.g. table scans) happening. Also check out the sys.dm_exec_requests DMV when you've got an active blocked request, particularly the wait_type, wait_time, and wait_resource columns.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.