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.
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.)
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:
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…
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.
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:
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:
These recommendations are also set by the SQL Server 2022 setup wizard if you choose the recommended settings on the memory step.
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%
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…
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…
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:
How often do you run backups, corruption checking, index maintenance, and statistics updates?
Click here to take the anonymous poll.
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.
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?"
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:
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…
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.
If you right-click on a database in SQL Server Management Studio, you get an option to set Compatibility Level at the database level:
When you upgrade SQL Server or you want to improve performance, which option should you choose? Should you always go with the newest compatibility level? If you have an old application, can you upgrade SQL Server but still keep the old compat level without the vendor knowing? Let's hit the common questions.
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?
You've been working hard all month to get through our newest class, Fundamentals of PowerShell for DBAs. This week, it all starts to come together as you put together PowerShell plus SQL Server to automate tasks on a regular basis:
March 27, Mon: Objects and Namespaces
March 28, Tues: PowerShell and the SQL Server Agent
March 29, Weds: Creating Agent Jobs
March 30, Thurs: Scheduling PowerShell Job Scripts
March 31, Fri: Auditing Permissions with PowerShell and Demos
