Press "Enter" to skip to content

Category: Administration

Finding Unique Key Violations with Extended Events

Published 2022-07-06 by Kevin Feasel

Grant Fritchey points out another use for extended events:

Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here’s a little one that came up, auditing unique constraint violations.

It can also handle most other types of errors, making this a robust way of tracking issues. Back in the 2008 days, I built a little WPF program to watch for all of the errors on the couple of production SQL Server instances I managed. At one point, I saw one of the devs trying to write a query and getting an error. I IM’d the dev and said “You forgot the GROUP BY clause” (or whatever the problem was—it was something minor like that). He came over with a bit of panicked excitement, trying to figure out how exactly I was able to see what he wrote given that I was nowhere near his cube. Good times.

Comments closed

Finding and Documenting SQL Server Instances

Published 2022-07-06 by Kevin Feasel

Tracy Boggiano continues a series on things to do at a new job as a DBA:

In my previous post, I expounded on my first 30 days I had at four jobs in the last four years. and how to setup your jobs box. I commented and got quoted on the fact that if it’s documented I don’t support it. So, these are methods of getting things documented, some including just having to have meetings, others running code.

One I believe in having a Central Management Server (CMS) where you can register your servers. Put them in as many groups as you desire but have core group such as Dev, Test, QA, UAT, Prod, Prod Sec, etc. The rest could be by application name if needed. I always have a set of names that are for the DBAs to use to do our work, other teams can have theirs for their work, i.e., deploying code.

If you aren’t using dbatools yet you should be. While not every shop can use to manage everything it is works every well for most tasks and that includes scanning the network for SQL Instances. Because unless you could into a well oiled machine there will be instances they don’t know about and one day someone will come knocking asking to fix it. Warn your security team before you run this.

Read on for examples of how you can find instances (assuming the security team is okay with it!), some of the information you’d want to document, and more. I would also recommend the most recent episode of the SQL Data Partners podcast, in which we talk to Jen and Sean McCown about documenting and managing your SQL Server inventory.

Comments closed

Cumulative Updates and GDRs

Published 2022-06-27 by Kevin Feasel

Aaron Bertrand clarifies two concepts:

The underlying problem is that servicing complex software is, well, complex. Microsoft simplified this for our little corner of the world when they announced that SQL Server 2016 would be the last release to get service packs. We still have Cumulative Updates (CUs) and General Distribution Releases (GDRs) to deal with, but they tend to only cause confusion around Patch Tuesday (or the – cough – odd time a CU breaks things). Before I explain, let’s define these:

Read on for the definitions and why the GDR path exists.

Wait, I thought the German Democratic Republic (GDR / DDR) re-unified with the Federal Republic of Germany (FRG / BRD) in 1990… Ah, the lengths I go to for an awful joke.

Comments closed

The First 30 Days as a DBA

Published 2022-06-27 by Kevin Feasel

Tracy Boggiano has some experience with new jobs:

Over the last four years, ok it seems longer than that, I’ve started four jobs. A couple just weren’t good fits. One I was at for three years. I currently just finished my first 30 days at my fourth one. Having done the first 30 days several times over the last few years, I’ve searched each time what you would do when you start that new position to take over the environment. What would you evaluate, where to start with everything, what to do first? With no luck mind you. So, I’m going to blog about my journey through this as I’ve done it several times over my career and believe it can help others as they start new positions know where to begin. Coincidentally, Aaron Bertrand (t) just blogged about his first month at Stack Overflow as a DBRE.

I can’t believe it’s been four years since Tracy and I worked together. Somebody’s been messing with my time machine, right?

Comments closed

Stopping Azure Kubernetes Service Nodes

Published 2022-06-27 by Kevin Feasel

Andrew Pruski wants to shut the whole thing down:

A while back I wrote a post on Adjusting Pod Eviction Timings in Kubernetes. To test the changes made in that post I had to shut down nodes in an Azure Kubernetes Service cluster.

This can be done easily in the Azure portal: –

However I did a presentation recently and didn’t want to have to keep jumping into the portal from VS Code…so I wanted to be able to shut down the nodes in code.

So here’s how to use the azure-cli to shut down a node in an Azure Kubernetes Service cluster.

Read on to see how but also read Andrew’s warning / disclaimer so you don’t mess anything up in a production environment.

Comments closed

Distributed Replay Deprecated in SQL Server 2022

Published 2022-06-22 by Kevin Feasel

Brent Ozar starts the wake:

For SQL Server 2022, Microsoft deprecated Distributed Replay.

The idea behind the feature was that you’d capture a trace against your production environment, set up another environment for load testing or QA testing, and then replay that exact same workload against it. You’d be able to measure which queries got better or worse, and how.

The reality was a complete mess. It was a giant pain in the rear to set up and use, to the point where I got frustrated with it within a few hours and asked my peers about their experiences with it. I got back a string of four-letter words – everybody really struggled to get it across the finish line. Over subsequent versions, Microsoft made token efforts to improve it, but never really gave it the love it required.

Yep, I can concur. What we wanted was a simple button-click (or easy-to-navigate UI) that let you capture “What does a real production workload look like?” and then the ability to re-run it elsewhere, like on new hardware. What we got was indeed a mess.

I don’t fully agree with Brent’s argument that the right answer is to build app-level testing. If everything was architected and developed for this, then yeah, that might be a better answer. But unless you’ve built all relevant applications around APIs (so they can be programmatically invoked rather than trying to do everything via Selenium) and have put in the legwork necessary to track and re-run calls, I think you end up with an even bigger mess—especially if there are multiple applications working with the same database. I do agree that this is a hard problem regardless of the path you choose.

Comments closed

DOP Feedback in SQL Server 2022

Published 2022-06-22 by Kevin Feasel

Erik Darling talks about a potentially exciting feature:

I’m not going to demo DOP feedback in this post, I’m just going to show you the situation that it hopes to improve upon.

To do that, I’m going to run a simple aggregation query at different degrees of parallelism, and show you the changes in query timing.

Figuring out where that elbow is (in other words, when you move from approximately-linear gains to sub-linear gains) can be extremely helpful. Of course, this is like solving a partial equilibrium problem: it’s part of the problem but there’s a whole separate general equilibrium problem from there—what’s the best number of cores for this query with the constraint that I have all of these other queries running on a busy server? But before I make it seem like I’m minimizing the value of this, the partial answer will, in many circumstances, be good enough.

Comments closed

Sending E-mails on Database Blocking

Published 2022-06-20 by Kevin Feasel

Thomas Williams combines a few tools:

If your SQL Servers are under pressure, you want to know. Blocking and blocked processes impact end-users, and if not addressed can slow or even stop a database. In this post, I’ll outline a method I use to get timely notifications of blocking processes that you can use too.

I adapted my approach from Tom Collins’s excellent – and still relevant – 2017 article “How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report” at https://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html. You could implement exactly what Tom covers in his post and come out on top. I’ve gone one small step further to send a formatted HTML e-mail with a table of blocking & blocked processes; like Tom, I generate the table using Adam Machanic’s fantastic sp_WhoIsActive stored procedure, which I’ve assumed is present in the master system database. The complete solution is the sp_WhoIsActive stored procedure (which you’ll need to download and create, see http://whoisactive.com/downloads/), a SQL Agent job with a job step that runs sp_WhoIsActive and sends the e-mail, and an alert that calls the SQL Agent job when there’s blocked processes.

Read on for the script, as well as some important notes.

Comments closed

Refreshing SQL Managed Instances which Use TDE

Published 2022-06-17 by Kevin Feasel

Bradley Ball keeps the dev environment up to date:

Hello Dear Reader!  I was working with some friends lately and we needed to set up a process to refresh their Development Environment databases from Production.  Additionally, the databases are encrypted using Transparent Data Encryption, The SQL MI instances are in different regions, and the SQL MI Instances are in different subscriptions.  To duplicate the environment, in order to match our friends, we did the following setup.

Click through for a high-level overview, step-by-step guidance, and a whole lot of detail.

Comments closed

Reading the SQL Server Error Log

Published 2022-06-17 by Kevin Feasel

Lee Markum has two ways to read the SQL Server error log:

Reading the SQL Server Error Log is important when troubleshooting many issues for SQL Server. Some example issues would be errors related to Always On Availability Groups, long IO occurrence messages, and login failures.

Unfortunately, the SQL Server Error Log can be very full of information, making specific things hard to find, especially if you’re just visually scrolling the Error Log. Even if you’re recycling the Error Log each day and keeping 30 or more days of error log, on a busy system, the error log can still be quite full, even for a single day.

Click through for those techniques.

Comments closed