Below is my opinion, I do not work for Jira and have no knowledge of their back-end magic.
I think one of the main reasons that you would want to do that overhead with Jira running on a SQL Server backend is that Jira is not the "administrator" of the database; your DBA is. It creates the objects it needs and runs with it. End users (actual end users PLUS Jira Administrators) can then create a mess in the system and cause a bunch of performance issues.
We have been running the product for a while now (since 2016) with minimal performance issues. The performance issues we did hit were network related OR due to 3rd party addons in Jira than anything else. Our Jira Admins have all had training done and keep up on best practices with Jira to ensure things flow smoothly. The same work is done by the administrator of ANY system. If you hired a SQL Server DBA, you would hope that they have some knowledge on how to administer all currently supported SQL Versions such as SQL Server 2019. But if that DBA has 20 years experience at XYZ Inc and they are running SQL Server 7.5 still, that new DBA to your team is going to offer a LOT of bad practices and likely cause performance issues on the system.
I find most 3rd party tools that support multiple database platforms end up having odd tweaks or changes to "default" setting, such as the isolation level; it isn't isolated to Jira.
Atlassian is also not retiring ALL on-premise versions of their tools - Data Center is still around and is likely not going away anytime soon.
My understanding is the main reason for retiring the "Server" model of Jira was that it was the only version that had a 1-time fee for the license. Your renewal costs were just so you could install the latest version. If you decided to stop paying, you could still use Jira with no limitations except for having no support and having no upgrades you could install.
To add to what Jeff said though, if the application administrator does a poor job of maintaining the tool, OR if the company who is using it is trying to use it for something it was never designed to do, it will have performance issues. Imagine throwing a C# developer who has never written a SQL query in their life and asking them to update data in the database using ONLY TSQL. There is a HIGH chance that they are going to develop a row-based solution with a loop of some flavor. Their query will work and may even run pretty quickly initially while the number of rows is small, but over time as row counts rise it will have performance issues.
A Jira administrator falls into a similar bucket as that. You throw a DBA in as a Jira admin and don't train them on best practices and how to keep peak performance, you are going to have performance issues.
Like any system, maintenance is required and you need a good, smart administrator on the system so they know what to look for and what to manage to help improve performance. Plus you need a good administrator who can know where to look for performance problems. If SQL Server starts performing a bit sluggish, a good DBA will know that it isn't ONLY on the SQL side that you need to investigate. It is a good starting point, but depending on the symptoms (single query slow vs all queries slow), it could be an issue at the OS level, or even a hardware issue. Same thing applies to Jira (and ANY vendor-supplied tool). I've seen our ERP tool get laggy and it was a database related issue. I've seen our service desk application get laggy and it was due to the hardware firewall. I've had poor performance on SSRS and it turned out to be a DNS issue.
Performance issues for a single company/user on a tool used by a LOT of companies usually means one of three things. Either you misconfigured it, you are using it wrong, or you are not doing proper maintenance on the tool.
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.