JD Edwards and SQL Server Compression

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s