Understand why it is important to review Estimated Execution Plan for SQL Queries

In this article we look at the need for reviews estimated execution plan for SQL Queries

We often make plans regarding something, in advance, and these are usually based on estimates. While making these plans, we believe that we have taken all possible factors into consideration, but often things do not turn out as per plan. This happens mainly because most of the plans are not full proof, no matter how hard we think or try; we do miss out on a few crucial details in the plan, or are required to make a few last minute changes in the plan, resulting in unfavorable results. To avoid this kind of situation it is important to always review an estimated plan before executing it.

Estimated Execution Plan For SQL QueriesFor SQL users it is suggested to run an ‘Estimated Execution Plan’ for SQL Queries before finally executing them, as that gives an idea about how much information a query would bring. As the name suggests the estimated execution plan will give estimates about how many rows will be returned, which operators will be used to acquire data etc. It provides a reasonably accurate guide for how the query would be executed. However, the ‘Actual Execution Plan’ will be displayed only after executing the query, before executing the query, what is displayed is the estimated execution plan. The actual execution plan is more accurate than the estimated execution plan. It might also be a little different from the actual plan; this is discussed ahead in the article.

In some cases creating an estimated execution plan is not possible as the query involves parameters and no values, which is difficult for SQL to translate, it understands literal values. In case of a long-running query, it will take longer for the execution plan. Parameters should be passed in if required. It might also happen that a function displayed in the estimated execution plan is not shown in the actual execution plan, due to certain reasons. This can be explained with the example of the scalar- value function, which is shown in the estimated plan but not in the execution plan.

Estimated Execution Plan And Actual Execution PlanThere might also be situations where there is a difference between the estimated execution plan and the actual execution plan, this happens mainly due to changes made in the tables after the estimated execution plan. Query optimizer uses table stats and indexes to display the estimated plan and decide on the actual plan, if the stats are changed in the time between the estimate and the actual execution, differences will show up. Adding a new index or updating an existing row while executing a query will result in differences from the estimated execution. These differences might also be large! They can be avoided by regularly updating table stats and indexes. You can refresh stats using automatic updates.

Running an estimated execution plan before actual execution of a query is imperative for SQL users not only because it gives you an estimate of what you will get, but also because it will help you do away with any kind of error in the tables or indexes and get better results. 

Dealing with unexpected SQL Crashes can be a challenge if you do not have elaborate failsafe measures in place

SQL crashes can often cause considerable grief to a DBA if elaborate failsafe measures are not in place. In case of small firms running a SQL Server instance, chances of a RAID5 or higher implementation being present is quite rare. In such cases one should immediately get a sql recovery tool like DataNumen SQL Recovery to recover the lost records. This powerful application is designed to extract every possible data element stored in the SQL database with the exception of XML. Further it can negotiate SQL files of humongous sizes with ease and can even extract data from such files stored on a virtual server.

Author Introduction:

Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/

Comments are closed.