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

go

create procedure sp_estimated_vardecimal_reduction_alldb

as

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

   (

      Databasename,Tablename

   )

)

 

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

begin

 

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

 

   declare @obj nvarchar(256)

  

   declare tcur cursor local fast_forward for

   select distinct s.name + N''.'' + t.name 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 ty.name in (''decimal'',''numeric'')

 

   open tcur

   fetch next from tcur into @obj

   while @@fetch_status = 0

   begin

 

      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

     

   end

   close tcur

   deallocate tcur'

 

   exec sp_executesql @cmd

 

   fetch next from dcur into @db

end

 

close dcur

deallocate dcur

 

select z.* from (

select *,

case when avg_rowlen_fixed_format > 0 then

cast((((avg_rowlen_fixed_format-avg_rowlen_vardecimal_format)

/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

 

return

go

 

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

Comments

No Comments