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.

Auto ID jumping issue with SQL 2012

 

I Once came across an issue with the SQL table records insert where the Auto Increment ID jumped by   around 1000 when it is supposed to increase by 1.

For example, let say we have a table for User detail with a primary key column “UserID”. Identity insert is enabled on this column. It was increasing by 1 for some time and suddenly it jumped from 9001 to 10,001 something like that.

I had no clue at all. Search on the net directed me to the following Microsoft thread. After reading it, I realized that every time the SQL server restarted auto ID will jump. As per Microsoft it is a feature in the latest version of SQL server. (SQL 2012).

They have given a work around to get rid of this feature/issue.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d256650-0e94-4d0f-8b52-0ba6e1903215/primary-key-auto-incrementing-by-1000-instead-of-1?forum=transactsql

http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity

What we need to do is to change the settings as given below

1. Open "SQL Server Configuration Manager"
2. Click "SQL Server Services" on the left pane
3. Right-click on your SQL Server instance name on the right pane
4. Click "Properties"
5. Click "Startup Parameters"
6. On the "specify a startup parameter" textbox type "-T272"
7. Click "Add"
8. Confirm the changes

The given work around worked like a charm.

Hope this helps.

Import data from Excel to SQL Server

Import data from excel is a very basic feature available in SQL server. But I tend to think that not every one is aware of this function.Let me share the work flow and some of my thoughts on it.

1)      Right  click on the database to which we want to import and click on the “Import Data” option there 

2)      Click on next and get to the following  screen

Here first we need to select “Excel” from the data source value list. Then we can specify the path of the excel data file.

If we can notice at the bottom, there is an option called “First row has Column names” which used to specify whether the first row in the excel file is the header row (which has SQL column names). If we unselect this then SQL will name the columns of the imported table on its own.

On the other hand if it is ticked, those headings will be taken as column names by SQL.

Let say our excel file contains some data like this

Here the ID, First Name and Last Name will be taken as the field names by SQL.

3)      Click on next and we will get to the following screen.

Here we can specify the database to which we are going to import the data.

4)      Click next twice and now we are here.

SQL will name the destination table something like “[dbo].[Sheet 1 $]” as shown in the screen shot. Actually the table name value “[Sheet 1 $]” taken from excel sheet’s name. We can rename excel sheet name to “Employee” to keep the table name meaningful.

Also we can rename it here as well.

 
There is a button called "Edit Mappings". Let's  click on it and go to the column mappings screen.

Here we can change the data types as well as the size of the fields. As you see we also have "Drop and re-create destination table " and "Enable identity insert" options there. For the ID field, SQL put "float" type by default. I have changed it to integer. Same goes for the size of the name fields too.


Now let it have the default values for the rest of the screens and complete the execution.

The last screen will look like this

Now if we go to the DB and check the “Employee” (the table name which given in the previous step) table, imported data will be there.

Few additional notes

• Let say we did not select the option “First row has Column names” mentioned in step 2, then our result set will look like this

 

  

     Here the header row also has been taken as a data row. And SQL itself will name the columns as F1, F2 and F3.

• We should not leave any space in between rows since that will result in records with null values.                          

           Let’s have the excel file like this

 

      If we import this to the DB then the result set will be

•   And it is very important that we should start from the first row itself in the excel file as the first row will be taken as the header row. 

      Hope this helps. Have a nice day.