Improves Stored Procedure Performance in SQL Server

1.SET NOCOUNT ON is used in procedure for improve the performance of the store procedure.
put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.
This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
@Param1 = <Default_Value_For_Param1, , 0>,
@Param2=<Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Improving SQL Performance 
http://www.componentace.com/help/absdb_manual/increasesqlperformance.htm