SQL Server Tips
Want to make your SQL Server run faster? Here are a few quick tips to speed up your SQL Server immediately!
Tip #1 Use The Latest SQL Server Compatibility Level
Open SSMS, Connect to your SQL Database Engine, take a look at the version number displayed. It says something like this: SERVER_NAME (SQL Server 15.0.2070.41) - DOMAIN\USERNAME)
Note that this version number means what SQL Server version is installed, however, it DOES NOT mean the databases are actually using that version!
Run the following T-SQL Query to find out what version of SQL Server is installed:
SELECT @@VERSION
Time to update SQL Server!
While still connected to your Database Engine with SSMS, go to the Object Explorer tab, Expand the Databases folder to find your database, right click on it and select Properties, on the left select the Options page, on the right side next to Compatibility level, click the dropdown and select the highest version available, and press OK to save changes!
You could also quickly update the Compatibility level of your databases using T-SQL.
Copy and paste the following T-SQL code to update your SQL Server:
Make sure to use the appropriate version number to your server (SQL 2008 = 100, SQL 2012 = 110, SQL 2014 = 120, SQL 2016 = 130, SQL 2017 = 140, SQL 2019 = 150 )
USE [master]
GO
ALTER DATABASE [master] SET COMPATIBILITY_LEVEL = 150
ALTER DATABASE [model] SET COMPATIBILITY_LEVEL = 150
ALTER DATABASE [msdb] SET COMPATIBILITY_LEVEL = 150
ALTER DATABASE [tempdb] SET COMPATIBILITY_LEVEL = 150
ALTER DATABASE [YOUR_DATABASE_NAME] SET COMPATIBILITY_LEVEL = 150
GO
-- If you have SQL Server Reporting Services (SSRS) installed, run the following also
USE [master]
ALTER DATABASE [ReportServer] SET COMPATIBILITY_LEVEL = 150
ALTER DATABASE [ReportServerTempDB] SET COMPATIBILITY_LEVEL = 150
GO
This is typical issue for in place upgrade installations. Let's say you're running SQL 2017 and you've upgraded to SQL 2019. Guess what? By default SQL Server will continue to operate at the older SQL 2017 version instead of using the latest improvements of SQL 2019.