The 201 Buckets Problem, Part 1: Why You Still Don’t Get Accurate Estimates


I’ll start with the smallest Stack Overflow 2010 database and set up an index on Location:

USE StackOverflow2010 GO CREATE INDEX Location ON dbo.Users(Location); GO SELECT COUNT(*) FROM dbo.Users; GO

There are about 300,000 Users – not a lot, but enough that it will start to give SQL Server some estimation problems:

When you create an index, SQL Server automatically creates a statistic with the same name. A statistic is one 8KB page with metadata about the object’s contents. In this case, it’s a list of up to 201 Location values, and the number of people who live in each