Is there any way to deallocate empty pages on a heap table during a delete operation other than putting an exclusive lock on the table or rebuilding it? None of these options are acceptable for my production environment.
Add a comment
|
2 Answers
Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space.
Otherwise, you're looking at:
- ALTER TABLE (mytablename) REBUILD - which takes it offline
- Doing deletes with TABLOCK hints
- TRUNCATE TABLE (mytablename)
I know some folks think it's trendy, but heaps just aren't a good fit for active OLTP systems that have to deal with deletes (which cause the empty space problem) and updates (which cause the forwarded fetches problem.)
answered Apr 7, 2017 at 11:30
-
Yes, I have been deleting with TABLOCK thinking that it will put a shared lock which is OK for my environment. But when checking locks on the table I`ve found out that it put an exclusive lock for DELETE operation Commented Apr 7, 2017 at 11:49
-
6Yep, when you say lock the table, that isn't sharing. :-D Commented Apr 7, 2017 at 12:26
You can reclaim the space by shrinking.
From SQLskills SQL101: Why does my heap have a bunch of empty pages? by Paul Randal (emphasis added):
On the extreme end (in my opinion), you could reclaim the empty heap space using a shrink operation. Shrink won’t free up space inside pages as it moves them (with the exception of compacting LOB pages as it goes – somewhat unsuccessfully depending on which version and build you’re on – see KB 2967240), but it will remove empty pages rather than moving them. This will effectively shrink the heap after a large delete, but with the usual caveats about shrink causing index fragmentation and generally being an expensive, slow operation to perform.
