Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

In SQL Server 2008, DMV sys.dm_db_index_physical_stats, what does compressed_page_count mean in comparison with page_count. I have a table with the following info:

Object_id     Index_type_desc      avg_frag_in_percent    page_count     compressed_page_count        avg_page_space_used_in_percent       avg_record_size_in_bytes
5464654        CLUSTERED INDEX         0                    140992          1410                                        99.24                        111.2
4465464        NONCLUSTERED INDEX      0                    52532           526                                         99.69                         32.2

Does it mean, that the table has 140992 pages, but after compression it is brought down to 1410 pages or something else?

[BOL says:] MSDN compressed_page_count: The number of compressed pages.

share|improve this question

1 Answer

up vote 3 down vote accepted

The clustered index has 140992 pages, from which only 1410 are page compressed. Page compression is attempted only when it fills up, see When Page Compression Occurs:

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.

It seems that in your case very few pages got compressed. Note that you still benefit from row compression.

share|improve this answer
Thank you. You have answered several of my questions in the past and including this one too. Thank you, appreciate it greatly. I wish you were in my Phone_a_Friend_DBA phone list. – UB01 Apr 26 '12 at 17:20
Follow up question: So does the setting FILLFACTOR to 100 only work if the data in that section of the B-Tree has enough data fill up the page, right? So looks like in my table there are many non-FULL pages. Is there a way to force it to fill the pages entirely. Because for this table there are no updates. A million records are INSERTED (appended at the end) every day. So filling each page to FULL makes big difference. Your thoughts? – UB01 Apr 26 '12 at 17:26
1  
FILLFACTOR only plays a role during index build. You can try to rebuild the index and see if you get better compression. Afterwards, with an append only use pattern each page gets one and only one chance to compress. If it misses it, all subsequent inserts go in key order on the next page, so it will never trigger a compression on this page. What is your row size average avg_record_size_in_bytes? – Remus Rusanu Apr 26 '12 at 17:33
Thank you for the quick response. The numbers I have in the post (above) are after REINDEXING with FILLFACTOR 100. Yes, I agree you that since the records are added in increasing order of the clustering key, there will never be a chance to add more rows to existing pages (and perform COMPRESSION). So how do I get around this? I REINDEXed the table several times today. avg_record_size_in_bytes is 111.257 & 38.268. So about 74 and 210 records go into each page. Interestingly avg_page_space_used_in_percent is 99.24% & 99.69 %. Now I am more confused !! – UB01 Apr 26 '12 at 18:00
Also, I added avg_record_size_in_bytes & avg_page_space_used_in_percent to the original post above. – UB01 Apr 26 '12 at 18:07
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.