News & Opinion
Breaking News: Hold Off on SSMS 22.11.0
There's a new SQL Server Management Studio update out today.
I'd hold off on it for now.
Category: Production Database Administration
Production DBA
Your Database Just Hit One Terabyte: Now What?
You were minding your own business, and all of a sudden it happened.
You glanced at file sizes one day, and your eyes got big. The numbers got a little large while you weren't looking. This is a great time to stop and think about a few changes to the way you're managing this database.
There Are Days When I Feel Like Giving Up on the Plan Cache and Query Store.
In theory, SQL Server performance monitoring is pretty simple:
Review the server's top wait types
Find the queries causing those wait types
Fix those queries, or improve the way the server reacts to them (indexes, settings, etc.)
Free SQL Server Performance Monitor App by Erik Darling
Let's hop right into it. I've got one lab server (named Colorful), so when I open the app, my estate overview isn't particularly interesting - yours will be:
Go into the server's details, and you see the top wait types over time:
Production DBA
Let’s Build a Better KILL. What Do We Need to Consider?
When there's a performance emergency, a lot of us perform the same manual tasks over and over. We run sp_BlitzWho or sp_WhoIsActive, look for blocking, long-running or out-of-control queries, and kill them. I'm going to build a new sp_kill for the First Responder Kit to make that easier, faster, and safer, and I want your…
Performance Tuning
The Version Store Won’t Clear If ANY Database Has Open Transactions.
Short story: what the title says.
This is especially problematic for folks who merge multiple databases onto the same server. All it takes is one badly-behaving application to leave its transactions open, and suddenly it causes the rest of the databases to run TempDB out of space. That app's transaction might not even be changing anything, and it might have never caused problems for that application before - but once it shares a TempDB with other apps, it causes cascading problems.
Performance Tuning
TempDB Filling Up? Try Resource Governor.
TempDB is one of the banes of my existence.
Anybody, anybody who can query your server can run a denial-of-service attack in a matter of seconds just by filling it up with a simple query:
Microsoft Now Recommends You Set Max Memory to 75% and Min Memory to 0%.
Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
Somehow I missed this a few years ago, and I bet a lot of y'all did too. Note the new "Recommended" column in the memory settings documentation:
Production DBA
Poll Results: Yes, Your DBAs Can Read the Data.
Last week, I asked if your database administrators could read all of the data in all databases. The results (which may be different from this post, because I'm writing the post ahead of time and the poll is still open):
Yes: 61%
Production DBA
Poll: Can Your DBAs Read the Data?
It's a simple question: can your database administrators read all of the data in all databases? Yes Yes, but we trust them not to - as in, we've got written policies in place, and they know they're not supposed to go poking around in the payroll tables or the human resources database Yes, if they…
Production DBA
Should Someone Try to Get a DBA Job?
There was an interesting post on Reddit yesterday asking what DBA jobs are like these days. Here's a snippet of the most important part: Our friend works for maybe 15 hours a week, on call for maybe 40 but is always running around not in front of his computer. She works odd hours, takes the…
Production DBA
Poll Results: How Often You’re Running Maintenance Tasks
Last week, I asked y'all how frequently you ran your full backups, corruption checks, index maintenance, and stats updates. The results after over 1,000 responses:
A few thoughts:
Production DBA
[Poll] How Often Do You Run Maintenance Tasks?
How often do you run backups, corruption checking, index maintenance, and statistics updates?
Click here to take the anonymous poll.
Production DBA
Copilot in SSMS is Kinda Like IE in SSMS
You are not ready for how weird my take is.
Last week at the PASS Summit conference in Seattle, Microsoft showed off the upcoming SQL Server Management Studio v21.
Production DBA
Why Generative AI Scares the Hell Out of DBAs
I was chatting with a client's DBA about this thought-provoking blog post about data governance in the age of generative AI. The DBA's concern was, "What if we hook up generative AI tools to the production database, and someone asks for, say, recommended salary ranges for our next CEO based on the current CEO's salary? Will the AI tool basically bypass our row-level security and give the querying person information that they shouldn't be allowed to see?"
Production DBA
Read This Before Your Users Install SSMS 20
SQL Server Management Studio 20 Preview 1 is out, and the new connection dialog has a big change:
When you click Connect, you're likely going to get an error:
Production DBA
ChatDBA: ChatGPT 4 Answers 5 Office Hours Questions
Every now and then, someone asks a question on PollGab about why I don't use ChatGPT to answer these questions. And sometimes, I wonder that myself - I wonder why people don't put their questions into ChatGPT, especially GPT 4, because it's pretty damned good. I did this a while ago using ChatGPT 3.5, which…
Production DBA
It’s Friday. I’m Not Answering These 14 Office Hours Questions.
Sometimes, I don't care if questions get a lot of upvotes at https://pollgab.com/room/brento - I just don't wanna answer 'em. They're not necessarily bad questions, but I'm just not interested in them, or I've answered them repeatedly, or I don't have a good answer.
Maybe y'all do, though, so I've numbered these. If you want to leave your answer in the comments, prefix it with the question number you're answering.
What Does Setting the SQL Server Compatibility Level Do?
Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
If you right-click on a database in SQL Server Management Studio, you get an option to set Compatibility Level at the database level:
Production DBA
Office Hours Speed Round, Easy Text Answers Edition
Some of the questions y'all post at https://pollgab.com/room/brento have easy one-line answers. Let's knock 'em out:
George: Hi Brent, what recently has been the most challenging/surprising/new-to-you performance issue you have encountered?
