Production Database Administration

Home
Production Database Administration

How to Trace a Nested Stored Procedure Without Using Profiler

Last Updated February 16, 2021

You’ve got a stored procedure that calls other stored procedures that build dynamic SQL that call other functions, and there’s so much calling going on that you would like to phone a friend. Your query plans look like piles of spaghetti, and when you zoom in, you just see more tiny piles of tiny spaghetti.…

What’s a Size-of-Data Operation?

Last Updated February 9, 2021

Brent Ozar

Production Database Administration

Database professionals say, “That’s slow because it’s a size-of-data operation.” A size-of-data operation is when the database has to write all of the affected data again. What you’re asking for might seem small, but for the database, it causes a lot of work. For example, let’s say you live in a strict household, and your…

Free Webcast: Help! My SQL Server Maintenance is Taking Too Long!

Last Updated January 22, 2021

Brent Ozar

Backup and Recovery, Company News, Index Maintenance, Production Database Administration

“Time for a smoke break.” You manage growing SQL Server databases with shrinking nightly maintenance windows. You just don’t have enough time left each night to do the necessary backups, corruption checking, index maintenance, and data jobs that your users and apps want to run. Cloud storage isn’t helping the problem, either. Stop playing Tetris…

Frequently Asked Questions from Office Hours

Last Updated December 17, 2020

Brent Ozar

Production Database Administration

In my Twitch & YouTube live streams, some questions seem to come up every week. I’m not expecting this post to stop ’em by any means – most folks on the live stream don’t seem to read the blog regularly – but I thought I’d share ’em here. Q: Why is the same query sometimes…

New Class: Fundamentals of TempDB – Instant Replays Ready Now

Last Updated December 7, 2020

Brent Ozar

TempDB, Videos

Yesterday, I taught my first Fundamentals of TempDB class, and as always, you can watch the Instant Replays right after class completes. Here’s the abstract: You’ve been working with Microsoft SQL Server for a couple of years, and you know a little bit about TempDB. You’ve heard that temp tables and table variables have different…

[Video] How to Troubleshoot Someone Else’s Temp Table Contents

Last Updated November 12, 2020

Brent Ozar

TempDB, Videos

Let’s say you need to troubleshoot someone’s query performance, and they’re using temp tables. You want to peek in at their temp table contents from another session while their query is running. SQL Server makes that a little bit challenging. Nic Neufeld showed how to use DBCC PAGE to do this, but today I’m going…

Paul White Explains Temp Table Caching 3 Ways

Last Updated November 9, 2020

Brent Ozar

TempDB

Paul White, Debugger of SQL Server Paul White (Blog, @SQL_Kiwi) is a legendary SQL Server blogger who’s written a ton of good material. You should read all of it. You won’t, and I can say that with authority because…I don’t read it all either. It’s too much. But when your job eventually requires you to…

Announcing a New Live Online Class: Fundamentals of TempDB

Last Updated November 30, 2020

Brent Ozar

Company News, TempDB

You’ve been working with Microsoft SQL Server for a couple of years, and you know a little bit about TempDB. You’ve heard that temp tables and table variables have different performance characteristics than regular user database tables, and you’ve heard that you’re supposed to have multiple data files. You’re wondering what exactly goes on behind…

Databases Are Like Walk-In Coolers. #tsql2sday

Last Updated October 13, 2020

Brent Ozar

Production Database Administration

In the back of a restaurant, there’s a walk-in cooler (or a few of ’em.) When a chef needs to make something, she goes into the walk-in cooler, gathers the necessary ingredients, and then comes out to the kitchen to do the cooking. She doesn’t cook inside the walk-in – that wouldn’t be efficient. She just…

Updated Fundamentals of Database Administration Class: Just $39

Last Updated August 23, 2020

Brent Ozar

Production Database Administration

You’re a systems administrator or developer who’s been stuck in charge of the SQL Server. You have a couple of production SQL Servers, no high availability or disaster recovery, and around 100GB of data or less on each server. You’ve never been to a “real” SQL Server training class, and you’ve been Googling your way…

How to Find Out Whose Queries are Using The Most CPU

Last Updated August 10, 2020

Brent Ozar

Monitoring

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…

SQL Server Problems We Don’t Have Anymore

Last Updated August 5, 2020

Brent Ozar

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

Last Updated July 23, 2020

Brent Ozar

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

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…

Production Database Administration

Save your cart?