Tips To Improve SQL Server Query Performance

Here are some tips to improve your SQL query performance.

1. Choose Correct Data types

2. Always avoid nchar & nvarchar if you are not going to store Unicode data

3. Always Avoid Select * statement, it’s better to mention the column name in select statement.

4. Use Exists instead of IN, NOT Exists instead of <> and Not Like keyword

5. Create Clustered and Non-Clustered Index

6. Try to keep Clustered Index Small

7. Avoid using Cursors, try to implement using while loop

8. Use Table Variable and Temporary table cleverly

9. Use Union All in place of Union

10. Define All Primary keys and Foreign Key Relationships

11. Define All Unique and Check Constraints

12. Partition Tables Vertically and Horizontally

13. Avoid Expensive Operators such as Having, Not Like, Distinct, Order By

14. Avoid Explicit or Implicit functions In Where Clause

15. Full Qualify database Objects using Database.Schema.Objects

16. Use Locking and Isolation Level Hints to minimize locking

17. Use Stored Procedure or Parameterized Queries

18. Avoid Long Actions in Triggers

19. Avoid Long Running Transactions, Transactions that require user input to commit

20. Use Set NOCOUNT ON in Stored Procedure

21. Don’t use SP_(Your SP Name) for user defined stored Procedure name

22. Try to convert Sub Query to Join

23. Use TRY-Catch blocks to handle errors

24. Always use column list in your insert statements

25. Don’t use column numbers in ORDER BY clause

26. Try to avoid trigger on transaction table which gets many Insert, update, Delete operations.


Performance Improvement

T-SQL SIDE

1. Include SET NOCOUNT ON inside SP 
2. If table’s rowcount is required, then use sysindexes query instead of COUNT(*) 
3. Try to avoid CURSOR wherever required 
4. Try to use UNION ALL instead of UNION wherever required 
5. Try to avoid DISTINCT wherever required 
6. Try to avoid unnecessary complicated joins 
7. Try to return only required columns instead of using SELECT * 
8. Try to avoid IN, instead EXISTS query can be used 
9. Avoid triggers 
10. Try to avoid function in WHERE query or SELECT query 
11. Try to avoid CONVERT or CAST function 
12. If less number of records are involved, then TEMP variable can be used instead of TEMP table, otherwise go for TEMP table 
13. Avoid large number of DML operations in single transaction 
14. Avoid update on a table which is affecting lot of records. Divide this query in chunks and update the records in chunks which will be faster 
15. Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often 
16. Try to avoid ISNULL wherever required 
17. Avoid sorting if not required. Can it be achieved using Index? 
18. Try to use in-built function instead of using system tables. 

e.g. if exists (select * from sysobjects where name = 'tblTest1908')

drop table tblTest1908

if object_id('tblTest1908') is not null
drop table tblTest1908



DESIGN & MAINTENANCE SIDE

1. Have a database design as per normalization rule 
2. Create Clustered Index and non-clustered based on the usage 
3. Try to rebuild the indexes monthly/weekly based on the data insertion/updation 
4. In worst case, denormalization 
5. If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK. 
6. If you experience a large number page splits, then increase the Fillfactor which will reduce page splits as it will have more room to accommodate new data. 

EXECUTION PLAN

Execution Plan will give you an idea which execution plan SQL Server internally uses.


Table Scan
Ø This means, no clustered index is being used to retrieve the data. It is using a heap structure and does a full table scan.

Ø If a table is having less number of records, then it is fine otherwise index needs to be created.

Ø Sometimes, though a non-clustered index is available, SQL server uses table scan. This will happen in following 2 scenarios.

§ If total number of retrieved rows are higher relative to total number of records available in a table

§ If total number of rows having almost a same value are coming from a table

It will be efficient when output number of rows are greater than 50 % of total number of rows



Index Seek
Ø This means, non-clustered index is being used to retrieve the data. It happens generally when fewer rows are being returned. It focuses on qualifying rows and qualifying pages which contain these qualifying rows. It will be efficient when output number of rows are less than 50 % of total number of rows
Clustered Index Seek

Ø This means, clustered index is being used to retrieve the data. This is the fastest way to get the data
Clustered Index Scan

Ø This is just like a table scan, only difference is – it is happening on clustered index table instead of heap structure. It happens due to same reason mentioned in table scan
Bookmark Lookup

Ø It is a mechanism to navigate from non clustered index row to actual data row in base table (clustered index)


Hash Join/Nested Loop/Merge Join

Hash

Nested Loop

Merge


When being used?


No adequate index on join columns

When only one table has index and other does not have

When both the tables have clustered index (sorted rows)


Phase
Build Phase
Probe Phase



Further Info

Smaller table will be build input and larger will be probe input.

Each row of smaller table will be linked with larger one and being stored in Hash Table

For each row of outer table, all the rows of inner table will be executed

Very effective for larger tables. Scans n1 + n2 rows unlike n1 X n2 as in Nested loop because the records are physically sorted. So rowID = 6 will scan only till RowID = 6, the moment it gets 7, it skips.

Types 

In Memory


Stores in Memory
Grace


When there is no sufficient memory then it will be stored in partitions.

USING DTA (DATABASE TUNING ADVISOR)


Create one *.sql file with various DML operations or SPs on which you want to perform tuning

Open DTA from "Tools" Menu

Connect to a server

Select Database & chose tables which are being used in DML Operations or SPs you have specified in saved file

Select a file which you have saved in Step # 1 

In "Tuning option", select different options and "Start Analysis"




It will give result like this.