Vidas Matelis Analysis Services Blog

With SQL Server 2005 Service Pack 2 Microsoft introduced storage format vardecimal. As I work a lot with big fact tables, I decided to test how this new format affects storage. If you want to get estimated impact on using vardecimal storage format, you can run stored procedure sp_estimated_rowsize_reduction_for_vardecimal. This SP expects table name as parameter and returns row count, average row length for fixed format and average row length for vardecimal format.

I found that it is good idea to check every table for potential savings from changing storage format as it depends on how many decimal type fields there is in particular table. I noticed that in many cases it depended entirely on specific data in table on how much space was saved. But on average the more decimal type of fields I had in the table, the bigger was storage savings.

Based on my tests (scripts and results provided below) I would recommend using the new vardecimal storage format in these cases:

• When the table contains decimal type fields with a lot of 0 or NULL values. This is quite common in data warehouse loading scenarios when not all measures are available from the beginning of the data warehouse load or when some measures are context dependent. I have to point that 0 values and NULL values in a vardecimal field used exactly same storage.
• When most of the values in the decimal field have a value much smaller then the precision defined, but you had to make the precision larger due to rare high numbers.

I did some test with sample table to see how vardecimal data is save in data page. Below are steps to reproduce my tests.

• At first I created a new database. I did not want to use an existing database as during testing I will have to change database options.
  CREATE DATABASE varDecimalTest;
  go
  use varDecimalTest;
  go
• I created a new table with 2 fields. First field is just for visual reference and the second field is decimal(19,2).
  CREATE TABLE dbo.TestTable1 (id char(3) NOT NULL
  , decimalField1 decimal(19,2) NULL);
  go
• I loaded 4 records into the new table: the first one with NULL value, second with a 0 value, third with a small decimal number and fourth with a large decimal number.
  INSERT INTO dbo.TestTable1(id, decimalField1) VALUES(‘111’,NULL);
  INSERT INTO dbo.TestTable1(id, decimalField1) VALUES(‘222’,0);
  INSERT INTO dbo.TestTable1(id, decimalField1) VALUES(‘333’,123.12);
  INSERT INTO dbo.TestTable1(id, decimalField1) VALUES(‘444’,12345678901234567.12);
• To see how data is save in the page we have to find our test tables first page number. I used the following statement to get the first page number:
  SELECT p.rows, a.first_page
  FROM sys.partitions p
  JOIN sys.system_internals_allocation_units a ON a.container_id = p.partition_id
  WHERE p.object_id = OBJECT_ID(‘dbo.TestTable1’)
• In my case previous statement returned first page as 0x980000000100. That converts to page 0x98 (hex) or 152 (decimal). I run a DBCC PAGE statement to get page layout.
  dbcc traceon(3604);
  DBCC page(varDecimalTest,1,152,1)
  dbcc traceoff(3604);
• Results are below. I included just record data:
  Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
  Memory Dump @0x4428C060
  00000000: 10001000 31313144 24816300 44fef97f †….111D$.c.D…
  00000010: 0200fe†††††††††††††††††††††††††††††††…
  Slot 1, Offset 0x73, Length 19, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
  Memory Dump @0x4428C073
  00000000: 10001000 32323201 00000000 00000000 †….222………
  00000010: 0200fc†††††††††††††††††††††††††††††††…
  Slot 2, Offset 0x86, Length 19, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
  Memory Dump @0x4428C086
  00000000: 10001000 33333301 18300000 00000000 †….333..0……
  00000010: 0200fc†††††††††††††††††††††††††††††††…
  Slot 3, Offset 0x99, Length 19, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
  Memory Dump @0x4428C099
  00000000: 10001000 34343401 c880e97d f4102211 †….444….}..”.
  00000010: 0200fc†††††††††††††††††††††††††††††††…
• From the previous result we see that our test table uses 19 bytes for each record.
• Now lets run a stored procedure to estimate our storage savings if we switch to vardecimal storage:
  EXEC sp_estimated_rowsize_reduction_for_vardecimal ‘dbo.TestTable1’
• Results:
  avg_rowlen_fixed_format avg_rowlen_vardecimal_format row_count
  ———————— —————————– ———
  19.00 15.50 4
• Lets enable vardecimal storage in database:
  EXEC sys.sp_db_vardecimal_storage_format N’varDecimalTest’, N’ON’;
• Now lets do a similar test but with vardecimal storage. We will start with creating a table, enabling vardecimal storage on this table and then loading the same records:
  CREATE TABLE dbo.TestTable2 (id char(3) NOT NULL
  , decimalField1 decimal(19,2) NULL);
  go
  — Enable vardecimal format on our test table
  EXEC sp_tableoption @TableNamePattern = ‘TestTable2′
  , @OptionName=’vardecimal storage format’
  , @OptionValue=’TRUE’;
  go
  INSERT INTO dbo.TestTable2(id, decimalField1) VALUES(‘111’,NULL);
  INSERT INTO dbo.TestTable2(id, decimalField1) VALUES(‘222’,0);
  INSERT INTO dbo.TestTable2(id, decimalField1) VALUES(‘333’,123.12);
  INSERT INTO dbo.TestTable2(id, decimalField1) VALUES(‘444’,12345678901234567.12);
  Go
• There is a reason why we first changed our test tables option and then added records. Microsoft does not guarantee that the table sys.system_internals_allocation_units will always have a correct first data page number. During my tests if I insert demo data first and then change table option, I am was not getting reliable first page information.
• We are ready identify first page for the table again.
  SELECT p.rows, a.first_page
  FROM sys.partitions p
  JOIN sys.system_internals_allocation_units a ON a.container_id = p.partition_id
  WHERE p.object_id = OBJECT_ID(‘dbo.TestTable2’)
• Result in my case is 0x980000000100 ,and that translates into file 1 and page 0x98 (hex) or 154 (decimal).
  Previous statement returned 0x980000000100 that translates to page 0x98 (hex) or 154 (decimal).
• Lets run DBCC PAGE again and see how the data is now saved in the page:
  dbcc traceon(3604);
  DBCC page(varDecimalTest,1,152,1)
  dbcc traceoff(3604);
• Results from previous statement are below:
  Slot 0, Offset 0x60, Length 10, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
  Memory Dump @0x450CC060
  00000000: 10000700 31313102 00fe†††††††††††††††….111…
  Slot 1, Offset 0x6a, Length 10, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
  Memory Dump @0x450CC06A
  00000000: 10000700 32323202 00fc†††††††††††††††….222…
  Slot 2, Offset 0x74, Length 18, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
  Memory Dump @0x450CC074
  00000000: 30000700 33333302 00fc0100 1200c21e †0…333………
  00000010: c780†††††††††††††††††††††††††††††††††..
  Slot 3, Offset 0x86, Length 24, DumpStyle BYTE
  Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
  Memory Dump @0x450CC086
  00000000: 30000700 34343402 00fc0100 1800d01e †0…444………
  00000010: dc8c540c 5669f320 †††††††††††††††††††..T.Vi.
• Results analysis: Null and 0 value records use 10 bytes (was 19 in fixed storage), so we have a 9 byte savings for these records. Third demo record uses 18 bytes – just 1 byte savings. And fourth demo record uses 24 bytes – that is 5 bytes more than fixed decimal record.
• Now I have to point out that actual record size savings depends on other factors too. For example if my table would have other variable length fields, I would save 2 bytes per row. Also, the more decimal type fields are in the table, the more likely you will see bigger savings.
• Done

Note: I have to point out that the vardecimal storage option is available only in Enterprise, Developer or Evaluation editions.

Vidas Matelis

Added May 31, 2007: Microsoft released paper on this: http://msdn2.microsoft.com/en-us/library/bb508963.aspx