How can you optimize the performance of a DBMS after an upgrade?

In Oracle database there are several ways to improve our database performance, especially after the upgrade, Database Statistics is crucial for our database, so After an upgrade, it's essential to gather and update statistics for all database objects. use some tools that help us to Identify and tune poorly performing SQL statements like Oracle SQL Tuning Advisor and SQL Access Advisor. change some parameters that enhance our performance.

If your dbms comes with an upgrade pre-check utility, like mysqlcheck, use it to get some good insight on what issues you may be some potential upgrade issues. For some upgrades, there may be new reserved words, new configuration parameters to consider, or existing configuration parameters that may need to be tweaked. An upgrade pre-check utility will help highlight those and more, like index length.

For SQL Server instances, I suggest these for planning: 1. Run the data migration assistant. This will provide a report on any breaking changes between the source and target, as well as provide any concerns found between the source database compatibility levels and the various compatibility levels available on the target. 2. Consider how to move any source server level objects like logins, credentials and sql agent jobs. These can be scripted using T-SQL, moved by powershell, or probably ssis packages as well. Timing of when to move these should also be planned. Sql agent jobs can have a database context in which they run so the database has to be on the target first. 3. Plan how to move the databases. Log shipping or some other method?

Planning is one of the most important step in upgrade process. Less risky way to upgrade is to use Datapump for export/import appropriate schemas. But that is if you are lucky to have someone who knows what schemas needed. Unfortunately it is not always the case. Sometimes you have to do upgrade on the same host (different ORACLE_HOME) and this way required much more careful planning and testing.

Planing is very important before an upgrade. At this stage, you get to choose the type of upgrade that is suitable for the business needs, putting into consideration the Recovery Point Objectives/ Recovery Time Objective of the client’s SLA. You plan for the disaster recovery strategy should there be any failure. You should also take comprehensive backup of your environment before starting your upgrade.

Test in a non-production environment. For SQL Server, use Query Store to capture performance data from prod before restoring the database to non-prod. Then test the new setup in non-prod. Query Store will capture the performance of the new system as well, allowing for a before/after comparison.

Test, test, test. As a DBA you may consider yourself lucky if company has a good, experienced QA team. Ideally QA team has to have ability of some automation testing. In this case if you upgrade DB on lower environment, and got a "green" light from QA team, you may go ahead with Prod environment. But life is not always ideal. If no QA team in the company the only way to test is to involve Dev team. Also sometimes it is hard to get approval for downtime from management team even to patch database. In this case such argument as a "security" reason may help. Because (A) it is true, we have to patch DB to avoid any security issues and (B) this reason usually works with upper management as well as with clients.

After the upgrade, update statistics on all tables to provide the query optimizer with accurate information for making efficient execution plans. It is important to examine execution plans for critical queries and ensure they are optimized. Consider using new features introduced in the upgraded SQL Server for improved performance.

Monitoring is extremely important part usually underestimated. OEM is giving us a good opportunity to monitor database. Just not enough. It is slow and not faxable enough. Latest version of OEM integrated with Grafana as a presentation layer, which is very good. But there is a different way to populate data for Grafana to make it much more faxable and valuable to monitor any aspect of Oracle database. I used bunch of my Python scripts to pull data from the dictionary and send that data to time series database, so Grafana can pick it up and draw very nice charts. It helped a few companies I worked for to find out some issues which were invisible before we implemented this monitoring solution.

Monitoring performance after an upgrade is important. This can be done using the monitoring tools available or third party tools. You analyze current performance metrics and compare with the performance metrics before the upgrade to ascertain if there is performance improvement. Performance metrics to monitor in DBMS such as SQL Server are: CPU, Wait stats, Blocking session, memory, deadlock scenarios etc.

Pre and post checks of parameters during upgrade is one of the essential steps to prevent any performance issue after database upgrade.

however, I suppose, if we have a great performance in the previous version, it's the best idea to capture Baselines in the previous version. and after the upgrade, we can migrate SQL Plan Baselines from the old version to the new version, bear in mind that before doing any action in the production environment you must test them in a test environment and check all reports related to performance like AWR or use Real Application Test tool which is the best tool that informs you about your change

Assuming you have done the testing of the queries regressing after the upgrade in the non-production environment, there would still be a lot of tuning required to be done on production since the data volume is not the same in most of the environment. Take a baseline of the queries and procedures before the upgrade using built in tools like query store in SQL server and compare the regression if there is any after the upgrade. Pick them and look if there is any change in the execution plan. Rebuild the indexes or just update the stats and see if it works. If not, you need to tune your queries since the behaviour changes with a new cardinality estimator which is the heart of querying the database.