Grant Fritchey

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…

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…

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…

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…

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…

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…

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…

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…

05Oct 2020 by Grant Fritchey 10 Comments

Assuming Good Intentions

Professional Development

With all the stuff going on around the world over the last year, maintaining some degree of mental stability can be quite a challenge. Speaking only for myself, while my family and I have been fine through all this, the changes to my situation, while mostly benign, have still had their challenges. Again, speaking only for myself, I've found those challenges do lead to negativity on my part. Further, that negativity can bleed into my communication with others, hurting that communication. I have seen others suffering from this as well. Whatever stresses they're going through clearly leads to negativity in their communications. Now, when we're looking at this situation, I'm negative, someone else is negative, we're going to communicate really poorly. So, how to address this? Well, there are a…

28Sep 2020 by Grant Fritchey 4 Comments

Capture Execution Plans Only For Long Running Queries

SQL Server

I love questions. Most of all, I love questions I can answer. I spotted this question recently: How can I use Profiler to capture execution plans for queries over a certain duration? Oh, that's easy. You don't use Profiler. You use Extended Events. Query_post_execution_showplan Extended events are just better than Profiler. Period. One of many things that is superior is the way in which the events are configured. Take for example query_post_execution_showplan. Here are the fields it captures: This event will capture execution plans plus runtime metrics. It can easily be filtered on any of the fields listed, and you can even add the database_name field if you want. So, to filter by duration is pretty simple: CREATE EVENT SESSION ExecPlansDuration ON SERVER ADD EVENT sqlserver.query_post_execution_showplan (WHERE ([duration] > (1000000)))…