First published on MSDN on Oct 30, 2008
Well meanwhile the Roll-Out is as good as finished. The only tables remaining are some tables with BLOB fields which can’t be done ’online’. Those tables will be done in our quarterly downtime where we need to apply a whole stack of new SAP Support Packages, exchange to more recent SAP executables, etc. But all tables which could be done online and some of the smaller tables with BLOB columns meanwhile are in Row-level format. As mentioned in the first articles already, we only talk about the Clustered Indexes/Data and not the non-clustered indexes at this point in time. SAP does not yet support compression on non-clustered indexes. SQL Server 2008 would be able to compress non-clustered indexes as well. We are working with SAP to allow compression of non-clustered indexes in the future as well.
So what did we experience? Our data volume shrunk by 1.75TB. This represents space savings of 29%. Since we are honest folks, we will not hide that parts of that effect roots in the defragmentation which takes place when we rebuild the tables in the Row-Level format. However since the database got completely unloaded and reloaded in February 2007 during our Unicode Migration, we had plenty of older data in the tables which wasn’t fragmented at all. Also given the fact that we got the original SAP productive database around 10 times in sandbox, test systems and our DR site, the savings on disk space really accumulate up.
The effects on different tables varied. Some larger effects we saw on the table ANLC which shrunk to 1/5 of its origin size. Also some of our custom tables shrunk down to 40% of its origin. We had one of those custom tables which was around 700GB with 1.6 billion rows which ended at around 280GB after the Row-Level compression. COSP out of the SAP standard tables also compressed extremely well shrinking to ¼ of its origin size.
The best of it all, it doesn’t cost any CPU. Row-Level compression is just another way to store some datatypes. Hence all we saw was reduction of I/O rate, but no measurable increase in CPU consumption.
So what do we do with all the free space in our SAP ERP system? Well we currently look at 2TB free space with around 4.6TB data volume in the database remaining. Since our monthly growth rate at the moment is around 150GB (due to row level compression it went down as well), it only is a matter of time when the free space is eaten up again. Hence we simply leave the sizes of data file as is now and have the monthly growth rate eat up all this free space again but we could delay the purchase of additional storage thanks to Row level Compression.
The Row Level compression work was done in an online mode over 4 weekends and no negative impact to any business processes occurred. The execution was done very successfully and has gotten a lot of attention within Microsoft IT to repeat it also with other Non-SAP SQL Server databases.
The stored procedure we used and will use in the future will be released in this blog within the next few weeks.
