SQL Server Query Performance Improvement Tips

In this post I have compiled all the SQL Server Query performance improvement tips which I used all the time or the ones I found on the net. As every SQL  query performance improvement article says, there are no hard and fast rules as such. Still keep using some basic techniques would definitely help us to some extent.

1)      Dirty Reads - add WITH(NOLOCK) for reads

When select data from a database table we could read from the table without locking it(it does not need to wait for other processes to release the lock). (Use READ        UNCOMMITTED). But there is a risk of reading an uncommitted row that is subsequently rolled back.

SELECT Column1,Column2 FROM [Table1] WITH(NOLOCK) WHERE Column1='somevalue'

2)      Use intermediate result set

When we join multiple huge tables, we could take the biggest ones separately and filter out them appropriately first before use them in joins with other quite big tables.
This option might not be apt for all the cases. But we could use it wherever possible.

3)      Use char,varchar instead of nchar,nvarchar

Nchar and Nvarchar both will take double size memory compared to char and varchar.  We don’t need to use Nchar and NVarchar unless we need to store Unicode characters.

4)      Avoid cursors 

We need to avoid cursors as they will cause huge performance degradation. There are alternatives like while loop we can use instead of.

5)      Use inline expression instead of user defined function

Normally inline expressions are much faster than User defined functions. This might reduce the readability of the Query. But performance wise inline expressions are way better than UDFs.

6)      Parameter sniffing 

When a select query is executed with some input parameters, the SQL query optimizer will create an optimized execution plan according to the given parameter values and this plan will be saved in the cache (let say this time the result set has only 2% of the total no of rows). When next time we execute the query with some other parameters which will give different result set let say 30% of the total no of rows, same execution plan won’t be an optimized one.

 To avoid this we need to use local parameters especially in complex search queries.

7)      Use db schema name before sp names 

Having said that use db schema name would give a performance improvement. But this is not tested.Ex : use SELECT Name FROM dbo.User instead of SELECT Name FROM User 

8)      Choosing between temporary table and table variable

          http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article

9)      Avoid select *

We all tend to use this as it makes our work easy. But this is a common SQL bad practice that we need to avoid. We should select only the columns which we really need.

10)   De-normalization if appropriate

In some complex queries, joining multiple huge tables would likely give a performance issue which is quite unavoidable.  But in any case, if we are to join to some big table from which we need only one or two columns and if avoiding the join would give any performance improvement, then we can redundantly save the particular column values in some other relevant table which we use in the join.

11)   Proper indexing

Indexing is a very important factor in SQL query performance.  This should be discussed as a separate topic I believe.

12)   Not use high length data type as  a primary key

Lengthy key would take up much space and it would likely reduce the performance drastically sometimes.

13)   Use IF EXISTS instead of SELECT COUNT(*) – this is not tested

14)   Use BETWEEN instead of IN –this is not tested

15)   Avoid functions in where clause

We should avoid the usage of functions in where clause as much as possible since it stops using index on that column if any.

16)   Use sort only when it's needed

As sort requires some additional processing we could avoid it unless we really need it.

17)   Give priority to frequently used values in IN clause –This is not tested

18)  Use full text search  to search char or varchar columns–not tested

19)   Use EXISTS instead of IN – Having said that “EXISTS” works faster than “IN” operator. But this is not tested.


Hope this helps.