Tuesday, May 8, 2012

[trick] use compression SP to find partition size

I _know_ I'm missing a simple query here, probably using sys.partitions (actually, just found it.  Doh!  sys.dm_db_partition_stats), but I needed to know the size of a partition before and after adding several columns.  So...


sp_estimate_data_compression_savings
       @schema_name =  'dbo'
     , @object_name =  'my_staging' --then after adding fields
     , @index_id = NULL  
     , @partition_number = 290
     , @data_compression = 'row'

It gave me the size of that particular partition.  In retrospect the DMV is probably faster/stronger/better, but this worked in a pinch.