Secrets of SQL Server Execution plan (2/5)

Haja Batcha

In the previous post we saw some limitations of execution plan, along with some tweaks. In this post we will go through some of the backstage things which are happening on the SQL Engine.

First i would like share some truths regarding the query optimizer, actually it was the disclaimer by the query optimizer that the indexes suggested by the optimizer should be reviewed by human brain. But some guys will apply the index suggested by the optimizer without much analysis, in that case this index will not be used by the query at all.

For example you are optimizing a code, and the optimizer is suggesting for a missing index. And you have created the suggested index and go back to the query execution plan but still it is suggesting the same missing index which you have created. Once this happens you can make sure, what you have created is not appropriate for that query. In this case the best way to resolve is to look deeper into the query. The query may need the same index with some small changes, which include changing the order of the key column or included column according to the need or adding index filter according to your where condition. By this way you will get the full benefit of the query optimizer.

Secrets of SQL Server Execution plan (3/5)

Haja Batcha

So for we have saw somelimitations of execution plan, some alarming signs which tell us about the need for update statistics etc on my previous blogs. Now we will see how to analyse some more complex execution plans. As usual we will start with tip. It is always better to set the IO Statistics on while optimizing the query, which gives some clear picture about your query. While working with very big execution plan it will be little bit hard for us to check whether our indexes are used are not. for such situation just wright click anywhere on the plan and click “Show Execution Plan on XML” then you can use CTL+F to find any specific index. Also generating a XML plan has lot more benefits, such as memory fractions, parameters list used while optimization etc..

Also we should keep in mind the execution plan distributes the cost across the operators,  you may saw high number on Key lockups which require covering index. And you may saw warning symbol over hash match join etc.. which are all just instigators. There is no one rule fits all in query optimization which is the reason why we don’t have a query tuning robot yet.