I have a quick question on Ola Hallengren Index Optimize Maintenance . Do we have data_compression = page ?
I have checked online and see @DataCompression = 'Page' --please add this line for IndexOptimize script .
Not sure if it is true or not so checking with the experts.
March 1, 2026 at 1:35 am
easy to check - why not try it out instead of asking someone to confirm what you found - or read the code itself.
March 1, 2026 at 2:37 am
The client has an offshore team and they will not help or let me see what they are trying to do. I in the ProdFix env i was able to get the results they wanted but when ready to implement in PROD , they raised an issue so nobody has a clue does it work or not work.
Compression is new to us at this client , they invited a SQL Expert and he suggested ..
March 1, 2026 at 8:37 am
You may modifiy it by yourself. BTW. why do you want to use compression?
March 1, 2026 at 1:18 pm
It is not DB compression and the SQL Server SME suggested we use compression = page during index rebuild option..
@DataCompression = 'Page'
March 2, 2026 at 8:24 pm
My opinion - if you don't have access to their systems, that means it is not your problem. You can't see how they have their script configured plus I doubt you can test it. Is it the base Ola script or did they customize it for their needs? If you can't say with 100% certainty and you have no access to verify, then it is not your problem. All you can do is suggest.
Also, I tend not to blindly trust SQL Server SME's at a company only because it is easy for them to be the SME when it was originally set up and not grow and learn - you get in a rut and things work so you keep going without knowing about new features. OR you get the opposite effect where a new feature comes in and they want everyone to start using it without understanding the impact. Compression does have impact (higher CPU utilization for example) so I'd be sure to test things before randomly turning features on or off, even if recommended by an SME.
Now that being said, I doubt that Ola's script has the built-in feature to turn on page compression across all indexes in a database as that is not often what you want to do. The decision for index compression should be set up at index creation time and changed only after careful testing. The script MAY support that, but I highly doubt that it would as MOST of the time, that's not what you want to do.
BUT the best place to determine if something has a feature is to check the docs - docs for Ola's index maintenance are here https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html - and I see no mention of "compression" in there, so it is likely they don't natively support it. That doesn't mean that your client didn't modify the script to support it though...
Just my 2 cents 🙂
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 3, 2026 at 12:38 am
Ola's index script DOES NOT allow for datacompression as a parameter.
while anyone can change it and allow for that to be supplied as a parameter, this is not desirable, nor needed in its majority of cases, as compression should be decided on a per index/table basis, not blindly applied the same to all of them.
note that the script does a REBUILD (DO NOT ALLOW THEM to use Reorg EXCEPT for columnstore indexes) - which means that whatever compression was defined on the index will be retained.
March 3, 2026 at 1:20 am
Thanks a lot for your suggestions ..Brian Gale & Frederico_Fonseca i would suggest them but not get too deeply involved .
@DataCompression = 'Page' --please add this line for IndexOptimize script . this was mentioned so really not sure if this exists or not so i will stay out and suggest them ..
March 3, 2026 at 6:14 am
Thanks a lot for your suggestions ..Brian Gale & Frederico_Fonseca i would suggest them but not get too deeply involved .
@DataCompression = 'Page' --please add this line for IndexOptimize script . this was mentioned so really not sure if this exists or not so i will stay out and suggest them ..
As mentioned before: It's up to you to add this parameter. The script is open source.
March 3, 2026 at 10:01 pm
Just to add to what deubel_m said, Ola's scripts are not managed, maintained, or supported by SQLServerCentral. SOME users of this forum use them and can offer advice about them, but those scripts are not associated with this site (as far as I am aware).
So if you want changes to them, the best bet is to put an issue in the github repo (I think it is a github repo, may be gitlab or some other git host) asking for that and someone MAY add it or give reason why they aren't adding it, or you could fork the repo and make the changes your self or even just download the scripts and modify them yourself before deployment.
Something to note though - if your client currently uses those scripts AND has modified them for their needs or is running an older version, you taking the scripts directly from the git repo, you may be missing changes that they require. Best bet is to request a copy of the scripts from the client to ensure you are changing the correct version, then make your changes and send it over to them.
Just my 2 cents though. If you are unable to change the scripts yourself, I'd send a copy of them over to your SQL Server SME and ask them to add it in rather than asking people on a forum (who are not paid for this) to do your work for you 🙂
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 4, 2026 at 2:08 am
Lol cannot ask the SME as he just gives us direction to do this do that .. but nothing concrete .. I have checked online and i got the answer @DataCompression = 'Page' add this and it does the trick BUT we never know so just wanted to make sure if anyone worked on page compression and had got desired results .
Thanks very much for your time folks .. appreciate your help .. will try to implement and see if this works .. will update here after compression.
March 4, 2026 at 2:07 pm
You asked if we got "desired results" - what results were you desiring? Are you looking for performance gains (or losses)? If so, turning compression on across the board will do that. You get performance gains because the data is compressed so fewer bytes to pull per request meaning faster data transfer (disk, memory, network) but it comes at the cost of higher CPU. So if you have too many transactions hitting, your CPU can't keep up, and you get performance loss. If the transaction count is low, then you should see performance gains. It also depends on where your bottleneck was - was it with disk, network, memory, CPU or did you have no bottleneck and things were working good?
Also, I HOPE you documented the previous state before making the change across the board. If the change causes issues, you have to be sure you can roll back if you break things. It may seem better on a test system with low transaction count, but promoting it to prod where transaction count is higher may result in performance tanking, so make sure you have a rollback plan before applying to prod.
I also find it a bit weird that the SME refuses to get their hands dirty. This sounds like he is a theoretical SME not a practical SME. What I mean is that he has read the books and forums and someone once told him that turning it on helped performance so he recommends turning it on across the board without knowing the impact. I used to be like that where I'd read "turn this feature on and watch things get better" and I'd do it and most of the time it was good, but sometimes it was good in the test systems but would tank production systems. I learned, researched, and improved through trial and error as well as reading. I'd be leery of anyone who claims to be an SME but refuses to touch the code as it sounds like they lack the real world experience.
EDIT - wanted to add that the @dataCompression = "Page" isn't a parameter of Ola's scripts. Here is the full script for index and statistics maintenance and you can clearly see that @dataCompression isn't a parameter of it - https://github.com/olahallengren/sql-server-maintenance-solution/blob/main/IndexOptimize.sql
So if you want that added, you will have to implement it yourself.
EDIT 2 - one more thing to add: if you customize Ola's scripts, it makes it very hard to get any community support. Your change sounds pretty small and easy to do, but if the script breaks due to it, the community support (this forum, the git repo, etc.) is going to tell you to undo your changes and grab the latest version from the git repo to deploy overtop of your customized one and see if the problem goes away. I've got nothing against anyone who uses Ola's scripts, but I prefer to keep my critical scripts (such as maintenance and backups) written in-house, well documented, fully tuned for my environments, and fully supported internally. My backup scripts work great for my specific environment, but they are not a "kitchen sink" solution like Ola's. They don't handle every possible backup solution out there (RedGate, Idera, etc.) or one-off backups (like a copy-only backup). They are built around what my system and team requires and nothing more or less. It does have some sanity checks built in around indexes (such as you can't reorg an index if page or row lock is disabled and we have some 3rd party tools that put that into the DB for some dumb reason). My scripts are tailored for my environment and won't work for everyone, but they work great for my environment and that's all that really matters - does the script work for your environment? Mine are also smaller, more manageable stored procedures. There is a "main" stored procedure that is used to call all of the others based on parameters, and then one for backup of a database, one for backup of all databases, one for indexes maintenance on a database, one for index maintenance on all databases and so on. Each stored procedure is short and has a specific purpose. The "all database" ones really just use a cursor and loop through each DB and call the "on a database" version of the stored procedure. Reason for this is that it is easy to test and verify that the stored procedure works. It also reduces code duplication so if a bug is found, I fix it in one place and it is fixed everywhere. But my scripts may work for my environment but are not likely to work for yours. they are not universal scripts like Ola's where it should work everywhere.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 15, 2026 at 4:07 am
Brian,
So we implemented and tested with the latest Ola scripts but @DataCompression = 'Page' does not exist so it failed , we went ahead with rebuilding each index with Page compression and got it complete.
So all from Google is not right unless you test and make sure it exists ,I was totally shocked with the @DataCompression = 'Page' (as it said it existed but never existed)
Long story short we implemented each compression and got it working as expected.
Thanks a lot for all of your suggestions !!
Just wanted to point out - that parameter not existing is what I (and others on here) have been saying the whole time, so it is no surprise that it didn't work. I provided a link to the official docs as well as the github repo for this section of the code and it said the same thing, so not sure where you got the idea it should work except from some random googling which gave you bad results.
On top of that, the advice of almost everyone here was to not blindly turn on page compression across all of your indexes. You should test it in a sandbox to measure the impact on a simulated load before making the change as it will increase CPU utilization which could result in an overall performance impact to your system...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply