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:
I ran a poll over on LinkedIn to find out if people have 100% of their databases under some kind of source control or version control.
I broke the answers up into two sets, developers and non-developers, because I had a hunch that the developers' answers would be very different than the rest, and indeed they were:
I was supposed to be on a flight to Mexico, but had to cancel at the last minute. While waiting for my favorite bagel shop to open, I went through your professional development questions from https://pollgab.com/room/brento.
https://youtu.be/XeK7sE0Ch98
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:
Black Friday is coming.
Every November, we run a big sale on our training & apps. It's a really big deal for us - and for you. I like to think of it as a reward for my long-term readers who can hold out, make plans, and budget to buy at a specific time of year.
I took your top-voted questions from https://pollgab.com/room/brento, including a few career-oriented ones:
https://youtu.be/GefrKvKfG1Q
This Query Exercise was very different: I didn't ask you to solve a particular problem. I pointed out that I've heard advice that SELECT MAX is faster than SELECT TOP 1, and that's not quite true. I asked you to find factors that would cause these two queries to get different execution plans: [crayon-6a79dc0fba894591492077/] In the…
The short story: SQL Server 2022 finally saw some growth this quarter! Two years after the release, 1 in 10 SQL Servers is finally running the latest version.
The long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2024 version of our SQL ConstantCare® population report.
I've worked with Microsoft SQL Server for so long that I just kinda took backups for granted. We run a backup command, and SQL Server: Logs activity to the transaction log while the backup is running Reads the exact contents of the database files, and writes them out to a backup file When it's done,…
I was busy, so I asked a friend to fill in for me and answer your top-voted questions from https://pollgab.com/room/brento. He did a pretty good job:
https://youtu.be/oaRIxcf8sKE
These two queries both get the same answer from the Stack Overflow database:
[crayon-6a79dc0fbb19a468286876/]
I don't get it. I've given this feature one chance after another, and every time, it takes a smoke break rather than showing up for work.
The latest instance involved the recent Query Exercise where you were challenged to fix a computed column's performance. In the comments, some folks noted that performance of the query was actually great on old compat levels, like SQL Server 2008, and that it only sucked on newer compat levels like 2016 and later.
What a comfy morning to sit down with a breakfast margarita and tackle your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=E97eDEDLQU8
Every year, Stack Overflow runs a developer survey about technology, work, community, and more. This year's results include 65,437 responses from developers around the world. The results are biased towards the kinds of developers who use Stack Overflow - 76% of the respondents reported that they have a Stack Overflow account. I would guess that…
While taking a dip in Cabo, I went through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=OaHImrIMM6Y
In last week's Query Exercise, we added a user-defined function to the Users table to check whether their WebsiteUrl was valid or not. I noted that even with an index on Reputation, SQL Server 2022 simply ignored the index, did a table scan, and spent 2 minutes of time calling the user-defined function on a row-by-row basis.
Last night, two major IT disasters struck:
Microsoft Azure's Central region went down for about 4 hours. The official post-mortem isn't out yet, but rumor has it that while decommissioning legacy storage services, the product group deleted the wrong thing.
Crowdstrike pushed a bad update, leading to blue screens of death on Windows systems worldwide, affecting banking, healthcare, airlines, and more.
On a pleasantly mild morning, I sat down on the patio and took your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=4chYaiYUaSk
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.
Stack Overflow publishes a data dump with all user-contributed content, and it's a fun set of data to use for demos. I took the 2024-April data dump, and imported it into a Microsoft SQL Server database.
It's an 31GB torrent (magnet) that expands to a ~202GB database. I used Microsoft SQL Server 2016, so you can attach this to anything 2016 or newer. If that's too big, no worries - for smaller versions and past versions, check out my How to Download the Stack Overflow Database page.
