T-SQL Tuesday 134 has been invited by James McGillivray (blog|twitter)

2020 was a strange year for us all. The pandemic has effected everybody to differing extents and we’ve all had to learn how to deal with the issues that have arisen this year. I’ve been introduced to some excellent games this year and they have been part of my coping strategy. I’d like to introduce you to some of these games.

This game is by far and away my favourite game that I have played in 2020. The game begins as you wake up by a camp fire and leads…. somewhere. I went into this game completely blind and was totally blown away by the setting, the story and the exploration.

My first major experience was of taking off a planet in my janky little space ship, trying to line up with the moon, and noticing tree tops entering my field of vision as the planet swatted me out of the sky while on its normal orbit.

If you have not played this game, I highly recommend it, but go in completely blind for the best experience.

Terraria is a fun little pixel survival game. You play as a character who has to explore the world while mining and dealing with threats. Playing with friends is by far the best way to play this game. You can work together to mine certain resources, build homes and deal with the threats of the world. 

I’ve spent 130 hours playing this game, most of which was over a couple of weeks when the latest patch was released. It was a great way to connect with friends and have fun. 

Arguably one of the most popular games of the year, Among Us pits little tic-tac space people against one another. There are impostors planted within the crew and the crew’s job is to complete their tasks or to throw the impostors out of the air lock before they’re all killed. The impostor’s job is to kill the crew before they’re discovered.

Lies and deceit with friends is excellent fun. How much do you really know them? Can they be trusted? Are they going to shank you the moment you turn your back? Are you going to shank them?

Isolation levels are something that are often overlooked when designing a database from scratch and by the time it is decided that a different isolation level is warranted it’s often too late to update all of that old code to ensure it works correctly. It’s by far better to start with the correct setting to make sure your code performs it’s best.

First, let’s take a look at the different isolation levels available in SQL Server (Microsoft documentation);

Read Uncommitted 

This is the isolation that allows for the fastest performance, but at a cost. Using this setting, select queries are able to read any data in the database even if it’s being modified by another transaction. This means that you can read incorrect values very easily. This may not be a concern for your application and the performance benefits might outweigh the costs but beware, this is a risky setting unless you’re sure what you’re doing.

Read Committed

This is the SQL Server default. This level stops data from being read while it is being modified and avoiding those dirty reads that uncommitted introduces. Statements that have multiple transactions can have data modified by other statements between these transactions so that’s something to be careful of. If this is a concern you can use;

Repeatable Read

This is like Read Committed but also specifies that if a transaction has read any data then another transaction can not modify or delete that data until the current transaction is completed. This means you’re going to have less concurrency (more blocking) because your writers are going to be blocked by your reading processes.


This is the most restrictive of the isolation levels. Range locks are taken out for all statements (SELECT included) and not released until the transaction is completed. Range locks are much wider than row locks so we’re going to have more data locked. 


Now, this is the interesting one. For this setting, your SELECT queries will take the latest committed data. This means that if a query is currently in the process of modifying data and a SELECT wants to read it then the SELECT will use the last known good data. This setting enables the Version Store which is stored in tempdb to store all of this ‘last known good’ data. The Version Store is an additional cost to the database but it’s one that’s worth considering for the performance improvement of your read queries.

Now go and find out what isolation levels you’re using and whether this is the correct one for you.

I’m ecstatic that I’ve been selected to speak at SQLBits 2020 in London this April. SQLBits is ” the largest Microsoft Data Platform conference in Europe” over 5 days in total. It’s a great event and the Saturday is completely free (and on a weekend) if you can’t get tickets for the main event.

I’ve had a couple of sessions chosen, I’ve put a summary below of each and I’d love to see you there.

Creating test data with SQL 2019 and Python

Friday 3rd April – 15:45

Do you have a need to create test data but not the means? Why not do it with SQL 2019 and Python? In this session we’ll go through (from the beginning) what you need to get this going and exactly how to do it, no Python experience is necessary.

Making your Emergency Toolkit

Saturday 4th April – 10:30

Have you ever had to jump on a server that’s having issues and fix it quick? Me too. We’ll go through creating your own tailored set of scripts that can help you triage and fix problems efficiently and quickly. 

For both of these talks I’ll give you all of the scripts used so you can reproduce them at home.

One other thing of note, the Friday night party is always themed. This year is The Greatest Showman. It’s sure to be a laugh, I’ll be going as the Bearded Lady, come say hi!

This month’s T-SQL Tuesday has been brought to us by Jess Pomfret who asks for our favourite Life Hack that we use to make our days easier.

Now I’m not going to talk about technical tools here, I’m going to talk about something that has helped me get into the correct head space over the last 6 months. 


I know, I know, too much like hard work right? 

Getting in some exercise before work has been amazing for both my physical and mental health. Getting in a class before work is a great way to wake yourself up properly, it’s not unusual to be yawning on the way to the gym but you can guarantee that you’re awake after that class.

It also gives you a sense of accomplishment for doing something positive before you’ve even entered the office. Whatever happens you’ve done your exercise and nobody can take that away from you. It doesn’t have to be the gym, a quick walk outside might be all that you need, I’ll leave that up to you.

I know that not everybody can get to the gym and having time with a family can be difficult but it’s really worth spending that little that you might be able to on yourself. You’re worth looking after.

This month’s T-SQL Tuesday invite comes from Mohammad Darab and invites a letter to your 20 year old self. I love this idea for a theme and am really looking forward to other people’s write-ups. To date this in time, I’m currently 35 so this will be a 15 year flashback.

I’ll be honest, I don’t have many regrets in life. Some things haven’t gone as I’d have expected but everything has happened together to bring me to where I am now.

Some pieces of advice I would have sent back to my 20 year old self:

  • That degree you’re doing? Yeah, complete that because you’ll be paying it off for the next 15 years.
  • Don’t stress about your career yet, that will sort itself out in due time.
  • Don’t stop playing Rugby, you’ll get fat.
  • Enjoy your hair while you have it.
  • Finally: don’t worry, you’ll grow into your face;

There’s not a lot of professional suggestions there, that’s probably a mix of how happy I am with my career as it currently is as well as how much my life has changed over the last 15 years.

I quite like the introspective of this post so see you in another 5 years for another update!

Have you ever heard the phrase ‘don’t keep all of your eggs in one basket’? Yeah, people have learned that particular lesson the hard way. Don’t make the same mistake.

When you do a full backup you’re taking a copy of your database (and all of the data within) and storing it somewhere nice and safe. If something tragic happens to your database you will have a copy of it stored elsewhere. 

You can take a full backup manually using the GUI, just right click on your database and select Tasks -> Back Up…

You get to select things like what database you’re backing up, the type of backup you’re taking and the location you’re saving it to. To save it to the default location you can just click OK and watch it take a full backup.

If you have a little time have a read through the options you have. The main suggestion is to save this backup to a different drive than the one that your database is stored on (if your database is on D: then save your backups to another drive if you have one available).

You should also consider backup compression. It’s going to save you space when you schedule your backups regularly.

Talking about those scheduled backups, this is something that should be being done regularly. You’ll need to consider your own RTO/RPO goals to see how much data you’re willing to lose (in minutes). A good default is to set your full backups to nightly and your log backups to this figure (e.g. if you’re willing to lose up to 15 minutes of data then take log backups every 15 minutes).

Do you want an easy way to create your agent job to schedule that backup? At the top of the GUI when taking a backup there’s a little Script button and you can send the script to an agent job;

From here you can set your own SQL Server Agent job, the main thing you need to do is to give it a schedule (based upon your own RTO/RPO goals). Once that’s done you’re golden. Check back tomorrow and make sure your backups are working.

Don’t forget to practice restoring these backups elsewhere to make sure that they’re working without anything silly like corruption!

Do do do dooooo, do do do do do……..

You know that SQL Server 2008 or 2008 R2 box you’ve got sitting around on an old dusty server somewhere? You’ve got 3 months to upgrade this to a version of SQL Server that was released in the last decade.

Just in case you’ve missed it, on July 9th this year (2019) Microsoft is ending support for these versions of SQL Server.

But what does that mean for you?

Well, some of you might just not care. And that’s fine if it’s a decision you’ve come to when considering the risks of staying on an unsupported version of SQL Server. But bear in mind;

  1. You won’t be getting any security updates for your box.
  2. Microsoft won’t even touch you if you tried to raise a support case with them.
  3. You know those bugs they find and patch? Nope, you’re stuck with them now.
  4. You’re missing out on all of the features in later versions of SQL Server that used to be on enterprise edition only.

Worth the risk? It’s totally your decision. I certainly wouldn’t want to be running this risk.

If you’re like me and don’t want to accept these risks, consider this the 3 month mark to have these old instances upgraded or taken out of commission.

Good Luck!

Azure Data Studio supports installing extensions and has it’s own marketplace where you can get the full install details.

On your left bar, choose this funny little icon;

It will show what extensions you already have installed and also the marketplace. If you sort by below you’ll be able to scroll through the marketplace.

Click on any that take your fancy, it’ll open a page about it along with animations showing how they work (if the publisher has included them).

You can also build your own or download other extensions directly from source. Let’s go get Phil Scott’s pre-release of queryplan.show;


Go ahead and download the .vsix file. Once you’ve got that, open ADS, open the command palette (Ctrl+Shift+P) and find Extensions: Install from VSIX…

Navigate to your download folder and select the vsix file you’ve just downloaded.

If it’s not on the store you’ll probably see the following error message, I’m going to click Yes but you need to know that you trust the publisher (or you like living life dangerously, you rebel)

YOLO right?

Once it’s installed you’ll have to reboot Azure Data Studio to enable it

Once it’s reloaded you’ll see that your new extension is enabled

Nice work, you can now add Master With Azure Data Studio to your C.V.

Each extension will have different install instructions that will be shown on their GitHub page. Follow these and you’ll have access to those sweet extensions.

Ever wondered how many things Azure Data Studio can do? Open the command palette and have a scroll through.

Press Ctrl+Shift+P and you’ll see the command palette at the top of the screen. You’ll see your recently used commands appear at the top, the rest will be scrollable;

There’s a whole section for your installed extensions, a whole bunch for source control and loads of others. Have a dig through and see what’s interesting to you

You’ll use the command palette a lot, get comfortable with it and your life will be a lot easier.

If you’ve ever had to get involved in query performance you’ll have used execution plans. Azure Data Studio gives execution plans too but they’re a little tricky.

Let’s build a quick query and gather our execution plans. This query will work on any database;

In your query editor window the obvious button is the ‘Explain’ button. This works and will give you the estimated query plan.

You’ll recognise the execution plan that you see in ADS as it looks very much like the same version in SSMS.

Estimated plan in Azure Data Studio

Here’s what it would have looked like in SSMS

The same plan in SSMS

One advantage ADS has over SSMS is that you can also see the Top Operations natively.

This has been possible in Sentry One Plan Explorer for a long time but it’s also now native in ADS. It’s great when you’re looking at a massive plan and want to drill down into the major pain points quickly.

Getting the actual execution plan is a little more complicated. It’s not a nice easy button so you’ll want to get used to shortcuts.

press Ctrl+Shift+P to open the command palette and type ‘run’. You’ll notice the command to ‘Run Current Query with Actual Plan’. That’s the ticket;

You’ll also notice that there’s an even better shortcut. Ctrl+M is going to execute the query and give you the actual plan.

There is currently a gotcha with ADS actual execution plans where it only renders the last code block. There is an open github issue for this so keep an eye on it as there’s new stuff being released every month.