Product has been added to your cart.

Production Database Administration

How to Find Out Whose Queries are Using The Most CPU

August 11, 2020

You’ve got a bunch of users on the same SQL Server, and you wanna know who’s using the most resources. SQL Server doesn’t make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can’t intuitively tell which USERS are. To solve this…

Brent Reading Book

SQL Server Problems We Don’t Have Anymore

August 5, 2020

Production Database Administration

We have completely new problems today. I was cleaning out a folder of presentations and I realized there are a whole slew of problems that we had to worry about ten years ago, and they just hardly ever come up today. Virtual Log Files (VLFs) – it’s been years since I’ve run across a server…

How to Install Azure Data Studio to Work with SQL Server

July 22, 2020

Production Database Administration

Download Azure Data Studio on your desktop. Do this on your desktop, not the SQL Server itself. You always want to run management tools from your own machine to reduce the overhead on the SQL Server itself. After it downloads, install it – just accept the defaults. During the installation process, it may ask: You…

How to Get Started with SQL Server Management Studio

July 21, 2020

Production Database Administration

In the big picture, you need to: Download the free Microsoft SQL Server Management Studio and install it, accepting the defaults Connect to your SQL Server Create a low-privileged login so that you don’t accidentally drop objects Learn to switch back & forth between the low-privileged account and your regular one Let’s get started! 1.…

Get Alerted When Your SQL Server Restarts with sp_SendStartupMail

July 15, 2020

sp_SendStartupMail

When your SQL Server restarts, how do you find out? I’ve seen a few senior DBAs do a really smart trick: they write a stored procedure to send them an email, and then they mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. (You could…

Eyes up here, kid

What If You Really DO Need to Shrink a Database?

July 14, 2020

Production Database Administration

You’ve heard that shrinking a database is bad because it introduces both external and internal fragmentation, it causes blocking, it causes transaction log growth while it runs, and it’s slow and single-threaded. You understand that if it’s just a matter of 10-20-30% of a database, and the database is only 100-200GB, you might as well…

Page Life Expectancy Doesn’t Mean Jack, and You Should Stop Looking At It.

June 17, 2020

Page Life Expectancy is a Perfmon counter that’s supposed to track how long pages stay in memory before they get flushed out to make room for other pages that are needed instead. Paul Randal has blogged this a few times, and rather than rehash ’em, I’d rather point you to a couple of his roundups:…

[Video] Can You Tell When a Rollback Will Finish?

April 23, 2020

Production Database Administration

I happened to see this DBA.StackExchange.com question about killing a big delete, and I really felt for the admin involved: Four days ago a user ran the command below on a table with 400,000,000 rows. It’s still running and the log file is increasing in size. Hoo boy. When you kill a query, it’s hard…

[Video] How Can I Measure If My Database Does More Reads Than Writes?

April 7, 2020

Production Database Administration, Videos

When someone asks you, “Is this database read-intensive or write-intensive?” you probably look at sys.dm_io_virtual_file_stats to measure the file activity – but that isn’t really correct. After all, your users run a lot of queries all day that are simply satisfied from the buffer pool. SQL Server caches your data so it doesn’t have to hit…

That's it, I'm rolling back to SQL Server 6.5

Breaking News: SQL Server 2019 CU2 Breaks Agent.

March 1, 2020

Breaking News, SQL Server 2019

That’s it, I’m rolling back to SQL Server 6.5 You reported it here in the comments, and over at SQLServerUpdates, and in this DBA.StackExchange.com question. The official word is in: yep, SQL Server 2019 Cumulative Update 2 breaks Agent jobs on some servers, causing them not to run. That official word is only in that…

Which SQL Server Enterprise Edition Should I Download?

February 6, 2020

Production Database Administration

When you’re downloading SQL Server, it’s important to choose Enterprise Core, not Enterprise. The plain “Enterprise” one is limited to just 20 CPU cores. But let’s say you didn’t know that, and you waltzed over to the download page. There are two Enterprises listed, and it’s not really clear what the differences are between the two:…

Why Database Monitoring Tools Are So Hard to Interpret

January 27, 2020

SentryOne’s Greg Gonzalez recently published a post about “Why Wait Stats Alone Are Not Enough.” Sure, Greg’s obviously biased towards his own monitoring tool, but his post is really well-written and fair. I want to explain the same story, but from a different (and simplified) angle. Say we’ve got a really simple database server, and…

Three Reasons to (Temporarily) Change Max Worker Threads

January 8, 2020

Configuration Settings

When you go to the deli, there’s a “take a number” dispenser where you tear off a number and wait for your number to be called. If you walked into a deli and you saw 50 people lined up all waiting their turn, and you went to take a number and the dispenser was completely…

Microsoft’s Guidance on How to Set MAXDOP Has Changed

December 16, 2019

Production Database Administration

For years, SQL Server’s Maximum Degree of Parallelism (MAXDOP) – the number of cores that a parallel query could use – defaulted to 0, meaning unlimited. This led to rampant CXPACKET waits because queries went wild with parallelism. Overly wild, like me after six shots of tequila within an hour wild. (It’s not my fault:…

Yep, still the same smile.

DBA Training Plan 23: Congratulations! Now, It’s Your Turn.

September 23, 2019

DBA Training Plan, Professional Development

I bet you think you suck at databases. You’re sitting there on your computer, reading all kinds of blogs, watching presentations online, and you’re thinking, “Everybody knows more about databases than I do.” We all feel that way. There’s even a name for it: Impostor Syndrome. I have it too. For example, about a decade…

Live Class Season Pass

DBA Training Plan 22: Where to Learn More

September 17, 2019

DBA Training Plan, Professional Development

Whew. We’ve worked through a couple dozen posts together in this DBA Training Plan, and you’re still just getting warmed up. First, pick your specialty. Here’s some of the more popular specializations in the SQL Server business: Production administration – managing uptime, building new boxes, troubleshooting clusters, setting up storage, designing virtualization infrastructures Performance tuning…

DBA Training Plan 21: Building a New SQL Server

September 16, 2019

DBA Training Plan, High Availability

I have a secret setup checklist. Oh sure, our free First Responder Kit has a SQL Server setup checklist that walks you through a few things you need to do before you install, plus a few things to do immediately afterwards. That’s good. It’s actually pretty darned good. But it’s not the secret checklist. Gather round and cover your…

My new hardware

DBA Training Plan 20: Planning Your Next Server

September 12, 2019

Architecture, DBA Training Plan

When the business decides it’s time to give up fixing the old server because it’s not fast enough or reliable enough, stop. The worst thing you can do is jump directly to the hardware vendor’s site, check a few boxes, and order a new shiny server that ends up with exactly the same problems as…

SQL Server Licensing Guide

DBA Training Plan 19: The L-Word, Licensing

September 6, 2019

DBA Training Plan, Licensing

You’ve gotten through a lot of this training. You’ve tried fixing the problem with indexes, query tuning, and judicious application of various configuration switches. You’ve been watching webcasts and using free scripts, but… Things just aren’t getting better. Before you start browsing your cloud vendor’s sizing price list or your favorite server vendor’s build-and-price site,…

Mitch Bottell is preparing to take an exclusive lock on some BBQ.

DBA Training Plan 18: Managing Concurrency

September 4, 2019

DBA Training Plan, Locking, Blocking, and Isolation Levels

Locking isn’t a problem – queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won’t show up as a problem. (That’s one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…