T-SQL Query Performance Tunning Tricks

I am trying to add some T-SQL query performance tricks for SQL server developers. Some of are very obvious but still i have seen professional developers not uses these.

1. IN / BETWEEN

When you have choice of using IN / BETWEEN in TSQL, BETWEEN is good choice.

SELECT  eid,ename,salary FROM Employee WHERE eid IN (1,2,3,4,5,6,7,8,9,10)
SELECT eid,ename,salary FORM Employee WHERE eid BETWEEN 1 AND 10

BETWEEN is much more efficient than IN.

2. SUBSTRING/LIKE

Sometime it is possible to replace SUBSTRING character function with LIKE. SUBSTRING function forces the Table scan istead of allowing optimizer to use and Index.

SELECT eid,ename,salary FROM Employee WHERE SUBSTRING(ename,1,1) = ‘S’
SELECT eid,ename,salary FROM Employee WHERE ename LIKE ‘S%’

Both query returns the same result but LIKE clause gives the batter performance in this case.

3. STRING FUNCTION , if possible avoid it.

SELECT eid,ename FROM Employee WHERE UPPER(ename) = ‘XYZ’

We can rewrite the same query
SELECT eid,enam FROM Employee WHERE ename = ‘XYZ’ or ename = ‘xyz’

Second query run much more faster than the first query which has UPPER function.


4. Union / Union All 

If using the UNION statement, keep  in mind that, by default, UNION performs the  similar of a SELECT DISTINCT on the final output of the result query. In other words, UNION takes the results of two like  recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows.

This process occurs even if there are no duplicate records in the final recordset.
If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows. But if not, use UNION ALL, which is less resource intensive. 

How many number of processors SQL server has?

The simple way to count the number of processors SQL Server has

exec xp_msver 'processorcount', 'processortype'

Identifying the protocols being used by current connections

There are four different Net-Library options are available for SQL Server.

Shared Memory
TCP/IP
Named Pipes
VIA (Virtual Interface Adaptor)

Below script provides you details about existing connections when connection was establish and which protocol used by particular connection.

SELECT session_id,connection_id, connect_time, net_transport, net_packet_size, client_net_address
FROM sys.dm_exec_connections

To learn more about protocol please click here

MS SQL Server 2005 - Reporting Services Fix for slow loading on first time report load

Configuring the new instance of SSRS (SQL Server Reporting Services) , i have observe that when you first time click on the SSRS reporting site it takes longer time to load sometime forever. Once it is load one time than it would be good and no issue. If you close the session and reload everything first time it takes again longer time.
I have load balancing in Reporting server so i thought it was taking longer time but after reading online material and applying following change on IIS - App Pool setting , it resolved the issue.

Select the second tab - Performance and there is setting of the App Pool, Shutdown worker processes after being idle for (time in minutes) : 20 mins. This is the default setting.Unchecked that, and you should see the performance improvement. On a site you which runs slowly you can see the direct improvement right away. It may require to restart IIS service.

How many number of TempDB data files?

TempDB system database plays as important role on SQL Server performance.

When you install SQL Server by default SQL Server will create one small TempDB data file and one log file in the default location for TempDB on your SQL Server instance. You should change the default file locations of TempDB, otherwise default file locations, the TempDB files will be in a sub-directory on the same drive where your SQL Server binary files are located. This is most likely your C: drive on your database server, which is not a good place. If this the case and your TempDB is in C: drive, good practice is to move it to fast, dedicated logical drive.

You also need to create some additional TempDB data files, which should all be the same size. If all of the TempDB data files are the same size, SQL Server will use all of them equally. The reason to have multiple data files is to reduce possible allocation contention, as objects are created and destroyed in TempDB.

Microsoft suggest to create one TempDB data file per physical process core. But now a days there is four,eight and more than that core processors. So I think this is unnecessary. In general consent you should start with four or more TempDB data files and be sure to make all of the same size and later look for allocation contention and make appropriate decision for additional TempDB data files.