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