SQL SERVER 2016 – Comparing Execution Plans

New tools bring new capabilities and sometimes these are ingrained deep into the product that we can easily miss them out. And SQL Server 2016 is no different. After installing the CTP, I was pleasantly surprised to see this addition to help our DBA’s who are doing performance tuning and query tuning activities.

So what am I talking about? Think of a scenario wherein you are doing some sort of testing in your QA environment. You seem to be troubleshooting a production scenario and found that very scenario to be working very well in your test environment. Though you have taken the data from the production database to the test QA server less than a month back, you are still baffled with the question – why this difference?

The next requirement everyone looks at is to start comparing the execution plans between your production and test environment for the same query. Some of the execution plans on a production server can run to pages sometimes and are difficult to find difference visually.

SQL Server 2016 – Plan comparison

If you have the execution plan from your production environment, then go ahead and open it in SQL Server 2016 Management Studio and right click to get the following option:

compare plan 00 SQL SERVER 2016   Comparing Execution Plans

You can see that an interesting addition. The “Compare Showplan” needs to be selected. Now, select the plan that was generated from your test environment. This will make bring both the execution plans on a side-by-side view as shown below. Look at the Purple color on the nodes.

compare plan 01 SQL SERVER 2016   Comparing Execution Plans

This represents the fact that both the nodes are same but have some differences in some properties. Now it is upto us to right click and bring the properties tab.

compare plan 02 SQL SERVER 2016   Comparing Execution Plans

Here we can go ahead and compare each of the node properties to find where the discrimination between the plans are. This is awesome capabilities added in the new version.

I am sure you will find this feature of great use in query tuning exercises in your environments. Having said that, one thing I have seen interesting is, if you take a live “Actual Execution Plan” and then try to compare it with a saved .sqlplan file – it will come up with an error as shown below:compare plan 03 SQL SERVER 2016   Comparing Execution Plans

Do let me know if you would use this feature and if you have used this feature – what is some of the enhancements you wished this comparison operator had? I would like to learn from you for sure.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join

I just had an interesting conversation the day before when I was discussing about Join Order in one of my recent presentations.

The comment which triggered all the conversation was “If I want to change the order of how tables are joined in SQL Server, I prefer to use CTE instead of Join Orders”. 

During the conversation user was suggesting that he wanted his tables to be joined in certain ways in SQL Server but SQL Server Engine Optimizer was re-organizing everything to give the most optimal performance. His needs were to join tables in certain ways and did not care about the performance. To join tables as per his need he had to use a FORCE ORDER hint of the table. However, he recently learned about CTE and believed that CTE will help him to achieve his tasks without the help of FORCE ORDER hint.

He believed that as CTE syntax is constructed before the SELECT statement SQL Server will build a result set instead first and will use the same resultset to join the SELECT statement following the CTE. Well, the casual conversation converted to debate and it was getting very difficult as everybody started to express their opinion with very loud voice. Finally, we decided to run an example on my machine.

NOTE: The example uses INNER JOIN only and the conclusion applies to only INNER JOIN. In the case of OUTER JOIN there is an entire different story, we will cover that in future blog posts.

We created two examples and executed them one by one. Once we executed them, we compared their resultant – they were identical. Right after that we compared the order of the join for both the example.

Example 1: Without CTE

USE AdventureWorks2012
SELECT c.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.PersonCreditCard pc ON pc.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.Customer c ON c.CustomerID = p.BusinessEntityID;

Execution Plan:

wtihoutcte SQL SERVER   Does Use of CTE Change the Order of Join in Inner Join

Example 2: With CTE

USE AdventureWorks2012
(SELECT c.CustomerID
FROM Sales.PersonCreditCard pc
INNER JOIN Sales.Customer c ON c.CustomerID = pc.BusinessEntityID)
SELECT cte.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN MyCTE cte ON cte.CustomerID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID;

Execution Plan:

wtihoutcte1 SQL SERVER   Does Use of CTE Change the Order of Join in Inner Join

When we compare both the execution plan, it is very clear that there is no change in the order of the table orders. Both the statements have a very similar execution plan and tables were joined in exactly the same order. The matter of the fact in the case of CTE, it was interesting to see that SQL Server started to join very first one table from CTE and another from SELECT statements and continued to build the entire execution plan.

The reason for this is very simple – CTE is not temporary table kind of object or feature. It is just an expression to represent your SELECT statement in such a way that it increases your readability and usability. They do not execute before the regular SELECT statement or build a result set before hand. It actually executes just like regular SELECT statement all together.

In summary: CTE does not impact Table Join Order when all Joins in the query are INNER JOIN. 

Here is one of the my favorite videos on this subject:


Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Video – Beginning Performance Tuning with SQL Server Execution Plan

Traveling can be most interesting or most exhausting experience. However, traveling is always the most enlightening experience one can have. While going to long journey one has to prepare a lot of things. Pack necessary travel gears, clothes and medicines. However, the most essential part of travel is the journey to the destination. There are many variations one prefer but the ultimate goal is to have a delightful experience during the journey.

Here is the video available which explains how to begin with SQL Server Execution plans.

Performance Tuning is a Journey

Performance tuning is just like a long journey. The goal of performance tuning is efficient and least resources consuming query execution with accurate results. Just as maps are the most essential aspect of performance tuning the same way, execution plans are essentially maps for SQL Server to reach to the resultset. The goal of the execution plan is to find the most efficient path which translates the least usage of the resources (CPU, memory, IO etc).

Execution Plans are like Maps

When online maps were invented (e.g. Bing, Google, Mapquests etc) initially it was not possible to customize them. They were given a single route to reach to the destination. As time evolved now it is possible to give various hints to the maps, for example ‘via public transport’, ‘walking’, ‘fastest route’, ‘shortest route’, ‘avoid highway’. There are places where we manually drag the route and make it appropriate to our needs. The same situation is with SQL Server Execution Plans, if we want to tune the queries, we need to understand the execution plans and execution plans internals. We need to understand the smallest details which relate to execution plan when we our destination is optimal queries.

Understanding Execution Plans

The biggest challenge with maps are figuring out the optimal path. The same way the  most common challenge with execution plans is where to start from and which precise route to take. Here is a quick list of the frequently asked questions related to execution plans:

  • Should I read the execution plans from bottoms up or top down?
  • Is execution plans are left to right or right to left?
  • What is the relational between actual execution plan and estimated execution plan?
  • When I mouse over operator I see CPU and IO but not memory, why?
  • Sometime I ran the query multiple times and I get different execution plan, why?
  • How to cache the query execution plan and data?
  • I created an optimal index but the query is not using it. What should I change – query, index or provide hints?
  • What are the tools available which helps quickly to debug performance problems?
  • Etc…

Honestly the list is quite a big and humanly impossible to write everything in the words.

SQL Server Performance:  Introduction to Query Tuning

My friend Vinod Kumar and I have created for the same a video learning course for beginning performance tuning. We have covered plethora of the subject in the course. Here is the quick list of the same:

  • Execution Plan Basics
  • Essential Indexing Techniques
  • Query Design for Performance
  • Performance Tuning Tools
  • Tips and Tricks
  • Checklist: Performance Tuning

We believe we have covered a lot in this four hour course and we encourage you to go over the video course if you are interested in Beginning SQL Server Performance Tuning and Query Tuning.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – View XML Query Plans in SSMS as Graphical Execution Plan

Earlier I wrote a blog post on SQL SERVER – Parallelism – Row per Processor – Row per Thread, where I mentioned the XML Plan. As a follow up on the blog post, I received the request to send the same execution plan so that the blog readers can also use the same and reproduce it on their machine. I realized that I have actually never written on how one can send a graphical execution plan to another user so that they can reproduce the same exact details without all the actual tables, indexes and objects.

sqlplan1 SQL SERVER   View XML Query Plans in SSMS as Graphical Execution Plan

Here is very simple method on how one can do that.

Right Click on Execution Plan and click on “Save Execution Plan As…”.

sqlplan2 SQL SERVER   View XML Query Plans in SSMS as Graphical Execution Plan

You can save the plan with the extension .sqlplan. The same plan can be sent to another user via email or a USB drive. Another user can just double click on the file and open the execution plan at another local computer without physically having any underlying object.

This is very simple trick; you can also send the execution plan in text format as well. We will talk about it in some other post.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit

During the SQL Server Optimization training, I enjoy teaching Execution Plans. I am always sure that questions related to estimated cost will be raised by attendees. Following are some common questions related to costs:

Q: What is the estimated I/O cost?
Q: What is the estimated CPU cost?
Q: Why there is no unit of measurement for estimated costs?

There are several other questions. However, let me try to answer the above questions today.

qexe SQL SERVER   Execution Plan   Estimated I/O Cost   Estimated CPU Cost   No Unit

Estimated I/O Cost and CPU Cost are just cost estimations as the names suggest. SQL Server Query Optimizer uses the cost to make the decision on selecting the most efficient execution plan. When any query is executed, the SQL Server Query Optimizer prepares several alternative execution plans to execute the query. Each alternative plans each operation and assign some type of cost on the basis of the heuristic data. This estimated number just implies the amount of work CPU or I/O has to do to complete the task. Due to this reason, there is no unit assigned for these estimations. These estimates should be used by us in the same way by which the SQL Server uses it – The estimate should be used to compare different queries with each other.

Let me know your thoughts on this. Do post here if you have any other questions. I will post the answers in separate posts.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

I was recently invited to participate in big discussion on one of the online forum, the topic was Actual Execution Plan vs. Estimated Execution Plan. I refused to participate in that particular discussion as I have very simple but strong opinion about this topic. I always use Actual Execution Plan as it is accurate.

Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running query. I just run query and have correct and accurate Execution Plan.

Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M

You can use SSMS to include the execution plan as well.

ExecutionPlan SQL SERVER   Actual Execution Plan vs. Estimated Execution Plan

Reference : Pinal Dave (http://blog.SQLAuthority.com)