Grant Fritchey - Intimidating Databases and Code

17Feb 2020 by Grant Fritchey 6 Comments

Why I Support Code Automation Tools Like ORMs

I know I'm a weirdo. I've always been a weirdo. When I was a DBA (now I only play one on TV), I was a weirdo too. Case in point, ORM tools. Whether we're talking nHibernate, Linq, or Entity Framework, the degree of loathing for these tools by most DBAs is really hard to measure. Yet, after an initial period of difficulty (here are some ancient blog posts documenting that pain), I've come to believe that code generation tools are a very important part of what we do. Further, that they are not evil, or wrong, or bad. Let's talk about this just a little. A Tale of Two Teams At my previous employer there was a degree of friction between the developers and the DBAs (shocking, right). Both sides…

11Feb 2020 by Grant Fritchey 10 Comments

Be Kind

Professional Development

Simply put, we are not always going to agree. Please, take this as someone who was nicknamed "The Scary DBA" for reasons comic and tragic. I've screwed up a bunch of stuff in my career. Yes, some of it was through ignorance of technology. However, most of it was through badly managed interpersonal relationships. I was not always kind. I was not always empathetic. I caused problems on teams sometimes that were just unnecessary. If I had just thought things through, a little, and practiced more kindness, I'd have had a much nicer time of it and influenced people in a more positive way. Now, I read this post by Jennifer Jones. I wanted to get angry at people for not treating her well. But all I could think about…

10Feb 2020 by Grant Fritchey 5 Comments

Extended Events: Histogram Output

SQL Server, You Can't Do That In Profiler

The single most important thing to remember about Extended Events is that this functionality is not simply a replacement for Profiler/Trace, but a whole new tool with new functionality. My first example for functionality that you simply cannot get in Profiler/Trace is the ability to output to a Histogram. Profiler/Trace can output to a table or to a file. Extended Events can have a target that is a file, same as Profiler. However, you can also have a target: etw_classic_sync_targetevent_counterhistogrampair_matchingring_buffer Read about each of the types in the Microsoft documentation here. I'm going to focus for the moment on the histogram target because it lets you do some fun stuff and easily collect data that you simply can't collect using Profiler/Trace without hopping through a bunch of flaming hoops. Setting…

04Feb 2020 by Grant Fritchey 6 Comments

The State of Database DevOps 2020

DevOps

I've been an advocate for database DevOps since before DevOps was a thing. When I first switched from being a developer full-time to being a DBA full-time, the first shock to my system (and there were a lot of them) was that people didn't put their database code into source control. Turns out, at the time, more than 20 years ago, the reason was because it was brutally difficult to do. Fast forward to today, and there are no serious impediments to implementing source control for your databases. Further, the impediments to automating more and more of development, testing, and deployments for databases are shrinking faster and faster all the time. Whether or not you're doing a literal DevOps process, the concepts of breaking down the barriers to communication, adopting…

03Feb 2020 by Grant Fritchey 14 Comments

Profiler: Time To Go

SQL Server

I've decided that, in fact, it is time to start moving people off the ancient technology, Profiler. Before, I always said, stay where you're comfortable. However, keeping people comfortable means that they're also going to keep promoting Profiler/Trace to new people on new platforms. That is a real problem. To fix the problem of old school, slow, inferior, methods of data collection, troubleshooting, and consuming metrics, we need to educate people. Extended Events are not simply a replacement for Trace. They're not simply another way to gather query metrics. No, in fact, this is a whole new tool, with new functionality and a very high level of support and engagement from Microsoft. Extended Events are where all new functionality since 2012 provides mechanisms for monitoring behavior. As technologists we should…

27Jan 2020 by Grant Fritchey 9 Comments

Extended Events Misperceptions: Profiler is Easier, Part 2

SQL Server, Tools

I wrote a short blog post about the misperception that Profiler was easier than Extended Events when it came to the core concept of "click, connect, BOOM, too much data". Go read it if you like, but I don't think it's actually an effective argument for how much easier Extended Events is than Profiler. Here, we're going to drill down on that concept in a real way. Let's start with a little clarification. I'm going to be a little lazy with my language. Trace is a scripted capture of events on a server. Profiler is a GUI for consuming a Trace, either live or from a file, and for creating Trace events. However, almost everyone refers to 'Profiler' when they mean either Trace or Profiler. I may do the same…

21Jan 2020 by Grant Fritchey No Comments

Microsoft Tools That Help Query Tuning

SQL Server, T-SQL, Tools

Query tuning is not easy. In fact, for a lot of people, you shouldn't even try. It's much easier to buy more, bigger, better hardware. Yeah, the query is still slow on newer, faster hardware, but not as a slow as it was. However, sooner or later, you're going to have to start to spend time fixing queries. In fact, you can find that fixing queries actually is more cost effective than buying more hardware. The problem is, query tuning is not easy. So, what do you do? Microsoft Can Help There are a number of tools available to you, right now, provided by Microsoft that can help you better and more easily tune your queries. This ranges from extended events to query store, and absolutely includes execution plans and…

20Jan 2020 by Grant Fritchey 10 Comments

Extended Events Misperceptions: Profiler is Easier

SQL Server

I know, I know, I'm on #teamexevents so I think that Extended Events can do no wrong, but let's address this thought that Profiler is easier. Now, if we're strictly talking knowledge, sure, if you've got a lot of experience with Profiler/Trace and very little with Extended Events, of course Profiler is easer. However, what I'm told is that Profiler doesn't require very much set up, while Extended Events does. That's just wrong, but let's put it to the test. The Test For the comparison, we're not going to do anything special with either tool. I'm just going to start collecting query data with the fewest possible clicks and/or key strokes. I'm going to use both tools short cuts to make this as fast as possible. The goal is, click,…

13Jan 2020 by Grant Fritchey 1 Comment

Causality Tracking in Extended Events

SQL Server

If you go through all the stuff I've written about Extended Events, you'll find that I use causality tracking quite a bit. However, I've never just talked about what causality tracking is and why I use it so frequently. Let's fix that issue now. Causality Tracking Causality tracking is quite simple to understand. It's property that you set for a given session. A session, of course, is defined by one or more events and a target. You can define things about a session, like it's name, when you define the session itself. Turning on, or enabling, causality tracking is just a matter of defining that the session will have causality tracking. It looks like this in the GUI: It looks like this in the T-SQL code: CREATE EVENT SESSION QueryBehavior…

06Jan 2020 by Grant Fritchey 6 Comments

Execution Plans: First Operator

SQL Server, T-SQL

The first time you see a new execution plan that you're examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator. First Operator The first operator is easily discerned (with an exception). It's the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case: This is regardless of how you capture the execution plan (with an exception). Whether you're looking at an execution plan from the plan cache, Query Store, or through SSMS, the execution plan, regardless of complexity, has this first operator. In this case, it says UPDATE: If you get an execution plan plus runtime metrics (previously referred to as an "actual" execution plan), you'll still see…