Query Store Data Cleanup

SQL Server
Microsoft supplies quite a few knobs to control how Query Store performs data cleanup. You can set your cleanup various ways, and, they interact. Let's talk about how and why Query Store does it's data cleanup. Query Store Size Limit The most important thing to understand here is that Query Store won't just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it's 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It's a database-by-database setting (as so much of Query Store is). You can change this through SSMS: Change the "Max Size (MB)" value. That's it. Nice & simple. You can also…
Read More

Presenting On Basics Is Difficult

Professional Development
Over the last year, at work and after work, I've been teaching myself a lot of brand new technologies. As such, I'm reliant on others to have put together coherent, approachable, documentation, classes and videos. Let me tell you up front, that is not always the case. There are a lot of videos out there, that have the information you need, but it's presented so poorly that it's almost incomprehensible. Let's talk about it. Mea Culpa Please let me start with the full knowledge that I've frequently failed in this myself while presenting. I'm not talking to you now from the top of the mountain. I'm absolutely talking to you as a peer who is suffering along side you. I've spent my entire tech career teaching others (more than 30…
Read More

PGSQL PHRIDAY #3: What is the PostgreSQL Community To You?

PostgreSQL
Very excited to take part in my third #PGSQLPhriday blogging event, even more so because it's a topic that's quite near and dear to my heart, community. To say that I'm new to the PostgreSQL community isn't simply an understatement. Other than some online stuff, I haven't been anywhere near the PostgreSQL community. That's not for a lack of trying (multiple sessions submitted to multiple events), but so far, still just doing the online thing. So, I don't know that I'm fully qualified to discuss what, specifically, the PostgreSQL community means to me. Instead, let's talk about why you want a vibrant and strong community. I'm going to start with my qualifications to discuss community (not that anyone, anywhere, needs qualifications to take part in community, man, I dislike gatekeeping).…
Read More

SQL Server 2022 Query Performance Tuning

SQL Server
If you're interested in getting a digital copy, my brand spanking new book is now available here. It's in the intro, but let me tell you a little bit about the new book. It's really new. Some of the older versions of the book were simply updated, a bunch of changes to most chapters, a couple of new chapters, fixes for old mistakes, ta-da, new book. Not this time. This time, I rewrote it all. From scratch. Now, some of the chapter titles are the same. Quite a few of the examples are the same (if code illustrates something successfully, I'm reusing it). However, overall, it's a brand new book. There's a lot of new material too. The last update was for SQL Server 2017. There has (almost) been two…
Read More

Validating Backups: PGSQL Phriday #002

PostgreSQL
It's time again for the another PGSQL Phriday, this time, the question has been asked: How do you do PostgreSQL backups? Honesty up front. I'm very much just beginning my journey of learning PostgreSQL. I've been documenting that learning over here at Simple-Talk (more on the way there), including backups. For this post, I'm not going to tell you about my "experience" maintaining a PostgreSQL backup routine because, well, there isn't any. Instead, I have something else to say about backups that I learned, the hard way I might add, while working in SQL Server, that is 100% applicable to PostgreSQL. Backups Do Not Matter You heard me. In a post that's supposed to be about how you do backups, I'm telling you backups don't matter, and yeah, I'm serious.…
Read More

Getting Help Online

Professional Development
I spend a lot of time in the forums on various web sites, trying to assist people with getting help online. It's shocking how hard they make it. Let's talk about it, just a little. Tell Me the Real Problem One thing you see a lot is that people, for whatever reason, will absolutely not simply state what the actual problem is. You'll get stuff like "What SQL Server internal behavior prevents dynamically naming local variables?" And you're left scratching your head, why on earth would someone want to dynamically name local variables? Only to find out, after lots of comment & discussion, they thought that you needed to rename variables when changing values. When you get stuck, take a moment to describe the problem, but aim for the root…
Read More

Monitor Cardinality Feedback in SQL Server 2022

SQL Server, You Can't Do That In Profiler
It's possible for you to see new technology at work if you use Extended Events to monitor cardinality feedback. To put it simply, cardinality, the number of rows being returned, is estimated by SQL Server. Sometimes, it gets these estimates right. Sometimes, it gets them wrong. New functionality within SQL Server 2022 uses Query Store to see how well those estimates are working. If they're off, the optimizer can actually change plans to get you different behaviors based on this feedback. There's even more than one way to monitor cardinality feedback. Let's talk about it. Extended Events First up, to really see the full set of behaviors in action, we can use Extended Events: CREATE EVENT SESSION [CardinalityFeedback] ON SERVER ADD EVENT sqlserver.query_ce_feedback_telemetry, ADD EVENT sqlserver.query_feedback_analysis, ADD EVENT sqlserver.query_feedback_validation, ADD…
Read More

Query Store at PASS Data Community Summit

SQL Server, T-SQL
While Query Store has been out for quite some time now, released in 2016, there's still quite a lot of missing understanding of what Query Store can do for you, and, how it does it. I've put together a new presentation on the Query Store, "Using Query Store to Understand and Control Query Performance", incorporating the latest stuff from 2022, but still showing you all the goods from 2016, for the PASS Data Community Summit. Why Query Store I've been in love with Query Store since it was released. If you look through my blog, I've been talking about Query Store a lot. I provided a little help to Tracy Boggiano on her book (yes, emphasis on hers, because it is, I just helped), Query Store for SQL Server 2019.…
Read More