April 30, 2021 at 3:02 pm
Jeff Moden wrote:ScottPletcher wrote:I have trouble even imagining the business or technical case where I'd ever design a table to have massive deletes all over the table at the same time.
You're definitely preaching to the choir there. I didn't design that table nor the processes that drive it. It just seems insane to me to have such massive deletes instead of just avoiding the writes to begin with. Heh... of course, they did a lot of strange things where I work both before I got there and now. We've made a lot of progress fixing some of the older things but tight schedules on new things still prevail and I'm not the one doing the code reviews for half the folks. I have a grand appreciation for being tired. Between ailing family and work, there's not much time for sleep. 😀
We can't avoid the writes. We must log everything clients do on our system, so that we can use it to troubleshoot later if needed.
When we ran into disk issues, we decided we didn't need more than 1.5 years of logged data online. And, since we keep all db backups for min 10 years now, we decided to just delete it, no other backup needed. Maybe it wasn't ideal, but it was the only relatively easy way I saw to get back a big chunk of space in a short time. Lucky, in a way, that we had so many log tables of such overall size.
Totally agreed. You'll just find out that REORGANIZE logs a hell of a lot more activity than even a REBUILD in the FULL RECOVERY model does.
You're also preaching to the choir about NOT keeping years of data... I just haven't won that battle with this particular table yet. It's also not the really important part for this this table. The fact that this table should NEVER fragment is the important part and the bloody deletion of rows that shouldn't have been inserted in the first place is the real problem that I need to address when I get time to do it.
Heh.. and yeah... as bad as this problem is, there are actually worse problems that are still in line ahead of this one. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2021 at 9:29 am
Clustered index scans should definitely be adversely affected by masses of empty space in pages from deleted rows, since the scan will have to read lots more pages than it otherwise would.
But they won't be affected by whatever number of empty pages (having all records marked for deletion).
Mass deletions most likely affect the same data which was mass inserted sometime ago, and which occupied bunch of consequitive pages.
Deletion will get rid of those pages as whole, not creating empty spaces in remaining pages.
_____________
Code for TallyGenerator
May 2, 2021 at 2:13 pm
ScottPletcher wrote:Clustered index scans should definitely be adversely affected by masses of empty space in pages from deleted rows, since the scan will have to read lots more pages than it otherwise would.
But they won't be affected by whatever number of empty pages (having all records marked for deletion).
Mass deletions most likely affect the same data which was mass inserted sometime ago, and which occupied bunch of consequitive pages.
Deletion will get rid of those pages as whole, not creating empty spaces in remaining pages.
No, but if pages have only a row or two, that would be a major issue.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 3, 2021 at 1:23 pm
When you insert a bulk of data, say, 8 MB (let’s take a small bulk) of a daily load, it fills 1000 pages full, and partially 1001st page, if it’s a bit more than 8Mb.
3 months later, when the data expires, you’ll delete the very same set of data, emptying 1000 pages completely, and 1 page partially.
Even if that 1page is 95% empty the overall effect of the empty space in a clustered index scan will be below 0,1%, if the table holds only 1 day worth of data. And for 3 months we have about 1 million densely populated pages mixed with 10 or 20 partially empty ones.
I don’t think there is any kind of justification for defragmenting such an index.
As Jeff repeated time after time, the real problem with fragmentation starts when you have UPDATEs, especially expAnsive ones, when you do some random DELETEs, and when you have record-by-record table population with the new records arriving not according to the order of a clustered index. But even in the latter case, if you leave the clustered index alone, the empty spaces will be likely filled up during following inserts, so lack of density would do a good service to the overall performance.
_____________
Code for TallyGenerator
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply