6 Query Tuning Techniques - Solve 75% SQL Server Performance Problems (by Amit Bansal)
HTML-код
- Опубликовано: 8 фев 2025
- SQL Server Performance Tuning Master Class is happening again. Learn more. bit.ly/SQLMaes...
Want Amit Bansal to deliver a private batch for your team members? Drop an email to contact@sqlmaestros.com
Want access to Master Class recordings for a lifetime? Explore here: bit.ly/SQLM_MCR
Amit Bansal frequently delivers free webinars for the SQL community. Learn more: bit.ly/SQLMaes...
Join the SQLMaestros WhtsApp Community: chat.whatsapp....
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
Very nice content....8:10 Non Sargability , 2nd Not using Index 18:40 Implicit Conversion 29:30 DPS 38:00, Bookmark lookup 56:00, Parameter sniffing 1:12:11 (Community service ;-D)
not all heroes wear a cape... thank you!
DPS was a life and career changing event for me and helped me to leap towards my great career journey as a SQL Developer. DPS indeed instilled me the confidence that I could also become a SQLMaestros with a different work experience altogether. Your videos are all worth watching, and missed attending those live sessions. Hope to join those sessions personally soon !
You are amazing Amit and please continue the great work and continue to transform our lives ! May God continue to bless you and your upcoming sessions.
Great video. Keep doing these. I would love to see how you simulate a situation where tempdb is full and a query is still running
VERY clear and excellent explanation i use all the queries on my ssms and it worked correctly thanks very much from Texas USA
Nice sir so important topic I'm new to sql server tuning performance though almost i stared used sql for 5months.
Another hidden performance trick is to be careful about using NVARCHAR and VARCHAR interchangeably. I've had instances where query performance was improved by over 80% by simply casting nvarchars to varchar or varchar to nvarchar in the underlying data or subqueries. More often than not, converting early and staying consistent with data types can improve performance. Don't discount the cost of casting between types.
It was clear and concise explanation. I understood it completely. Thanks for this video.
"Not *your* performance problems, your *SQL Server* performance problems."
Bruh. Why you gotta do me like that? Hehe. Cheers. These videos are excellent.
Really nice explanation on Tunning SQL by Amit
This course is brilliant. Thank you sir very much. Greetings from germany
Really appreciate your contents which is quite Informative. Keep up the good work in future also. Thanks Buddy.
Excellent explanation
this is a good example
You are just Amazing ...!!! Kudossssssssssss
Excellent explanation Keep it up!!
thanks for useful information.....keep post about sql server
very helpful SQLMaestros, Thanks again
Very detailed explanation in a very simple way.
Very nice. I've got answers for many of my questions..
Amit, this is great... Time to build out a zero to hero course :-).... Id help and Id take it after haha. Thank you sir.
Can you please explain how to use query store with example this very helpful to everyone
excellent explanation, great contribution, thank you!
Very nice looking for this from long time
Awesome details covered Thankyou.
Very very Very informative. Thanks a lot. Keep posting some ticks and tips also as a short videos.
specifically liked the implicit/explicit conversions improving performance
Thank you it's new to learn
Super session. Great thanks.
Thank you for this very useful video!
Superb explanation.. magical video
Thanks 🙏
Very useful! Thanks for the video
Very good content. Exceptional way to explain topic
Thank you!
Very useful information, thanks for sharing the video
Awesome very clearly explained
Really informative with all possibilities
Sir for recompile, I have a question
At the same time procedure run if another request comes at that time what will happen?
Is error occurred?
at 11:25 convert...='01/01/2020' is not the same as 14:23's in where clause , here ">=" is being used instead of "=", then why are you saying that the output of the query will be the same ?
Great information thank you
Sir it's really nice explanation 👌
Thanks
While optimizing T-SQL Queries, in the logical query execution plan, what are the top 3 operators which we should eliminate / optimize?
Very informative. Thank you very much!
Nice sir very helpful for me 👍
How to improve performance of a view where only joins are used without any 'WHERE' available.
Great video Amit. However, Pinal Deve says index has nothing to do with performance, index is the culprit, delete the indexes if you want to improve performance. Now, I'm confused, two SMEs say two different things. Correct me if I'm wrong
Do your own testing, Mr Lazy
Index has nothing to do with performance? The entire point of indexes is performance. Granted, there are scenarios where indexes can hurt performance, but just blankly saying "index has nothing to do with performance" is just wrong.
video is really helpful.but need a guidance , i came across a bug in sql query , which returns incorrect records 1 in 1000 records. how to tackle such issues, request you please make a video of such scenarios.
Yes really useful section all the SQL server developers
Thanks for sharing knowledge
In the second case: if you are looking for first name "ken" with the index last name+first name+middle name what would be the solution without creating a new index to be a seek and not scan?
Can anyone explain why 'INCLUDE' is being used to create the index?
At: 9:30 mins, Line 16-18
TIA!
Very toot content very very helpful
Thanks sir
amazing tips
Very helpful!
In the first example why didn’t you just remove the conversion and put the modified date in the same format as your solution ‘yyyy/mm/DD’?
Excellent
any idea on how to tune table where column need like '%%'
It was awesome
Thanks I gained some knowlege
At 54:58, the result of 4th problem's fix gives me same result as with implicit parameterization. Can anyone help me with the reason?
Can you share where to find the database to test myself? I found AD2016 but it has TransactionHisotry table!
I have one query that take too much time.
here is the explanation that I make
-> there are 300 columns in one table.
-> I want to filter on all 300 column with like operator i.e. column_name like '%[search term]%'
can you help to improve this query
Use reverse(column) and use xxxx% instead of %xxxx
Hi Amit, Great Explanation... But i couldn't understand why you change the Business Logic to make SQL Server Faster.? For Example; Take Seek, you change the Business logic to show us what makes scan and seek, good till that... But how to make the Scan converted to Seek without changing the Business Logic.?
He didn't change the business logic. I believe you're talking about what he says at 13:50 (section starts at 11:25) - what he's saying is if you are changing your query to improve the performance, you must make sure that your new query is logically the same - that means, your modified query must always still return exactly the same result that the old query did. Otherwise if you don't, then if this query was a part of some application, then your change has just introduced a bug where the system isn't returning the correct results any more.
In his example, he is querying a DateTime column for records on a particular date (i.e. from midnight to midnight - the 24 hour period of that date). In the first (bad) query, he achieves this in his WHERE clause by converting the DateTime value to a string which does not include the time component, and compares it to a string of the date he wants to get results for. In the fixed query, the new WHERE clause is selecting rows where the value is >= the date he wants results for AND < the following day.
The improvement is that he's not converting the column to a string, instead he's keeping the data as a DateTime as it already was. The result is that the query will return results for that entire day, from midnight to midnight. Meaning, his new query is logically the same and returns exactly the same results - that's the part where he says to make sure your modified query is logically the same.
In the improved query, now SQL Server is able to use the index to find the data much faster (seek - skipping only to the relevant rows in the table) because it isn't doing the string conversion. In the old query it would need to do a scan - it would need to convert the DateTime to a string for every row in the table before it is able to do the string comparison to get the results.
Yes for 38:20
is there any scenario where indexes will reduce the performance? please help me out from this
The more indexes you have on a table, the slower INSERT commands will be since after every insert, EVERY index will be updated.
That scales really badly if you have a big table with various columns with too many indexes, not to mention it will consume more disk storage.
Indexes trade insert performance in favor of search performance but its usually not an issue if you use indexes in a smart way (IE not having an index for unused columns and such)
@@pizzaiolom thanks a lot
Whats the impact of explicit conversions on sargability?
I want to join performance tuning class? Can any body pls help me?
Please drop an email to contact@sqlmaestros.com
Hi Amit, when i execute the below query getting error
select q.query_hash,
q.number_of_entries,
t.text as sample_query,
p.query_plan as sample_plan
from (select top 20 query_hash,
count(*) as number_of_entries,
min(sql_handle) as sample_sql_handle,
min(plan_handle) as sample_sql_handle
from sys.dm_exec_query_stats
group by query_hash
having count(*) > 1
order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as
t
cross apply sys.dm_exec_query_plan(q.sample_sql_handle)
as p
Msg 8156, Level 16, State 1, Line 32
The column 'sample_sql_handle' was specified multiple times for 'q'.
Please assist me
Problem is in min(plan_handle) as sample_sql_handle. Change it to: , min(plan_handle) as sample_plan_handle
good job :)
I have implemented Table Partitioning in Azure Sql Server and my table is working fine but sometimes It takes time to retrieve data like in 1 min it retrieves more than 1 lakh records and sometimes i get only 15000 .Please help me on this
28 min has been passed and still have focus ... Nice
My sql server occupying 95% of memory and hence system become slow. How can I fix it. It's a virtual machine and have 60 gb ram. Pls help me.
hi
Lots of useful content but skipped first 10 minutes or so...
This is very helpful. Thanks a lot!