SQL Performance Tips 3

Hi All,

Me again with another SQL Performance tip.

Another way to optimize a query is using the Actual Execution Plan.

Well, first of all by using it you will see what indexes are missing, what table or join is causing the performance issue, which join is returning a lot of rows and many more.

It worth learning how to use the Actual Execution Plan.

Now about indexes, you must keep in mind that not all indexes that are suggested to be created by Actual Execution Plan are good to be created, and this is for several reasons:



1) By adding too many indexes to one table you will slow down the Insert and Delete statements on that table, because for each Insert and Delete statement MS SQL will try to reorganize all the indexes for that table and that usually takes some time to be done. This is why you have to think if it worth adding that new index to that table or not by yourself.

2) Actual Execution Plan suggests the new index based on the query that you have run, you could have another similar query on the same tables but with different search criteria so when creating a new index you need  to take into consideration that, maybe you make some modifications to that index so that it will be used in both queries and will give some performance improvements for both of them. Otherwise you will add that index and the second query will run even slower than previously.

Good luck.