Posted on

Extended Events: Avoid the XML

One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped.

Look, I get it. I don’t like XML either. It’s a pain to work with. It’s actively difficult to write queries against it. If there weren’t a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let’s see how.

Posted on

Data Storage in 2021: Choosing the Right Tools for the Job

Editor’s Note: The following is an article written for and published in DZone’s 2021 Data Persistence Trend Report.

Reading about the death of the relational database seems like a regular occurrence. However, here we are in 2021, and the relational data store is going strong. If we look at the DB-Engines Ranking website, six of the top 10, including the top four spots, are all relational data stores. Evidently, structured, or relational, data storage is here to stay. Yet four of the top spots are held by non-relational engines. Could that mean that relational data storage is really dying?

Posted on

Database Fundamentals #24: More Filtering Data

In this post, we continue discussing the functionality of the WHERE clause. We started with the basics of the logic using things like AND, OR, and LIKE or '='. Now, we'll expand into some other areas.

Functions in the WHERE Clause

SQL Server provides you with all sorts of functions that can be used to manipulate strings, modify dates or times or perform arcane mathematical equations. The problem with these is that if you do them on columns in tables it can lead to performance issues. The trick then, is to not perform functions on the columns in the tables. We'll cover this in more detail when we get to indexing, variables, and parameters. Just don't get into the habit of putting functions on the columns in your tables in the WHERE clause.

Posted on

Database Fundamentals #23: Filtering Data

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. 

Posted on

Containers: Upgrading SQL Server From 2017 to 2019 RTM

Upgrading SQL Server From 2017 to 2019 RTM

Throughout the pre-release of SQL Server 2019, I was demoing an effectively instant and magical container upgrade from 2017 to 2019. However, when I finally downloaded the release bits in a new image, the magic went away. In fact, I got errors. So what happened?

Non-Root User

In SQL Server 2017, the containers were running as root. The thing is, you're basically setting up your instance to run as an administrator of the system. We all know that's a no no. So, in SQL Server 2019, Microsoft fixed this, and now the SQL Server instance within the Linux container runs as mssql. Much better.

Posted on

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

CROSS JOIN

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;
You might also like:  Overview of SQL Joins in SQL Server

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:

Posted on

Continuous Learning

In case you can't tell from some of my blog posts, I'm a bit of an advocate for DevOps. I'm extremely fortunate in my employer, Redgate Software, that they are also huge advocates for DevOps. We not only teach it and promote it, and, oh yeah, make AWESOME tools for it, we practice DevOps in what we do.

However, this post is not about DevOps. Instead, I'm trying to leverage some of the concepts of DevOps, Continuous Integration and Continuous Deployment, to arrive at some ideas around learning that I want to share.

Posted on

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

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:

Posted on

Compliance Ain’t Easy

I’m sure by now you’ve heard of GDPR and some of the large-scale data breaches that have occurred within it. If you haven’t heard of the GDPR, you’ve been living under a rock, or you’re like me, a United States citizen (it’s amazing how little we know about this oncoming train). If you’re seeing the four letters GDPR strung together for the first time, then you better jump on learning about it right now. Why? Let’s string together more letters — CPPA. That stands for the California Privacy and Protection Act. That’s a law modeled off the GDPR that goes into effect in 2020 (yeah, in nine months).

Compliance Isn’t Always Spelled GDPR

Maybe you’re not in an EU country and you don’t have any person’s data from there. Maybe you think that the CPPA won’t apply to you because you’re not in California and don’t have any person’s data from that state. However, do you collect credit card information? Are you compliant with the PCI rules? That’s the Payment Card Industry, by the way.

Posted on

system_health Extended Events in Azure SQL Database

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.Image title

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:

Posted on

Missing Indexes in the Query Store

I've shown before how to use the DMVs that read the plan cache as a way to connect the missing indexes suggestions with specific queries, but the other place to find missing index suggestions is the Query Store.Image title

Pulling From the Query Store

The plans stored in the Query Store are exactly the same as the plans stored within the plan cache. This means that the XML is available and you can try to retrieve information from it directly, just as we did with the missing index queries against the DMVs. Here's the query modified for the Query Store:

Posted on

Installing Extensions to Azure Data Studio

If you're even thinking about experimenting with — let alone actively using — Azure Data Studio, you need to plan on installing a few extensions. Buck Woody has a great list that you should look through in this post. If you're just getting started with Azure Data Studio, I have an introduction here.

Depending on the extension, this could be as simple as a mouse click. However, not all the extensions are that easy. Let's explore this just a little so when you do start using Azure Data Studio, things are easy.

Posted on

DevOps and Automation Will Eliminate the DBA

I’ve been reading about the death of the DBA ever since I first made the jump from full-time developer to full-time data professional. The first time I heard it was when SQL Server 7.0 was released. Did you know that SQL Server 7.0 was self-tuning? In fact, it was so self-tuning that the DBA is a relic of the past and no one will be paid for that kind of work anymore.

Right.