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
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
06May 2019 by Grant Fritchey 2 Comments

Query Store, Plan Forcing and Table Variables

SQL Server, T-SQL
This weekend I was in Stockholm in Sweden, talking Query Store and plan forcing with Steinar Anderson, when he mentioned the problems he had while forcing plans that had table variables in them. Don't panic. Of course you can force a plan with a table variable, most of the time. Steinar had a fairly focused problem. Before I go on to explain the issue, let me be really clear, Steinar figured out the issue all on his own. When he outlined the problem, I saw immediately what his conclusion was going to be. What's spurring this blog post is that Steinar said he'd searched on the internet and no one had talked about the issue yet. So, let's talk about it. Plan Forcing With Table Variables First up, let's show…
Read More
15Apr 2019 by Grant Fritchey 2 Comments

Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

Database Fundamentals, SQL Server
The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you have to include whether or not you’re dealing with a RIGHT or a LEFT JOIN. The OUTER word, just like the INNER key word, is not required. OUTER JOIN Imagine a situation where you have a list of people. Some of those people have financial transactions, but some do not. If you want a query that lists all people in the system, including those with financial transactions, the query might look like this: SELECT p.LastName, ft.TransactionAmount, ft.TransactionDate, ft.TransactionTime FROM Personnel.Person AS p LEFT JOIN Finance.FinancialTransaction AS ft ON p.PersonID = ft.PersonID; Except for the addition of the LEFT key word, this…
Read More