September 27, 2011 at 6:05 pm
I have a problem with a heap table that has a single index (not clustered).
For reasons that are not domain to the problem the table does not have a clustered index.
The table has approx 3m records, but has 18m dpages allocated.
According to sys.dm_db_index_physical_stats there are 0 forward records.
The avg_fragmentation_in_percent < 1%.
The avg_page_space_used_in_percent = 1.92%.
The table is populated via a delete, and a series of 5 or 6 insert statements.
There are no updates to the table.
The sum(max_length) for the table is 3444.
There are no LOB datatypes defined on the table (only uniqueidentifier, int, money, datetime, bit, varchar, char).
By my calculation there should be around 2m dpages allocated.
From the above there should be at least 15m pages with no data, but the pages don't seem to get deallocated when they have no data on them.
What gives? What's going on? It appears that a heap deletes data from it's pages, but never reuses the empty space. When data is added it just adds more pages to the end of the data . Is this correct?
SQL Version is Microsoft SQL Server 2008 (SP1) - 10.0.2714.0
As a consequence the database has blown out in size. Just to stop the non value adding 'Oh you should have a clustered index' type responses, this is what we are planning:-
To resolve the issue we will BCP the data out, truncate the table then BCP the data back in. This will be followed by the addition of a clustered non unique index to make sure the problem does not reoccur (provided we get the vendors OK, and the performance testing passes).
September 27, 2011 at 8:48 pm
It would depend on the deletes. The extents could be reused, and dropped out on deletes, but if you deleted a page or two out of each extent, I'm not sure if SQL would go back through and start to reuse the pages in allocated extents in a heap. It might allocate new extents.
I'll ask around about the storage engine here.
Do you have a structure of the rows? Are they very wide?
September 28, 2011 at 1:37 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 6:13 am
Gail
If we ever meet I'll be buying you a beer. Hell - I'll be buying you several! Many thanks for the link, if gives me something to work with.
pcd
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply