As you can tell from previous posts, I’ve taken a few Microsoft Certification exams here in 2020. As you may have also heard, there’s been this Coronavirus pandemic happening at the same time, which means any such exam taken since mid-March of this year has to be done online and not in the comfort of a test taking center.

I’ve taken four of these exams so far this year from the comfort of my home. It’s definitely different from taking an exam at a testing center. If you’re interested in learning a bit more, click on through.

Continue reading “Online certification exams are definitely different” →

According to the myriad of statistics used to track such things, the post “How I passed the 70-761 certification exam” has been one of the most popular on this site in recent months. If this means folks are studying for that exam, then I thought I’d post a similar post for 70-762 since I passed that one as well.

This exam was originally scheduled for retirement at the end of June, but now you can attempt this certification until January 31, 2021.

If you’re motivated to study for this exam, let me share a bit to try to help you.

Continue reading “How I passed the 70-762 certification exam” →

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 “Query Store capture note: All does not mean what you think it means” →

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 “Why can’t I remove this extra transaction log file?” →

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 “Removing ad hoc plans from Query Store” →

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 “Using Erik Darling’s sp_pressure_detector to resolve CPU issues” →

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 “How I passed the 70-761 certification exam” →