Version 2.3 of sap_use_db_compression released
Given that our own SAP Basis Team is pretty aggressively working with SQL Server 2008 R2 and plans to go productive on CTP2 of SQL Server 2008 R2 in 5 weeks from now, I needed to rework the stored procedure sp_use_db_compression a bit. As I described in the former blog article, the way to get ALL data of a table into UCS2 compression which has been compressed under SQL Server 2008, is to rebuild the clustered index. So far the logic of sp_use_db_compression didn’t allow rebuilding an index with the same type of compression. This was more or less a step of optimization to safe resources and time. Hence for the specific case of rebuilding indexes in the same type of compression I introduced the option ‘force_rebuild’. This option is required in order to rebuild indexes with the type of compression the indexes already are compressed in. So the typical case for SQL Server 2008 R2 where one wants to rebuild many indexes and tables with the same compression type to get ALL data into UCS2 compressed format, this option would be required. A typical call of the procedure could look like:
sp_use_db_compression ROW, @maxdop=1, @online= 'ON', @verbose=1, @force_rebuild=1
Default for the new option is 0 and hence not to rebuild indexes which already are compressed in the requested type. I also did a bit code clean-up and fixed some smaller bugs. Microsoft’s own SAP Basis team will use this procedure pretty soon since we want to get the database smaller again. After archiving it currently is around 5.1TB completely ROW level compressed data plus three tables Page compressed. Once on SQL Server 2008 R2, we will go through all tables again in order to get the benefit of UCS2 compression. We think that we’ll get the data volume down to less than 4TB again.
Have fun Juergen