About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

MVP Logo
Pyramid Analytics

Search

Pyramid Analytics
blank
Report Portal
blank

Vardecimal storage usage in SQL Server 2005

March 25th, 2007 by Vidas Matelis

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 0×980000000100. That converts to page 0×98 (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 0×60, 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 0×73, 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 0×86, 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 0×99, 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 0×980000000100 ,and that translates into file 1 and page 0×98 (hex) or 154 (decimal).
    Previous statement returned 0×980000000100 that translates to page 0×98 (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 0×60, 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 0×74, 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 0×86, 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

Posted in SQL Server | No Comments »

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

XL Cubed