Sql Performance Tips

Hi here some more important tips on Sql Performance,that will help you most:

Try To Avoid Sub Queries: Always try to avoid sub queries. Because sub queries hit logs many times that’s why log file get overloaded, so in place of sub queries use temporary table which create log into tempdb and which will not cause the slow performance.
A few tips for using temp tables in an efficient manner.
• Only include columns or rows you actually instead on using
• Do not use the select into statement; It places a lock on system objects
• Always delete the temp table yourself... that’s just a waste of resources
• If the temp db is not already on its own disk consider doing this will help greatly In closing I would like to say, I personally think that temp tables are a great tool and, if used correctly can be the best situation.

Use SET NOCOUNT ON statement: In a Stored Procedure many times you have seen that with every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is useful in debugging but it is useless after that. By setting SET NOCOUNT to ON, we can disable this feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO


Not to use “sp_” in User Defined Stored procedure: As it is cleared that in sys stored procedure are started with “sp_”. So if you are creating a sp by naming convention starts with “sp_” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)
If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25
The compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.