Posts by Brent Ozar

Home
Brent Ozar

Brent Ozar

1985 posts

https://www.brentozar.com

I make Microsoft SQL Server go faster. I love teaching, travel, and laughing. I live in Iceland with my wife Erika.

Free Webcast: Planning a SQL Server to Azure Migration in 2021

Last Updated February 25, 2021

Brent Ozar

Microsoft Azure

You manage database servers for your company, and they’re thinking about migrating everything to Microsoft Azure. You’ve searched the web for information and advice, but everything you’re finding is outdated. You just want a list of things to review on your servers, an overview of what won’t work in the cloud, and how to pick…

New SQL ConstantCare® Feature: One Serious Query to Tune

Last Updated February 20, 2021

Brent Ozar

SQL ConstantCare

Our monitoring tool, SQL ConstantCare®, is different. Some monitoring tools send you a whole bunch of emails for every little thing that’s happening on every one of your server – and having you end up just creating an Outlook rule to dump them all into a folder that you never read. PAGE LIFE EXPECTANCY IS…

Updated First Responder Kit and Consultant Toolkit for February 2021

Last Updated February 22, 2021

Brent Ozar

First Responder Kit Updates

Along with the usual bug fixes & improvements, this month’s release includes a brand new sp_BlitzAnalysis script from Adrian Buckman. It’s for folks who have an Agent job to log sp_BlitzFirst to tables every 15 minutes, saving wait stats, Perfmon counters, file stats, etc into tables. sp_BlitzAnalysis reads those tables and sums up activity in…

How to Trace a Nested Stored Procedure Without Using Profiler

Last Updated February 16, 2021

Brent Ozar

Extended Events

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

Changing Statistics Cause Longer Compilation Times

Last Updated February 13, 2021

Brent Ozar

Execution Plans, Statistics

I need to be up front with you, dear reader, and tell you that you’re probably never going to need to know this. I try to blog about stuff people need to know to get their job done – things that will be genuinely useful in your day-to-day performance tuning and management of SQL Server.…

What’s New & Undocumented in SQL Server 2019 Cumulative Update 9

Last Updated February 15, 2021

Brent Ozar

SQL Server 2019

Last week’s SQL Server 2019 Cumulative Update 9 snuck in some undocumented things. We got two new DMVs for Always On Availability Groups, sys.dm_hadr_cached_replica_states and sys.dm_hadr_cached_database_replica_states. They have a subset of data from other AG DMVs: I sure wish I could point you to the documentation or KB article on those. But we have no…

Query Store is the Flight Data Recorder. It’s Obvious What We Need Next.

Last Updated February 9, 2021

Brent Ozar

Humor

When Microsoft brought out Query Store, they wrote: Similar to an airplane’s flight data recorder, Query Store collects and presents detailed historic information about all queries, greatly simplifying performance forensics by reducing the time to diagnose and resolve issues. I have a straight face under here, for real It certainly works, making it easier for…

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…

I Sat Down for an Interview with Forrest Brazeal.

Last Updated February 4, 2021

Brent Ozar

Cloud Computing

Here’s how the interview started: Forrest Brazeal: Brent, you’re a performance specialist who’s worked with massive SQL Server deployments; how does it make you feel when you hear cloud architects saying things like “relational databases don’t scale?” What do you wish they knew? “Time for a smoke break.” Brent Ozar: They’re right. Nothing scales worth a…

Partitioned Tables Cause Longer Plan Compilation Times.

Last Updated February 26, 2021

Brent Ozar

Partitioning

Folks sometimes ask me, “When a table has more indexes, and SQL Server has more decisions to make, does that slow down execution plan generation?” Well, maybe, but the table design choice that really screws you on compilation time is partitioning. If you choose to partition your tables, even tiny simple queries can cause dramatically higher…

Unusual Parameter Sniffing: Big Problems with Small Data

Last Updated February 3, 2021

Brent Ozar

Parameter Sniffing

Normally when we think about parameter sniffing, we think about a tiny data vs big data problem: the tiny data’s plan goes in first, and when we try to process big data, performance is terrible. But sometimes, it’s the exact opposite. I’ll start with the large Stack Overflow database (I’m using the 2018-06 version, but…

My Live Streaming Setup for 2021: Iceland Edition

Last Updated February 1, 2021

Brent Ozar

Home Office, Streaming

Erika and I moved to Iceland for a while. For those of you who don’t follow my personal blog, here’s why we moved, and how we got here with a teleworker visa. You can spy on our adventures via my Instagram, Facebook, or Twitter. We’re working half the time while we live here, and sightseeing the…

Can You Get Parameter Sniffing on Updates and Deletes?

Last Updated January 27, 2021

Brent Ozar

Execution Plans

Sure you can – parameter sniffing is everywhere. Anytime that you have one piece of code that needs to handle a varying number of rows, you’re probably gonna have to worry about parameter sniffing. I’ll start with any Stack Overflow database and run a couple of non-parameterized update statements. I’m using literals here, not parameters:…

“But Surely NOLOCK Is Okay If No One’s Changing Data, Right?”

Last Updated January 25, 2021

Brent Ozar

Locking, Blocking, and Isolation Levels

Some of y’all, bless your hearts, are really, really, really in love with NOLOCK. I’ve shown you how you get incorrect results when someone’s updating the rows, and I’ve shown how you get wrong-o results when someone’s updating unrelated rows. It doesn’t matter – there’s always one of you out there who believes NOLOCK is okay…

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…

What SQL Server Feature Do You Wish Would Go Away?

Last Updated January 21, 2021

Brent Ozar

Humor

I asked a two-part question on Twitter: if you could only remove one feature from SQL, why would it be cursors? If you could only remove one feature from SQL, why would it be cursors? — Brent Ozar (@BrentO) January 20, 2021 The responses were great: Auto-shrink does not agree — Warner Chaves (@warchav) January…

Query Hints You Can Use to Avoid Blocking

Last Updated January 21, 2021

Brent Ozar

Locking, Blocking, and Isolation Levels

No, not NOLOCK. You get the wrong query results, even when your query results aren’t supposed to be changing. I’m talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm. SET LOCK_TIMEOUT 1000 – if you run this…

Updated First Responder Kit and Consultant Toolkit for January 2021

Last Updated January 21, 2021

Brent Ozar

First Responder Kit Updates, Videos

You know how a lot of app release notes say “bug fixes and improvements”? We’re not gonna lie: this one’s all bug fixes. Huge shout out to the community contributors for catching stuff and contributing fixes. New behavior for SQL Server 2008 & R2 users: sp_BlitzLock has never worked on SQL Server 2008, but starting…

We Lost Gareth Swanepoel.

Last Updated January 14, 2021

Brent Ozar

Community

Thirteen years ago, I met Gareth Swanepoel (@GarethSwan) for the first time, interviewed him for his first real sysadmin job, and … turned him down. Gareth Swanepoel circa 2007 Thankfully, my manager overruled me and hired him anyway. Thank goodness – and thank goodness Gareth forgave me for my incredibly bad judgment, hahaha. Gareth proved…

Which Microsoft Certification Should You Get?

Last Updated January 4, 2021

Brent Ozar

Professional Development

You work with data, and you’re thinking about getting a Microsoft certification. Which one should you get? If you need the certification to get a job… Then get the certification you see mentioned the most often in the descriptions of the job you’re looking for. This is just a little tricky because certifications shown on…