Summary: A PeopleSoft 9.1 database on SQL Server compressed a maximum of 70%.

I’ve been doing some rudimentary testing today on a customer’s SQL Server 2008 R2 PeopleSoft database just to see what the compression ratios are. Different data types compress at different rates, but this post will show you what a PeopleSoft 9.1 database compresses at.

One large table:

ALTER INDEX ALL ON PS_CM_MATCHED_COST REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, data_compression = page, STATISTICS_NORECOMPUTE = ON, maxdop = 0);

sp_spaceused PS_CM_MATCHED_COST

-- NO COMPRESSION

name                  rows       data      index_size 
------------------- --------- ----------- ------------ 
PS_CM_MATCHED_COST  52661662  10406784 KB   135888 KB 

-- PAGE 
-- 1 min 29 sec to compress to PAGE
 
name                  rows       data       index_size 
------------------- --------- ------------ ------------ 
PS_CM_MATCHED_COST  52661662    2168888 KB    11496 KB 

-- ROW
name                  rows        data      index_size 
-------------------- --------- ----------  ------------
PS_CM_MATCHED_COST   52661662   5455040 KB    28176 KB 

Using PAGE compression, it took 2 hours, 6 minutes and 22 seconds to compress the

Here’s the uncompressed baseline:

FILE_SIZE_MB  SPACE_USED_MB NAME       FILENAME
------------- ------------- ---------- ------------------- 
242595.63     236784.56     FSSYS_Data F:\data\FS91SSD.mdf
   700.50         10.58     FSSYS_Log  L:\FS91SSD_log.LDF

Using PAGE compression, it took 2 hours, 6 minutes and 22 seconds to compress the 236GB of data and indexes. The size went from 236GB used to 70GB for a reduction of 70%.

FILE_SIZE_MB SPACE_USED_MB  NAME       FILENAME
------------ -------------- ---------- ---------------------
242595.63    70592.00       FSSYS_Data F:\data\FS91SSD.mdf
   700.50       78.89       FSSYS_Log  L:\FS91SSD_log.LDF

Compression with ROW method the database goes from 236GB to 142GB:

FILE_SIZE_MB SPACE_USED_MB  NAME       FILENAME
------------- ------------- ---------- ------------------
142500.00     142190.44     FSSYS_Data F:\data\FS91SSD.mdf
  1300.50         21.27     FSSYS_Log  L:\FS91SSD_log.LDF

Here’s the script to compress the entire database:

DECLARE @SQL NVARCHAR(2000) 
declare @psfttblname sysname

declare tblname cursor for select name from sysobjects where name like 'PS_%' and type = 'U'
OPEN tblname

FETCH NEXT FROM tblname 
INTO @psfttblname

WHILE @@FETCH_STATUS = 0
BEGIN
 
-- SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' REBUILD WITH (DATA_COMPRESSION=PAGE, maxdop = 0)' 
 set @SQL = 'ALTER INDEX ALL ON '+ @psfttblname+' REBUILD WITH (SORT_IN_TEMPDB = ON, data_compression = page, STATISTICS_NORECOMPUTE = ON, maxdop = 0);'
 print @SQL
 print 'start: '+cast(getdate() as nvarchar(20))
 EXEC (@SQL) 
 print 'end: '+cast(getdate() as nvarchar(20))
 print ''
FETCH NEXT FROM tblname 
INTO @psfttblname
end
close tblname
deallocate tblname

Space Free and Check Status of Compression Scripts:

select
 a.FILEID,
 [FILE_SIZE_MB] =
 convert(decimal(12,2),round(a.size/128.000,2)),
 [SPACE_USED_MB] =
 convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
 [FREE_SPACE_MB] =
 convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
 NAME = left(a.NAME,15),
 FILENAME = left(a.FILENAME,30)
 from
 dbo.sysfiles a

-- http://stackoverflow.com/questions/16988326/query-all-table-data-and-index-compression

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
 [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

SELECT [t].[name] AS [Table], [i].[name] AS [Index], 
 [p].[partition_number] AS [Partition],
 [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] > 1 --and object_id('PS_CM_MATCHED_COST') = t.object_id

To shrink the database file from 242GB to 80GB took 37 minutes:

-- shrink database

dbcc shrinkfile('FSSYS_Data', 80000)

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
---- ------ ----------- ----------- --------- --------------
6      1    10240000    256000      9014872   9014872

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- 37 min