GOING. NUTS.
Okay, I'm sorta putting out a generic cry for help because I know a lot of people who follow me are super smart and hopefully someone can give me some insight or an avenue to explore that I haven't yet.
I. Hate. SQL. Server. Performance. Tuning.
We're having a problem here. We have a file storage service in our system that consists of two tables, one that stores hierarchy information and file data, and one that stores metadata such as file properties, folder stats, etc. Most of our file data is less than a meg, but we have some edge cases of files that are REALLY large. Also, it only appears that the problem is occurring when we're accessing the file storage service through our SOAP service, but that doesn't make sense because in the end everything goes through the same stored procs and data layer.
Under load, SINGLE ROW delete performance in the table that stores file data is ABYSMAL on large files - I'm talking query timeout abysmal. I know that SQL server's deletion methods are a garbage collection process involving marking deleted rows as ghost rows, and I also know that dealing with files this large, the data pages that store the BLOB data are exclusive to the large BLOB, i.e. they're not shared with data from other rows. Delete speed should be the same regardless of data size, because copying that BLOB data into the transaction log is an asynchronous process - correct me if I'm wrong, PLEASE. During the scope of the transaction in which we're performing the delete, the deallocated pages and ghost rows act as the transaction log information and they're cleaned up and logged at the next checkpoint - again, correct me if I'm wrong.
The delete process is somewhat complex but basically consists of selecting from a view that contains a combination of the file data table and the metadata table and updating stats on parent folders (total file size, file count), then deleting children of the object, then deleting the object itself, then updating stats in the metadata table. While I can't confirm it (the data model doesn't seem to reflect it) I have to assume that there's some sort of constraint linking the data table and the metadata table with a cascading delete between the two - I think this may be a pain point?
When we do the child delete, we perform a delete on the data table, filtering by UserId first and then by the path of the object, using a like statement with the full path of the object being deleted. The data table has a non-clustered index on UserId and FullPath. The metadata table's primary key matches that of the primary key in the data table and has an index on that key as well.
We've done some serious optimization to ensure that all rows touched by the delete process are locked pre-emptively before the proc runs, so that following delete statements are queued rather than possibly deadlocking while trying to operate on rows shared between the two operations. The delete statements themselves seem to have fairly low read numbers.
We created a test fixture that uploads 20 files of random size between .1mb and 50mb to the file storage of 10 users under the same parent user (but all their hierarchies are independent of eachother) and then cycles through the process of selecting all the files in the target folder, renaming them, then deleting them. Selecting touches the data table, as does renaming, and deletion touches both the metadata table and the data table. Concurrency is a requirement for encountering this issue, so each test user connects in a thread and performs this process simultaneously.
When this happens, it's almost instantaneous that timeouts start occuring, and it's never on renaming - always deletion. Occasionally select statements will run slow but I believe that those select statements are running slowly because of raw disk IO and CPU utilization and blocking/deadlocking are not a factor. Execution plans also show absolutely no abnormal activity - in general, I'm not aware of any table scans that are occuring or other dead ringers for poor performance.
There's only one other clue I have right now, and that's that when I run the following query:
----Find Row lock waits
declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname=i.name, i.index_id --, partition_number
, row_lock_count, row_lock_wait_count
, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_in_ms
, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count descI see that our metadata table (and its index on the primary key) accounts for a disproportionate number of row locks and blocking operations compared to the rest of the database - i.e. total row lock wait time in ms is 256 times greater than that of the next guilty operation, and it is the source of 41 times more row locks than any other object in the database, even though its average row lock wait time is lower than the worst culprit (its average wait time is 44.3ms, the worst is our user table with an average wait time of 2191.5ms) but I really don't know what to do with this other information other than believe that the metadata table and not the file data itself is responsible for the delete performance. Still, I'm having a hard time reconciling this with the fact that file size APPEARS to be a direct factor in whether or not the issue is encountered at all.
I really hope this is at all useful to anyone who might have enough knowledge to make an intelligent suggestion, thanks.
I. Hate. SQL. Server. Performance. Tuning.
We're having a problem here. We have a file storage service in our system that consists of two tables, one that stores hierarchy information and file data, and one that stores metadata such as file properties, folder stats, etc. Most of our file data is less than a meg, but we have some edge cases of files that are REALLY large. Also, it only appears that the problem is occurring when we're accessing the file storage service through our SOAP service, but that doesn't make sense because in the end everything goes through the same stored procs and data layer.
Under load, SINGLE ROW delete performance in the table that stores file data is ABYSMAL on large files - I'm talking query timeout abysmal. I know that SQL server's deletion methods are a garbage collection process involving marking deleted rows as ghost rows, and I also know that dealing with files this large, the data pages that store the BLOB data are exclusive to the large BLOB, i.e. they're not shared with data from other rows. Delete speed should be the same regardless of data size, because copying that BLOB data into the transaction log is an asynchronous process - correct me if I'm wrong, PLEASE. During the scope of the transaction in which we're performing the delete, the deallocated pages and ghost rows act as the transaction log information and they're cleaned up and logged at the next checkpoint - again, correct me if I'm wrong.
The delete process is somewhat complex but basically consists of selecting from a view that contains a combination of the file data table and the metadata table and updating stats on parent folders (total file size, file count), then deleting children of the object, then deleting the object itself, then updating stats in the metadata table. While I can't confirm it (the data model doesn't seem to reflect it) I have to assume that there's some sort of constraint linking the data table and the metadata table with a cascading delete between the two - I think this may be a pain point?
When we do the child delete, we perform a delete on the data table, filtering by UserId first and then by the path of the object, using a like statement with the full path of the object being deleted. The data table has a non-clustered index on UserId and FullPath. The metadata table's primary key matches that of the primary key in the data table and has an index on that key as well.
We've done some serious optimization to ensure that all rows touched by the delete process are locked pre-emptively before the proc runs, so that following delete statements are queued rather than possibly deadlocking while trying to operate on rows shared between the two operations. The delete statements themselves seem to have fairly low read numbers.
We created a test fixture that uploads 20 files of random size between .1mb and 50mb to the file storage of 10 users under the same parent user (but all their hierarchies are independent of eachother) and then cycles through the process of selecting all the files in the target folder, renaming them, then deleting them. Selecting touches the data table, as does renaming, and deletion touches both the metadata table and the data table. Concurrency is a requirement for encountering this issue, so each test user connects in a thread and performs this process simultaneously.
When this happens, it's almost instantaneous that timeouts start occuring, and it's never on renaming - always deletion. Occasionally select statements will run slow but I believe that those select statements are running slowly because of raw disk IO and CPU utilization and blocking/deadlocking are not a factor. Execution plans also show absolutely no abnormal activity - in general, I'm not aware of any table scans that are occuring or other dead ringers for poor performance.
There's only one other clue I have right now, and that's that when I run the following query:
----Find Row lock waits
declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname=i.name, i.index_id --, partition_number
, row_lock_count, row_lock_wait_count
, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_in_ms
, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count descI see that our metadata table (and its index on the primary key) accounts for a disproportionate number of row locks and blocking operations compared to the rest of the database - i.e. total row lock wait time in ms is 256 times greater than that of the next guilty operation, and it is the source of 41 times more row locks than any other object in the database, even though its average row lock wait time is lower than the worst culprit (its average wait time is 44.3ms, the worst is our user table with an average wait time of 2191.5ms) but I really don't know what to do with this other information other than believe that the metadata table and not the file data itself is responsible for the delete performance. Still, I'm having a hard time reconciling this with the fact that file size APPEARS to be a direct factor in whether or not the issue is encountered at all.
I really hope this is at all useful to anyone who might have enough knowledge to make an intelligent suggestion, thanks.