Database Development: Microsoft SQL Server

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.


Tip #2 Use Parameterized Queries

Aside from the security benefits when using parameterized queries, there is a Performance benefit of using them versus passing literal values in the query.

Parameterized Queries sent from a .NET Application for instance take advantage of Cached Query Plans.

Queries using literal values on the other hand does not use Cached Query Plans, it always creates a new plan when using Simple Caching.


Of course, this does not apply when using Forced mode for Query Plans. However, Forced mode may use more resources than needed depending on type of the queries.