Statistics

SQL Server uses statistics to guess how many rows will match what your query is looking for. When it guesses too low, your queries will perform poorly because they won’t get enough memory or CPU resources. When it guesses too high, SQL Server will allocate too much memory and your Page Life Expectancy (PLE) will…

When I’m reviewing plans with folks, I get this response a lot: But SQL Server should know there’s no data that matches! It has statistics! Statistics are guideposts, not guarantees. We’ll start with the Stack Overflow database and put an index on LastAccessDate, which also creates a statistic on that column. We’ll check the min…

When you run a query, how does SQL Server estimate the number of rows? I’ll explain in this half-hour video demo using playing cards, then showing the same issues in SQL Server Management Studio: Here’s the demo script I use in the video. To learn more, check out our free statistics class on YouTube.

In the last post, I talked about how we don’t get accurate estimates because SQL Server’s statistics only have up to 201 buckets in the histogram. It didn’t matter much in that post, though, because we were using the small StackOverflow2010 database. But what happens as our data grows? Let’s move to a newer Stack…

I’ll start with the smallest Stack Overflow 2010 database and set up an index on Location: Transact-SQL USE StackOverflow2010 GO CREATE INDEX Location ON dbo.Users(Location); GO SELECT COUNT(*) FROM dbo.Users; GO 123456 USE StackOverflow2010GOCREATE INDEX Location ON dbo.Users(Location);GOSELECT COUNT(*) FROM dbo.Users;GO There are about 300,000 Users – not a lot, but enough that it will…