29Mar 2021 by Grant Fritchey 8 Comments

Extended Events: Avoid the XML

SQL Server, Tools
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. Live Data Window I have a video that goes into this in detail right here. But the core concept is simple.…
Read More
22Mar 2021 by Grant Fritchey 9 Comments

Query Store as an Upgrade Tool

SQL Server, Tools
There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That's because of the new cardinality estimation engine introduced in 2014. Most queries won't notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool. The Steps We're going to…
Read More
15Mar 2021 by Grant Fritchey 20 Comments

Apologies

Uncategorized
Hello all! I wanted to apologize to you. I haven't been keeping up with the blog well at all over the last couple of months. I have simply been struggling with motivation. I have enough for my work commitments, but extra-curricular stuff has just been lagging. So, please allow me to say I'm sorry for not getting more sharing out there. Next, I'm making a commitment to you that I will be posting regularly again from this point forward. As a part of that, I'd sure love to hear from you on what kind of content you'd like to see. Do we need more on execution plans, or on SQL Server fundamentals? Would a bunch of stuff on Extended Events or Query Store be more interesting? How much DevOps stuff…
Read More
15Feb 2021 by Grant Fritchey 10 Comments

Learning A Little Oracle

Oracle
As part of my job, I've been tasked with doing some of my work in Oracle, so I'm learning Oracle. Allow me to share a little of my pain as I explore a space I've only ever dabbled in. Getting Started in Oracle Back in the day, in order to get started with Oracle, you begin at the Oracle web site. There, you download an installation, after getting a license (or while, whatever). In our modern era, this is the hard way to get stuff done. The first place I went was Azure. There is excellent Oracle support on the Azure platform and, best of all for me, it's really easy to fire up an Oracle VM. I had a server up and running in no time. Win! Another way…
Read More
25Jan 2021 by Grant Fritchey 13 Comments

SELECT * Hurts Performance, Badly

Uncategorized
Quite a few years ago, I wrote a post about SELECT * and performance. That post had a bit of a click-bait title (freely admitted). I wrote the post because there was a really bad checklist of performance tips making the rounds (pretty sure it's still making the rounds). The checklist recommended a whole bunch of silly stuff. One silly thing it recommended was to simply substitute ALL columns (let me emphasize that again, name each and every column) instead of SELECT * because "it was faster". My post, linked above, showed that this statement was nonsense. Let's be clear, I'm not a fan of SELECT *. Yes, it has some legitimate functionality. However, by and large, using SELECT * causes performance problems. SELECT * Hurts The most fundamental place…
Read More
14Dec 2020 by Grant Fritchey 2 Comments

Getting Started Reading Execution Plans: Highest Cost Operator

SQL Server, T-SQL
Reading execution plans in SQL Server is just hard. There's a lot to learn and understand. I previously outlined the basics I use to get started when I'm looking at an execution plan for the first time. However, just those pointers are not enough. I want to explain a little further why and how those basic steps are how you get started reading execution plans. To begin with, instead of talking about the first operator, which I've detailed before, we'll talk about the highest cost operators. Highest Cost Operator Every execution plan within SQL Server includes what the optimizer has determined to be the estimated cost of each operation. All these estimated operator costs are tallied up, and that makes up the estimated cost of the whole execution plan. You…
Read More
23Nov 2020 by Grant Fritchey 10 Comments

Why Aren’t You Automating Database Deployments?

DevOps
Building out processes and mechanisms for automated code deployments and testing can be quite a lot of work and isn't easy. Now, try the same thing with data, and the challenges just shot through the roof. Anything from the simple fact that you must maintain the persistence of the data to data size to up time, and you have real problems in front of you. However, adopting database deployment automation and testing has enormous benefits. Faster, safer, production deployment enhances the protection built around your production systems. Whether we want to use the loaded term of DevOps or not, the benefits of this style of development and deployment are easily documented and measured. So, why are so few people doing it? Conservation of Momentum If we were talking about a…
Read More
16Nov 2020 by Grant Fritchey No Comments

The Learning Curve for DevOps

AWS, Azure, DevOps
If you're attempting to implement automation in and around your deployments, you're going to find there is quite a steep learning curve for DevOps and DevOps-style implementations. Since adopting a DevOps-style release cycle does, at least in theory, speed your ability to deliver better code safely, why would it be hard? Why is there a Learning Curve for DevOps? I recently did a presentation on a simple Continuous Integration process. Here are the tools that I used in the demo: Local Git repositoryVS CodeAzure Data StudioAWS CodeCommitAWS CodePipelineAWS CodeBuildAWS RDS PostgreSQLShell commandsYAMLDockerFlyway Also, I regularly present using Azure DevOps Pipelines too. Here's the list of tools that might be in a given demo: Local Git RepositoryVS CodeAzure Data StudioAzure Git RepositoryAzure DevOps PipelineAzure DevOps AgentRedgate DeployAzure SQL DatabaseYAMLPowerShelltSQLt While there…
Read More
02Nov 2020 by Grant Fritchey 2 Comments

Database Fundamentals #29: Create Foreign Keys With Table Designer

Uncategorized
The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing data from being removed in the parent table if it’s in the child table. The rules for these relationships are not terribly complex: The columns in the two tables must be the same data type, although, if SQL Server can automatically, and correctly, convert the data you can get away with different data types. But don’t do that. It’s begging for an issue. Keep them the same and you won’t have any problems.The child values can be nullable, which means that any child data is unknown.The child data can also be required, meaning that you have to have the relationship, no…
Read More
12Oct 2020 by Grant Fritchey 2 Comments

Most Costly Statement in a Stored Procedure

SQL Server
A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don't have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital. Happily, Extended Events are here to help. Finding a Costly Statement Query tuning is initially an act of discovery. Which queries, batches, procedures are inflicting the most pain on us. That pain could be measured a bunch of ways. The three most common, in particular order, are: Frequency with with a given query/batch/procedure is called.Resources used by that query.Length of time that it takes…
Read More