April 26, 2011 at 1:59 pm
Reading through this http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx SQLCAT post, it is stated that under certain condititions a row in a PAGE COMPRESSED table can be ROW COMPRESSED instead of actually PAGE COMPRESSED.
My question is if there is a way to actually know, at the ROW level, if the data row is actually ROW or PAGE compressed?
--
Thiago Dantas
@DantHimself
April 26, 2011 at 2:17 pm
dant12 (4/26/2011)
Reading through this http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx SQLCAT post, it is stated that under certain condititions a row in a PAGE COMPRESSED table can be ROW COMPRESSED instead of actually PAGE COMPRESSED.My question is if there is a way to actually know, at the ROW level, if the data row is actually ROW or PAGE compressed?
Could you cite the exact piece of that article that gave you that understanding?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 2:32 pm
SQLRNNR (4/26/2011)
dant12 (4/26/2011)
Reading through this http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx SQLCAT post, it is stated that under certain condititions a row in a PAGE COMPRESSED table can be ROW COMPRESSED instead of actually PAGE COMPRESSED.My question is if there is a way to actually know, at the ROW level, if the data row is actually ROW or PAGE compressed?
Could you cite the exact piece of that article that gave you that understanding?
Sorry, it indeed is a large article.
It was the Newly-Inserted Rows topic.
also, thanks for Paul White(SQL Kiwi) for pointing me towards the article on twitter
--
Thiago Dantas
@DantHimself
April 26, 2011 at 2:46 pm
I see. That makes it very interesting. My understanding from reading that is that the Row compression would only be temporary if the Page is Page Compressed. It makes sense when further above in the article it states that the Page Compression is a superset of row compression.
This now turns into needing to find out which pages are page compressed (since not all pages have to be compressed based on the algorithm) for any given table. And then to find which rows are on which pages. Deep Dive into internals.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 2:47 pm
dant12 (4/26/2011)
Reading through this http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx SQLCAT post, it is stated that under certain condititions a row in a PAGE COMPRESSED table can be ROW COMPRESSED instead of actually PAGE COMPRESSED.My question is if there is a way to actually know, at the ROW level, if the data row is actually ROW or PAGE compressed?
Other than curiosity, why do you want to know? What will you be able to do with that information?
April 26, 2011 at 3:01 pm
depending on the fill factor settings and table load, indexes can take awhile to actually get fragmented. getting an actual percentage of rows not currently compressed can factor in the decision of rebuilding an index. this percentage would be smaller in tables with a clustered index, but in heaps it can become a big percentage
but yes, it's mostly curiosity
--
Thiago Dantas
@DantHimself
April 26, 2011 at 3:17 pm
dant12 (4/26/2011)
depending on the fill factor settings and table load, indexes can take awhile to actually get fragmented. getting an actual percentage of rows not currently compressed can factor in the decision of rebuilding an index. this percentage would be smaller in tables with a clustered index, but in heaps it can become a big percentagebut yes, it's mostly curiosity
I saw nothing in that article that said that rows would not be compressed, only that they would be be compressed with row compression or page compression, depending on SQL Servers internal algorithm, and that index non-leaf pages are always row compressed on indexes that are page compressed.
April 26, 2011 at 3:40 pm
Michael Valentine Jones (4/26/2011)
dant12 (4/26/2011)
depending on the fill factor settings and table load, indexes can take awhile to actually get fragmented. getting an actual percentage of rows not currently compressed can factor in the decision of rebuilding an index. this percentage would be smaller in tables with a clustered index, but in heaps it can become a big percentagebut yes, it's mostly curiosity
I saw nothing in that article that said that rows would not be compressed, only that they would be be compressed with row compression or page compression, depending on SQL Servers internal algorithm, and that index non-leaf pages are always row compressed on indexes that are page compressed.
Exactly.
I didn't mean in the sense that the row wouldn't be compressed, but in the sense that it would only be row compressed and not page compressed.
Rows in a heap are not automatically compressed to PAGE level after inserts, thus requiring a rebuild of the table partition.
ps: interestingly enough, a partial answer to the question is answered in the appendix D and E of the same article, doh
sys.dm_db_index_physical_stats with the DETAILED parameter returns both a page_count and a compressed_page_count, next best thing i guess
--
Thiago Dantas
@DantHimself
April 26, 2011 at 3:43 pm
dant12 (4/26/2011)
Michael Valentine Jones (4/26/2011)
dant12 (4/26/2011)
depending on the fill factor settings and table load, indexes can take awhile to actually get fragmented. getting an actual percentage of rows not currently compressed can factor in the decision of rebuilding an index. this percentage would be smaller in tables with a clustered index, but in heaps it can become a big percentagebut yes, it's mostly curiosity
I saw nothing in that article that said that rows would not be compressed, only that they would be be compressed with row compression or page compression, depending on SQL Servers internal algorithm, and that index non-leaf pages are always row compressed on indexes that are page compressed.
Exactly.
I didn't mean in the sense that the row wouldn't be compressed, but in the sense that it would only be row compressed and not page compressed.
Rows in a heap are not automatically compressed to PAGE level after inserts, thus requiring a rebuild of the table partition.
ps: interestingly enough, a partial answer to the question is answered in the appendix D and E of the same article, doh
sys.dm_db_index_physical_stats with the DETAILED parameter returns both a page_count and a compressed_page_count, next best thing i guess
I should have kept reading because that is the step I was working on - pulling info from that function to get the pages and comrpessed pages.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply