PERFORMANCE TUNING:

Shortly : The performance tuning is  reduce the load of the
database.

stored procedure:
--create a sample performance tuning procedure

CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
BEGIN

--NOCOUNT is use to reduce the load 
--if its off the procedure get (full memory) of execution

SET NOCOUNT ON;

IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO

CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO

CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO


STEPS:
1.Good code ideas
2.Reduces use more query in same procedure
3.use clear structure of code 
4.always done stored procedure is good. because,Transact-Sql
get load of execution

Agree with John, Just to add this is a way to make our query
fast and to reduce the over load on server. 

The RECOMPILE option
SET NOCOUNT ON/OFF
Creating Indexes on tables are few points to fine tune our
query.