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
07Oct 2022 by Grant Fritchey 2 Comments

PGSQL Phriday #001: Two Truths and a Lie

PostgreSQL
As a part of my own journey of learning within PostgreSQL, I've decided that I'm going to take part in PGSQL Phriday as often as I can, just as a way to continue to stretch my knowledge of this platform. Along the way, hopefully, I can help you learn a little too. The topic of this first post is Two Truths and a Lie about PostgreSQL. Now, the lie could easily be that I'm in any way qualified to talk about this topic, however, I can do a little better than that. Let's start off with simple, but important information, backups: Pg_dump can go to either a straight set of SQL, or, to a compressed digital formatPg_dumpall backups can be used to restore over existing databases with dataYou can use…
Read More
04Oct 2022 by Grant Fritchey No Comments

DevOps From Redgate

PASS
In a few weeks at the PASS Data Community Summit, I'll be joining several other Redgaters to put on an all-day precon where we take you on a database DevOps journey. Please let me tell you all about it. From Nothing The plan is simple. We're going to take you from a fully manual deployment process, to a fully automated process over the course of the day. We'll be deploying something about every 30 minutes. As the day progresses, those deployments will become more and more automatic. We'll be using a variety of tools, but the big driver will be Redgate's Flyway. The fact is, most people recognize the need for a DevOps-style approach to their database deployments. However, doing it just isn't easy. This all-day seminar is intended to…
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
29Sep 2022 by Grant Fritchey No Comments

Why You Need Presentation Skills

Uncategorized
And no, the answer is not because you want to be a Microsoft MVP. Multiple surveys have been published over the decades that list "fear of public speaking" as one of people's top fears. Some are even more afraid of this than death. Yet, public speaking is a skill we all need. Allow me to take a moment to explain why. You Are On Stage All The Time You may not think so, but you are tasked with a lot of public speaking as part of your job if you're in IT. Seriously. You constantly do it. Don't believe me? Allow me to ask a few questions. Have you in the last couple of months tried to convince the boss to implement a process change or adopt a new technology?…
Read More
28Sep 2022 by Grant Fritchey No Comments

Query Tuning At PASS Data Community Summit

PASS
The all new, in-person, PASS Data Community Summit is in just a few weeks. Since I'm shortly going to be publishing a 100% rewritten book on query tuning, I decided to take a look at what kind of sessions on the topic are going to be available at the Summit. Query Tuning With The Best You can search through the published sessions here. I didn't even bother typing in "query tuning". Far too lazy. I just typed "query" and already saw some amazing stuff. In no particular order... One of the people I've ALWAYS learned from, Kimberly Trip is delivering a precon, Indexing for Performance. That's going to be a great day of good learning. Kim is insanely knowledgeable and a fantastic presenter. Well worth your time. Erin Stellato, another…
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
12Sep 2022 by Grant Fritchey No Comments

Look Into Chocolatey

Professional Development
Just a suggestion, but I'd say you should look into Chocolatey. Let me explain why. Sabbatical For those who don't know I was recently on a six-week sabbatical from work (thank you Redgate) and I tacked a week of vacation to that. While I did clean out email during that time (can you imagine coming back to seven weeks worth... <shudder>), I didn't do software updates of any kind. In the meantime, Docker was updated. VSCode, SSMS, a whole slew of others. Not to mention, the busy little beavers at Redgate released umpty-million updates. My machine needed love, so I typed the following: choco upgrade all -y Then I went to get some coffee. Why? Because all that software that was out of date, it was getting updated, automatically by…
Read More