April 5, 2011 at 1:13 pm
I have a Powershell script that uses SMO to get data compression status on all tables and on all dbs. I use hascompressedpartitions property to check table's data compression status. If I run select distinct object_name(object_id) from sys.partitions p inner join sys.tables t on p.object_id = t.object_id, I get a lot more results than if I used SMO. I understand that heaps and any nonclustered indexes will be skipped with SMO and therefore I check for those properties now. My problem is however, if I issue a compression statement against each of these items, the statement completes successfully, then I run either T-SQL or SMO and these objects reappear. Although if I right click the property of the object, it says it is compressed, in my case, with PAGE compression. My question is, is it the data types, compatibility level or a bug in SQL 2008R2 that are stopping me from having a successful result. What I am trying to achieve is to create a policy that will check data compression and alert when an object is found, as described in this doc
this is my ultimate goal:
select count(*)
from sys.partitions p where p.data_compression=0
in the current state, there is no way I can get p.data_compression=0
April 5, 2011 at 2:42 pm
should've RTFM, got the answer:
Nonclustered indexes do not inherit the compression property of the table. To compress indexes, you must explicitly set the compression property of the indexes. By default, the compression setting for indexes will set to NONE when the index is created.
When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.
When a heap is configured for page-level compression, pages receive page-level compression only in the following ways:
Data is inserted by using the BULK INSERT syntax.
Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax.
A table is rebuilt by executing the ALTER TABLE ... REBUILD statement with the PAGE compression option.
Here's the info
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply