Performance Tuning High Level- SQL SERVER

Analyze 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 Scan

• Index Seek

• Bookmark Lookup

• Hash Join

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.

• KEEPIDENTITY

• KEEPDEFAULTS

• FASTFIRSTROW

• • FORCESEEK

• HOLDLOCK

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.