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);