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. Other information includes whether your plan uses Table Scan, Index Seek, Bookmark Lookup, Hash Join, etc. Based on the above information, you can tune your query easily.

• Table Scan
• Index Seek
• Bookmark Lookup
• Hash Join

Table Hints

Make use of table hints appropriately. Table Hints will make the query execution faster by forcing the plan to choose the appropriate joins or any other stuff mentioned as the hints. You can retrieve the top percentage of results; many hints are available in the SQL server.

• 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, and avoid the Index Seek in the plan if you use it.

Partitioning

Partition the table whenever required because query retrieval will be affected whenever the number of rows increases. It will take more time the select query. Based on the selection process, partition the table horizontally or vertically to avoid that, making 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 was much more stuff in it; it's just the introduction.

Avoid conditions in the stored procedure.

Don't use If conditions unless required because the stored procedure will be recompiled every time. It won't be stored in the cache.

Update Statistics

Update the statistics on a weekly or monthly basis; it will update the index information of a table. When a lot of insert and update operations are 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 bookmark lookup in the execution plan.

LDF & MDF Location

Have the log and data files in a separate drive, which will drastically increase the performance.

Appropriate Usage of Index

Don't create Index for all the tables; sometimes, it will slow down performance. When you create an index, it stores additional information internally in the database; Data will be fetched quickly even if you don't create an 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, and OLAP services. This will avoid 100 percent CPU utilization.

Profiler and Database Engine Tuning Advisor

Through the profiler, you can easily trace which query takes more time to execute. Once selected, you can tune the queries using the tuning advisor tool already built into the SQL server.