When Scans are Superior to Seeks

Brent Ozar shows that index seeks are not always better than index scans:

Somewhere along the way in your career, you were told that:
– Index seeks are quick, lightweight operations
– Table scans are ugly, slow operations

And ever since, you’ve kept an eye on your execution plans looking for those performance-intensive clustered index scans. When you see ’em, you go root ’em out, believing you’ve got a performance problem.

Thing is, … they lied to you. Seeks aren’t necessarily good, nor are scans necessarily bad. To straighten you out, we’re going to walk through a series of demos.

The rule of thumb I like to use is: if you need to go through more than 20% of the data, you’re generally better off scanning. If you need to go through less than 0.5% of the data, you’re generally better off seeking. Everything in between is the “it depends” zone.

Related Posts

Table Variables and Parallelism

Erik Darling shows off a neat trick for inserting with parallelism into a table variable: One of the many current downsides of @table variables is that modifying them inhibits parallelism, which is a problem #temp tables don’t have. While updating and deleting from @table variables is fairly rare (I’ve seen it, but not too often), you at minimum need an insert […]

Read More

Adaptive Joins and Index Width

Erik Darling wants to pump your indexes up: Now, there’s an Extended Event that… Used to work. These days it just stares blankly at me. But since I’ve worked with this before, I know the problem. It’s that Key Lookup — I’ll explain more in a minute. Adaptive joins won’t do all the work for you, […]

Read More

Categories

April 2019
MTWTFSS
« Mar May »
1234567
891011121314
15161718192021
22232425262728
2930