Press "Enter" to skip to content

Category: T-SQL Tuesday

Moving Files Associated with Availability Groups

Published 2021-10-18 by Kevin Feasel

Eitan Blumin has a doozy of a short script:

Today, I’m sharing with you a cool Powershell script that basically implements the methodology necessary to move database files to a new location in AlwaysOn Availability Groups, without breaking HADR.

It’s based on a few very useful step-by-step guides on the topic such as this one and this one and this one. But it takes it a step further by being a single cohesive Powershell script that does everything end-to-end.

Well… Almost everything… The only thing it’s missing is somehow disabling any SQL Agent jobs that may be performing backups. I still haven’t figured out how to possibly automate such a thing, so you’d have to do that manually on your own.

Click through for instructions, notes, and warnings, as well as the script itself.

Comments closed

Things You Can Do with Random Values

Published 2021-10-14 by Kevin Feasel

Andy Yun shows off some random skills:

First, there are times where you need multiple random numbers. Simply using multiple iterations of RAND() in a single statement won’t cut it, as you need to vary the seed. So I keep this snippet handy for when I need a bunch of random values in a single statement:

Click through for that as well as two more uses of RANDOM(). This is my reminder that RANDOM() generates data across a uniform distribution (every value in the range is equally likely to be chosen), making it great for these sorts of experiments but can look weird by itself if you’d expect non-uniform distributions of the data. For that, you would need some distributional trickery—though frankly, between the uniform and normal distributions, you’ve probably covered about 95-99% of test dataset needs.

Comments closed

Index Maintenance and Pipeline Operation Scripts

Published 2021-10-14 by Kevin Feasel

Kevin Chant has a two-fer for us:

My first personal go-to script is one that has helped me out a lot over the years. Because I have used it a lot to identify missing indexes. I know there are a few different versions available online that you can use. However, I tend to use the one that comes with Glenn Berry’s Diagnostic Queries.

It is so easy to use. I’m not sharing the snippet of code on here because I want to encourage people to download the entire diagnostic script instead. Just download the script that is relevant for your version of SQL Server and search for ‘Missing indexes’.

Read the whole thing.

Comments closed

15 Short Code Snippets

Published 2021-10-14 by Kevin Feasel

Chad Baldwin goes the extra mile:

I’m excited that this will be my first time participating in a T-SQL Tuesday topic!

Most of my time is spent writing T-SQL, PowerShell and working in the PowerShell terminal, so that’s how I’ll split the post up.

I had to cut it short otherwise this post would be a mile long. If you’re interested in seeing more quick tricks, SQL Prompt snippets, etc, please leave a comment and let me know and I can do a Part 2 in the future.

Click through for a baker’s dozen plus a couple spares.

1 Comment

Adding Debug Logic to T-SQL Procedures

Published 2021-10-13 by Kevin Feasel

Deborah Melkin does not take kindly to bugs:

I often find that I have to write complicated stored procedures where I need to check things as I go along. My go-to for using this snippet is when I write stored procedures that use dynamic SQL. You’d be surprised (or not) at how often I have had to do this over the years. There’s been functionality where the user gets to choose the columns being used, rewriting ORM data layer “catch-all” queries to improve performance, and cross database queries where the name of the database may not be the standard name (think development and QA databases living on the same SQL instance.)

Click through for an example of where the @Debug parameter pays off. My recollection was that, for really long NVARCHAR(MAX) strings, running PRINT by itself might cut off the code after ~4000 characters, but that could be a historical recollection.

1 Comment

Repurposing Helpful Scripts

Published 2021-10-13 by Kevin Feasel

Deepthi Goguri re-shares some helpful scripts:

For the past couple of years as a DBA, I migrated several databases and used many handy scripts that helped me made my work easier. These scripts may be simple but if you have a migration project involving several SQL Servers with some hundreds of databases, test and production database migrations becomes tedious. I would like to share some of then here which you might already known them very well.

Click through for three scripts.

Comments closed

Short Query Store Queries

Published 2021-10-13 by Kevin Feasel

Mala Mahadevan has a few short-ish Query Store scripts for us:

I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.

My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.

Click through for that script as well as two more.

Comments closed

Building a DBA Database

Published 2021-10-13 by Kevin Feasel

Andy Mallon wants a database all of his own:

I’ve written about my DBA Database quite a few times. Some of the code in that database is written recently, and some of it was originally written a decade ago. Regardless of how old it is, or how good of a DBA I was when I wrote it, these little scripts are life-savers, and they are always at my fingertips.

And Andy has made them available to your fingertips as well. And the rest of your hands, I suppose.

Comments closed