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:

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 ;-)

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.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.