Index Tuning to Avoid Blocking

Looking for comprehensive, deep-dive training on SQL Server Performance Tuning? Explore SQLMaestros All-In-One Performance Tuning Bundle. Five courses at a highly discounted price. sqlmaestros.com/all-in-one-bu...
The bundled content focuses on internals, troubleshooting, query tuning, performance optimization, tips, tricks, and loads of content from the real world.
If you are a fan of live classes, you can explore the SQL Server Performance Tuning Master Class. 40 hours of deep-dive LIVE training. Attend LIVE & get the class recordings for a lifetime. sqlmaestros.com/sql-server-wo...

[@agusumriadi2264]

Thx mate, finally I found what I looking for 👏

[@krupapatel6713]

@8:53 - Clustered index needs to scan each data page on leaf node and fetch its matching record or not? Index Scan
@13:33 - Non cluster fix problem. Index seek. Find address line on leaf node and get corresponding data from base table

[@blackisblack22]

Thanks for the tip.

[@juancarlosvasquezgarcia2896]

excelent thanks !!!

[@saiabhilash3151]

Learnt a new thing sir .Thanks for the video

[@hd11_88]

Hi Amit, Always love the way you are explaining. How about enabling RCSI for the database? I'm pretty sure, you had thought of it but would you be kind to explain the limiting factors that stopped you to turn it on? Thanks.

[@rajeshbehera5730]

Nice explanation 😊

[@user-it8by6lf6w]

well explained Sir

[@ahmad-murery]

Very informative, and I really did learn something here,
Now, what if we're selecting the same rows we're updating, we'll hit a deadlock again right?
I'm saying that because I hit a deadlock in the past on a MariaDB sql server, the problem was very similar to what you've just demonstrated here but in my case I was extracting some data from a record while an update transaction still running on the same record.
the problem was not happening all the time and after a lot of time diagnosing/troubleshooting I finally found that in some scenarios the transaction was not committed.
Thanks Amit! please keep it up

[@MostafaElmasryDBA]

Thanks for your great video, but I have another case for locking sometimes we have Updated on table X and select on table Y and the select is locked by update😱 although both transactions are executing in different tables what is your advice on this case?