SQL Server Management Studio v21 Preview 1 is out and let's give it a quick whirl. It's installed with the Visual Studio installer. That doesn't mean Visual Studio is installed or required - it's not - it's just that the SSMS team is leveraging the work that the VS folks have put in over the years.
This month on the blog is Community Tools Month, and I'm going to be talking about some of the most useful and influential tools out there.
You can't learn them all - *I* can't learn them all - because there just aren't enough hours in the day to do your work, keep your skills sharp, take care of yourself, and take care of the ones you love.
DBA.StackExchange.com is a Stack Overflow site that's specifically targeted at database administrators. There tend to be some pretty interesting questions in there - not just for SQL Server, but all kinds of databases - but at the same time, there's also a lot of noise. To help find fun questions, I use The Power of…
If you're still using sp_who, sp_who2, and Activity Monitor, listen up: there's a great free replacement, and it's now open source, too!
sp_WhoIsActive is a much more powerful way to analyze what's happening right now on your server. Here's what you need to know:
Stack: Dumped
Wanna learn the schema of the free Stack Overflow public database? If you want the Full Nelson, it's all documented here. That's a great resource if you're looking to learn more, but generally when you're looking at a data dump and building up demo queries, you wanna get an idea of where the data skews, and how the tables relate.
The nice folks over at StackOverflow.com (shout out, @onlybluefeet) updated their public data dump last month. This time around, I'm giving you two options:
Full size circa 2017/12: 19GB torrent (magnet.) Expands to a ~137GB SQL Server 2008 database. Because it's so large, we distribute it with BitTorrent - if you're new to that, here's detailed BitTorrent instructions.
The nice folks at Stack Overflow publish their entire data set (data included) in XML format. It's tons of fun for demos, but you need a way to get it into a relational database.
The Stack Overflow Data Dump Importer (SODDI) makes this point-and-click easy. Just download the latest release, install it, download the XML exports, and you can import it into MySQL, SQLite, or Microsoft SQL Server.
In our work as consultants, we see a lot of shops that have a monitoring tool, and yet they're still not able to get to the root cause of a SQL Server performance or reliability issue. In many of these cases, the problem isn't the people: the problem is that they bought a tool that doesn't do what they think it'll do.
Building stuff
I sometimes really hate coming up with ideas, and much prefer someone to just say "I want this" so I can go out into the world and figure out how to do that. Occasionally though, I realize that I'm not going to get all that much direction. That's what happened with sp_BlitzQueryStore.
For some of you, the headline is self-explanatory, and you just want the URL, so here it is: https://github.com/olahallengren/sql-server-maintenance-solution For the rest of you, here's what it means. What are Ola Hallengren's maintenance scripts? Ola Hallengren's maintenance scripts are a widely used replacement for maintenance plans. His backup, integrity check, and index optimization stored procedures are…
I hate rebuilding indexes
There. I said it. It's not fun. I don't care all that much for reorgs, either. They're less intrusive, but man, that LOB compaction stuff can really be time consuming. What I do like is updating statistics. Doing that can be the kick in the bad plan pants that you need to get things running smoothly again.
With SQL Server AlwaysOn Availability Groups, you can offload backups to a replica rather than running them on the primary. Here's how to do it:
1. Install Ola Hallengren's utility scripts on all of the replicas. During the install, it creates a laundry list of SQL Agent jobs, but doesn't set up schedules for any of them. More on that in a couple of steps.
If you're a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren's free database maintenance scripts. They're better than yours (trust me), and they give you more flexibility than built-in maintenance plans.
However, the index maintenance defaults aren't good for everyone. Here's how they ship:
[crayon-6a8699b477536172742053/]
The defaults on some of these parameters are a little tricky:
