HTML clipboardAnalyze Query execution plan
You can view the query execution plan while executing the query in your query
analyzer. Through that you can tune your query. It will show you whether the
appropriate tables are using the proper index or not. Other information like,
whether you plan is using Table Scan or Index Seek or Bookmark Lookup or Hash
Join and etc. Based on the above information you can tune your query easily
Table Hints
Table Hints will make the query execution faster by forcing the plan to choose
the appropriate joins or any other stuff that is mentioned as the hints. You can
retrieve the top percentage of results; like that so many hints are available in
sql server. Make the use of table hints appropriately.
Avoid Functions in Where Clause
Don't use any inbuilt function in the where clause, it will make the execution
plan more complicated; it will avoid the Index Seek in the plan if you use it.
Partitioning
Partition the table whenever required, because whenever the number of row
increases, query retrieval will be affected. It will take more time in the
select query. To avoid that, based on the selection process just partition the
table horizontally or vertically, which will make the query retrieval faster.
Apply Scale Up and Scale Out Strategies
Apply the scale up and scale out strategies whenever required. Scaling up is
nothing but increasing the hardware support of the database server, which will
increase the performance. Scaling out is nothing but partitioning the table
horizontally or vertically. There were lot more stuff in it, it's just the
introduction.
Avoid if conditions in the stored procedure
Don't use If conditions unless its required, because each and every time stored
procedure will be recompiled. It won't be stored in the cache.
Update Statistics
Update the statistics in a weekly or monthly basis; it will update the index
information of a table. When a lot of insert and update operation done on a
table, we should update the index information, for that we need to update our
statistics, which will improve the performance of a transaction.
Covering Index or Composite Index
Use Covering Index aka Composite Index to avoid the unnecessary book mark lookup
in the execution plan.
LDF & MDF Location
Have the log file and data file in separate drive, which will increase the
performance drastically.
Appropriate Usage of Index
Don't create Index for all the tables, sometimes it will slow down the
performance too. When you create index its storing additional information
internally to the database; Data's will be fetched quickly even if you don't
create index for a table. So Index creation should be done according to the
usage.
Resource Governor
A single server is used to provide multiple services, so allocate the server
resources based on the usage of each service, like reporting services, OLTP,
OLAP services. This will avoid the 100 percent CPU utilization.
Profiler and Database Engine Tuning Advisor
Through profiler you can easily trace which query is taking more time to
execute, so once selected you can tune the queries using tuning advisor tool
which is already inbuilt in sql server.