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)
