Estimating vardecimal storage savings in all databases

One of the new features in SQL Server SP2 is the vardecimal storage format which can be used to significantly reduce the space required by decimal and numeric columns in a table at the cost of a small CPU overhead. The storage savings must be balanced against the additional CPU usage that is required to convert the storage format of the rows every time that they are accessed. Additionally, writing to a table that is using vardecimal storage format can slow performance because of an increased number of page splits. Refer to Books Online for all the caveats and notes about using this new storage format (available only in Developer,Enterprise and Evaluation editions).

A new system stored procedure sp_estimated_rowsize_reduction_for_vardecimal allows you to supply a table name and it will return estimated space savings (if any) however it only works on 1 table at a time so I decided to "supersize" it! The procedure in the attached zip file (and shown below) will examine all tables in all databases for possible space savings from the vardecimal storage format. A couple of notes

  • You must be a sysadmin to run this procedure
  • It must be created in the master database
  • It doesn't take into account userdefined types
  • Remember these are estimates, always test to determine the IO vs CPU tradeoffs

use master


create procedure sp_estimated_vardecimal_reduction_alldb


set nocount on

declare @db sysname

declare @cmd nvarchar(4000)


create table #results


   Databasename sysname not null,

   Tablename nvarchar(256) not null,

   avg_rowlen_fixed_format decimal (12,2) null,

   avg_rowlen_vardecimal_format decimal (12, 2) null,

   row_count  int null,

   constraint pk_results primary key clustered






create table #data


   avg_rowlen_fixed_format decimal (12,2) null,

   avg_rowlen_vardecimal_format decimal (12, 2) null,

   row_count  int null



declare dcur cursor local fast_forward for

select [name] from sys.databases where database_id > 4

and state_desc = 'ONLINE'


open dcur

fetch next from dcur into @db

while @@fetch_status = 0



   set @cmd = N'use ' + quotename(@db) + N'


   declare @obj nvarchar(256)


   declare tcur cursor local fast_forward for

   select distinct + N''.'' + as [Table]

   from sys.tables t

   join sys.schemas s on t.[schema_id] = s.[schema_id]

   join sys.columns c on c.[object_id] = t.[object_id]

   join sys.types ty on c.system_type_id = ty.system_type_id

   where in (''decimal'',''numeric'')


   open tcur

   fetch next from tcur into @obj

   while @@fetch_status = 0



      truncate table #data

      begin try

      insert #data

      exec sp_estimated_rowsize_reduction_for_vardecimal @obj

      insert #results select db_name(),@obj,* from #data

      end try

      begin catch

      end catch


      fetch next from tcur into @obj



   close tcur

   deallocate tcur'


   exec sp_executesql @cmd


   fetch next from dcur into @db



close dcur

deallocate dcur


select z.* from (

select *,

case when avg_rowlen_fixed_format > 0 then


/avg_rowlen_fixed_format) * 100) as numeric(5,2))

else 0 end as [Compression%] from #results) z

where [Compression%] > 0

order by Databasename,Tablename


drop table #data

drop table #results





Published Sunday, February 25, 2007 9:04 PM by sqldbatips


No Comments