Optimize SQL

In a competitive IT industry, the primary challenge is to make the product available in the market for users so that they don’t opt for any other alternatives. The development of products is aimed to benefit the customers with great performance and latest technology. The developed product and its performance is good enough for users to make best use of technology. Of course, most of the products/applications might be dealing with heavy data flow between back-end services and database servers.

Table of Content

  1. Best Practices to get high performance in SQL Queries
  2. What is a SQL Execution Plan?
  3. How do you create a SQL Execution Plan?
  4. What are the components of a SQL Execution Plan?
  5. How can execution plan improve query performance?
  6. Conclusion

In a competitive IT industry, the primary challenge is to make the product available in the market for users so that they don’t opt for any other alternatives. The development of products is aimed to benefit the customers with great performance and latest technology. The developed product and its performance is good enough for users to make best use of technology. Of course, most of the products/applications might be dealing with heavy data flow between back-end services and database servers.

An ideal and responsible team of dedicated software developers considers it a duty to write an efficient query that gives quicker and optimized results. Apart from that, we also have to optimize SQL queries which are performing slow and unable to give faster results.

SQL Server query performance tuning is seen as a primary concern because of the constant battle of database managers to achieve the highest performance and the lowest use of resources for their managed systems.

Using SQL Execution Plans as the first and foremost approach for any database administrator, we recall query output tuning. This is how the strategy advises us what to tune. We demonstrate how the tasks are done internally with the execution of road map presentation. The most costly aspect of the query, signs that the best write up query and the recommended indexes.

1. Best Practices to Get High Performance in SQL Queries

Before we jump into the execution plan, let’s go through some best practices to write high SQL queries. Here we assume that you have basic knowledge of how to write SQL queries and the points below are for achieving high performance following the best practices.

  • Perform this query for the required columns only, this would ensure that the db query is fired for the necessary columns and that no unnecessary columns are fetched, ensuring that efficiency and specifications are better fulfilled. Unless it suits the complete purpose, do NOT reply queries.
  • Subqueries should be avoided. Perform functions to join or write as needed.
  • Utilize proper indexes (for faster search results).
  • Often be aware of NULL events in your results.
  • Often use table aliases where there is more than one source involved in your SQL statement. It increases readability, maintenance and assures that the proper columns are collected.
  • In the ORDER BY clause, do not include column numbers. The main purpose of using this is for usability and scalability and not just performance. It might not be a concern while you create the database, but when time progresses and new columns are applied to the SELECT statement or whether you have used ColumnNumber, the original table is reordered. When you use ColumnNumber, then the results will be unpredictable and wrong in terms of ORDERBY.
  • It is important to use INSERT statements in the column list. We advise this to help Software developers avoid the table modifications for the NULL values added to the columns. Thus the impact can be easily identified.
  • For T-SQL code, never use double quotes.
  • You should not use a prefix for stored procedures that starts with “sp_”.It is a syntax of the system that SQL provides us. It is advised to follow a unique pattern to name written procedures that can be differentiated easily.

2. What is a SQL Execution Plan?

As discussed, the Execution plan is a graphical representation of different operations performed by the SQL query processor. When you execute any query then the query processor will generate an execution plan along with the Query to be initiated. Basically, it provides two primary execution plans.

  • Estimated Execution Plan
    1. This type of plan is generated before the query executes or we can say during the compilation time.
    2. It is just an estimation by the query processor.
    3. No runtime information is provided with this.
  • Actual Execution Plan
    1. This type of plan is generated after the query is executed or we can say after run time.
    2. It is giving actual information by the query processor.
    3. It provides all information like which are the steps involved when we execute that query.

3. How do you create a SQL Execution Plan?

An execution plan is generated when you execute any query which necessarily includes the query along with the plan. There are many other ways to execute this plan. Like you can use the Menu button, and toolbar buttons and shortcut keys. For more details, let’s get deep into the process below.

  • Shortcut key: There is a shortcut key available to check for the Estimated Execution plan. You can press Ctrl+L after writing the query in the Query window.
  • In the Context Menu of the Query Window, you will find a menu with the name “Display Estimated Execution Plan”. It will work the same way as the above step will do. It will display the Estimated Execution Plan. Also, in the Query menu, there is an option available named “Display Estimated Execution plan”.
  • In toolbar, there is a button with this image. Image And this is exactly how the Actual Execution plan functions there is a shortcut key available for this and that is Ctrl + M.

(Note: If the button does not exist then check on Add or Remove Buttons. Also, in the Query menu there is an option available named “Include Actual Execution plan”.

Estimated Execution Plan Example

Estimated Execution Plan Example

Actual Execution Plan Example

Actual Execution Plan Example

As you can see in the example it is showing the Actual Execution Plan. We have executed the query and there are 3 tabs available. If you check the difference it will show you the time taken for the scan. There are 3 parts in which we can divide our execution plan.

  1. Clustered Index Scan (Clustered)
  2. Sort Operation
  3. Select Operation

4. What are the components of a SQL Execution Plan?

As there is no data available in the table and it’s a simple query so the estimated execution plan and actual execution plan will be the same but if you go with the big queries then you will see the difference. You can use that to optimize your Query.

When you hover on the Clustered Index Scan there will be detailed results available. Have a look at the below screenshot.

Clustered Index Scan

If you see in the image above, SQL has provided different details. We will discuss everything in detail.

  1. Physical Operation: Physical Operators are the objects that perform such operations. Some of the Examples are Index Seek, Clustered Index Scan etc. Logical Operators are giving direction to this kind operator to perform the defined operations.
  2. Logical Operation: In Physical Operation, Our Software Developers use the work of Logical Operators. It also gives a clear picture of what query is necessary to process and how will it perform.
  3. Actual Execution Mode: This section will portray the actual plan to be executed. It is used by the Processing engine and it is used to execute the Query.
  4. Estimated Execution Mode: It is similar to the above plan but the only difference is it is showing estimated value.
  5. Storage: The output fired by the query is like an optimizer extracted from the query.
  6. Legit facts and figures for all executions- The actual plan will show all the real figures and numbers in the execution plan. Based on the condition that will give us no records and no returns.
  7. Actual Number of Batches: This will exist only in the Actual execution plan. If it’s a Batch query then it will return No of Batches.
  8. Estimated Operational Cost: If there are any other operational costs involved in our query then it will do the calculation for that and will be displayed here.
  9. Estimated I/O Cost: It shows the accurate number of input and output costs.
  10. Estimated CPU Cost: It estimates the cost to execute the operations with the CPU.
  11. Estimated Subtree Cost: When the Execution plan is generated then it generates Tree. From now, you’ll be able to calculate
  12. Number of Executions: This will exist only in the Actual execution plan. In the single batch, the number of executions that can be handled by the Optimizer.
  13. Estimated Number of Execution: Similar to the above one but the only difference is that it will give you the Estimated value.
  14. Estimated Number of Rows per Execution: This is just an estimation from Optimizer that how many rows are going to be returned.
  15. Estimated Number of Rows to be Read: This is just an estimation from Optimizer that how many number of rows are going to be read.
  16. Estimated Row Size: As the name suggests, it is showing you the estimated row size of the storage.
  17. Actual Rebinds- This will be active during the actual execution plan. It gives information like how many times an object must be reevaluated to process.
  18. Rewind Actual numbers: This part will be repeated in the actual execution plan again.
  19. In correlated operation, the total number of rows is executed by using inner result datasets repeatedly.
  20. Ordered: It determines if the dataset on which operation is performed has implemented sorting or not. If you check in the above example it is giving you False because till now sorting is not done. Once sorting will be done then it will be true.
  21. Node ID: This follows a unique type of numbering from Right to left and then the usual Top to bottom. So, we can say that Bottom Right will have NodeID=1 and Top Left has Maximum Node based on the Execution Plan Tree.

In Additions, there are two more components. We can check its information below.

  1. Object: Defines the Table on which we have performed that query or operation.
  2. Output List: Defines the selected columns which will be displayed in the Dataset or result.

Search data in the table

We are now going to delve deeper into this with an example so before we begin let’s brush up our knowledge about indexes and comparison between those.

Table Scan: In this type of scanning, the scan is comprehensively executed in a way that it touches every row of the table, irrespective of whether it qualifies the given search result or not. This type of scan is an efficient way to check a small table in which the majority of the rows would qualify for the predicate. The estimated cost would be proportional to total number of rows in the table.

Index Scan: If the table has a clustered index, then the executed query should cover all the rows and columns. So, it is advisable to fire a query that would cover most of the rows or almost all the rows of the table. I.e. a query without a WHERE or Getting clause, the index search would be used. In the process of database optimization, the query optimizer chooses the best one from the available index. And based on that information, the clause functions are clearly defined when the whole table is scanned.

This clause keeps the statistical information of the database.

The moment you choose the right index. The immediate next step is to navigate the tree structure to all the matching data points and using SQL Query processor or engine extract the exact records.

One of the major differences between a full table search and an index scan is that when data is sorted in the index tree, the database engine understands when it has reached the limit and still looks for that. It can then submit the question or, if appropriate, pass on to the next data set.

Index Seek: The cost is directly proportional to the number of qualifying rows and pages, rather than the total number of rows in the table, since only qualified rows and pages containing these qualifying rows are affected by a search of these three, this is the quickest one.

5. How can execution plans improve query performance?

Execution Plan

From the above image displayed in the execution plan, there are 4 different queries with some minor changes. Let’s take a look at each of these one by one and try to understand what improvements we can make by observing execution plans.

Query 1: SELECT DepartmentID, DepartmentName FROM Department WHERE DepartmentName = ‘HR’

This is a table which does not have any primary key defined and hence it does not have any clustered index created. This performs a complete table scan which is visible in the first execution plan. This query takes the maximum time if the number of records in the table are in millions.

Query 2: SELECT EmployeeID, EmployeeName, DepartmentID, BirthDate FROM Employee WHERE DepartmentID = 3

This query is performing Index scan which is little faster than table scan as it would be bringing data in the sorted order the way it was stored through clustered index. It will still be slower if the table is having huge data.

Query 3: SELECT * FROM Employee WHERE BirthDate = ‘1982-08-07’ I have created a non-clustered index on the BirthDate column as visible in the first image above. Please note that the columns are shown in the included tab in the image below. This means, the index seek can only be performed if there are many columns selected in the select clause and WHERE clause is on the Birthdate column.

Still the third execution plan is showing Index scan. Confused! Please refer to the first bullet point in the best practices section to get high performance. You can In the SELECT clause we have written * and not the specific columns. This has prevented index seek ability and we didn’t receive the desired performance that was expected. In spite of creating a non-cluster in the database. Now, let’s check the final query.

Final query

Query 4: SELECT EmployeeID, EmployeeName, DepartmentID, BirthDate FROM Employee WHERE BirthDate = ‘1982-08-07’

You will note that the Index seek has been used for this query as we followed the best practices and we have non-clustered indexes created accurately.

To identify performance improvements, one should be aware about best practices and in-depth knowledge about the SQL server. The execution plan helps finding out missing things by its graphical representation so one could easily find out the action items to improve performance in a particular query. I hope this example would have given the idea about how to find out improvements in the query.

6. Conclusion

In this article, we did an extensive research and gained insights on the Execution plan. Some of the secret strategies were known on how to produce the execution plan, the discrepancy between the projected execution plan and the real execution plan, and various components of the execution plan nodes. Also we learnt about how execution plans help finding improvements in the query. This tool has been very useful for the DBA to deal with day to day challenges and when there is a big data concern so you can check the execution plan again and optimize the query whenever needed.

More Useful Resources:
How to Configure Database Mirroring for SQL Server
How to compare two SQL Server Databases using SQL Server Data Tools

Vishal Shah

Vishal Shah has an extensive understanding of multiple application development frameworks and holds an upper hand with newer trends in order to strive and thrive in the dynamic market. He has nurtured his managerial growth in both technical and business aspects and gives his expertise through his blog posts.

Related Service

Know more about our Custom Software Development Service

Learn More

Want to Hire Skilled Developers


    • Leave a message...

    Related Articles
    Asp net Core and Docker
    ASP.NET Core and Docker
    Jul 2, 2021
    Amazon Lambda vs Azure Functions
    AWS Lambda vs Azure Functions: Serverless Computing
    Mar 22, 2021
    How to Build a Serverless Web Application in Azure
    How to Build a Serverless Web Application in Azure?
    Apr 28, 2021