Watch Out Now
We talked about a bunch of index stuff this week, and we still barely scratched the surface. That’s okay, though. If you try to cram it all in at once, you’ll get overwhelmed and forget half of it anyway.
If you want to jump ahead to some more advanced topics, check out my presentation about indexes here.
You probably have questions about indexes that I didn’t cover here. Feel free to ask them in the comments section.
The core concepts I want you to take away from this week are:
- Indexes put data in order
- That order is what makes searching and joining more efficient
- Having data in order can also help other parts of the query
- Putting data in order without an index requires memory, sometimes a lot
Frog Meditation
If you’re wondering why I didn’t talk at all about index fragmentation, it’s because it’s not an effective query or index tuning strategy. I see far too many people set up some scripts to “fix” it, and then walk away from real tuning work.
I don’t get it, the indexes aren’t fragmented. Why is my query still slow?
Leaving aside that index maintenance scripts all measure the wrong thing, hammering your server night after night with pointless maintenance doesn’t fix the root cause of why performance is bad.
It doesn’t fix problems with functions, sargability, table variables, local variables, or anything else.
Sure, you can say your query got 100ms faster because you got pages back in order, but who cares about that when it still runs for 5-10 seconds, or longer?
Riding Through
The next couple things we’re going to cover this month are wait stats and query plans.
After that, who knows?
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.