SQL performance tuning and query optimization using execution plan

Performance tuning begins with understanding execution plan and finding & fixing expensive operators one by one in below flow:

First Lazy spool: Lazy spool is most expensive operator among all, which happens because of duplicate aggregation. We can remove duplicate aggregation by temporary tables, cte, etc.

Second Hash match: which is always because of unsorted data. Which means either missing index or indexes are not properly utilized, which can be because of :
1. function use in join or where condition
2. First column of existing index is not part of where clause or join
We can address this by adding new index, fixing query to utilize existing index or altering index to new filter configurations.

Keylookup: Keylookup just indicate, that index is missing some data. We can easily resolve it by adding missing column to index key or include part. But there is a catch as, we don't have privilege to alter index all the time. So, some times, we cannot avoid keylookup.

Index Suggestion:
Index key columns are the one which are part of where clause or joins.
Index include columns are the one which are part of only selection.

Partition Elimination: we check if partition are getting eliminated in query or not. As sometimes even tables have partitions still query go for scan instead partition elimination.

A lot of times, I have seen, developer use different datatype in storedprocedure parameters which don't match to actual column. And end up in no partition elimination.

Two reasons why query is not eliminating partition:
1. Data type mismatch or function used on where clause or type casting
2. Partition key column is not part of where clause

BAD Views: Views sometimes becomes bad when developer don't alter them instead they join same table to view which is already part of view to get some extra data. Instead they should write new view or alter existing view to get extra columns

Minimizing sub queries: We can minimize sub queries, if subquries belong to same table to get different column using cross apply or outer apply.
Example we have to get most recent orderdate, order amount, order shipped date, etc for each customer. So instead of writing different sub query for each column, we can use cross apply or outer apply to get data in single query.

[srinubathina]

This is the best class in terms of SQL Performance Tuning. Thank You..

[satheeshkandukurii]

Really awesome...pls do more videos on performance tuning

[kaustubhpradhan]

Extremely helpful. Searched a lot for help and video stands out to be unique. Thank you. :-)

[sharmavasundhara]

This is by far the best SQL query optimization videos I've watched! I'm going to share this with everyone I know who can benefit from this. Thanks @techsapphire 🙌🏽

[kaushikkrishnan]

Amazing, super helpful to get a direction on how to go about optimizing queries

[venkatasumanbhavanasi]

Hi yogesh, this is really helpful for developers who donno the concepts and tricks to optimize queries and simply writing the queries as their own which makes client in bad situation. This video helped me alot. Thank you..

[palashmondal]

I asked to optimize query by product owner after that start watching your video, it help me a lot. Thank you so much!!

[mithunraj]

Really this is one of the BEST video ever, so easy to understand. Very very very helpful. Thanks a lot for this brilliant one.

[saikumarramisetty]

Thank you so much for the awesome video, I got a clear idea about the Performance tuning

[mohammedminhajulhaq]

We need to share this kind of videos to maximum people. This is much underrated in terms of views. A lot to learn from this video. Thank you sooo much for keeping it simple and understandable. We can't get this much of advise and knowledge after paying also. Attended some pluralsite videos of some SQL experts but didn't understand a bit but in this 1 video got so much of information. Thanks a ton once again. Keep it up good work 👍👍👍👍

[priyankasarkar]

Awesome Sir..:) On point tutorial! Please make more videos on triggers, cluster, cursor.. Thanks in advance..:)

[jagjeetrathore]

i watched many videos, found this one awesome for beginners

[prabasmessi]

Thank you so much for sharing the knowledge. Best explanation for query optimization i have seen till now.

[NareshDama-qviu]

Very useful information.. thank you so much!!

[mrkamranumer]

Wonderful and Excellent Knowledge based in-depth Performance Tuning Video

[saurabhashok]

Simply outstanding explanation. Thanks!

[dbatexas]

thanks for details, video could be more in depth like why nested loop join vs hash join, what is lazy pool or eager spool/spills. but other than all good concepts and topics. it takes times to explain all details so we could give links for people to learn like nested loop runtime is O(n2) Vs merge join O(nLogN) etc etc

[anuragjains]

Nicely explained. Thank you very much!

[radha]

Very informative and core knowledge for every level of SQL developer. Thank you

[jaredlising-simplybrewedph]

Thank you so much for such detailed explanation! Appreciate it so much! 😊