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

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

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

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

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

system_health Extended Events in Azure SQL Database

Azure, SQL Server
The system_health Extended Events session is incredibly useful. Further, it's running, by default, in every server you have under management that is 2008 or greater. Things are not the same in Azure though. system_health in Azure SQL Database If you look at the documentation for system_health, it shows that it's applicable to Azure SQL Database. However, if you try to run the example query, it won't work. This is because the implementation of Extended Events inside Azure SQL Database is a little different. Instead, you need to use the Azure SQL Database equivalent system views to create the same query like this: SELECT CAST(dxdst.target_data AS XML) FROM sys.dm_xe_database_session_targets AS dxdst JOIN sys.dm_xe_database_sessions AS dxds ON dxds.address = dxdst.event_session_address WHERE dxds.name = 'system_health'; Now, running this in Azure, prepare to be…
Read More

How Do You Export A Database in Azure Data Studio

SQL Server, SQL Server 2016, SQL Server 2017
I've been writing a bunch about Azure Data Studio. I've also been recording videos on the topic. A comment I received recently asked how to export a database from Azure Data Studio. It made me want to explore the topic of exporting a database as it relates to Azure Data Studio. Export? When we say export, what exactly do we mean. It could be as simple as exporting data to a flat file for consumption in Excel or something. It could be creating a backup. Maybe we mean creating a bacpac file. We could also be looking at creating individual scripts for objects within the database. Finally, what about a full export of the database object definitions? Any or all of these could be what the question was about. So,…
Read More

Critiquing Grant Fritchey: Circa 2008

DevOps, SQL Server
For several years, I ran a regular feature on this blog, Speaker of the Month. I attend a lot of events, so I have the opportunity to hear a lot of people talk about various topics. I decided, as an attempt to help out, to call out individuals that I thought had given a great presentation. There was no other reward beyond my attempts to promote others. In addition to that promotion and praise, I did offer criticism as well. It was never intended to be hurtful or in any way negative. I was hoping to point out people that I thought were great at presenting and provide a tip or two to make them even better. Not everyone liked it. In fact, a few people were decidedly, animatedly, against…
Read More

Get Your Learn On

PASS, SQL Server
There is one truth that I can say about technology with an absolute certainty: It's going to change. Get your learn on! Technology is going to be shifting under your feet, constantly. Even if all you ever do is work with SQL Server, on premises, on hardware, without VMs, containers, clouds or any of that foofaraw, things are going to change. Dealing With Change What's the best way to deal with change? Get your learn on! Learning all the new stuff is absolutely necessary. It's unavoidable. Even if you're not running SQL Server 2019 in production today (and very few of you should be as I write this because it's still in preview) because you're still on SQL Server 2005 (and, by the way, support ended in April of 2016,…
Read More

Explicitly Drop Temporary Tables Or Wait For Cleanup?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn't remember the specifics, but I said it actually didn't matter. However, that answer has bugged me, so I set up a quick test. Explicitly Drop Temporary Tables We could make this a crazy set of tests, but I wanted to keep things relatively simple. I created two procedures that create identical temporary tables. One drops the tables, the other doesn't: CREATE PROC dbo.BOMDrop AS CREATE TABLE #BOMData (BOMLevel SMALLINT, PerAssemblyQty DECIMAL(8, 2), ComponentName VARCHAR(50), ProductAssemblyName VARCHAR(50), UnitMeasure VARCHAR(50)); INSERT #BOMData (BOMLevel, PerAssemblyQty, ComponentName, ProductAssemblyName, UnitMeasure) SELECT bom.BOMLevel, bom.PerAssemblyQty, c.Name, pa.Name, um.Name FROM Production.BillOfMaterials AS bom JOIN Production.Product AS pa ON pa.ProductID = bom.ProductAssemblyID JOIN Production.Product AS c ON c.ProductID =…
Read More