Programming, Technology, Tutorials

How To Read SQL Server Query Execution Plans

August 1, 2017

Introduction

SQL Server query execution plans provide you insight on the performance of your queries. You should run execution plans on every query prior to deploying them to production. This article discloses how to run and apply execution plans for enhanced database performance.

Your queries could be the core element for performance issues in your web applications. Unless you’re acquainted with designing and optimizing queries, you may not understand that the way you create a query has a significant effect on your database performance. SQL Server Management Studio carries a tool within their software that permits you to construct an execution plan. The execution plans provide you some insight on what components of your queries aren’t optimized.

How To Run A SQL Server Query Execution Plan

Click on the “Query” menu item in the Studio and choose the kind of execution plan you wish to run. There are two sorts of plans estimated and actual. The two plans may possess substantially disparate results hinged on the last time you refreshed your data. The estimated execution plan furnishes data based on statistics from your SQL Server. For example, you didn’t refresh your database from production in many months. The estimated execution plan will merely run from preceding month’s statistics.

The actual plan runs the query and draws statistics from extant results. The central discrepancy between the actual plan and the estimated plan is that estimated plans run from preceding query statistics and actual plans run the query and return extant results. This discrepancy is particularly why you can perceive critical differences when you run an estimated plan in development and an actual plan on production servers.

How To Read A SQL Server Query Execution Plan

When you are done creating your plan, you should study the statistics. SQL Server provides you a visual plan that’s arranged as a tree. You read the plan starting from left and ending to the right. Every result, join, loop, indexed and non-indexed procedure is delineated. The execution plan classifies each process into elements. You can analyze each of these elements for CP and I/O costs, the size of the result set, and the number of rows returned. These components are paramount when you optimize your queries.

Take note that the costs are presented as percentage numbers, and these numbers substantiate to 100%. Elements with high percentage costs are where you ought to target your optimization.

Predominant Elements To Target

Contingent on the magnitude of your query, you could have numerous elements displayed in your execution plan. The one element to be aware of is a table scan without an index. Table scans compel the database engine to go through the entire table rather than utilizing the indexes. If your table holds thousands of rows, a table scan may drastically cripple performance. You ought to join tables and run queries based on indexes on crucial columns. Thus, the majority of your execution plans must show clustered and nonclustered index scans.

What you should do next is hover your mouse over one of the elements. You will perceive a tooltip presenting the resource usage for the element’s activity. You have to decrease I/O and CPU usage since spikes in these resources will deteriorate performance on the server itself. If there is too much resource usage resulting from your queries, your server and additional web applications situated on it may impede.

Examine the magnitude of the result set. A few developers return thousands of rows when it’s unnecessary. Refrain from returning thousands of rows and filter the results in the front-end code. Rather, establish optimized queries that filter out superfluous records and return filtered results to the front-end application. This will diminish the magnitude of your result set and optimize the query for improved speed.

Lastly, review the text results after running a query. If SQL discovers that you ought to be using an index, it will show a notice in green. While inadequately indexed tables won’t at first generate issues, your performance will deteriorate as the web application becomes larger.

Conclusion

Performing minimal optimization efforts on queries will drastically boost the speed of your database. You must run execution plans on all database queries to guarantee that you have the ideal performance prior employing to production servers. It may appear like an unnecessary phase in the development process, but it preserves time and reduces the need for troubleshooting when the database no longer returns data expeditiously.

You Might Also Like

Back to top
%d bloggers like this: