Updated version of sp_use_db_compression – Version 2.42
In regards to SQL Server 2008 R2, I worked on some more options of the procedure to compress tables. The last version I released can be found here: https://blogs.msdn.com/saponsqlserver/archive/2009/10/11/version-2-3-of-sap-use-db-compression-released.aspx
One case I didn’t consider in that last version was the fact that there is a mix of tables being ROW compressed and Page Dictionary compressed. This was the situation on Microsoft’s ERP system. All our SAP Basis team wanted to do is to rebuild the clustered indexes with the same compression type as the index already had. As mentioned in earlier posts this would apply UCS2 compression to already existing data when running on SQL Server 2008 R2. Version 2.42 of the procedure as published in this article does contain such an option.
The new option is named @force_rebuild_same. The call of the procedure to execute a rebuild of the clustered indexes with their current compression type would look like:
sp_use_db_compression @maxdop=1, @online= 'ON', @force_rebuild_same=1
In this case one CPU would be used to rebuild he indexes in an online manner. All the clustered indexes which are Row or Page Dictionary compressed will be rebuilt in their actual compression method. As such the procedure was used last weekend in Microsoft’s SAP ERP system. We by far didn’t do all tables, but cut another 400GB out of the database, which came down from 5.3 to 4.9TB in data volume. However the largest tables still need to be done and we expect another 600 GB of space savings. Happy space saving J
Another SAP customer of ours out of the Defense industry space used the @write_verbose_table option quite intensively. They simply used this procedure to get the alter table/index commands in the correct order (sorted from the smallest table to the largest table) and then copied the commands and formed batches of the commands which they executed in periods of low activities. Also a good way to use the procedure. Find the new version at the end of the blog article. I also got positive feedback on the usage of the procedure out of the non-SAP space. Thanks for using it and thanks for the feedback!
Hope this new procedure helps! Juergen