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
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
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
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
- Include SET NOCOUNT ON inside SP
- If table’s rowcount is required, then use sysindexes query instead of COUNT(*)
- Try to avoid CURSOR wherever required
- Try to use UNION ALL instead of UNION wherever required
- Try to avoid DISTINCT wherever required
- Try to avoid unnecessary complicated joins
- Try to return only required columns instead of using SELECT *
- Try to avoid IN, instead EXISTS query can be used
- Avoid triggers
- Try to avoid function in WHERE query or SELECT query
- Try to avoid CONVERT or CAST function
- If less number of records are involved, then TEMP variable can be used instead of TEMP table, otherwise go for TEMP table
- Avoid large number of DML operations in single transaction
- 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
- Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often
- Try to avoid ISNULL wherever required
- Avoid sorting if not required. Can it be achieved using Index?
- 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
- Have a database design as per normalization rule
- Create Clustered Index and non-clustered based on the usage
- Try to rebuild the indexes monthly/weekly based on the data insertion/updation
- In worst case, denormalization
- If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
- 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
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