Over the last few weekends we’ve been compressing a JD Edwards database, and I thought I’d post the results. The following is a report based on the project.

Compression Results

Summary:

Over the weekend I completed compressing the Top 10 Tables per our original discussion.

Initially, the database was 273GB of space used inside it. After all the compression has been finished, we are now at 157GB.

This is a reduction of 42.51% in the JDE database size.

The database files are still the same size. I have not shrunk them yet… and wouldn’t advise it unless you needed the space back for some reason or another.

You should now be able to go years without having to consider adding any more disk space or worry about IO problems.

List of Tables/Indexes Compressed

	Schema	Table	Rows	Type
1	PRODDTA	F0101Z2	2728142	PAGE
2	PRODDTA	F0115	3583766	ROW
3	PRODDTA	F03B11	3813755	PAGE
4	PRODDTA	F03B14	5277380	PAGE
5	PRODDTA	F03B21	6766369	PAGE
6	PRODDTA	F0911	19424122	PAGE
7	PRODDTA	F4074	6801498	PAGE
8	PRODDTA	F4111	8210392	PAGE
9	PRODDTA	F42119	8104459	PAGE
10	PRODDTA	F49211	2799436	PAGE
11	PRODDTA	F49299	6878974	PAGE

Data Points:

Baseline:

BACKUP DATABASE successfully processed 33442892 pages in 473.482 seconds (551.811 MB/sec).

After First Wave of Compression:

BACKUP DATABASE successfully processed 26944689 pages in 340.404 seconds (618.398 MB/sec).

After Final Compression:

BACKUP DATABASE successfully processed 19244052 pages in 354.502 seconds (424.099 MB/sec).

Final File Sizes and Utilization :

As you can see, you now have 434GB of empty space in your database now…

FILEID	FILE_SIZE_MB	SPACE_USED_MB	FREE_SPACE_MB	NAME
1	75000.00	23223.69	51776.31	JDE_PROD_Data
2	109000.00	92.33	108907.67	JDE_PROD_Log
3	100000.00	38333.88	61666.13	JDE_PROD_Data2
4	150000.00	44162.38	105837.63	JDE_PROD_Data3
5	150000.00	44173.75	105826.25	JDE_PROD_Data4

Here is a list of the scripts that I ran for each table over the weekend and the amount of time it took to compress each one. To remove the compression on a table, run the same script, but just change the followiong parameter:   DATA_COMPRESSION = PAGE  to DATA_COMPRESSION = NONE

— 13 min 50 sec

ALTER INDEX ALL ON [PRODDTA].F03B14 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 1 min 47 sec

ALTER INDEX ALL ON [PRODDTA].F03B21 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 1 hour 6 min 18 sec

ALTER INDEX ALL ON [PRODDTA].F42119 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 17 min 17 sec

ALTER INDEX ALL ON [PRODDTA].F4111 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 3 hours 27 min 57 sec

ALTER INDEX ALL ON [PRODDTA].F0911 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

—  F49299 2 min 36 sec

ALTER INDEX ALL ON [PRODDTA].F49299 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— F49211 2 min 7 sec

ALTER INDEX ALL ON [PRODDTA].F49211 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— F0101Z2 4 min 56 sec

ALTER INDEX ALL ON [PRODDTA].F0101Z2 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);