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. Let us learn about Performance Tuning with SQL Server Execution Plan.
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 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 the 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 (https://blog.sqlauthority.com)
6 Comments. Leave new
Hi Pinal,
Nice option “Scroll back to Top” at the bottom of our blog.
This traveling example fit well to the subject.
Sometimes you want to go from Warsaw to NY, but there is no connection, so you fly to the Berlin to find another flight. Then you fly to Rom, Paris, Moscow and finally you`re in London. After that you discover some ship to Mexico.
When you finally goes to america (yeah!) you decide to stay where you are becouse another effort will be bigger than is should be ;-)
Nice artcile. The 4 hr video course is not viewable. Do we need to signin to view the video?
Hi…
I have installed SQL 2012 Enterprise Edition on VM and Restored Database from SQL 2005 Standard Edition. Now, when I execute query on both servers which are having same database. SQL 2012 Enterprise Performance is slower than SQL 2005 Standard Edition.
Even SQL 2012 Enterprise installed on high configuration server.
Can you guide which parameters should be verified?
Excellent..with a single video i got a good idea of a Execution Plan..
Thanks a Lot :)