02Dec 2019 by Grant Fritchey 5 Comments

Tracking CPU Use Over Time

SQL Server
A question that I've seen come up frequently just recently is, how to track CPU use over time. Further, like a disk filling up, people want to know how to predict their CPU usage, so that they can easily decide "now is when I upgrade the hardware". Well, the bad news is, that ain't easy. CPU Use Over Time There are a bunch of ways to look at processor usage. The simplest, and probably most common, is to use the Performance Monitor counters such as '% Processor Time'. Query this, you can get an average of the processor usage at a moment in time. Ta-da! Fixed it. I thought you said this was hard Grant. Well, hang on. Are you running on a single processor machine? If so, cool, maybe…
Read More
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
09Sep 2019 by Grant Fritchey 9 Comments

Get the Last Actual Plan With sys.dm_exec_query_plan_stats

SQL Server, T-SQL
I've always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It's not that we're going to get a completely different execution plan when we look at an actual plan, it's just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server…
Read More
03Sep 2019 by Grant Fritchey No Comments

Techorama Netherlands 2019

SQL Server
You only have a few more weeks to sign up for one of my favorite events, Techorama. This year is the second time the event will be held in the Netherlands (it started out in Belgium). I'm very honored to get to say that I'll be speaking again this year. The reason I like the event so much is because of how it draws from pure developers, architects, data specialists and analysts almost equally. Techorama succeeds at getting all the people into the room that ought to be in the room. Then, Techorama makes it possible for all these people to share knowledge and understanding about the Microsoft stack. I have several different sessions, but the one I want to talk about at the moment is the one on SQL…
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
12Aug 2019 by Grant Fritchey 6 Comments

Announcing a New Book: Query Store for SQL Server 2019

SQL Server
I'm pleased as punch to be able to share with you the fact that I helped Tracy Boggiano write a whole new book on Query Store. It will be available in October. You can pre-order it now. Tracy is a great author and a serious scholar of SQL Server. She's put together an excellent resource on Query Store. I added a couple of chapters just to help her out, but this is truly her book. Query Store is incredibly simple to use. It is however a bear to manage, so getting Tracy's book is a must if you're going to be implementing Query Store on your databases. By the way, you really should be implementing Query Store on your databases. Tracy covers all the topics from reporting with Query Store,…
Read More
08Jul 2019 by Grant Fritchey 4 Comments

sp_execute_external_script and SQL Injection

SQL Server
In order to take advantage of R and Python (and Java in SQL Server 2019) directly from your SQL Server scripts, you'll be using the function sp_execute_external_script. When you see this code in use for the first time, it's going to remind you of sp_execute_sql. The very first thing I thought about was, "Oh no. Another SQL Injection vector." I have a little good news and a little bad news. It's Not SQL The first and most important thing to understand is, we're not talking about SQL. Let's start with looking at some code. This is straight from the examples in the Microsoft documentation linked above: DROP PROC IF EXISTS generate_iris_model; GO CREATE PROC generate_iris_model AS BEGIN EXEC sp_execute_external_script @language = N'R' , @script = N' library(e1071); irismodel <-naiveBayes(iris_data[,1:4], iris_data[,5]);…
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