Pinal, you are an SQL Wizard! Thank you for the continuous SQL video learning series . Great work. You are my first reference when I have to troubleshoot my SQL Queries performance .
Well presented Pinal. SQLAuthority.com has been a generous source of information and a rock solid point to fallback when we have a tuff SQL times. Big fan
It's evident that all three queries are good in the context of I/O, CPU, and plan percentage. Sometimes we won't get all the good measures in a single query and for that reason, we say it as performance 'tuning'.
A real eye opener Pinal! Thank you! I always thought that I/O would automatically improve when the execution plan is optimized. I've relied on your articles a lot over the years. Thank you!
Thanks Pinal, I've definitely picked up a lot from you over the last 10-12 years, but still not a sql expert as I spend a little more time in frontend or backend web code. Every time I watch one your videos or read a blog post, I always come away finding out how much I don't know. Good stuff as always.
Thank you for a real example. This often happens to me since there are several developers and each tries it's own method. Usually have IO issues so I'm going to check the Rank option.
I really watched your video first time. really a fan of your Craft Video amazing. before that, i have read your articles much time but I really like video learning. i really follow in my start Kudvancat and Shiv Prasad and now in SQL you are the master and your way of presenting is amazing.
Great video! Very informative. Your blog was a great source of information many years ago for me when I had to deal with a super complex DB System at my old work. Keep up the great work! My gut feeling was right, based on query complexity TOP(1) combined with UNION.
You totally rock! The nice thing about your videos....the average programmer-wannabe should be able to understand them. If they don't, banish them to the SQL Blackhole!
Well done. The format of this vide is great and the real example is helpful and realistic. Your discussion of what constitutes "efficiency" is well explained. Thanks Pinal Great job
Your videos are great, informative and entertaining! I had guessed option 2, I have seen good performance from TOP operator like that in a number of cases.
I had guessed the third query but had failed to realize that we were then using the results of the aggregate to interrogate the table a second time. One key point to remember is if the query might be executed multiple times. Sometimes, the optimizer can cache the results. This is particularly important when the query may be processed for multiple requests. This might occur on a website for example. I always like to understand the usage that will happen, so I can test what happens if the query is executed with different filter criteria for multiple requests (or maybe the same...). I have found that some compatible queries might be slower for the first execution, but subsequent queries cause the original (slower query) to be faster. (A key reason to use procedures/functions and make them deterministic...[Will not execute multiple times if passed the same parms.)
Another great video Pinal. I didn't even guess the most efficient query as I thought it would depend on what kind of efficiency :) Very informative. Thanks.
Great video! I thought that cost is related to time and IO. I'm now at a loss as to what exactly the query cost is for. Can you explain what the benefit of an optimal execution plan (i.e. query cost) is if IO and time are worse in the first query? Meaning, what is SQL server using to calculate the query cost and what benefit do I have from using the query with the best execution plan?
Clustered indexes are good to avoid forwarded records. Watch this video blog.sqlauthority.com/2021/02/15/forwarded-records-and-performance-sql-in-sixty-seconds-155/
@Pinal, I am a bit confused, can you please clarify again here? Are you telling that the performance check depends on the client's needs? either he is interested on IO or Time, developer need to check with him on it?.
Mr. Bond you are savior. Really useful info...could you please also know in terms of query efficiency what metric stand out the most IO read or Elapsed Time taken query to execute. Shouldn't execution plan give result based on IO read and Elapsed Time?
Nice video, Please make a video on how to write a query interms of Excecution plan, CPU Usage and Time. Is using subquery reduces the performance ?, Joins vs using subqueries. Is using IN clause makes query slower ? If yes any other alternatives ?
Thanks for simplifying the things for better understanding to everyone. I think, the "Scan count" in IO Stats didn't considered in this video, is that by intention?
That analysis you can do via SQL Wait Statistics. If you have subscribed to SQLAuthority.com newsletter, you may have received the script. The link is in the video's blog.
Hi Pinal, I am big fan of your SQL in sizty seconds short videos series..I am desparately looking for a video from your side about 2 scenarios which is mostly asked in Interviews :
Awesome sir, ur like SQL encyclopaedia.. I just had a chance to watch ur presentation to craft … It’s really surprised me … Can you please do videos on performance tunings in procs & Indexes … Thanks a lot in advance 🙏🙏🙏
You can create an index on any column, you should first check the performance of your query with the methods explained in the video and decide if the index is good or bad.
Hi PinalDev, Thanks for your explanation. can you please explain how the 2nd query works faster when the io operation takes more time than 3rd query. Are io operation and executive time not directly proportional??
Hello sir I have one sql stored procedure and it is almost taking 3 minutes to get 75K records. Can we do improvement this SP? Please provide your inputs.
Hi Pinal, Great Insight, and I just want to share my thought process in selecting 2 nd query as my answer before watching the whole video I thought aggregation and Window introduce sorting which is extra work to perform, so eliminated those two and opted for 2.
In the second query why not just skip the subquery and UNION the TOP 1's? Wouldn't that perform best? Also, would EXISTS improve the performance or execution plans in this? I've never thought of writing a query like that to get the top/bottom result. I'll have to keep this in mind.
Pinal Dave is truly remarkable for his deep expertise and practical insights in SQL Server.
Thanks for your kind words.
Have got immense help from you Pinal for the last 15 years.. You are an institution. Take my sincere regards.
Swapan, you made my day... thank you so so much!!
Pinal, you are an SQL Wizard! Thank you for the continuous SQL video learning series . Great work. You are my first reference when I have to troubleshoot my SQL Queries performance .
Thanks so much for your note. I am very motivated.
Just got this video from suggestion, and my conclusion is u are superb sql man..
Thank you for your kind note.
This is Gold! Thank you! It would show the most efficient query, even the query itself is written very inefficiently from a solution point of view!
Thanks for the kind comment
I am really happy this SqL Authority blog is now on videos. Than reading the blogs, it's more clear Pinal. Thank you so much.
You are most welcome
Last several yours we are reading your articles.. you are doing asume work .... keep it up please
Thanks a ton
Well presented Pinal.
SQLAuthority.com has been a generous source of information and a rock solid point to fallback when we have a tuff SQL times.
Big fan
Thanks for watching.
Never seen anyone explaining sql with so much of excitement! Thank you Pinal Dave!!
You are welcome
It's evident that all three queries are good in the context of I/O, CPU, and plan percentage. Sometimes we won't get all the good measures in a single query and for that reason, we say it as performance 'tuning'.
Fair point!
Thats why you are genius sir, and because of this many of us following you though have other bloggers
Thanks.
Day by day I am learning a lot from you sir. Thank you so much for teaching all of us. God bless you.
It's my pleasure
The web is full of answers to my SQL questions. Your website is my "go-to" default getting the right answer.
Thank you so much!
Really helpful video for SQL query performance PoV. Thanks a ton SQL genious Pinal
Thanks
I am your fan for many years. Thank you and god bless you. I wish I could give you one thousand likes.
Kindest comment ever.🙏
A real eye opener Pinal! Thank you! I always thought that I/O would automatically improve when the execution plan is optimized. I've relied on your articles a lot over the years. Thank you!
Thanks!
Brilliant article! STATISTICS TIME AND IO are my new best friends :-)
Thanks!
I am your fan brother and following you from more than 10 year on your blog.. I learnt a lot from your blogs.. keep doing brother
Thanks a ton
Thanks Pinal, I've definitely picked up a lot from you over the last 10-12 years, but still not a sql expert as I spend a little more time in frontend or backend web code. Every time I watch one your videos or read a blog post, I always come away finding out how much I don't know. Good stuff as always.
Great to hear! You made my day!
Thank you for a real example. This often happens to me since there are several developers and each tries it's own method. Usually have IO issues so I'm going to check the Rank option.
Good
Hi..you are 60 secs videos are very helpful in our day-to-day work. Thanks for your continuous teaching.
Glad to hear that
your blogs are very help full for the DBAs great effort
Glad you think so!
Thanks, appreciate it. My first thought was #2 as that's normally the fastest (and the one I would use).
Glad it helped!
I really watched your video first time. really a fan of your Craft Video amazing. before that, i have read your articles much time but I really like video learning. i really follow in my start Kudvancat and Shiv Prasad and now in SQL you are the master and your way of presenting is amazing.
Thanks
Very productive video in 60 sec. I wonder if every valuable learning were at that time.
Thanks so much for kind note
Thanks Pinal, very clearly explained.
Glad it was helpful!
Good job! Thank you!
Actually clients want quick executing query, but as a developer I prefer query that takes low server resources.
Fair Point.
Great video! Very informative.
Your blog was a great source of information many years ago for me when I had to deal with a super complex DB System at my old work. Keep up the great work!
My gut feeling was right, based on query complexity TOP(1) combined with UNION.
Fantastic!
Brilliant!! Those three factors are has to be in considerations
Thanks!
bossman, you're a genius. thank you for this video.
Thanks!
You totally rock! The nice thing about your videos....the average programmer-wannabe should be able to understand them. If they don't, banish them to the SQL Blackhole!
Thanks a lot!!!!
Thank you for a very clear and informative video. Exactly what I needed.
Glad it was helpful!
Thanks pinal. It's very useful for me..
Glad it was helpful!
Again great video Sir. the query itself is written very efficiently and informative from a solution . Thanks
Thanks for watching this video
Thanks Pinal for your valuable series. Really learning a lot through these!!
Happy to hear that!
Good explanation thank you pinal Dave sir
So nice of you
Well done. The format of this vide is great and the real example is helpful and realistic. Your discussion of what constitutes "efficiency" is well explained. Thanks Pinal Great job
Glad you watched it and thanks a lot.
Great explanation. Keep it up and wait for another more than 60 second video. 👍👍
Thank you so much 😀
Your videos are great, informative and entertaining! I had guessed option 2, I have seen good performance from TOP operator like that in a number of cases.
Thank you so much for watching the video.
Wow..Nicely explained.
Thanks a lot sir🙏🙏
You are welcome
Thanks, Pinal for the clear explanation. Can you share the final query that you have rewritten the 3rd query using the 2nd method as u mentioned @5:18
Yes, sure
"SQL in the sixty seconds" Its Great😊Thank you
Thank you too!
I love this 60 sec idea. Wonderful!!!
Thank you so so much.
cool examples and explanation. thanks
Thanks for watching.
This is pure gold Pinal...Thanks
Thanks for watching
Thank you. Your videos are really helpful. Great work.
You are welcome.
I had guessed the third query but had failed to realize that we were then using the results of the aggregate to interrogate the table a second time.
One key point to remember is if the query might be executed multiple times. Sometimes, the optimizer can cache the results. This is particularly important when the query may be processed for multiple requests. This might occur on a website for example. I always like to understand the usage that will happen, so I can test what happens if the query is executed with different filter criteria for multiple requests (or maybe the same...). I have found that some compatible queries might be slower for the first execution, but subsequent queries cause the original (slower query) to be faster. (A key reason to use procedures/functions and make them deterministic...[Will not execute multiple times if passed the same parms.)
Thank you for your kind note.
Thank you - could you do a video on result sets and how it works in join conditions?
Sure thing!
Should be required viewing for all SQL developers (and half of the SQL Admins I've worked with).
Very very kind of you!
Thanks, I always enjoy your videos and they are interesting too.
Glad you like them!
Another great video Pinal. I didn't even guess the most efficient query as I thought it would depend on what kind of efficiency :) Very informative. Thanks.
Glad it was helpful!
Thanks Pinal for these wonderful videos.
Glad you like them!
Hi Pinal..List out the recommended videos in the discription as well..
Thanks🙏.. your videos are really helpful
Noted!
Hello Pinal
Could you please explain what do we need to check if we are to perform a DB health check? Also, How do I reduce the I/O bottle neck?
Many of the video talks about it. You can also follow sqlauthority.com where I write on the same topic.
Keep up the good work Pinal!
Thanks a lot!
Great nicely explained
Glad it was helpful!
Thank you Pinal for sharing.
You're welcome
You are an SQL king!
Very kind of you!
Great video!
I thought that cost is related to time and IO. I'm now at a loss as to what exactly the query cost is for. Can you explain what the benefit of an optimal execution plan (i.e. query cost) is if IO and time are worse in the first query? Meaning, what is SQL server using to calculate the query cost and what benefit do I have from using the query with the best execution plan?
Yeah they are not linearly connected. I will create future video on this topic.
@@PinalDaveSQLAuthority I was wondering when you might do this video. I really would like to understand this. Thanks
@@mosheritterman3472 I usually cover this in my training so I will have to carve out this one from it. I will plan it very soon.
Real internet Gold! :) Thanks Pinal!
My pleasure!
Could you please create such video for Azure Data warehouse(Dedicated SQL Pool) . There optimization concept varies from normal sql database
ok
Sir can you have an example of Indexing. (e.g. When to use clustered and non-clustered index?) and also the best practice on how to apply it.
Clustered indexes are good to avoid forwarded records. Watch this video blog.sqlauthority.com/2021/02/15/forwarded-records-and-performance-sql-in-sixty-seconds-155/
Nice sir wounded full explanation
Thanks for watching
nice video.!! thanks
can you do more video for parameter sniffing ? what is parameter sniffing and how to handle it...
Sure great point...
"SQL in the sixty seconds" I love it!! 😊Thanks, Pinal!
Lots of conversation around the or not the :-)
It will be helpful if you can explain with reason difference in performance for each query.
Lots of reason, I wish I was able to explain in just few minutes.
I am quite new to SQL. If I want to know more about what is page reads and how Statistics work then any guide ?
I have blogged about this concepts at SQLAuthority.com
This was a great video... excelent... thanks master Jedi!
Glad you enjoyed it!
@@PinalDaveSQLAuthority : i want you to be my master jedi, and i can become your padawan learner
what do you say?
😃
@@EBlancovitch Thanks for your kind note.
Thanks you for giving such information..
You are welcome
@@PinalDaveSQLAuthority we met at Orange techolab.. may be you remember
@Pinal, I am a bit confused, can you please clarify again here?
Are you telling that the performance check depends on the client's needs? either he is interested on IO or Time, developer need to check with him on it?.
Yeah, I often check with the clients.
Thanks once more, for sharing this knowledge
My pleasure
Thankyou, very nice session
Glad you liked it
Mr. Bond you are savior. Really useful info...could you please also know in terms of query efficiency what metric stand out the most IO read or Elapsed Time taken query to execute. Shouldn't execution plan give result based on IO read and Elapsed Time?
Execution plan has different purpose, it shows how the query is executed.
Nice video, Please make a video on how to write a query interms of Excecution plan, CPU Usage and Time. Is using subquery reduces the performance ?, Joins vs using subqueries. Is using IN clause makes query slower ? If yes any other alternatives ?
Check other videos on this channel. Many of the topics are already covered..
Thanks for simplifying the things for better understanding to everyone. I think, the "Scan count" in IO Stats didn't considered in this video, is that by intention?
Yeah, because they do not play role as intense as page read.
Excellent sir👍
Keep watching
Thank you for all this. But how do we know there is a problem with the execution plan or IO or execution time.
That analysis you can do via SQL Wait Statistics. If you have subscribed to SQLAuthority.com newsletter, you may have received the script. The link is in the video's blog.
Great video!!!
Glad you liked it!
All i can say is amazing.
Thanks
Hi Pinal, I am big fan of your SQL in sizty seconds short videos series..I am desparately looking for a video from your side about 2 scenarios which is mostly asked in Interviews :
Noted
Thank you sir, very well explained.
Sir can you please create a course for Performance tuning and optimization.
Yes, soon
Awesome sir, ur like SQL encyclopaedia.. I just had a chance to watch ur presentation to craft … It’s really surprised me … Can you please do videos on performance tunings in procs & Indexes … Thanks a lot in advance 🙏🙏🙏
Thanks and welcome
Can we create index for timestamp column? If Data is continuously insert into database with timestamp column.
You can create an index on any column, you should first check the performance of your query with the methods explained in the video and decide if the index is good or bad.
@@PinalDaveSQLAuthority thankyou for your response
Thanks for the video. Great job!
Thanks 😊
Always great content and delivered with a smile. Thanks for making SQL more interesting for us all.
Thanks so much for kind comment.
Hi PinalDev,
Thanks for your explanation.
can you please explain how the 2nd query works faster when the io operation takes more time than 3rd query. Are io operation and executive time not directly proportional??
They are not directly proportional. There are many other aspects as well when it is about query performance.
@@PinalDaveSQLAuthority thanks , I am expecting those things in the up coming videos 😊
can you share same things with huge data, indexes, and caching
Please explain bit more.
Very best Sir
Thanks!
Great video. Do you have any URL or UDEMY course about SQL Tuning on SQL Server and SQL Server Instance-Server Tuning also? Please let me know.
I currently do not have course on udemy but it will good idea .
Efficiency of Execution plan versus efficiency on time how is this different? Could you pls explain
They are different parameters. You should tune query for the one which is the most appropriate.
@@PinalDaveSQLAuthority I mean, isn't that the best execution plan will ensure quick response?
@@anantharamakrishnan19 no, it is not true. That is what this video is talking about.
I believe Window Function, because I believe it only requires one table scan.
Thanks!
You are great !
Thanks for watching!
Hello Pinal how can we get the info of deadlock situation happened in the past
Past details are hard to get. You can configure extended events for the future events.
I am oracle dba but I like your videos :)
Thanks
Wow..great video...thanks!!
You are welcome
Hi Do you have any idea about OBIEE regarding istallation
Sorry , no idea
Please can u explain query optimization
I have done already few videos on this topic. I will create more as I go.
Hello sir I have one sql stored procedure and it is almost taking 3 minutes to get 75K records. Can we do improvement this SP? Please provide your inputs.
This requires deeper consulting engagement.
Hi Pinal, Great Insight, and I just want to share my thought process in selecting 2 nd query as my answer before watching the whole video
I thought aggregation and Window introduce sorting which is extra work to perform, so eliminated those two and opted for 2.
Thanks for sharing!
In the second query why not just skip the subquery and UNION the TOP 1's? Wouldn't that perform best? Also, would EXISTS improve the performance or execution plans in this?
I've never thought of writing a query like that to get the top/bottom result. I'll have to keep this in mind.
Absolutely, that can be done too! However, it will not impact performance, which I have tried before. Thanks for your kind comment.