As many of you know, Query Store is a wonderful tool for identifying query regressions, which in turn makes it an equally wonderful tool for migrations involving an upgrade to SQL Server. You can toggle between compatibility levels, observe which queries perform worse, and then proceed to troubleshoot any, uh, troublesome queries.

But what if you wanted to use Query Store for a different reason in a migration? What if to see which stored procedures are actually being used, and which are just sitting there unused like that box of commercial toilet paper you panic purchased last month?

What if you set the capture mode to “All”, but then noticed you weren’t actually capturing All of the queries in your database?

Continue reading →

Before this post gets started, let’s get past the title. I know what many of you are thinking: what in the name of Paul Randal would make someone have an EXTRA log file? The short answer is: desperate times.

True, there’s no advantage to having more than one log file, but sometimes that one file grows suddenly and fills up the drive in the middle of a transaction and you’re stuck with those dreaded “THE DATABASE IS DOWN!!!” tickets until that transaction finishes. So, in the heat of the moment, you hit the panic button and create ANOTHER log file on a different drive.

Then, minutes, hours, or even weeks later, you want to put the universe back in order by resizing the original log file and removing the extra one. But what if you find you can’t remove that extra one, no matter what you try to do?

That’s what happened to me. Let me show you how I solved this riddle.

Continue reading →

This is not a post about the “optimize for ad hoc workloads” setting on your favorite SQL Server instance, but that is a good place to start.

Lots of folks that I’m too busy/tired/lazy to link to have promoted using this setting as a best practice for keeping your plan cache from bloating up like Violet Beauregarde in the Chocolate Factory. The theory goes, if a query is just run once, why do you even care about keeping the execution plan? Just bin it.

That said, this IS a post about Query Store, that nifty tool in the newer versions of SQL Server. It’s got a lot of functionality, such as persisting nearly all plans, which includes those ad hoc queries whose plans you probably didn’t want in the first place.

Yeah, unfortunately, there’s nothing like “optimize for ad hoc workloads” for Query Store. But for those of you who don’t want Query Store drowning in all your ad hoc nonsense, let me share a little something.

Continue reading →

Last Friday I spent my lunch break going through some links I had marked for future reading. One of them was a post earlier this month by Erik Darling about a new troubleshooting tool he made called “sp_pressure_detector,” including a video showing how to use it.

Why yes, I do often spend my lunch breaks watching videos of folks executing queries. Is it really so strange?

Anyhow, Darling’s tool is designed to provide five data sets showing a quick view of memory and processor usage based on several dynamic management views (DMVs). It’s gives you a quick look into what’s going on when the CPU and/or memory of your SQL Server instance are feeling discomfort.

Why do I mention this? Because little did I know the very next day I would be presented with a fine opportunity to use this tool. And by opportunity I mean a server on fire. (Well, not literally, but still…)

Continue reading →

FUN FACT: I have been a Microsoft Certified Professional for SQL Server for over 20 years. Second FUN FACT: the last time I took a Microsoft certification exam was 1999. Add these two together and you can probably deduce I had most recently passed exams relating to SQL Server 7.0

Oddly enough, I am 100% sure these facts have no current bearing on my abilities or career prospects.

Anyhow, after contemplating how in 2019 I had completed my university studies (at long last!) and had started presenting sessions relating to SQL Server, I decided the next challenge to tackle would be passing some current Microsoft certification exams. I figured if I am going to be speaking about the subject publicly I should probably see if I needed to fill in some knowledge gaps.

As it turned out preparing and passing the first exam took me just a handful of weeks. If you’ve recently considered taking this exam yourself, let me share with you how I did this.

Continue reading →

Twitter is a strange place. I mean, aside from all the outrage practice and animal videos.

Last week I sent out a tweet about how I used dbatools to update apply cumulative updates to 13 SQL Server instance in 12 minutes. It got nine retweets and over 40 likes. Now, 40 likes in the grand scheme isn’t much, but for a guy like me with a handful of followers – it certainly gets your attention.

In one of the replies I was asked to put a post together about how I did such a thing (the updates, not the tweet), so here we are. If you’ve never used PowerShell, this might be where you could start. It’s absurdly simple. I mean, it was only five lines in PowerShell, and you could easily do it in less than that.

Continue reading →

For the most part, the default configurations for SQL Server are pretty good. Yeah, there are a few you should definitely change (like say, the ones involving parallelism), but most of the others can be, uh, good enough as is.

But no one really likes “good enough,” right?

So most of us tweak some of these defaults to improve performance. Measure, adjust, document improvement, get a raise. Rinse repeat. And of course, if you’ve spend any time in SQL Server circles you’ll quickly learn one of the biggest improvements we can make for SQL Server isn’t even in SQL Server. It’s the Windows Power setting, which by default is set to “if you don’t need anything right now I’m gonna lay my processors down for a rest.”

Continue reading →

It’s been a while since I’ve had a new post here, and that’s largely due to the quantity of other things on which I’ve been working. This list of busy-ness – presumably completely uninteresting to you – includes making additional presentations for SQL Server training events.

Which makes for a nice intro to what I wanted to write about today – my first session I presented at a SQL Saturday last weekend in Orange County, California. I suppose some may read this a cautionary tale, but my intention is to let you know that even when you’re neck deep in the weeds (and I’m not even talking just about presentations here) you can still survive as long as you focus on one thing: delivering solutions to your audience.

But before I get to the parts where things went wrong…

Continue reading →

Maybe more people would use the Central Management Server feature of Management Studio if it had a name that didn’t sound as utilitarian as a heat pump. Something like PowerServer. Or…Mega Instance Policy Ranger. Or…One Query To Rule Them All.

Because that’s what it really is.

Continue reading →