I have the following scenario:
A huge table, that is also the most use table by far in the system. By huge I mean it is huge in number of rows and also huge in actual disk size, because it has 2 byte array columns that saves files from our system.
The problem is when I need to remove a large number of rows from it.
First I tried just a single delete with
in statement, but is froze the whole table (inutilizing the system with it), for about 45 minutes, when I decide to restart the database.
Then I tried deleting by chunks of 10 registries, and tried deleting 8k+ rows. It went really slow, and everywhere that got data from that table was slow too, and when it reached about 700 rows deleted, I started another process that used that same table, and got a deadlock.
How should I make these mass deletions from this table? I thought about splitting the table in 2, one for the 2 byte arrays that contains the most part of table volume, and other with the rest of the informations (that are simple things like varchars, integers, datetimes and so on). Would doing this benefit me on these cases of deletion? Or the number of rows would still cause these locks on the table?
The version of SQL is Microsoft SQL Server Standard (64-bit) 12.0.5000.0.