06Feb 2023 by Grant Fritchey 3 Comments

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
11Nov 2022 by Grant Fritchey 2 Comments

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
17Oct 2022 by Grant Fritchey 4 Comments

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
11Oct 2022 by Grant Fritchey No Comments

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
10Oct 2022 by Grant Fritchey No Comments

Check Every Metric

SQL Server
Recently, a person asked about the costs differences in an execution plan, referencing them as if they were performance measures. The key to understanding performance is to check every metric. When it comes to execution plans, I'm sure I've said this before, so please allow me to repeat myself. The cost numbers shown in an execution plan, which, barring a recompile, will be the same for an execution plan or an execution plan with runtime metrics (aka, estimated and actual plans), are not measures of performance. They do not represent actual metrics. Instead, they are calculations of a theoretical actual performance measurement. So, you can't look at two plans, with two costs, and say, "this plan will perform better." Instead, you can say, "this plan has a lower estimated cost."…
Read More
03Oct 2022 by Grant Fritchey 2 Comments

Function Vs. Performance

SQL Server
Recently I was looking through DBA.StackExchange when I saw a pretty simple question that I decided to answer. I went off, set up a test database and some test tables and quickly wrote a query to answer the question. I got it all formatted pretty and was on my way to post it when I saw that another answer was already there. Yeah. Identical to mine. Almost line for line. Well, nuts. I know. I'll write a blog post. The Setup The original poster had two tables that, frankly, are badly designed. However, they share enough data that they are "related" if not relational. Here's the code: CREATE DATABASE Testing; GO USE Testing; GO CREATE TABLE Table_A ( ID INT IDENTITY(1, 1), Score INT ); CREATE TABLE Table_B ( FromPoint…
Read More
19Sep 2022 by Grant Fritchey No Comments

Extended Events for Anything but Query Tuning: bulk_insert_usage

SQL Server
Wouldn't it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don't know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event. Bulk_insert_usage The BULK INSERT command is extremely useful within SQL Server. It's a way to move data into the database and provide some formatting on the way, efficiently, all through T-SQL. Hard to argue with the utility. Obviously, if you're doing traditional data collection through Trace or Extended Events, you'll see BULK INSERT commands within the T-SQL. However, Extended Events provides a specific event that tracks just the behavior of BULK INSERT: bulk_insert_usage. Documentation on this is somewhat sparse. Some of the best is from a standard source, Jason…
Read More
05Sep 2022 by Grant Fritchey No Comments

Database Fundamentals #33: Check Constraints from the GUI

Database Fundamentals, SQL Server
The other types of constraints are referred to as check constraints. They limit the data by defining a logical operation that checks the state of the data prior to allowing an insert or update to the table. The logical operation is not against other tables. The logic can be against multiple columns in the same table. Let’s start with a simple example. The business has determined that the transactions it’s recording will never be less than $10. As part of the business definition, they would like a constraint put in place that limits the TransactionAmount values in the Finance.FinancialTransaction table to only accept values that are greater than $10. Let’s create this constraint using the GUI and again, in the next post, using T-SQL. Check Constraint with the GUI We’ll…
Read More
15Aug 2022 by Grant Fritchey 1 Comment

Distributed Replay: The Little Engine That Almost Could

SQL Server
Honestly, sincerely, no kidding, I love Distributed Replay. Yes, I get it. Proof positive I'm an idiot. As we needed proof. To be a little fair to me, I love what Distributed Replay could have been, with a little more love. However, fact is, it's on the deprecation list for 2022. Which means, what minimal amount of love, if any, that Microsoft was giving to it, it's all gone, forever. Unlike the Little Engine That Could, turns out that Distributed Replay was the Little Engine That Almost Could, But Didn't. Really Didn't. Let's discuss it a bit. Distributed Replay The concept is wonderful. Capture a bunch of queries from your production system. Replay them on a non-production system for testing. Add in the idea of being able to chain together…
Read More