Posts by Brent Ozar

Brent Ozar

2781 posts

https://www.brentozar.com

I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I'm based out of Las Vegas. He/him.

Using Triggers to Replace Scalar UDFs on Computed Columns

Last Updated October 20, 2020

Brent Ozar

T-SQL

Your database is riddled with computed columns whose definition includes a scalar user-defined function. Even up to & including SQL Server 2019, which boasts faster scalar function processing, any table that includes a scalar function cause all access to that table to go single-threaded. In that case, a trigger can actually be a great replacement.…

Building SQL ConstantCare®: What Database Growths Do You Care About?

Last Updated October 15, 2020

Brent Ozar

SQL ConstantCare

SQL ConstantCare® alerts you when your databases have unusual growths. No, not like that one on your neck. And you should really get that checked out. No, I’m talking about a database that suddenly jumps in size, making you suspicious about whether somebody created a backup table or built a set of ridiculously large indexes.…

Updated First Responder Kit and Consultant Toolkit for October 2020

Last Updated October 11, 2020

Brent Ozar

First Responder Kit Updates

In preparation for this month’s new Fundamentals of Columnstore class, sp_BlitzIndex has a few cool new tricks! I’ll show you how to use ’em for free in the upcoming How I Use the First Responder Kit class, too. Columnstore indexes are kinda like an index on every column (called a segment), and plus they’re kinda…

Free Webcast on Thursday: Avoiding Deadlocks with Query Tuning

Last Updated October 11, 2020

Brent Ozar

Locking, Blocking, and Isolation Levels

To fix blocking & deadlocks, you have 3 tools: Have enough indexes to make your queries fast, but not so many that they slow down delete/update/insert operations. (I cover that in the Mastering Index Tuning class.) Use the right isolation level for your app’s needs. (I cover that in the Mastering Server Tuning class.) Tuning…

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…

Free Live Fundamentals Classes, Registration Open Now

Last Updated November 30, 2020

Brent Ozar

Videos

Update Oct 29th – classes are over. Thanks for joining in! Well, this quarantine just isn’t letting up, so it’s time for another round of free training. I’m running 3 free live classes on October 26-28: October 26: Fundamentals of Index Tuning – 9AM-5 Eastern – iCal October 27: Fundamentals of Query Tuning – 9AM-5 Eastern– iCal October 28: How I Use the First Responder…

Woohoo! We Hit 25,000 YouTube Subscribers.

Last Updated October 10, 2020

Brent Ozar

Videos

As I finished this weekend’s live stream, I noticed that we crossed a big number. The Brent Ozar Unlimited YouTube channel now has over 25,000 subscribers. Well, that’s kinda awesome. I’ve had a great time doing all kinds of free live streams during the quarantines, and it’s been super fulfilling seeing so many folks show…

What Is the SQL Server CEIP Service?

Last Updated October 7, 2020

Brent Ozar

SQL Server

The CEIP Service is the Customer Experience Improvement Program that sends telemetry data back to Microsoft. When you install Microsoft SQL Server, the CEIP Service is installed too. You can’t disable it during installation: it shows up in your services list whether you want it or not. You know it’s high quality when they can’t…

Poor Man’s Resource Governor: Database-Scoped Configurations

Last Updated September 26, 2020

Brent Ozar

Bad Idea Jeans

Let’s say you had report queries that were going wildly parallel, and you wanted to put a stop to it, but you couldn’t afford SQL Server Enterprise Edition. You could do is create databases with different MAXDOP settings: CREATE DATABASE [MAXDOP4] GO USE [MAXDOP4] GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4; GO CREATE…

Free Webcast on Wednesday: The New Robot DBAs in SQL Server 2017, 2019, and Azure

Last Updated October 3, 2020

Brent Ozar

Conferences and Classes

The robots are really just people wearing metallic masks. You’ve been hearing about all these robots that are coming to take your job. They’re going to automate all the performance tuning and make your life way easier, right? Or harder, I guess…since you’ll be looking for a job. Thing is, most of that is just…

Want to use columnstore indexes? Take the ColumnScore test.

Last Updated September 27, 2020

Brent Ozar

Columnstore Indexes

When columnstore indexes first came out in SQL Server 2012, they didn’t get a lot of adoption. Adding a columnstore index made your entire table read-only. I often talk about how indexing is a tradeoff between fast reads and slow writes, but not a lot of folks can make their writes quite that slow. Thankfully, Microsoft…

Announcing a New Class: Fundamentals of Columnstore

Last Updated September 26, 2020

Brent Ozar

Columnstore Indexes

Your report queries are too slow. Will columnstore indexes help? You’ve tried throwing some hardware at it: your production SQL Server has 12 CPU cores or more, 128GB RAM, and SQL Server 2016 or newer. It’s still not enough to handle your growing data. It’s already up over 250GB, and they’re not letting you purge…

Building SQL ConstantCare®: Warning You About New Large Tables & Indexes

Last Updated September 24, 2020

Brent Ozar

SQL ConstantCare

When I’m doing a SQL Server health check, one of the things I like showing clients is a list of their top tables by size. Inevitably, there are a bunch of tables that: Tables whose names start with “temp” or “backup” or “just_in_case” Tables with no clustered index that were clearly created with a select-into,…

The 4 Presentations I’m Proudest Of, and What Inspired Them

Last Updated September 24, 2020

Brent Ozar

Videos, Writing and Presenting

I was thinking back about the presentations I’ve been the most proud of over the years. I’ve written and performed a *lot* of stuff over time, but here are some of the free public presentations I’m the most proud of, and the stories of how they came to be. #4: Watch Brent Tune Queries (2014)…

I Would Love a “Cost Threshold for Recompile” Setting.

Last Updated May 5, 2022

Brent Ozar

Parameter Sniffing

I’m tired of sniffing your parameters. In environments where complex queries can get bad plans due to parameter sniffing, it would help to say that all queries with an estimated cost over X should be recompiled every time they run. For example, in environments where most of my workload is small OLTP queries, I’m fine…

SQL Server Suddenly Frozen? You Might Be Snapshotting Too Many Databases.

Last Updated October 7, 2025

Brent Ozar

Backup and Recovery

Snapshot backup tools like Azure Site Recovery and Veeam are great for sysadmins. They let you quickly replicate a virtual machine to somewhere else without knowing too much about the server’s contents. To pull it off, they use the Windows Volume Shadow Copy Service to do snapshot backups – often referred to as VSS snaps.…

Unindexed Foreign Keys Can Make Performance Worse.

Last Updated December 29, 2021

Brent Ozar

Foreign Keys

I know. You, dear reader, saw that title and you came in here because you’re furious. You want foreign key relationships configured in all of your tables to prevent bad data from getting in. But you gotta make sure to index them, too. Let’s take the Stack Overflow database – I’m using the 50GB Stack…

How to Create a Table with a Partitioned Clustered Columnstore Index

Last Updated September 16, 2020

Brent Ozar

T-SQL

If you need to create a table and you want it to be partitioned right from the start, AND you want it to have a clustered columnstore index, here’s a creation T-SQL example: CREATE TABLE dbo.MyTableName( Id int IDENTITY(1,1), SalesDate datetime NOT NULL, INDEX MyIndexName CLUSTERED COLUMNSTORE ) ON ps_MyPartitionFunction(SalesDate); GO 123456 CREATE TABLE dbo.MyTableName(…

Free Webcast Wednesday: Pushing the Envelope with Indexing for Edge Case Performance

Last Updated September 13, 2020

Brent Ozar

Indexing

Most of the time, conventional clustered and non-clustered indexes work just fine – but not all the time. When you really need to push performance, hand-crafted special index types can give you an amazing boost. Join Microsoft Certified Master, Brent Ozar, to learn the right use cases for filtered indexes, indexed views, computed columns, table…

Updated First Responder Kit and Consultant Toolkit for September 2020

Last Updated September 14, 2020

Brent Ozar

First Responder Kit Updates

Let’s give a big round of virtual germ-free applause to Adrian Buckman, Erik Darling, Garry Bargsley, Greg Dodd, Ian Manton, John McCall, Kuldar Andares, Pierre Letter, Stephen Anslow, Tom Butler, and Walden Leverich for volunteering their time to help make your job easier this month. Thanks to them, the September release is chock full of features – I’m not even going to try to sum…