Posts by Brent Ozar

Home
Brent Ozar

Brent Ozar

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

Autoparameterized Trivial Queries May Not Get Partition Elimination

December 3, 2020

Brent Ozar

Partitioning

How’s that for a niche topic? You’re probably never going to know this, but since I had to figure it out the hard way, I’m writing this down so I don’t forget it: queries that get trivial optimization may not get partition elimination. This post is going to be a little on the long side…

When Do I Need to Use DESC in Indexes?

November 27, 2020

Brent Ozar

Indexing

If I take the Users table from any Stack Overflow database, put an index on Reputation, and write a query to find the top 100 users sorted by reputation, descending: CREATE INDEX Reputation ON dbo.Users (Reputation); SELECT TOP 100 * FROM dbo.Users ORDER BY Reputation DESC; 12345 CREATE INDEX Reputation ON dbo.Users (Reputation); SELECT TOP 100…

How to Set & Get the Next ID Without Serializable Isolation

November 25, 2020

Brent Ozar

Locking, Blocking, and Isolation Levels

Say for some reason you can’t use identity columns or sequences, or maybe your application was built a long, long time ago – and you’ve got a table with a list of IDs to use next: DROP TABLE IF EXISTS dbo.Ids; CREATE TABLE dbo.Ids (TableName NVARCHAR(255) PRIMARY KEY CLUSTERED, CurrentId BIGINT); INSERT INTO dbo.Ids (TableName,…

How to Insert Rows and Get Their Identity Values with the OUTPUT Clause

November 24, 2020

Brent Ozar

T-SQL

Say you’ve got a two-step process where you’re: Inserting rows into a table that has an identity column, then Querying that table to figure out what identities you got There’s a a faster way that doesn’t require hitting the table twice: the OUTPUT clause. I’ll demonstrate it with the Badges table from the Stack Overflow…

Finding the One Query to Tune in a Multi-Query Batch

November 23, 2020

Brent Ozar

Execution Plans

When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on? For starters, do NOT believe the percentages you see in execution plans. Let’s take a classic performance tuning issue: scalar functions. I’ll start with the Stack Overflow database – if…

What It Takes To Write Two Blog Posts

November 20, 2020

Brent Ozar

Blogging, Videos

This week, I published two blog posts: How Bad Statistics Cause Bad SQL Server Query Performance How Scalar User-Defined Functions Slow Down Queries Let’s talk about the process of writing ’em. A couple I was putting together this week’s First Responder Kit release, I realized sp_BlitzIndex didn’t have URLs for a couple of common families…

When You’re Troubleshooting Blocking, Look at Query #2, Too.

November 19, 2020

Brent Ozar

Locking, Blocking, and Isolation Levels

When I’m troubleshooting a blocking emergency, the culprit is usually the query at the head of a blocking chain. Somebody did something ill-advised like starting a transaction and then locking a whole bunch of tables. But sometimes, the lead blocker isn’t the real problem. It’s query #2. Here’s a sample scenario: A long-running select with…

How Scalar User-Defined Functions Slow Down Queries

November 18, 2020

Brent Ozar

T-SQL

When your query has a scalar user-defined function in it, SQL Server may not parallelize it and may hide the work that it’s doing in your execution plan. To show it, I’ll run a simple query against the Users table in the Stack Overflow database. SELECT TOP 100 DisplayName, Location, Reputation, Id FROM dbo.Users ORDER…

How Bad Statistics Cause Bad SQL Server Query Performance

November 17, 2020

Brent Ozar

Statistics

SQL Server uses statistics to guess how many rows will match what your query is looking for. When it guesses too low, your queries will perform poorly because they won’t get enough memory or CPU resources. When it guesses too high, SQL Server will allocate too much memory and your Page Life Expectancy (PLE) will…

Updated First Responder Kit and Consultant Toolkit for November 2020

November 15, 2020

Brent Ozar

First Responder Kit Updates

No Comments

There’s a bunch of improvements & fixes in this month’s release, but before we get to those, I want to talk about you, dear reader. You make the First Responder Kit possible. When you buy my stuff, you’re funding my work on the First Responder Kit. I couldn’t do this if it wasn’t for you.…

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

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

November 11, 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…

MAXDOP Isn’t Really MAXDOP. It’s More Like DOP.

November 10, 2020

Brent Ozar

Execution Plans

Here’s how Books Online describes the Max Degree of Parallelism setting: You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. And here’s what the SQL Server 2019 setup screen says: When an instance of SQL Server runs on a computer that has more…

Announcing a New Live Online Class: Fundamentals of TempDB

November 9, 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…

Why Full Text’s CONTAINS Queries Are So Slow

November 7, 2020

Brent Ozar

T-SQL

SQL Server’s full text search is amazing. Well, it amazes me at least – it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that’s not how I see most people using it: I’ve seen so many shops using it for matching specific strings,…

Free Webcast: Why is the Same Query Sometimes Slow?

November 6, 2020

Brent Ozar

Conferences and Classes

I can smell your parameters from here You swear nothing has changed, but all of a sudden, out of nowhere, queries that used to be fast are suddenly slow. Even weirder, you take the slow query from the application, run it in SSMS, and it’s totally fast! What’s going on? You restart SQL Server or…

Statistics Aren’t Guarantees: SQL Server Still Checks.

November 4, 2020

Brent Ozar

Statistics

When I’m reviewing plans with folks, I get this response a lot: But SQL Server should know there’s no data that matches! It has statistics! Statistics are guideposts, not guarantees. We’ll start with the Stack Overflow database and put an index on LastAccessDate, which also creates a statistic on that column. We’ll check the min…

Why I Teach Training Classes on Weekends (the next one starts Friday)

November 3, 2020

Brent Ozar

Company News, Conferences and Classes

No Comments

You have a day job. And your day job comes with two big problems. Problem #1: it’s hard to pay attention during the week. Your calendar has tons of meetings scheduled for the foreseeable future, and you can’t just reschedule them whenever you want. Even when you’re not booked in a meeting, people are constantly…

How to Learn SQL Server in One Year

October 26, 2020

Brent Ozar

Conferences and Classes

You’ve been using Microsoft SQL Server for a few years, and it’s becoming increasingly important to your job. You can write a query, but… You feel like something’s missing. You attended a few local user groups or meetups before the quarantines hit, but you were disappointed at how scattered the sessions were. The material was…

New Music Friday: Killa DBA’s New Release Just Dropped

October 23, 2020

Brent Ozar

Humor

Familiar readers will know that I don’t actually do New Music Friday here very often. Okay, ever. I don’t do it ever. Because frankly, nobody writes music about us. Nobody except Homer McEwen, aka Killa DBA (YouTube – @KillaDBA). You may remember him from The SQL Server 2017 Song, and if not, you should go…