-1

I was trying to implement 'Row' data compression on sql server data table. After successfully executing script on one data table which was having 116000 records and 30 columns (10-15 columns could be eligible for Row data compression) I follow below steps for physical memory consumption verification:

Step 1) I calculate storage space for all my data file(mfd,ldf and ndf) for data base in drive .which comes out to be 6.63 GB.

Step 2) Successfully executed Row data compression script e.g.

USE [DbName]
ALTER TABLE [dbo].[TableName] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = ROW
)

Step 3) follow step 1) and it comes out to 6.66GB. There was no single transaction and data insertion between these steps.

Am i doing wrong verification or i have wrong thinking about data compression.

Assumptions: Data columns with Int(4 byte) data type and storing value less than 100 will be compressed to store in 1 byte(small Int) of space. Similarly for other data columns.

ironman
  • 629
  • 8
  • 20
  • How did you calculate sapce used? Based on datafiles? This is wrong, because SQL Server will not release space. You must look to table space used. – user_0 Jan 07 '15 at 08:34
  • Did you include the log in the calculation? Logs are transient. Do not count them. – usr Jan 07 '15 at 10:33
  • If you are talking about .ldf file then Yes i included log file in calculation. – ironman Jan 07 '15 at 11:01
  • I'm unclear on where we stand. What's your plan right now? – usr Jan 07 '15 at 11:24
  • This time i calculated storage amount without log file (.ldf) and result is same. log (.ldf) file storage amount increased but .mdf file size increased significantly. – ironman Jan 08 '15 at 05:15
  • Well, the solution to your problem is given on this page. You just have to follow up on everything that ways said. – usr Jan 08 '15 at 10:58

1 Answers1

0

There's procedure that shows you the data: sp_estimate_data_compression_savings. You can run it prior to assess if the table will benefit from compression or not.

As for table size, you can use system function to determine the size. This can be a good starting point:

declare @object_id int = object_id('dbo.mytable');
declare @dbid int = db_id();
select 
table_size_in_bytes = page_count * 8192
--, * 
from sys.dm_db_index_physical_stats(@dbid, @object_id, -1, 0, null)
where index_id in (0,1);
Endrju
  • 2,183
  • 14
  • 21