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.

Always create an index on the table

An index is a data structure to retrieve fast data. Indexes are unique lookup tables that the database search engine can use to speed up data retrieval. Simply an index is a pointer to data in a table. Mainly an index increases the speed of data retrieval. So always try to keep a minimum of one Index on each table. It may be either clustered or non-clustered Index.

Use Foreign Key with the appropriate action

In addition to protecting the integrity of our data, FK constraints also help document the relationships between our tables within the database itself. Foreign keys define a relationship and enforce integrity between two tables. A foreign key is a column or combination of columns that is the same as the primary key but in a different table. Also, define an action rule for the delete and update command; you can select any action among the No Action, Set NULL, Cascade, and set default.

Use Alias Name

Aliasing renames a table or a column temporarily by giving another name. Using table aliases means renaming a table in a specific SQL statement. Using aliasing, we can provide a small name to a large name, saving us time.

Use Transaction Management

A transaction is a unit of work performed against the database. A transaction is a set of work (T-SQL statements) that execute together like a single unit in a specific logical order as a single unit. If all the statements are executed successfully, the transaction is complete, the transaction is committed, and the data will be permanently saved in the database. If any statement fails, the entire transaction will fail, and the complete transaction will be either canceled or rolled back.

Use Index Name in Query

Although in most cases, the query optimizer will pick the appropriate Index for a specific table based on statistics, sometimes it is better to specify the index name in your SELECT query.

Example

SELECT  
e.Emp_IId,  
e.First_Name,  
e.Last_Name  
FROM dbo.EMPLOYEE e  
WITH (INDEX (Clus_Index))  
WHERE e.Emp_IId > 5  
Select Limited Data  

We should retrieve only the required data and ignore the unimportant data. The less data retrieved, the faster the query will run. Rather than filtering on the client, push as much filtering as possible on the server end. This will result in fewer data being sent on the wire, and you will see results much faster.

Drop Index before Bulk Insertion of Data

We should drop the Index before inserting a large amount of data. This makes the insert statement run faster. Once the inserts are completed, you can recreate the Index again.

Use Unique Constraint and Check Constraint

The CHECK constraint ensures that all values in a column satisfy certain conditions. A Check constraint checks for a specific condition before inserting data into a table. If the data passes all the Check constraints, then the data will be inserted into the table. Otherwise, the data for insertion will be discarded.

A Unique Constraint ensures that each row for a column must have a unique value. It is like a Primary key, but it can accept only one null value. In a table, one or more columns can contain a Unique Constraint. So we should use a Check Constraint and Unique Constraint because it maintains the integrity of the database.

Importance of Column Order in Index

If we are creating a Non-Clustered index on more than one column, then we should consider the sequence of the columns. The order or position of a column in an index also plays a vital role in improving SQL query performance. An index can help improve the SQL query performance if the query's criteria match the columns that are left the most in the index key. So we should place the most selective column on left most side of a non-clustered index.

Recompiled Stored Procedure

We all know that Stored Procedures execute T-SQL statements in less time than a similar set of T-SQL statements executed individually. The reason is that the query execution plan for the Stored Procedures is already stored in the "sys. procedures" system-defined view. We all know that recompilation of a Stored Procedure reduces SQL performance. But in some cases, it requires recompilation of the Stored Procedure. We are dropping and altering a column, Index, and trigger of a table and updating the statistics used by the execution plan of the Stored Procedure. Altering the procedure will cause the SQL Server to create a new execution plan.

Use Sparse Column

Sparse columns provide better performance for NULL and Zero data. If you have any column containing large amounts of NULL and Zero, then prefer a Sparse Column instead of the default column of SQL Server. The sparse column takes lesser space than the regular column (without the SPARSE clause).

Example

Create Table Table_Name  
(  
Id int, //Default Column  
Group_Id int Sparse // Sparse Column  
)  

Avoid Loops In Coding

Suppose you want to insert ten records into the table. Then instead of using a loop to insert the data into the table, you can insert all data using a single insert query.

declare @int int;  
set @int=1;  
while @int<=10  
begin  
Insert Into Tab values(@int,'Value'+@int);  
set @int=@int+1;  
end  

The above method is not an excellent approach to inserting multiple records; instead, you can use another method like the one below.

Insert Into Tab values(1,'Value1'),(2,'Value2'),(3,'Value3'),(4,'Value4'),(5,'Value5'),(6,'Value6'),(7,'Value7'),(8,'Value8'),(9,'Value9'),(10,'Value10');  

Avoid Correlated Queries

In A Correlated query, the inner query takes input from the outer(parent) query; this query runs for each row, reducing the database's performance.

Select Name, City, (Select Company_Name  
from  
Company where companyId=cs.CustomerId) from Customer cs  

The best method is to prefer the join instead of the correlated query as below.

Select cs.Name, cs.City, co.Company_Name  
from Customer cs  
Join  
Company co  
on  
cs.CustomerId=co.CustomerId  

Avoid Index and join hints

Use join or index hint if you are confident there is no better execution plan. In some cases, Index and join hints may increase the performance of a database, but if you provide any join or index hint, then the server always tries to use the advice provided by you, although it has a better execution plan, so such type of approach may reduce the database performance. If you have any doubts, make the server free to choose an execution plan.

Avoid the Use of a Temp table

Avoid using a temp table as much as possible because a temp table is created into a temp database like any basic table structure. After completion of the task, we require to drop the temp table. That raises the load on the database. You can prefer the table variable instead of this.

Use Index for required columns

The Index should be created for all columns using the Where, Group By, Order By, Top, and Distinct commands.

Don't use Index

The use of an index indeed makes the fast retrieval of the result. But, it is not always true. In some cases, using an index doesn't affect the performance of the query. In such cases, we can avoid the use of the Index.

1. When the size of the table is tiny.
2. The Index is not used in the query optimizer.
3. DML(insert, Update, Delete) operations are frequently used.
4. The column contains TEXT, nText type of data.

Use View for complex queries

If you are using join on two or more tables and the result of queries is frequently used, then it will be better to make a View containing the result of the complex query. Now, you can use this View multiple times so that you don't have to execute the query multiple times to get the same result.

Make transaction short

It will be better to keep the transaction as short as we can. Because the enormous number of transactions makes the table locked and reduces the database concurrency, always try to make shorter transactions.

Use Full-text Index

A full-text query returns any document that contains at least one match (also known as a hit). A match occurs when a target document contains all the terms specified in the Full-text query and meets other search conditions, such as the distance between the matching terms. Full-text queries can include simple words and phrases or multiple forms of a word or phrase. If your query contains multiple wild card searches using LIKE(%%), then using a Full-text Index can increase the performance.

Thanks for reading the article. As I asked in the beginning, if you have any doubts or if I wrote something wrong, write back to me in the comments section.

Read More>>

• SQL Server Performance Tuning Tips
• Transact SQL Query Performance Tuning Tips
• Transact SQL Query Performance Tuning Tips
• Improve Store Procedure Performance In SQL Server/ Store Procedure Performance Tuning
• Tips to Increase SQL Server Query Performance: Part 1
• Tips to Increase SQL Server Query Performance: Part 2
• Tips to Improve SQL Database Performance
• Transact SQL Query Performance Tuning Tips
• Tips To Increase SQL Server Stored Procedure Performance
• SQL Server Performance Tuning: Data Compression
• How To Optimize SQL Queries
• Tips And Tricks To Improve WEB API Performance

Watch here a full video for more information about MS SQL New Functions, Syntaxes, Performance Tuning Tips & Tricks.

Summary

This article taught us about SQL Server Performance Tuning Tips with examples.