SQL Server Optimization tips

Stored Procedures

• Don't name your stored proceedures sp_MyStoredProcedure. Starting the name with sp_ results takes longer time as the server always checks the Master database first.

• Use "SET NOCOUNT ON" to prevent the server from returning a message to the client with the number of rows affected by this operation. This is specially usefull if your stored proceedure contains multiple SQL statements.

• Use "return @@identity" in a stored procedure to return the identity to the client after adding/inserting data to the database. This is must faster than using a select statement to get that information. More information about this can be found here.

• Use TOP or "SET ROWCOUNT" to limit the number of records returned from a query. It is just a waste of resources to retrieve more records than necessary so making a limit is a good way to boost performance. More details can be found here.

• Use full qualified name when calling stored procedures using this notation ServerName.DatabaseName.OwnerName.StoredProcedure. Using this notiation the SQL-Server will find your stored procedure faster.