March 26, 2013 at 9:44 am
Recently I compressed all objects within a databases to page compression. Regardless of the object was a heap or clustered object. Heaps will keep their page compression on existing pages. New INSERT(s) that create new pages on heaps will be row compressed. I'm looking to maintain page compression and run a weekly rebuild on the heaps where row compression exists. Is the best way to approach this is to use sys.dm_db_index_physical_stats DMV? Compare the page count <> compressed_page_count. Where those two are not equal, is it fair to say there is row compression? Is there another way to go about this?
Below is an example of the script and partial output of that script.
Shouldn't I see more of a match on page count and compressed_page_count? Why are there so many mismatches?
SELECT o.name,
ips.partition_number,
ips.index_type_desc,
ips.record_count, ips.avg_record_size_in_bytes,
ips.min_record_size_in_bytes,
ips.max_record_size_in_bytes,
ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY record_count DESC;
index_type_descpage_countcompressed_page_count
NONCLUSTERED INDEX10964381077279
CLUSTERED INDEX19619301958193
HEAP 21348002134793
CLUSTERED INDEX891358891356
NONCLUSTERED INDEX253437253432
March 26, 2013 at 1:11 pm
First of all, I wanted to apologize for the multiple postings before. It was done by accident.
Okay. I found my answer to why the page_count and compressed_page_count do not match:
When a new table is created that has page compression, no compression occurs. However, the metadata for the table indicates that page compression should be used. As data is added to the first data page, data is row-compressed. Because the page is not full, no benefit is gained from page compression. When the page is full, the next row to be added initiates the page compression operation. The whole page is reviewed; each column is evaluated for prefix compression, and then all columns are evaluated for dictionary compression. If page compression has created enough room on the page for an additional row, the row is added, and the data is both row- and page-compressed. If the space gained by page compression minus the space that is required for the CI structure is not significant, page compression is not used for that page. Future rows either fit onto the new page or, if they do not fit, a new page is added to the table. Similar to the first page, the new page is not at first page-compressed.
When an existing table that contains data is converted to page compression, each page is rebuilt and evaluated. Rebuilding all the pages causes the rebuilding of the table, index, or partition.
http://msdn.microsoft.com/en-us/library/cc280464.aspx
I still need to be able to identify when a heap object needs to be rebuilt. Any help is much appreciated.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply