Query Optimization Tips

Before optimize any query clean buffer first after we can see what's exactly going on

  - DBCC dropcleanbuffer
  - DBCC FreeProcCatch

For See Statistsics Also See In Execution Plan

  - Set Statistics IO ON
  - Set Statistics Time ON

For Optimization We are Concentrate on 3 Most Statistic

1) NO of logical reads
2) CPU Time
3) Query Cost (QC)

Both 1st and 2nd You will Get From Upper Commands
But For QC Use Execution Plan

See and Note All Statistic and Then Apply This Instruction For optimization that Given Below:

1) Limit no of colums return from query
2) Create Primary Key
3) Create index which are using in workhours
4) Limit the no of rows by using top
5) Working with join have index on the column in the join
6) If you use multiple columns in where or order by clause then create compound index
7) If you are trying to fetch unique values use group by instead of distinct
8) Use exists in behalf of in().
9) set nocount on