Production Database Administration

Home
Production Database Administration

How to Install Azure Data Studio to Work with SQL Server

Last Updated July 23, 2020

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

Last Updated July 13, 2020

Brent Ozar

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_SendStartupEmail

Last Updated September 3, 2020

Brent Ozar

Monitoring

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…

What If You Really DO Need to Shrink a Database?

Last Updated July 14, 2020

Brent Ozar

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.

Last Updated June 17, 2020

Brent Ozar

Monitoring

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?

Last Updated April 11, 2020

Brent Ozar

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?

Last Updated April 5, 2020

Brent Ozar

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.

Last Updated March 4, 2020

Brent Ozar

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?

Last Updated February 6, 2020

Brent Ozar

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

Last Updated January 27, 2020

Brent Ozar

Monitoring

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

Last Updated January 7, 2020

Brent Ozar

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

Last Updated December 16, 2019

Brent Ozar

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:…

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

Last Updated September 21, 2019

Brent Ozar

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…

DBA Training Plan 22: Where to Learn More

Last Updated September 15, 2019

Brent Ozar

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

Last Updated September 18, 2019

Brent Ozar

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…

DBA Training Plan 20: Planning Your Next Server

Last Updated September 9, 2019

Brent Ozar

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…

DBA Training Plan 19: The L-Word, Licensing

Last Updated September 6, 2019

Brent Ozar

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

Last Updated September 3, 2019

Brent Ozar

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…

DBA Training Plan 17: Should You Partition Your Tables?

Last Updated September 2, 2019

Brent Ozar

DBA Training Plan, Partitioning

In the last episode, I talked about knowing when it’s time to scale out: identifying when our data is getting to be so large that we have to split it across multiple servers, and I explained why that is so challenging. But what about table partitioning – SQL Server’s ability to break up a single…

I would like to scale out my Porsche collection.

DBA Training Plan 16: Is It Time to Scale Out?

Last Updated August 27, 2019

Brent Ozar

Architecture, DBA Training Plan

In our last episode, we found out what your server was waiting on. In the closing remarks, I mentioned that you should find out which queries are causing the problem, and focus your mitigation efforts there. You might have circled back to the episode where we covered sp_BlitzCache, used a @SortOrder parameter that lines up…

Production Database Administration

Save your cart?