Hello Friends, Here I am discussing few things about how to improve SQL Performance.

After completing this tutorial you will be able to understand:

  • How to Improve SQL Performance.

Introduction:

While working with SQL Server the major thing that comes in front of us is to manage SQL Performance. This challenge becomes more complex when our database starts growing day by day. Here I am discussing few things using which you can improve your SQL Performance.

SQL Performance

Tips to Improve SQL Performance:

  1. Always try to use COUNT(ColumnName) in place of COUNT(*).
  2. Do not prefix your stored procedure with _sp because SQL Server finds the stored procedure with prefix _sp in master database first.
  3. It is good practice to use SET NOCOUNT ON and TRY-CATCH to avoid Deadlock condition.
  4. When performing search operation your where clause should have a column that has an Index.
  5. Use Like in place of SUBSTRING for better performance. For Example:
                           WHERE SUBSTRING(fieldname,1,1) = 'a'

                      Try using this Instead:

                           WHERE fieldname LIKE 'a%'
  1. Use Union All in place of Union.
  2. Drop unused Indexes.
  3. It is good to create Indexes on the column with Integer Data Type.
  4. Keep the size of clustered index small because this field may use in both clustered and non-clustered index.
  5. Try to avoid calculation in a join query.
  6. Create Clustered and non-Clustered index on the column.
  7. Avoid using Cursor for data iteration because cursor uses more server resources.
  8. Try to use the table variable in place of the temp table. Because temp table required connection with temp database.
  9. Try to call any object of SQL Server using the fully qualified name.Such as:
                           dbo.objectname

                       instead of:

                           objectname
  1. Use Exists instead of IN to check the existence of data.
  2. Try to use Column name in place of “*” in the select statement. For Example:
                          Select columnname,columnname2,….. from tablename

                     Instead of

                          Select * from tablename.
  1. Make sure you have chosen appropriate data type while designing your database. For example, use varchar in place of text data type. Use text data type only if you need to store large text.
  2. Avoid using nchar and nvarchar in place of char and varchar because both uses double memory as compare to other.
  3. Try to avoid using Bigint in place of Int because Bigint uses 8 bytes of memory versus 4 bytes of int data type.
  4. It is good practice to use stored procedure for better performance when you have complex queries and frequently used data.
  5. Use Joins in place of SubQuery.
  6. Avoid using long running transaction because it slows down performance.

Hope this is a useful post for you for understanding SQL Performance.

Also, you must follow the related links:

System Databases in SQL Server.

Constraints in SQL Server.

 

Thank You.

Leave a Comment