22Oct 2019 by Grant Fritchey 2 Comments

Every Execution Plan Is An Estimated Plan

SQL Server
I consider myself to be the most responsible for making such a huge deal about the differences between what is labeled as an Estimated Plan and an Actual Plan. I walked it back in the second edition of the Execution Plans book. Hugo and I completely debunked the issue in the third edition of the Execution Plans book. That is the one you should all be referencing now. As I like to joke, the guy who wrote the first two editions of the book was an idiot (and lest anyone take offense, let's be clear, I'm the idiot). Now, I'm trying my best to make this whole issue more clear. Let's talk about the "different" plans you can capture in SQL Server. Estimated Plan This is where you have a…
Read More
14Oct 2019 by Grant Fritchey 1 Comment

Database Fundamentals #23: Filtering Data

Database Fundamentals, SQL Server
If you've been reading these Database Fundamentals posts, you've already seen the WHERE clause because of your use of it when manipulating data with DELETE and UPDATE statements. It's also been used several times earlier in this series to limit the values returned from a SELECT statement. The primary places where people run into trouble with T-SQL is in the JOIN criteria and the WHERE clause criteria. This occurs because they don’t understand well enough what the filters and operators they’re using will do. They end up returning too much data because they didn’t us the WHERE clause or misapplied it. They also filter too much data out. Just remember, there are even more functions than we go over here in this series. While these basic operators answer most needs,…
Read More
07Oct 2019 by Grant Fritchey 5 Comments

SQL Injection Mitigation in SQL Server 2019

SQL Server
I've been teaching a lot more about SQL Injection lately (including blog posts). I've been doing this because, despite this being a 21 year-old problem with well defined solutions, we're still dealing with it. Recently, while sitting in the speaker room at Techorama Netherlands (fantastic event, strongly recommended), I had the opportunity to spend a little time with Niko Neugebauer. I was freaking out because my demos were failing (fixed 'em finally). Niko was talking to me about the new Feature Restrictions and their effect on SQL Injection in SQL Server 2019. I didn't know what he was talking about, so I had to look it up. Of course, top resource, Niko's blog. Feature Restrictions in SQL Server 2019 The Feature Restrictions in SQL Server 2019 are actually being added…
Read More
23Sep 2019 by Grant Fritchey 8 Comments

Database Fundamentals #22: Using the Join Operator, CROSS JOIN

Database Fundamentals
While the CROSS JOIN is not used much, and, depending on the size of your data it can be dangerous, there are some uses for it. For example, you may want to write a query that will summarize all the sales for the companies and people in your system. You can do this using what is called an aggregate operation or a GROUP BY: SELECT c.CompanyName, p.LastName, SUM(ft.TransactionAmount) AS 'TransactionTotals' FROM Management.Company AS c JOIN Finance.FinancialTransaction AS ft ON c.CompanyID = ft.CompanyID JOIN Personnel.Person AS p ON p.PersonID = ft.PersonID GROUP BY c.CompanyName, p.LastName; This will add all the values up in the SUM operation for each company and each person that has values so that your data will look like this: The only problem with this is, you can’t…
Read More
19Aug 2019 by Grant Fritchey 1 Comment

How Do You Make DevOps Succeed?

DevOps
I love going to SQLSaturday events because I'm always asked questions that make me think. I was just at SQLSaturday Indianapolis (a great event, if you weren't there, you missed out). I was giving a session called "Extending DevOps to SQL Server" (which I'm giving this Saturday at SQLSaturday Providence). I was talking about the fact that I've been involved in successful DevOps implementations and I've been involved in failed DevOps implementations. The question that came up was, "What were the key differences between the failed and successful organizations?" Great Question. Management Buy-In I've seen attempts to implement DevOps strictly from the IT side of things. A relatively high functioning team recognizes the benefits an agile approach that's oriented towards improved collaboration between people that uses automation in support of…
Read More
19Aug 2019 by Grant Fritchey 19 Comments

Profiler and Trace vs. Extended Events

SQL Server
It's a running joke among the more experienced (read, older) Microsoft Data Platform specialists as to whether you're #teamprofiler or #teamexevents. I'm very much #teamexevents, but I really don't care that you're #teamprofiler. If you want to use the old way of doing things, that's fine. However, I do have a bone to pick. Why on earth would you recommend to new data professionals working on modern systems, let's say at least 2016 or better, who are just getting going on this journey, that they should be using Trace? What are you smoking? Trace vs. Extended Events We're not just talking about where you are comfortable. I get it. You've used Trace/Profiler for 20+ years. Getting it going is all muscle memory. That's fine. As the kids say, you do…
Read More
05Aug 2019 by Grant Fritchey 4 Comments

Running Containers In a Virtual Machine

Containers
The more you work with containers, the more you just want to work with containers. However, there are still reasons to have a virtual machine for some types of workloads. So, what if you want to work with containers inside a virtual machine. Is that possible? Yes, and shockingly easily. Enable Virtualization In Virtualization I knew from conversations I've had previously that running Docker inside a virtual machine was possible. I just didn't know the details. So, with a complete lack of knowledge, I did the most expedient thing possible: I installed Docker in a VM and started it up. Now, let's talk about my setup for a moment. My laptop is running HyperV as my hypervisor. You have to have some type of hypervisor for Docker to work. I'm…
Read More
29Jul 2019 by Grant Fritchey 4 Comments

Sample Azure DevOps Pipelines

DevOps
I've said it before and I will repeat myself on this because it's an important concept: DevOps is about culture and communication, not tools Now, that said, to implement the automation required in DevOps, you're going to have to get into some degree of tooling. There are a whole slew of possible tools to support you: Jenkins, Team City, Octopus and more. All these tools offer excellent solutions with variations on limits, methodologies, etc. You'll need to explore them to understand which ones are best for you and your processes. I've been doing a lot of work lately in another tool, Azure DevOps. Let me show you a little of what I've done. Azure DevOps Pipelines I don't mean for this to be a complete tutorial on setting up Azure…
Read More
24Jun 2019 by Grant Fritchey No Comments

The Constant and Constantly Changing Role of the DBA

DevOps, Redgate Software, SQL Server
I've been working in and around data for over 30 years now. My title has changed a number of times and is poised to change again. My responsibilities have also shifted fairly continuously over that time. Even though it has been more than 20 years since I first took on the title of DBA, some aspects of the job are the same. However, over that 20 years, a stack of new technologies and processes have fundamentally changed a whole swath of what I do. The DBA Song Remains the Same Are your servers online? Are all databases available? Did the data load process run successfully last night? When was the last time a backup was run on this database? I honestly don't care if you're in the cloud, on-premises, or…
Read More
03Jun 2019 by Grant Fritchey 2 Comments

Which Query Used the Most CPU? Implementing Extended Events

SQL Server, T-SQL, Tools
A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it's not readily apparent how to get it. While you can look at what's in cache through the DMVs to see the queries there, you don't get any real history and you don't get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data. A…
Read More