How To Evaluate Index Effectiveness While Tuning SQL Server Queries
Вставка
- Опубліковано 26 лис 2024
- Become a member! / @erikdarlingdata If you like what you see here, you'll love my advanced performance tuning training:
training.erikd... Click here for 50% off a health check: training.erikd... - Наука та технологія
I knew this from a great book about indexes, which probably three people in the world have read; one of them was the author. But as it goes, since I am not 20 anymore, I need to repeat stuff I learn. So, this was super great seeing this in a video! Thank you so much. I probably will make a blog post about that, so that this life-altering information can be read in a book, online, OR seen on this channel...
Make sure to link to your post here. I’d love to check it out.
Thanks for recording all of these videos today Erik - I have loved your stuff for years.
3000 subs = "screw it long lunch"
Hahaha, thanks, it was quite a hectic day! Glad you're enjoying them.
Query: 1 = 1
SQL Server: *yawn*
Query: 1 = (SELECT 1)
SQL Server: *takes out graphing calculator, protractors, rulers, and a collection of pencils*
Hahaha, yeah, it’s amazing what confounds a giant calculator
Quite frankly, I'm puzzled. I understand using 1 = (SELECT 1) as the first placeholder in dynamic SQL; that makes sense. But why include it here at the end? This approach wasn't covered in my book on indexes
@@fpost337 if you typed that into a search engine instead, you may come across a post like this: erikdarling.com/whats-the-point-of-1-select-1/
Erik, great video and very useful, however, I have StackOverflow2013, after creating the inde p, ran the 3 queries, in the execution plan I have in one line the 3 elements you show but above them I have another line with 2 elements "Filter" and "Constant scan" then both lines get joined towards "Nested Loops(Inner Join". So should we have a specifique parallelism config? my sql is in a VM on Hyper-V. Thanks again for making all those videos for the ciommunity
I’m not sure that’s going to be because of parallelism settings, but I have DOP set to 8 and Threshold set to 50.
Why filter on dates at all when the selected date range does not narrow down the results? Of course you get delay without better results. Where clauses should be useful or left out. If the application is not requesting a specific date range, the query should have no date-specific where clauses (or where clauses that evaluate to true).
It appears that the whole video can be summarized by a simple heuristic: If an index is on more than one column, order the columns in descending significance.
Hey Frank, the point is that you can’t control what users will search for. Some may search for very inclusive things to get a wide data set. Thanks!