SQL Server Field Notes by Jeff Iannucci
If you’ve ever had to play administrator to a SQL Server instance, you’ve probably had to deal with TempDB data or log files that have grown unexpectedly. I know a lot of folks have had to do this, because the most read post on this site is “Help! My tempdb database won’t shrink!”
Consider it a right of passage of sorts. Also, consider that it might be entirely avoidable. Let me show you.
Continue reading “TempDB configuration for people in a hurry”
Presenting is difficult, and you already know that. It is also incredibly rewarding and beneficial to your career, but that’s not important for this post.
As difficult as it is to stand in front of a real or virtual room of folks and speak about a subject, there are several other difficult things about presenting that most of us don’t realize until we start preparing. There are technical concerns like trying to fiddle with Powerpoint or some other slide-related software or crafting demonstration code for scenarios.
There are also procedural concerns like figuring a title, an abstract/description of your session, and your biography. In fact, writing a simple biography can become one of the most frustrating and time-consuming parts of the entire process.
Let me help you with that.
This post is for the most recent #tsql2sday, a monthly exercise where a topic is proposed by a community member and everyone is invited to post their thoughts on the subject. This month Rob Volk has asked us “how would you explain database concepts to someone who’s not technologically savvy,” or relatedly, “explain databases like I’m five!”
I’m very excited to participate this month, because, as anyone who has seen my “Faster Transactions” presentation recently knows, I love using the analogy of SQL Server transactions as orders in a restaurant.
Let me tell you briefly about the “Le SQL Server” restaurant.
Something I’ve mentioned in previous career-related posts is the value of a mentor. In case it seemed more like a suggestion, let me be more direct about this: You need a mentor.
Yes, you. And me as well. Having a mentor is something of which there is no comparable value to any of our careers.
No matter how far along we are in our career paths, there are always others who have walked a similar path and are further ahead. And often, many of those folks are willing to help others just like me and you. And believe me, it would be incredibly foolish to not take them up on their offer.
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.
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”
By continuing to use the site, you agree to the use of cookies. more information
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.