Link Up
This post is links to a series of articles that it’s a shame more people haven’t seen, because they describe why some index builds/rebuilds take a lot longer than others.
…Category: Indexing
Steps For Getting Rid Of NOLOCK Hints
Way Out
Whenever I see people using NOLOCK hints, I try to point out that they’re not a great idea, for various reasons explained in detail all across the internet.
At minimum, I want them to understand that the hint …
Why Single Key Column Indexes Aren’t Good Choices
Choose To Lose
Most tables you encounter in SQL Server will have a clustered row store index on them, and probably some nonclustered row store indexes too.
If you live in the data warehouse world, you’ll probably see more and …
I/O Bound vs CPU Bound Queries
Handcuffed
When you’re looking for queries to tune, it’s important to understand which part is causing the slowdown.
That’s why Actual Execution plans are so valuable in newer versions of SQL Server and SSMS. Getting to see operator timing and …
Indexes And Data Access Patterns
Financial Advice
When I talk about “data access patterns” in relation to databases, it’s often met with blank stares.
What is a data access pattern? What does it have to do with indexes?
Well, my dear friend, today you’re gonna …
Why Comparing Columns Doesn’t Always Seek
Adjoining
When writing queries, sometimes you have the pleasure of being able to pass a literal value, parameter, or scalar expression as a predicate.
With a suitable index in place, any one of them can seek appropriately to the row(s) …
How SQL Server 2019 Helps You Link Queries To Missing Index Requests
Not Another Upgrade
When dm_db_missing_index_group_stats_query got documented, I was really happy. After all, this has been a peeve of mine for ages.
“Wow look at all these missing index requests. Where’d they come from?”
So this is neat! And it’s …
Spools Are Just Crappy Temp Tables
But My Tempdb
Using the scenario from yesterday’s post as an example of why you might want to think about rewriting queries with Table Spools in them to use temp tables instead, look how the optimizer chooses a plan with …
Multiple Distinct Aggregates: Still Harmful (Without Batch Mode)
Growler
Well over 500 years ago, Paul White wrote an article about distinct aggregates. Considering how often I see it while working with clients, and that Microsoft created column store indexes and batch mode rather than allow for hash …
Compressed Indexes And The Buffer Pool
Mail Drag
After my smash hit double diamond post about index tuning, I got a question questioning my assertion that compressed indexes are also compressed in the buffer pool.
Well, this should be quick. A quick question. Eighty hours …