27Apr 2020 by Grant Fritchey 7 Comments

How Does The CHOOSE Command Affect Performance?

SQL Server, T-SQL
Questions absolutely drive my blog content and I really liked this one: how does the T-SQL CHOOSE command affect performance. On the face of it, I honestly don't think it will affect performance at all, depending on where and how you use it. However, the answer is always best supplied by testing. T-SQL CHOOSE Command The CHOOSE command was added in SQL Server 2012. It's fairly straight forward. You supply an array and a numbered index for that array and CHOOSE will pull the matching value for that index. It works like this. We'll start with a simple proc and execute it: CREATE OR ALTER PROC dbo.CarrierAndFlag ( @SalesOrderID INT, @Flag INT ) AS BEGIN SELECT sod.CarrierTrackingNumber, CHOOSE(@Flag, 'A', 'B', 'C') AS Flag FROM Sales.SalesOrderDetail AS sod WHERE sod.SalesOrderID =…
Read More
20Apr 2020 by Grant Fritchey 4 Comments

The Very Best of Extended Events

SQL Server
Over the next couple of months, I'll be putting on a number of different sessions teaching about the tools supplied by Microsoft, for free, that can help you when tuning your queries. One of the most important of these tools is Extended Events. A couple of my sessions in the Redgate Community Circle livestream "Built-in Tools Make Query Performance Tuning Easier" will be on Extended Events. My livestreaming starts tomorrow, April 21, at 2pm Eastern. It will be recorded and made available for free. Follow the link for all the details, or, just subscribe to Redgate's YouTube account. I'm also going to be hosting a fundamentals introduction to Extended Events, "The Easy Way to Extended Events." Heck, I'm even going to be hosting a session showing how to use Extended…
Read More
16Apr 2020 by Grant Fritchey 4 Comments

Redgate Community Circle

Professional Development
I am very excited to announce that I will be taking my paid precon content "Tools for SQL Server Query Performance Tuning" and presenting it for free. This is all a part of a new initiative from Redgate Software called Community Circle. Read about that here. I'll be livestreaming the precon once a week on the Redgate Youtube. So, please tune in, every Tuesday at 13:00CDT. Or, you can watch the recordings of the livestream, which we'll host on Youtube, free, forever. Between classes, you can ask questions, make comments, and generally interact through a Discord server I've set up here. We'll be covering a whole slew of topics from Query Store to DMVs, SQL Server Management Studio to Extended Events, Execution Plans and more, lots more. Whether you watch…
Read More
13Apr 2020 by Grant Fritchey 10 Comments

Develop Resiliency

Professional Development
We are all going through some tough times. It's tougher for some more than others. Now is the time when you have to work on being resilient, and it ain't easy. However, there are things you can do to develop resilience. Like most things, it's actually a skill that can be learned. Let's start with a little disclaimer up front. I'm no expert on this topic. However, I do have a few bonafides and I'll share them so you know where I'm coming from. If you don't care, skip to the next section. My Training and Experience In Resiliency Without going into personal details, like everyone, one of my teachers has simply been life. At 57, I've done a few things and been a few places. I've gone through tough,…
Read More
06Apr 2020 by Grant Fritchey 6 Comments

What Prevents You From Securing Your Servers?

Database Fundamentals
Reading about how hackers are using SQL Server instances that are exposed on the internet AND have weak passwords to work into systems, I'm sitting here wondering why. I get it. A pretty hefty majority of these were simply set up by people who didn't know any better. Computers and databases are now so wonderfully easy, almost anyone can slap together a database server, and do. Simple ignorance leads them to misconfigure the systems and leave them exposed. So, while it's hard to forgive this ignorance, it's easy to understand. I sincerely doubt we'll ever be able to do much about it. I suppose if systems were designed to be secure from the moment of installation, that would help. Stuff like ElasticSearch having it's development servers have zero security out…
Read More
31Mar 2020 by Grant Fritchey 2 Comments

Unit Testing & Intelligent Query Processing

Uncategorized
Actually, these two topics don't have anything to do with one another. I just ran out of days to promote everyone individually who was taking part in putting on Redgate Streamed. Steve Jones Steve is one of the better people I know when it comes to the wisdom and need for testing. Speaking only for myself, I kind of hate setting up tests. Yes, I know how vital they are. Yes, I know they make an enormous positive impact on our ability to generate better code, faster. Yes, I know they're a fundamental aspect of DevOps. Yet, they're a pain the bottom. However, Steve has a way with them. He really does make them look easy. If you're like me and not a huge fan, then his session "The Basics…
Read More
30Mar 2020 by Grant Fritchey No Comments

Extended Events: Queries and Waits

SQL Server, You Can't Do That In Profiler
Wouldn't it be great to be able to put together queries and waits at the same time? You all capture query metrics using some method. Most of us query sys.dm_os_wait_stats or sys.dm_db_wait_stats. Combining them is hard. You could query the wait stats. Store the results in a table variable. Run the query in question. Then query the wait stats again into a different table variable. Join the two table variables together to find the differences. Ta-da, you have query waits. Well. Probably. If you're the only one running queries on the system. Also, you're not seeing system waits or other noise caused by activity on the system. Or, we could put Extended Events to work. Queries and Waits Just like Profiler/Trace, you can capture stored procedures, batches, and individual statements…
Read More
27Mar 2020 by Grant Fritchey 2 Comments

Figuring Out How To Hide Production Data

Redgate Software
There's a really simple conundrum that we go through all the time. The best data for development is productionYou can't have production data for development You have to split the difference here in a pretty fine way. Get the developers the best tools possible while protecting the production information. It's not easy, but I think I can help. Chris Unwin Of my many amazing co-workers, I sometimes think I'm the most jealous of Chris Unwin. It's not because he's smart and capable, most of them are. It's because he's young, filled with ideas, AND smart and capable. He'll be running things long after we're all gone. If you haven't heard Chris speak, I'd recommend you start with DBAle, the beer flavored podcast that he runs covering all things data. However,…
Read More
26Mar 2020 by Grant Fritchey No Comments

Time To Learn Git

Redgate Software
I love this quote from Kevin Hill (and not because he mentions me): 3 things I can no longer justify ignoring: #dbatools Git and #Docker for my dev SQL work@cl@sqldbawithbeard@Kendra_Little and @unclebiguns@GFritchey, I blame you 🤪😂 There’s more but those are top 3— SQL Cyclist (@Kevin3NF) November 9, 2019 The reason being, he's right. I'm spending time rewriting some of my own sample code to use DBATools. I'll try to post some of it here when I get it together. I've been screaming about the importance of containers in general and Docker in particular for quite a while now. Finally, I use Git. So should you. However, getting started with Git, especially in a database, isn't that easy. Kendra Little You might notice Kendra's name in the tweet above. She's one of the smarter, more capable people…
Read More
25Mar 2020 by Grant Fritchey 2 Comments

DB Automation with Azure DevOps

Redgate Software
I sincerely believe the key to your future as a DBA is your ability to automate everything you do. However, the single hardest thing that you have to do is keep up with the changing business and IT environment, which means, deployments. Lots of deployments. How are you going to get that done? Cathrine Wilhelmsen I have to admit, publicly, when I first met Cathrine, I was not the nicest person. I just couldn't believe that this person who sounded like they were from Ohio was actually Norwegian. I asked for her drivers license. Thankfully, Cathrine forgave me my rudeness and I'm very grateful for that. The reason I'm grateful is, she's seriously on top of her game when it comes to automation. At the upcoming Redgate Streamed event, April…
Read More