November 4, 2016 at 9:44 am
psred (11/3/2016)
Eric I have ran the query you have given and dont see any change before and after compression results.Eirikur it is not the archival data.
So, it didn't estimate any change at all?
Was that for ROW or PAGE compression? I'd suggest estimating for PAGE compression.
Also, run the following query, replacing reference to MySchemaName and MyTableName with the actual name for the table in question. This will tell you the space allocated to LOB and ROW OVERFLOW pages versus space allocated for IN-ROW pages, in addition to some other useful statistics.
select
SCHEMA_NAME(o.schema_id) as schema_name,
o.nameAS object_name,
isnull(i.name,'HEAP') AS index_name,
i.type_descAS index_type,
case p.data_compression_desc
when 'NONE' then 'NO'
when 'PAGE' then 'PAGE'
when 'ROW' then 'ROW'
end + ' COMPRESSION' as data_compression_desc,
sum(p.rows) as row_count,
cast(((sum(ps.in_row_used_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as in_row_gb,
cast(((sum(ps.row_overflow_used_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as row_overflow_gb,
cast(((sum(ps.lob_reserved_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as lob_reserved_gb,
cast(((sum(ps.in_row_used_page_count
+ ps.row_overflow_used_page_count
+ ps.lob_reserved_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as total_gb
from sys.dm_db_partition_stats ps
join sys.partitions p
on ps.partition_id = p.partition_id
join sys.objects o
on o.object_id = p.object_id
and o.is_ms_shipped = 0
and schema_name(o.schema_id) = 'MySchemaName'
and o.name in ('MyTableName')
join sys.indexes i
on p.index_id = i.index_id
and p.object_id = i.object_id
group by
SCHEMA_NAME(o.schema_id),
o.name,
i.name,
i.type_desc,
p.data_compression_desc
order by schema_name, object_name;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 4, 2016 at 11:18 am
Yes I checked at page level and see no difference in compression before and after.
Also the second query you sent resulted with lob_reserved_gb and total_gb are almost the same.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply