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!

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.

3. SET NOCOUNT ON

   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
   AS
   BEGIN
   SET NOCOUNT ON;
       declare @currentStudentId bigint
       select @currentStudentId=StudentId from tbStudent
       where StudentId=@studentId
       update tbAdmission
       set AdmissionDate=getdate()
       where StudentId=@currentStudentId               
   END
   GO

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

• SQL server error handling best practices