SQL Server : Performance degraded after upgrading the compatibility level from 2008 to 2014/2017/2019 (i.e. 100 to 120 Compatibility version).

In this article, we will discuss about the performance impact after change in compatibility level of your databases.

Please allow me to shed some light about the concept of Compatibility Level, as you know prior to SQL Server 2014, the database compatibility level of your user databases was not typically an important property that you had to be concerned with, at least from a performance perspective. Unlike the database file level (which gets automatically upgraded when you restore or attach a down-level database to an instance running a newer version of SQL Server, and which can never go back to the lower level), the database compatibility level can be changed to any supported level with a simple ALTER DATABASE SET COMPATIBILITY LEVEL = xxx command.

With SQL Server 2012 and older, the database compatibility level was mainly used to control whether new features introduced with a particular version of SQL Server were enabled or not and whether non-supported old features were disabled or not. The database compatibility level was also used as a method to maintain better backwards application compatibility with old versions of SQL Server. If you didn’t have time to do full regression testing with the newest compatibility level, you could just use the previous compatibility level until you could test and modify your applications if needed.

Now when you create a new user database in SQL Server, the database compatibility level will be set to the default compatibility level for that version of SQL Server. So for example, a new user database that is created in SQL Server 2017 will have a database compatibility level of 140. The exception to this is if you have changed the compatibility level of the model system database to a different supported database compatibility level, then a new user database will inherit its database compatibility level from the model database.

But Database Compatibility Level 120, this was when the “new” cardinality estimator (CE) was introduced. In many cases, most of your queries ran faster when using the new cardinality estimator, but it was fairly common to run into some queries that had major performance regressions with the new cardinality estimator. Using database compatibility level 120 means that you will be using the “new” CE unless you use an instance-wide trace flag or a query-level query hint to override it.

You can refer the below article from MS on the Differences between lower compatibility levels and level 120; https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017#differences-between-lower-compatibility-levels-and-level-120
Also there classic whitepaper wrote by Joe Sack on “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” that explains the background and behavior of this change back in April of 2014. ; https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

Microsoft’s recommended upgrade process is :
[+] Change the Database Compatibility Level and use the Query Store; https://docs.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-2017
- Upgrade to the latest SQL Server version and keep the source (legacy) database compatibility level.
- Enable Query Store, and let it collect a baseline of your workload.
- Change the database compatibility level to the native level for the new version of SQL Server.
- Use Query Store to fix performance regressions by forcing the last known good plan.

Please refer the below articles on the same :
[+] https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

Microsoft also introduce the new tool Query Tuning Assistant; Starting with SQL Server Management Studio v18, the new Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer SQL Server versions, as documented in the section Keep performance stability during the upgrade to newer SQL Server of Query Store Usage Scenarios. However, QTA doesn't roll back to a previously known good plan as seen in the last step of the recommended workflow. Instead, QTA will track any regressions found in the Query Store Regressed Queries view, and iterate through possible permutations of applicable optimizer model variations so that a new better plan can be produced.

Please have a look into the below article for more details on The Query Tuning Assistant workflow; https://docs.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-2017#the-query-tuning-assistant-workflow

RDS SQL Server : How to reduce the size on disc for RDS SQL Server database?

First of all I would like to explain you that SQL Server databases have two types of files; Data File and Log File.

1. Data files contain data and objects such as tables, indexes, stored procedures, and views.
Data files can be shrink, however it is not a good practice to shrink the data file unless necessary as it can result into blocking, dead locks and index fragmentation. The below documentation to provides more insights on why shrink data files should be avoided.

[+] https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/
[+] https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
2. Log files contain the information that is required to recover all transactions in the database.

Log Files can be shrink, based upon the log space utilization and on left a transaction open and running.
Now if you would like to know which file in the database is taking more space can use below command to identify file utilization:

USE [your_database_name]
GO

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
GO

Then accordingly perform the shrink, Here are the steps to perform the shrink for database files:

For Log files:
Firstly, I would humbly request you to check if you have an open or active transaction. The following queries may help:

1. DBCC SQLERF(LOGSPACE)
>> Provides the log space utilization
[+] How can I troubleshoot storage consumption in my Amazon RDS DB instance that is running SQL Server? -
https://aws.amazon.com/premiumsupport/knowledge-center/rds-sql-server-storage-optimization/
2. SELECT name, log_reuse_wait_desc FROM sys.databases
>> Provides why each database's log file is not clearing out
[+] sys.databases (Transact-SQL)
- https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15
>> Includes the possible reasons[+]:
a. Log backup needs to be run
b. Active backup running - because the full backup needs the transaction log to be able to restore to a specific point in time
c. Active transaction - someone typed BEGIN TRAN and locked their workstation for the night

[+] My Favorite System Column: LOG_REUSE_WAIT_DESC
- https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/
As already mentioned above, you can perform the shrink on the transaction log using the below command.

Step 1: Please run the below command to get the used space and free space of transaction log in the database [+]
DBCC SQLPERF(LOGSPACE); --> review transaction log file utilization per database level, if it is more than 80% free then only perform step2
[+] https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-2017
Step 2: Query to shrink transaction log file
USE [userdb-name]
GO
DBCC SHRINKFILE (logfilename, sizeinmb)

If this instance is vital to production, I would also like to recommend using CloudWatch Alarms [+] to monitor your 'FreeStorageSpace' to
prevent Storage Full issues. The alarm will monitor this metric and notify you when the defined threshold is breached, so you may take
immediate actions to increase storage.
[+] How can I create CloudWatch alarms to monitor the Amazon RDS free storage space and prevent storage full issues?
- https://aws.amazon.com/premiumsupport/knowledge-center/storage-full-rds-cloudwatch-alarm/
For Data Files:

You can use same 'DBCC SHRINKFILE' command for shrinking a data file to a specified target size.

The following example shrinks the size of a data file named 'DataFile1' in the user database to 1024 MB.

USE [userdb-name];
GO
DBCC SHRINKFILE (DataFile1, 1024);
GO

OR From GUI- right click on database>click task> shrink> files > select data file from drop down.
I would also like you to know that data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.
To know fragmentation information for the data and indexes of the specified database ‘DB_name’

  SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
	avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
	FROM sys.dm_db_index_physical_stats
	(DB_ID(DB_name'), NULL, NULL, NULL , 'SAMPLED')
	ORDER BY avg_fragmentation_in_percent DESC

To rebuild all indexes in a table, here is the sample query:
	ALTER INDEX ALL ON DatabaseName.SchemaName
	REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

[+] https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
These suggestions are based on best effort basis, to conclude I would highly recommend you to please test these thoroughly before implementing them on any production environment. This will help in proactive testing to avoid any unforeseen issues during actual implementation.