Working on Performance Tuning is very tough task specially on
large scale data hence the minor changes will give dramatic (+ Ve or -Ve)
impact on performance.
Working on
Performance Tuning is very tough task specially on large scale data hence the
minor changes will give dramatic (+ Ve or -Ve) impact on performance.
Before going to
tune the query as an experience developer you should aware on
If your application stops working suddenly, it may not be a
database issue. For example, maybe you have a network problem. Investigate a
bit before you approach the DBA!
The below are the few generic points for tuning.
Index Level Performance Tuning
1. Create indexes based on use. i.e don't create the index on the column where data type length is >=1000.
2. Keep clustered index keys as small as possible.
3. Consider range data for clustered indexes.
4. Create an index on all foreign keys.
5. Create highly selective indexes.
6. Create a covering index for often-used, high-impact queries.
7. Use multiple narrow indexes rather than a few wide indexes.
8. Create composite indexes with the most restrictive column first otherwise it will leads key lockups in execution plan.
9. Consider indexes
on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
10. Remove unused indexes.
11. Use the Index Tuning Wizard to identify the columns required index.
Algorithm or Tuning Mantra for SQL Code
No comments:
Post a Comment