You’re comfortable writing queries to get the data you need.
But you're uncomfortable if someone asks you how it works, how SQL Server and Azure SQL DB turn your queries into results. You know there are execution plans, but ... you're uncomfortable reading them. You know queries need indexes, but you're not sure which columns to put in order, or how SQL Server chooses between them.
Not all of the questions y'all post at https://pollgab.com/room/brento require long-winded answers. Let's conquer 22 questions in 15 minutes!
https://www.youtube.com/watch?v=XGvqkIRwupk
I didn't say that - Guy Glantser did.
Guy Glantser is an Israeli SQL Server guru with a ton of great presentations on YouTube. I've had the privilege of hanging out with him in person a bunch of times over the year, and I'll always get excited to do it again. He's not just smart, but he's friendly and funny as hell.
An interesting question came in on PollGab. DBAmusing asked: If a query takes 5-7s to calculate the execution plan (then executes <500ms) if multiple SPIDS all submit that query (different param values) when there's no plan at start, does each SPID calc the execution plan, one after the other after waiting for the prior SPID…
This week's query exercise asked you to find two kinds of locations in the Stack Overflow database:
Locations populated with users who seem to be really helpful, meaning, they write really good answers
Locations where people seem to need the most help, meaning, they ask a lot of questions, but they do not seem to be answering those of their neighbors
To answer today's questions from https://pollgab.com/room/brento, I strapped on my Apple Vision Pro and recorded my persona as if we were on a FaceTime or Zoom call together. It's delightfully creepy, as if I'm AI Brent from the Future.
https://youtu.be/7GSgddpxHsE
SQL Server Management Studio 20 Preview 1 is out, and the new connection dialog has a big change:
When you click Connect, you're likely going to get an error:
New this month: better sp_BlitzIndex performance on databases with tens of thousands of objects, sp_DatabaseRestore can run test scripts, and David Wiseman and Sean Killeen implemented basic automated testing for the First Responder Kit.
To get the new version:
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-6a364b6247c12187432448/]
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.)
Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone's Stack Overflow database might be slightly different, we had to start by finding the "end date" for our query. I'm working with the 2018-06 export that I use in my training classes, so here's my end date:
You've got production databases in SQL Server or Azure SQL DB, and you want to make 'em faster. You need to identify the database's bottleneck, prove the root cause, and then recommend fixes. You want to make the right choice for each bottleneck - should you do index changes, query tuning, or server-level settings? The…
SQLBits 2024 is next month, and the session agenda is out, including the Microsoft-led sessions.
If you're going to get the boss to buy you an online or in-person ticket, it'll help if you have a specific list of sessions you wanna attend. Managers love it when you can point at each session and say, "Here's why this is going to be useful to the business."
Today's Office Hours comes from a very different office! I climbed into the hot tub to review your top-voted questions from https://pollgab.com/room/brento. Don't worry, the webcast is completely safe for work.
https://www.youtube.com/watch?v=e1jg1_uHBxM
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…
In last week's Query Exercise, our developers had a query that wasn't going as fast as they'd like:
[crayon-6a364b624ad87933379266/]
The query had an index, but SQL Server was refusing to use the index - even though the query would do way less logical reads if it used the index. You had 3 questions to answer:
For this month's TSQLTuesday, I asked y'all to describe the most recent issues you closed.
If you ask someone in IT, "What do you do for a living?" they struggle with job titles. They say things like:
Post your database questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me discuss on the stream. If you'd like to be alerted when I live stream these, follow me on Twitch.
https://youtu.be/JtJSecikVhk
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-6a364b624b9a2448287891/]
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 last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location:
[crayon-6a364b624c1eb145697196/]
And make it read less pages only by tuning the query? You weren't allowed to make index or server changes, and you weren't allowed to hard code the location in the query since it might change over time.
The Core of the Problem
The main problem is that when we run a statement (like SELECT), SQL Server:
For this month's T-SQL Tuesday, I asked y'all to write about the most recent ticket or issue that you closed. (If you want to see other peoples' posts, you can check out the comments on that invite post, or wait til next week and I'll publish a wrap-up of everyone's answers.)
A past client emailed me with a performance emergency. Things had been going just fine, and then out of nowhere, things suddenly slowed down.
