There are few rules for optimizing slow running query. Let us look at them one by one see how it can help.
Rule # 1 : Always look at query plan first.
I always start looking at query plan. There is always something which catches eyes. I pay special attention to part which has taken the most expensive part of whole execution plan.
Rule # 2 : Table scan or clustered index scan needs to be optimized to table seek (if your table is small it does not matter and table scan gives you better result). Table scan happens when index are ignored while retrieving the data or index does not exist at all.
Rule # 3 : Bookmark lookup are not good as it means correct index is not being used or index is not sufficient for all the necessary data to retrieve. The solution of bookmark lookup is to create covering index. Covering index will cover all the necessary columns which needs to improve performance of the query. It may be possible that covering index is slower. Try and Error is good method to find the right match for your need.
Rule # 4 : Experiment with Index hints. In most cases database engines picks up the best index for the query. While determining which index is best for the query, database engine has to make certain assumption for few of the database parameters (like IO cost etc). It may be possible database engine is recommending incorrect index for query to execute. I usually try with few of my own indexes and test if database engine is picking up most efficient index for queries to run. I use Index Hint for this purpose.
Rule # 5 : Avoid functions on columns. If you use any functions on column which is retrieved or used in join or used in where condition, it does not take benefit of index. If I have situation like this, I usually create separate column and populate it ahead of time using simple update query with the value which my function will return. I put index on this new column and this way performance is increased. Creating indexed view is good option too.
Rule # 6 : Do not rely on execution plan only. I understand that this contradicts the Rule # 1, however execution plan is not the only benchmark for indexes. There may be cases that something looks less expensive on execution plan but in real world it takes more time to get data. Test your indexes on different benchmarks.
Rule # 7 : ___________________________________________
Let me know what should be the Rule # 7.
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
