Performance tuning : Beginner

The slow performance of stored procedures might have a lot of causes, like hardware, server memory, OS, network etc.
Primarily four parameters to monitor.
CPU : The amount of CPU time in milliseconds.
READS : A scalar counter, which shows number of logical reads performed by the server.
WRITES : Number of physical disk writes performed by the server.
DURATION : Amount of time taken to execute the batch or SP.
there are no specific rules to tune SQL statements. The ultimate goal is to reduce time and system resources.

Open the Management Studio and the stored procedure that needed to tune. Before running Profiler, execute the two commands below.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
These two commands clears the machine cache and cleaned the buffers. This allows to get the Profiler readings from scratch.

We should take readings from SET STATISTICS IO ON and SET STATISTICS TIME ON in Management Studio.

• Performance tuning few things:
  i) We should be careful about creating temporary tables, dynamic SQL and joining. Those things are the gold mine for the tuners as eliminating them can make your procedure much faster.
  ii) Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before.
• iii) It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
  iv) We always want to see the execution plan for each DML statement. The execution plan is the road layout, based on which the optimizer will decide which path will take less time and resources.
  v) Try and avoid table scans. If the query optimizer chooses a table scan, it will scan the whole table, which is far more time consuming than index scan. If possible, create indexes where table scan is used (though based on the size of the table, optimizer sometimes decide to go for a table scan though there are indexes present for the column, but in this case table should contains a small number of records).
  vi) Always try to write all the SQL statements in ANSI format. Generally, ANSI syntax not reduces the logical Reads, but it is more helpful to understand.