January 22, 2019 at 8:12 am
Greetings! Wondering if anyone can shed some light on a compression mystery. Back in mid-December, I spent a couple of weekends applying compression to 3 databases. Tedious work but worth the savings in disk & the performance gains. Last week, when someone started complaining that the performance had gone to hell in a hand basket, I discovered that all 3 databases were no longer compressed & we have no idea how this happened.
One thought that I had was that our nightly index optimize job was blowing away the compression but I came across a blog post that tested index rebuilds on compressed tables/indexes that demonstrated that the indexes retain their compression settings when rebuilt. As part of reapplying compression to the 3 databases this past weekend, I tested & confirmed those results. I also came across a blog post demonstrating that if indexes are disabled for ETL loads, if compression is not specified on the rebuild, the compression would be lost. I have confirmed with the owner that they do not disable indexes during ETL loads so that possibility has been ruled out. As an aside, I sent an email to Ola Hallengren, since we use his maintenance scripts but have not heard back from him yet.
Any ideas? I am baffled at this point. Out of an overabundance of caution, I've disabled the index optimize job we run on the databases & am utilizing reorgs instead. I have no desire to spend more weekends on this.
Links:
Index rebuild test: https://dba.stackexchange.com/questions/226685/do-sql-server-compressed-indexes-remain-compressed-on-rebuild-without-specifying/226687#226687
Rebuild disabled indexes: https://www.bi4all.pt/en/news/en-blog/sql-server-data-compression-rebuilding-disabled-indexes/
January 22, 2019 at 12:34 pm
I would recommend NOT using REORG - it is not doing what you expect it is as well as taking up a lot of log space...not to mention it is going to take much longer. If your rebuild script includes the compression clause and that clause specifies NONE - then the rebuild will recreate the index without compression.
My guess is that either the scripts are not reading that value appropriately - and therefore including NONE instead of either PAGE or ROW...or someone manually rebuilt the indexes without the compression.
Make sure you have the latest version of Ola's code - it could easily be an issue where the code does not accommodate compression settings.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 22, 2019 at 1:18 pm
Jeffrey Williams 3188 - Tuesday, January 22, 2019 12:34 PMI would recommend NOT using REORG - it is not doing what you expect it is as well as taking up a lot of log space...not to mention it is going to take much longer. If your rebuild script includes the compression clause and that clause specifies NONE - then the rebuild will recreate the index without compression.My guess is that either the scripts are not reading that value appropriately - and therefore including NONE instead of either PAGE or ROW...or someone manually rebuilt the indexes without the compression.
Make sure you have the latest version of Ola's code - it could easily be an issue where the code does not accommodate compression settings.
Good points & thanks for your response, Jeffrey.
The compression value is not defined in the rebuild command so I would hazard a guess that the default values are being taken from sys.partitions. Being fairly new to my company, I do need to check which version of Ola's script we are running but the parameters we use are pretty basic & I doubt that they have changed in later versions:
EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @FragmentationMedium = 'INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE', @SortInTempdb= 'Y', @LogToTable = 'Y';
January 22, 2019 at 1:50 pm
marvelousmayfield - Tuesday, January 22, 2019 1:18 PMJeffrey Williams 3188 - Tuesday, January 22, 2019 12:34 PMI would recommend NOT using REORG - it is not doing what you expect it is as well as taking up a lot of log space...not to mention it is going to take much longer. If your rebuild script includes the compression clause and that clause specifies NONE - then the rebuild will recreate the index without compression.My guess is that either the scripts are not reading that value appropriately - and therefore including NONE instead of either PAGE or ROW...or someone manually rebuilt the indexes without the compression.
Make sure you have the latest version of Ola's code - it could easily be an issue where the code does not accommodate compression settings.
Good points & thanks for your response, Jeffrey.
The compression value is not defined in the rebuild command so I would hazard a guess that the default values are being taken from sys.partitions. Being fairly new to my company, I do need to check which version of Ola's script we are running but the parameters we use are pretty basic & I doubt that they have changed in later versions:
EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @FragmentationMedium = 'INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE', @SortInTempdb= 'Y', @LogToTable = 'Y';
I don't use Ola's code - I have my own and found out quite by accident that my code was not correctly pulling the compression value when building the command. This caused the compression to be lost on those tables. For me - this was in a test environment where I was testing whether or not compression helped so it did not cause any issues, but I was able to insure my utility was correctly capturing the value and setting it appropriately.
There are a lot of potential issues with REORG though - and you really should not use it unless you have no other options.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply