Join Class

SQL Server Performance Tuning Tips

In this tutorial you will learn how to improve sql database performance, learn how you should optimize sql query to get faster output!

improve sql performance

Here are top 10 ideas for SQL performance tuning, best way to optimize SQL Query for better performance.

Improve SQL Database Performance

  1. Setup right Data Type

    Setting up appropriate data type while creating table is very important, like for string data type use n [nvarchar] instead of Text or Varchar, Text field was designed for storing large data, nvarchar is better than Varchar, because Varchar unnecessary occupy specified length, when nvarchar occupy only used space.

  2. Don't use select * from in SELECT statement

    You should always specify the column names with comma separation instead of using “* from”, because “* from” statement unnecessary fetch all the columns in that table or view, add extra load to query, which can be easily avoided.


    Whenever you write any stored procedure, make sure you specify “SET NOCOUNT ON”, because by default SQL query always return the number of rows affected for any operation, by setting NOCOUNT ON, you can reduce some load and improve performance.

    CREATE PROCEDURE usp_UpdateDetails
    @studentId bigint
        declare @currentStudentId bigint
        select @currentStudentId=StudentId from tbStudent
        where StudentId=@studentId
        update tbAdmission
        set AdmissionDate=getdate()
        where StudentId=@currentStudentId               
  4. Specify Schema name before object name

    Specifying schema name before object name is always considered as best practice, when you specify the schema name before object, it clearly indicates where to search, thus help query getting the result faster.

    Think of situation like if there are same object name under two different schema.

    Select [nickName] from students.student
    Select [nickName] from dbo.student
  5. Use Table variable instead of Temp Table

    • Table variable (@table) is created in the memory. when a Temporary table (#temp) is created in the tempdb database.
    • Table variable can be passed as a parameter to functions or stored procedures, that’s not possible with temp variable.
    • Table variables can’t be involved in transactions or logging. @table variable faster then #temp

    Learn more about SQL Temp Table and Table variable

  6. Don’t name procedure with “sp_” prefix

    You should not name any user-defined stored procedure with “sp_” prefix, this will indicate SQL server to search the stored procedure in master database, because all system defined stored procedure are prefixed with “sp_” , as a result this may slow down the performance.

  7. Use Try-Catch block to know the error root cause

    You should always use Try-Catch block, especially when writing any user defined function, stored procedure, triggers etc or any long code block, try to keep them as short as possible, that also help maintaining the code and indentifying the error root cause with no time.

    This practice also can save you from query causing dead-lock situation.

  8. Use EXISTS instead of IN with Where Clause

    Often we write query to find multiple matching records with where clause. like following two queries, both will get you the same result.

     --Good practice
    SELECT ProductName,Price, Size FROM tblProduct
    where PId EXISTS (Select distinct PId from tblOrder where odate >= getdate())

    Above query will perform faster than query below

     --Bad practice
    SELECT ProductName,Price, Size FROM tblProduct
    where PId IN (Select distinct PId from tblOrder where odate >= getdate())

    Try to avoid all above subquery, rather use SQL Join, query will produce faster result.

  9. Avoid Storing Duplicate Data

    Duplicate data on multiple tables can cause many problems like add unnecessary overload, difficult to maintain, and considered as bad design, and obviously create impact on sql query performances.

    So always make sure no duplicate data is getting stored, create relationship and use multiple views to meet your business requirement.

  10. Use Transaction

    Use transaction wherever you want to update more than one table, so if anything goes wrong you can take action immediately rather than waiting to be notified by error handling mechanism.

    This will help unnecessary code not gets executed, and cause some other problem.

Now, there are different ways to optimize sql query, read here about Sql query optimization tips- Part2

You also may be interested in following tutorial

Hire SQL Developer
SQL Performance Improvement Tips
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
MS SQL Examples | Join MS SQL Course