SQL Server Performance Tuning Tips

Introduction

In this article, we will learn about SQL Server performance tuning tips with examples.

Database

The Database is the most important and powerful part of any application. If your database is not working properly and taking a long time to compute the result, this means something is going wrong in the database. Here, database tune-up is required. Otherwise, the application's performance will degrade.

Database tuning is a very critical and fussy process. I know a lot of articles have already been published on this topic. But in this article, I tried to provide a list of database tune-up tips that will cover all the aspects of the database. Database tuning is indeed a database admin task, but we should have the basic knowledge for doing this. Because if we are working on a project with no admin role, then it is our responsibility to maintain the performance of the database. If the database's performance is degraded, it will have the worst effect on the whole system.

In this article, I will explain some basic database tuning tips that I learned from my experience and friends working as database administrators. Using these tips, you can maintain or upgrade the performance of your database system. These tips are written for SQL Server, but we can implement these into other databases, too, like Oracle and MySQL. Please read these tips carefully and at the end of the article, let me know if you find something wrong or incorrect.

Avoid Null value in the fixed-length field

We should avoid the Null value in fixed-length fields because if we insert the NULL value in a fixed-length lot, it will take the same amount of space as the desired input value for that field. So, if we require a null value in a field, then we should use a variable-length field that takes lesser space for NULL. Using NULLs in a database can reduce performance, especially in WHERE clauses. For example, try to use varchar instead of char and nvarchar.

Never use Select * Statement:  

We usually use a "Select *" statement when we require all the table columns. This is not a good approach because when we use the "select *" statement, the SQL Server converts * into all column names before executing the query, which takes extra time and effort. So, always provide all the column names in the query instead of "select *."

Normalize tables in a database

Normalized and managed tables increase the performance of a database. Not all tables require a 3NF normalization form, but if any table contains 3NF form normalization, it can be called a well-structured table. So, always try to perform at least the 3rd normal form.

Keep Clustered Index Small

Clustered index stores data physically in memory. If the size of a clustered index is huge, it can reduce the performance. Hence, an extensive clustered index on a table with many rows increases the size significantly. Never use an index for frequently changed data because when any change in the table occurs, the Index is also modified, which can degrade performance.

Use Appropriate Datatype

SQL contains many data types that can store the same type of data. Still, you should select an appropriate data type because each has some limitations and advantages over another. If we select an inappropriate data type, it will reduce the space and enhance the performance; otherwise, it generates the worst effect. So, choose an appropriate data type according to the requirement.

Store the image path instead of the image itself

Many developers try to store the image in the database instead of the image path. It may be possible that the application requires storing images in a database. But generally, we should use an image path, because storing image in a database increases the database size and reduces performance.

USE Common Table Expressions (CTEs) instead of Temp table

We should prefer a CTE over the temp table because temp tables are stored physically in a TempDB, which is deleted after the session ends. While CTEs are created within memory. Execution of a CTE is swift as compared to the temp tables and very lightweight too.

Use Appropriate Naming Convention

The main goal of adopting a naming convention for database objects is to make them easily identifiable by the users, their type, and the purpose of all things in the database. A good name indicates the action name of any object that it will perform. A good naming convention decreases the time required to search for an object.

* tblEmployees // Name of table  
* vw_ProductDetails // Name of View  
* PK_Employees // Name of Primary Key  

Use UNION ALL instead of UNION

We should prefer UNION ALL instead of UNION because UNION always performs sorting that increases the time. Also, UNION can't work with text datatype because text datatype doesn't support sorting. So, in that case, UNION can't be used. Thus, I always prefer UNION All.

Use Small data type for Index

It is essential to use a Small data type for the Index. Because the bigger data type size reduces the Index's performance. For example, nvarhcar(10) uses 20 bytes of data, and varchar(10) uses 10 bytes of data. So, the Index for the varchar data type works better. We can also take another example of DateTime and int. Datetime data type takes 8 Bytes, and int takes 4 bytes. A small datatype means less I/O overhead that increases the performance of the Index.

Use Count(1) instead of Count(*) and Count(Column_Name):  

There is no difference in the performance of these three expressions, but the last two expressions are not well considered a good practice. So, always use count(10) to get the numbers of records from a table.

Use Stored Procedure

Instead of using the row query, we should use the stored procedure because stored procedures are fast and easy to maintain for security and large queries.

Use Between instead of In

If Between can be used instead of IN, then always prefer Between. You can also use Between operator for the same query. For example, you are searching for an employee whose id is either 101, 102, 103, or 104. Then, you can write the query using the In operator like this:

Select * From Employee Where EmpId In (101,102,103,104)  

Select * from Employee Where EmpId Between 101 And 104  

Use If Exists to determine the record

It has been seen many times that developers use "Select Count(*)" to get the existence of records. For example

Declare @Count int;  
Set @Count=(Select * From Employee Where EmpName Like '%Pan%')  
If @Count>0  
Begin  
//Statement  
End 

Because the above query performs the complete table scan, you can use If Exists for the same query. That will increase the performance of your query, as below. But, this is not a proper way for such types of queries.

IF Exists(Select Emp_Name From Employee Where EmpName Like '%Pan%')  
Begin  
//Statements  
End  

Never Use" Sp_" for User Define Stored Procedure

Most programmers use "sp_" for user-defined Stored Procedures. I suggest never using "sp_" for user-defined Stored Procedures because, in SQL Server, the master database has a Stored Procedure with the "sp_" prefix. So, when we create a Stored Procedure with the "sp_" prefix, the SQL Server always looks first at the Master database, then at the user-defined database, which takes some extra time.

Practice using Schema Name

A schema is an organization or structure for a database. We can define a schema as a collection of database objects owned by a single principle and form a single namespace. Schema name helps the SQL Server find that object in a specific schema. It increases the speed of the query execution. For example, try to use [dbo] before the table name.

Avoid Cursors

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor is a set of rows together with a pointer that identifies the current row. It is a database object to retrieve the data from a result set one row at a time. But, using a cursor is not good because it takes a long time and fetches data row by row. So, we can use a replacement for cursors—a temporary table for or While loop may replace a cursor in some cases.

SET NOCOUNT ON

When an INSERT, UPDATE, DELETE, or SELECT command is executed, the SQL Server returns the number affected by the query. It is not good to return the number of rows affected by the query. We can stop this by using NOCOUNT ON.

Use Try–Catch

In T-SQL, a Try-Catch block is essential for exception handling. We can put all T-SQL statements in a TRY BLOCK, and the code for exception handling can be put into a CATCH block. A best practice and use of a Try-Catch block in SQL can save our data from undesired changes.

Remove Unused Index

Remove all unused indexes because indexes are constantly updated when the table is updated, so the Index must be maintained even if not used.