Posts by Brent Ozar

Brent Ozar

1764 posts

https://www.brentozar.com

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

Updated First Responder Kit and Consultant Toolkit for February 2020

February 20, 2020

Brent Ozar

First Responder Kit Updates

No Comments

We spent Valentine’s Day removing the bugs from your chocolates. You’re welcome. To get the new version: Download the updated FirstResponderKit.zip Azure Data Studio users with the First Responder Kit extension: ctrl/command+shift+p, First Responder Kit: Import. Download the updated Consultant Toolkit in your account EU customers: check your email for the updated version from Gumroad,…

Interesting Undocumented Tidbits in SQL Server 2019 CU2

February 19, 2020

Brent Ozar

SQL Server 2019

SQL Server 2019 Cumulative Update 2 is out, and Microsoft snuck in a few things that they didn’t mention in the release notes. Contained Availability Groups continue to get new investments. In the lead-up to SQL Server 2019’s release, we got a few presentations at various conferences where Microsoft folks said we’d get the ability…

WHERE GETDATE() BETWEEN StartDate AND COALESCE(CancelDate, EndDate) Is Even Harder to Tune.

February 19, 2020

Brent Ozar

Development, Indexing

In my last post, we started with a memberships table, and each membership had start & end dates. I’m going to create the table and populate it with everyone having an active membership – their StartDate is the same as their Stack Overflow account creation date, and their EndDate is around a year or two…

WHERE GETDATE() BETWEEN StartDate AND EndDate Is Hard to Tune.

February 18, 2020

Brent Ozar

Development, Indexing

Say you’ve got a memberships (or policies) table, and each membership has start & end dates: Transact-SQL USE StackOverflow; GO DROP TABLE IF EXISTS dbo.UsersMemberships; CREATE TABLE dbo.UsersMemberships (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, UserId INT NOT NULL, StartDate DATETIME NOT NULL, EndDate DATETIME NOT NULL, CancelledEarlyDate DATETIME NULL); GO 1234567891011 USE StackOverflow;GODROP TABLE IF…

Recommended SQL Server Books, 2020 Edition

February 17, 2020

Brent Ozar

Book Reviews

If you’re the kind of person who likes to read books, here are some that I think would expand your horizons. These links are Amazon affiliate links, so I make a few cents when you buy through ’em. It’s your way of tipping me for the book recommendations, so thank you! T-SQL Books: Level 1:…

How to Pass a List of Values Into a Stored Procedure

February 13, 2020

Brent Ozar

T-SQL

Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here’s what the table looks like: And here’s what my starting stored procedure looks like: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsersByLocation @SearchLocation NVARCHAR(40) AS SELECT * FROM dbo.Users WHERE Location = @SearchLocation ORDER BY DisplayName;…

What Should I Submit to the PASS Summit?

February 12, 2020

Brent Ozar

#SQLPass

Our industry’s biggest conference, the PASS Summit 2020, just opened their pre-conference workshop call for speakers. I’m going to submit a couple of sessions, but I want to hear from you first: if you were going to try to convince your boss to buy you a pre-conference workshop at Summit, what would be the topics…

#TSQL2sday: My Life Hack is an Hourglass. Yes, an Hourglass.

February 11, 2020

Brent Ozar

SQL Server

For this month’s T-SQL Tuesday, Jess Pomfret asked for our favorite life hacks. My life hack is a half-hour hourglass. (Halfhourglass?) Seriously, an actual physical object: I keep it on my desk. Whenever I start working on a task, I flip the glass. After 30 minutes, I need to stop and assess the progress I’ve…

How to Pick a 2020 Microsoft Database Conference

February 10, 2020

Brent Ozar

Conferences and Classes

As a consultant and speaker, I’m lucky enough to go to a crazy number of SQL Server conferences. Here’s what I think of each mainstream conference out there – who they’re for, and who they’re not for. SQL Saturdays 2020 dates: all over the world, size varies by city, typically 200-300 attendees Length: Friday pre-conference…

Which SQL Server Enterprise Edition Should I Download?

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

Wanna Learn How I Use the First Responder Kit?

February 5, 2020

Brent Ozar

First Responder Kit Updates

No Comments

My newest class, How I Use the First Responder Kit, has been going great – but there’s a catch. I designed this class as a mix of training and consulting, so I’m giving you advice on your server as we go. Because of that, I can only fit so many people (around 50) per class.…

What Are Soft Deletes, and How Are They Implemented?

February 4, 2020

Brent Ozar

Development

Normally when you run a DELETE statement in a database, the data’s gone. With the soft delete design pattern, you add a bit column like IsDeleted, IsActive, or IsArchived to the table, and instead of deleting rows, you flip the bit column. This can buy you a few advantages: Easier/faster undeletes History tracking (keeping deleted…

[Video] Watch Brent Tune Queries

February 3, 2020

Brent Ozar

Execution Plans, Videos

Ever wonder somebody else does it? Watch over my shoulder as I spend 9 minutes in PowerPoint explaining the big picture, and then about 40 minutes working on this stored procedure in the StackOverflow2013 database: If you enjoy that, the Watch Brent Tune Queries page has another video and other query examples. Enjoy!

Register Now to Watch Brent Tune Queries Tonight (Free)

January 30, 2020

Brent Ozar

Company News

It’s last-minute, I know, but I’m doing an online user group session for the Pensacola Data Professionals, and you’re welcome to attend online free if you like. It’s tonight at 8PM Eastern, 7PM Central, 5PM Pacific. Here’s the abstract: Watch Brent Tune Queries – Ever wonder how somebody else does it? Watch over the virtual shoulder…

How to Remove Times from Dates in SQL Server

January 29, 2020

Brent Ozar

T-SQL

Say that you’ve got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table: And let’s say you wanna find the users who last accessed the site on September 9, 2018 – regardless of the time. Here are a few ways to do it: Transact-SQL SELECT *…

Building PasteThePlan.com: Removing Comments

January 28, 2020

Brent Ozar

PasteThePlan.com

PasteThePlan.com We have this PasteThePlan where you can copy/paste the contents of execution plans, and then share the links around the web. You’ve pasted 16,621 plans so far, and I’m always amused to see them continuing to trickle in every day without us actively promoting that tool. It just works, doing its thing quietly. Back…

Why Database Monitoring Tools Are So Hard to Interpret

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…

Updated First Responder Kit and Consultant Toolkit for January 2020

January 24, 2020

Brent Ozar

First Responder Kit Updates

No Comments

A couple of times a year, I disconnect from the Internet to sit down with a planning book, my Epic Life Quest, and my future task list. I spend time making sure my day-to-day tasks are still prioritized right. When I do that, I often find out that I’ve lost focus on a particular task,…

Cool Query Hints

January 23, 2020

Brent Ozar

T-SQL

The SQL Server documentation has a pretty cool list of query hints: Yeah, I surf in dark mode. I’m a dark kinda guy. But wait – what’s that colored box? ENHANCE! I could make jokes here, but … every single thing in that caution is true. SQL Server really does typically select the best execution…

Where Should You Tune Queries: Production, Staging, or Development?

January 22, 2020

Brent Ozar

Execution Plans

The #1 fastest way to tune queries is in the production database, on the production server. I know. Put the knife down. You’re not happy about that, but hear me out: I’m specifically talking about the fastest way to tune queries. In production, you can guarantee that you’re looking at the same data, hosted on the…