6 Query Tuning Techniques - Solve 75% SQL Server Performance Problems (by Amit Bansal)
HTML-код
- Опубликовано: 2 ноя 2020
- Looking for comprehensive, deep-dive training on SQL Server Performance Tuning? Explore SQLMaestros All-In-One Performance Tuning Bundle. 5 courses at a highly discounted price. bit.ly/sqlmaestrosallinone
The bundled content focuses on internals, troubleshooting, query tuning, performance optimization, tips, tricks, and loads of content from the real world. 
Наука
![Beginner to T-SQL [Full Course]](http://i.ytimg.com/vi/cACat4KNncg/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.
VERY clear and excellent explanation i use all the queries on my ssms and it worked correctly thanks very much from Texas USA
This course is brilliant. Thank you sir very much. Greetings from germany
It was clear and concise explanation. I understood it completely. Thanks for this video.
Very nice. I've got answers for many of my questions..
Really appreciate your contents which is quite Informative. Keep up the good work in future also. Thanks Buddy.
Very detailed explanation in a very simple way.
"Not *your* performance problems, your *SQL Server* performance problems."
Bruh. Why you gotta do me like that? Hehe. Cheers. These videos are excellent.
Really informative with all possibilities
Very informative. Thank you very much!
Very useful! Thanks for the video
Awesome details covered Thankyou.
Really nice explanation on Tunning SQL by Amit
excellent explanation, great contribution, thank you!
Very useful information, thanks for sharing the video
This is very helpful. Thanks a lot!
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.
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 very Very informative. Thanks a lot. Keep posting some ticks and tips also as a short videos.
Very good content. Exceptional way to explain topic
Superb explanation.. magical video
Excellent explanation
Very nice looking for this from long time
Sir it's really nice explanation 👌
Awesome very clearly explained
Thank you for this very useful video!
Excellent explanation Keep it up!!
Thank you it's new to learn
Thanks for sharing knowledge
Nice sir very helpful for me 👍
very helpful SQLMaestros, Thanks again
Super session. Great thanks.
You are just Amazing ...!!! Kudossssssssssss
Great information thank you
Thanks I gained some knowlege
Thank you very much
Amit, this is great... Time to build out a zero to hero course :-).... Id help and Id take it after haha. Thank you sir.
thanks for useful information.....keep post about sql server
Thank you!
Yes really useful section all the SQL server developers
Very helpful!
Can you please explain how to use query store with example this very helpful to everyone
Very toot content very very helpful
Thanks sir
Thanks
Thanks 🙏
this is a good example
While optimizing T-SQL Queries, in the logical query execution plan, what are the top 3 operators which we should eliminate / optimize?
Excellent
amazing tips
specifically liked the implicit/explicit conversions improving performance
It was awesome
good job :)
Whats the impact of explicit conversions on sargability?
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.
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 you share where to find the database to test myself? I found AD2016 but it has TransactionHisotry table!
Can anyone explain why 'INCLUDE' is being used to create the index?
At: 9:30 mins, Line 16-18
TIA!
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.
How to improve performance of a view where only joins are used without any 'WHERE' available.
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?
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’?
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.
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?
any idea on how to tune table where column need like '%%'
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 ?
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
28 min has been passed and still have focus ... Nice
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
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
Yes for 38:20
I want to join performance tuning class? Can any body pls help me?
Please drop an email to contact@sqlmaestros.com
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
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...