SQL performance tuning and query optimization using execution plan
HTML-код
- Опубликовано: 11 июл 2024
- 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.
Scripts: drive.google.com/file/d/1RUNg...
This is the best class in terms of SQL Performance Tuning. Thank You..
Glad you like it!
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 🙌🏽
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 👍👍👍👍
Thanks. Note:
I also provide commercial support. Please contact if needed.
If you find this solution helpful than donate comfortable amount on papal to my email ID or link www.paypal.me/bimlamehla.
Donation is not mandatory is it just request to support free tutorial.
@@techsapphire can we have your contact number or whatsapp number/ email id
Extremely helpful. Searched a lot for help and video stands out to be unique. Thank you. :-)
Glad you find it good
Really this is one of the BEST video ever, so easy to understand. Very very very helpful. Thanks a lot for this brilliant one.
Glad it was helpful!
Thank you so much for the awesome video, I got a clear idea about the Performance tuning
Glad it helped!
Amazing, super helpful to get a direction on how to go about optimizing queries
Glad it was helpful!
I asked to optimize query by product owner after that start watching your video, it help me a lot. Thank you so much!!
Glad it helped!
Really awesome...pls do more videos on performance tuning
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..
Glad it was helpful!
Very useful information.. thank you so much!!
Thank you so much for sharing the knowledge. Best explanation for query optimization i have seen till now.
Glad it was helpful!
Wonderful and Excellent Knowledge based in-depth Performance Tuning Video
Glad you liked it!
Simply outstanding explanation. Thanks!
Glad it was helpful!
Amazing tutorial on query tuning... thanks a lot
i watched many videos , found this one awesome for beginners
Thank you for your feedback.
Very informative and core knowledge for every level of SQL developer. Thank you
Glad it was helpful!
Insanely good video. Helped for my DE interview
Glad it helped! I provide training as well.
Very excellent video .Thank you so much for this explanation.
Glad it was helpful!
Nicely explained. Thank you very much!
Thank you for your feedback.
Great video, brother. Have learnt so much so quick
Glad to hear it!
Nice explanation and got clear Idea on the such things. Thanks :)
Glad it was helpful!
Awesome Sir..:) On point tutorial! Please make more videos on triggers, cluster, cursor.. Thanks in advance..:)
Sure thing! Keep learning :)
amazing video!! very helpful, thanks!
Glad you found it helpful.
Amazing, very well explained, thanks!
Glad it was helpful!
Thank you so much for such detailed explanation! Appreciate it so much! 😊
Glad it was helpful!
Thanks! Thanks a lot Yogesh for your time and efforts in this particular video. This helped me a lot..
Glad it helped
Great tutorial! I learned a lot!
Glad it was helpful!
Thanks Much For this video! Really appreciate it! :)
Glad it was helpful!
Very informative... Brilliantly explained
Glad you liked it
Its very helpful. Please share more videos
Very Good Lecture gives me so much clarity.
Glad it was helpful!
This is just amazing, going to share with all mates.
Glad you find it helpful
Thank you for good teaching you explained it very clearly
Glad it was helpful!
Really great video... We look forward to learn more ✌️
Glad you liked it
Very helpful tutorial, thank you.
Glad it was helpful!
Thank you very much for all your effort.
Always welcome
Learned So much, Keep doing more videos. Respect++
Thanks, will do!
Excellent ! outstanding video., most of the concept were unknown.., really helped a lot., thanks a lot Bhai.., pls create more stuff like this..,
Thank you, I will
Good job. The explanation is very succint
Glad it was helpful!
Amazing Video Very useful !!!
Glad it was helpful!
This is an astoundingly good video
I'm glad you like it
Excellent performance tuning tutorial
Glad you liked it!
Really its awesome.. Its helped lot and easy to understand.. please make more videos. thank you
Thank you, I will
I went though this video and found it very useful.
Glad it was helpful!
thanks ... :) ... really great presentation.
Glad you liked it!
That's awesome, thank you very much!
You're very welcome!
Thanks for the complete explanation
Glad it was helpful!
Timstamps for various topics covered..
18:21 Hashmatch
23:54 KeyLookup
nice explanation, very helpful.
Glad it was helpful!
nicely explained every topic. Good Video
Glad you liked it
Excellent session Sir!!
Glad you liked it!
It's a brilliant explanation.
Glad you liked it
Thanks for your detail explanation
Glad it was helpful!
very explained Sir, Thank you for the video.
Glad it helped
I watched your video might be 4th or 5th time today, N every time I learn something new n brings my knowledge to next level..Very well explained sir. Keep Sharing such a live scenario n knowledge..This helped me lot..lots of love sir #bow ❤❤❤❤
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
Thank you for your feedback. Reason why I haven't covered details about latches and waits and why nested loops, etc is, mostly I make video for intermediate learner to expert level. And if I add these stuff, video become confusing for them, I tried to cover in best lehman terms. But I agree for some viewer details make more sense, but mostly people I train are not good with basics so, difficult to bring them in same page.
have you used sql sentry plan explorer? what is your opinion about it. I find it really facinating. I wish they had more vidoes on that.
Amazing Thank you so much!
Glad you like it!
Awesome, just below excellent,
Thank you so much 😀
this is best video for performance tuning
Thank you for positive feedback
Thanks allot it's really helpful
You're welcome!
Thanks a lot..
this is realy good picture of optimization .. also please add sql profiler in terms of optmization
Thank you for your feedback. I will add session on sql profiler too.
Superb information.
Thanks a lot
to the point. Thank you sir!!!!!
Glad it helped
Nice video. Thank you sir
Most welcome
This was such a good video I’m shocked ahahaha! Do you have any more optimisation tips and how diagnosing slow queries?
Yes I do! Alot are on my channel kindly check, I also provide commercial sessions
Thanks a lot for sharing this video, it's very informative and helped me understand many concepts.
Right now, I am looking at a complex query plan which is timing out(query) pretty often. That execution plan has many operations like Index seek (NonClusered), Clustered Index Scan, Clustered Index Seek etc. Please explain these terms as well. These are quite confusing when reading the definition of these.
I provide 1 to 1 session which comes up with a fee to clear your all doubts, if you are interested kindly reach me on yogesh.mehla@gmail.com
thanks is amazing
Welcome 😊
Exceptional presentation!! Much appreciated!! :o)
Thank you kindly!
Note:
I also provide commercial support. Please contact if needed.
If you find this solution helpful than donate comfortable amount on papal to my email ID or link www.paypal.me/bimlamehla.
Donation is not mandatory, it is just a request to support free tutorials.
AWESOME 👌
Thanks 🤗
outstanding Sir
Many thanks
good explanation..
Thanks and welcome
Hello. Thanks for the video. What exactly 'Double Aggregation' means? I mean I only see one aggregation query which is MAX in first query and also in the second. Can you please explain?
it is duplicate aggregation, not double aggregation. Kindly re refer video again
Very very nice
Thanks a lot
well explained.
Glad you found it helpful.
good information...
Thanks
thx you sir very nice video
Most welcome
While
optimizing T-SQL Queries, in the logical query execution plan, what are the top
3 operators which we should eliminate / optimize?
Lazy spool one and only other are more dependent on situation
I am trying to reproduce the same steps in my machine. I am not able to find Student table under Northwind database. Can you please where I can find the scripts for that? This video is starting to be very useful for me.
Hi Yogesh, at one point you mention "group before, not after" what do yuo mean by that?
I am confused about reference, if you want to discuss in detail, kindly reach me on skype yogesh.mehla@live.com
how to avoid key lookup when PK_clusteredIndex seek is in the exec. plan? I know if, non clustered on exec. plan, I can add those columns to the index but what to do with key lookup and PK_seek?
lookup are tricky to fix, depends on your instinct to fix them as they can cause more locks for insert delete and update if you add more columns
Hello Sir great video just had a small doubt how would the performance be if we used last value for the example problem for minimizing sub queries
single query will not have major impact, its important to have most of the queries to be optimized to ensure good performance
excellent
Many many thanks
Hash Join is not always bad, especially dealing with very large tables.
There is no term like always bad. It depends, if you are joining complex views, you may not be able to avoid hash join. So SQL decision is good, we have to form are query accordingly.
@@techsapphire agree
short and sweet. Well explained brother. Would you mind sending me the presentation.
Kindly share your email
Nice one
Thanks for watching
AWESOME
Thank you! Cheers!
Very nice explanation.One of the best.Could you please share the scripts also..
Please share your email
What version is the NorthWind database? Mine doesn't have the 'Student' column. Would you please paste the URL here to get the same Database you have on your presentation?
follow link in description for database backup
@@techsapphire Where is the link?
nice video sir
Thanks and welcome
can i get this database to practice the questions
Hi Yogesh! Thank you for the lecture; it was very educative, can you please share me the script and ppt presentation . Also do we need to SET STATISTICS IO OFF after the query optimization project? Thank you.
Sure, please share your email. You don't need to set it off as it is session level. So moment you close query window, it is gone
Hi yogesh, thanks you for the lecture. It was very educative. Please share the ppt, quries, and database back used in the video. My email id is krishanajangid2011@gmail.com.
@@techsapphire have you send to damukatabathina@gmail.com also...thanks
@@katabathinadamodar732 if u got script, would u, please share with me?
@@krishanajangid1763 if u got script, would u, please share with me?
Thanks so much for the great video!
Where can I get the slide and the script?
Kindly share your email for it.
techsapphire : ardianumam@gmail.com. Thanks in advance.
Sir,
I need this script and requesting you to forward sheiknainar@yahoo.co.in
really like this video........may get any suggestions currently, i working for my thesis on the topic of RESEARCH ON AI APPLICATION FOR OPTIMIZATION OF THE SQL QUERIES..
Is your research only SQL Server or other databases as well?
Hi sir,
It was nice video. I have one doubt while explaining you're telling everytime about sql server but I'm not using sql server in my project I'm using sql developer tool and I'm working as sql developer, So it is same for all ?
Please reply me.
Yes, correct. Performance optimization using query tuning is mainly job of SQL Dev not DBA
I have a table with more than 100 columns, so how many indexes can create for a single table?
there is no hard rule for it, depends upon you infrastructure and also sql version. Enterprise can go manage crazy load of indexes. Index should be created sensibly.
Is this query tuning applicable to oracle database as well?
yes query mistakes are common
Great content, as you mentioned it’s one out of two parts, could you please comment the second video link here
Post this video, there are many videos on how to capture expensive queries etc. You can check
Please share download database link, which you are using in this