When I was testing SQL Server 2025's new ability to limit TempDB usage with Resource Governor, I wrote a few fun diabolical demos. One of them was to generate giant spills to TempDB, quickly.
When you're looking at an actual (not estimated) query plan, and you see a yellow exclamation point on a sort operator, that means:
I'mma be honest with you, dear reader: this query exercise stems from a client problem that I couldn't figure out how to solve in the time constraints that I had, and it drove me crazy. I wanted to put hours into this to figure it out, and I was sure there'd be a fun T-SQL way to do it.
Personally identifiable information (PII) is freakin' everywhere.
When companies first start looking to identify and lock down their data, they think it's going to be as easy as identifying common columns like EmailAddress, DateOfBirth, SocialSecurityNumber, and so forth. They think, "We'll just encrypt those columns and we'll be fine."
Our prior Query Exercise introduced SQL Server's 201 buckets problem: its inability to accurately estimate rows for more than 201 outliers in a table. I followed up with a solution using filtered statistics to help with the next 200 outliers, and I talked about how that's really overkill for the simple problem we were facing in that initial challenge.
In this week's Query Exercise challenge, I explained SQL Server's 201 buckets problem. SQL Server's statistics only handle up to ~201 outliers, which means that outliers ~202-300 get wildly inaccurate estimates.
In our example, I had an index on Location and perfectly accurate statistics, but even still, this query gets bad estimates because Lithuania is in outliers ~202-300:
When you run a query, SQL Server needs to estimate the number of matching rows it'll find - so that it can decide which indexes to use, whether to go parallel, how much memory to grant, and more.
For example, take any Stack Overflow database, and let's say I have an index on Location, and I want to find the top-ranking users in Lithuania:
These two queries both get the same answer from the Stack Overflow database:
[crayon-6a6f0c78abb76794991086/]
Take any size of the Stack Overflow database and check out the WebsiteUrl column of the Users table:
Sometimes it's null, sometimes it's an empty string, sometimes it's populated but the URL isn't valid.
What are the best days of the week and times of the day to post a question at StackOverflow.com? It seems like a simple question, but it's surprisingly nuanced. I asked ChatGPT's latest version, 4o, and its answer made me laugh out loud. First off, the T-SQL is terrible: it creates a completely unnecessary temp…
For this week's Query Exercise, your challenge is to find out who keeps messing up the rows in the Users table.
Take any size version of the Stack Overflow database, and the Users table looks like this:
For this week's query exercise, let's start with a brief query to get a quick preview of what we're dealing with:
[crayon-6a6f0c78ac6c7038262930/]
That query has a few problems, but hold that thought for a moment. (You're going to have to solve those problems, but I just wanted to show you the sample data at first to give you a rough idea of what we're dealing with.)
For this week's Query Exercise, we're working with the Stack Overflow database, and our business users have asked us to find the new superstars. They're looking for the top 1000 users who were created in the last 90 days, who have a reputation higher than 50 points, from highest reputation to lowest. In your Stack…
Our developers have come to us with a problem query that isn't as fast as they'd like. Using any Stack Overflow database:
[crayon-6a6f0c78ad0b7607699152/]
It has an index, but SQL Server refuses to use that index in the execution plan:
If we force the index with a query hint, we do indeed get dramatically lower logical reads. In my particular database's case, the clustered index scan is 141,573 logical reads - but scanning the DisplayName index alone is just 38,641 logical reads.
Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It's even got an index to support it:
[crayon-6a6f0c78ad824152913853/]
You can test it with any version of the Stack Overflow database. To test it, we'll turn on a couple of tuning options:
[crayon-6a6f0c78ad829977676637/]
The actual execution plan does use our index - not just once, but twice:
For this week's Query Exercise, we're going to start with an existing query that has performance problems from time to time, depending on how it's used.
This query's goal is to find the top-voted Stack Overflow questions for any given tag - for example, here are the top-voted questions tagged "sql-server".
If we've gotta take the database down for maintenance - perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware - when's the best time to do it?
For this week's query exercise, the business has asked us to identify 3 1-hour periods with the least user activity, in this format:
For 2024, I'm trying something new: weekly homework challenges! For this week's query challenge, we're going to do some data validation. Pop open the Stack Overflow database and check out the CommentCount column on the Posts table: In theory, that's a fast way to check how many Comments a particular Post has, without querying the…
For 2024, I'm trying something new: weekly homework challenges! For this week, let's say we've decided to implement foreign keys, and we need to find data that's going to violate our desired keys.
We're going to use the Stack Overflow database, and we'll focus on these 3 tables:
