Development

Can SELECT * Make a Query Go…Faster?!?

January 14, 2020

Most of the time, you’ll hear advice that you should select as few columns as possible so that SQL Server can do less work. However, one of my clients hit a neat edge case scenario where SELECT * queries actually ran faster than picking a subset of columns. I can’t share the exact query, but I…

[Video] You shouldn’t dynamically create/alter/drop triggers.

January 7, 2020

Brent Ozar

T-SQL

Let’s get one thing out of the way first: this isn’t about the relative good or evil of triggers. I kinda like triggers, in fact – they’re good for enforcing business logic in a hurry when you can’t redo code across an entire application. Sure, in a perfect world, we’d always have the code do…

How to Make SELECT COUNT(*) Queries Crazy Fast

December 26, 2019

Brent Ozar

Columnstore Indexes, Execution Plans

When you run a SELECT COUNT(*), the speed of the results depends a lot on the structure & settings of the database. Let’s do an exploration of the Votes table in the Stack Overflow database, specifically the 2018-06 ~300GB version where the Votes table has 150,784,380 rows taking up ~5.3GB of space. I’m going to measure…

The Two Ways to Fix Non-Sargable Queries

December 18, 2019

Brent Ozar

Execution Plans

In the database world, when we say something isn’t sargable, what we mean is that SQL Server is having a tough time with your Search ARGuments. What a sargable query looks like In a perfect world, when you search for something, SQL Server can do an index seek to jump directly to the data you…

How to Think Like the Engine: When a Seek Isn’t

November 21, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine, Indexing

In our last episode, I introduced the concept of scan predicates: execution plan operations that weren’t able to seek directly to the rows they needed. Let’s take another query: Transact-SQL DropIndexes; GO CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO SELECT Id FROM dbo.Users WHERE LastAccessDate > '1800/01/01' AND DisplayName = N'Brent Ozar'; GO…

How to Think Like the Engine: Index Column Order Matters a LOT.

November 20, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine, Indexing

We’ve been working with the clustered index of the Users table, which is on the Identity column – starts at 1 and goes up to a bajillion: And in a recent episode, we added a wider nonclustered index on LastAccessDate, Id, DisplayName, and Age: Transact-SQL CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age ON dbo.Users(LastAccessDate, Id, DisplayName, Age); GO 123…

How to Think Like the SQL Server Engine: When Statistics Don’t Help

November 5, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Statistics

In our last episode, we saw how SQL Server estimates row count using statistics. Let’s write two slightly different versions of our query – this time, only looking for a single day’s worth of users – and see how its estimations go: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate BETWEEN '2018-08-27' AND…

How to Think Like the SQL Server Engine: Using Statistics to Build Query Plans

November 4, 2019

Brent Ozar

How to Think Like the SQL Server Engine, Statistics

In our last episode, SQL Server was picking between index seeks and table scans, dancing along the tipping point to figure out which one would be more efficient for a query. One of my favorite things about SQL Server is the sheer number of things it has to consider when building a query plan. It…

How to Think Like the SQL Server Engine: What’s the Tipping Point?

October 23, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine

In our last episode, I’d expanded our query to include DisplayName and Age – two columns that weren’t in our nonclustered index: Transact-SQL SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate > '2018-09-02 04:00' ORDER BY LastAccessDate; 1234 SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate > '2018-09-02 04:00' ORDER BY LastAccessDate; So as a result,…

How to Think Like the SQL Server Engine: So Index Seeks are Great, Right?

October 21, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine

In our last episode, we added a nonclustered index, and now even though this query has a filter and an ORDER BY, it hardly did any work at all – just a lightweight index seek: Ah, index seeks – nothing better than that, right? Well, not exactly. Let’s make one tweak to the WHERE clause…

Why Query Plans Can Look Different on Different Servers

October 9, 2019

Brent Ozar

Execution Plans, How to Think Like the SQL Server Engine

In the first post in my How to Think Like the Engine series, I started by doing a pretty simple query: But I noted that if you were following along on your computer, you might see a different execution plan. If I tweak just one thing about my SQL Server, I see a different plan:…

Mitch Bottell is preparing to take an exclusive lock on some BBQ.

DBA Training Plan 18: Managing Concurrency

September 4, 2019

Brent Ozar

DBA Training Plan, Locking, Blocking, and Isolation Levels

Locking isn’t a problem – queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won’t show up as a problem. (That’s one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…

DBA Training Plan 13: Why Do Cached Plans Go Bad?

August 26, 2019

Brent Ozar

DBA Training Plan, Execution Plans

It’s a little bit of a trick question: what could make a good query plan turn bad? In the last episode, we used sp_BlitzCache to spot the most resource-intensive, longest-running query plans in your server’s cache. Go run those queries again, right now, and I bet you’re going to see plans in the cache that…

Research Paper Week: Plan Stitch: Harnessing the Best of Many Plans

August 19, 2019

Brent Ozar

Execution Plans

Those of you who follow me on Instagram, Twitter, or Facebook know I’m taking most of August off for vacation. Erika and I spent the last couple of weeks in Telluride, had a brief stop in Sacramento for SQL Saturday, and this week we’re touring wineries in Napa and Sonoma. As part of recharging, I…

DBA Training Plan 12: What Query Plans Are In Cache?

August 15, 2019

Brent Ozar

DBA Training Plan, Execution Plans, sp_BlitzCache

In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I’ve also talked about the dynamic management views (DMVs) that let you query what’s in SQL Server’s memory. Let’s put those two things together…

DBA Training Plan 11: The Basics of Executing a Query

August 14, 2019

Brent Ozar

DBA Training Plan, Execution Plans

Up til now in the DBA Training Plan, we’ve been taking inventory of our servers, making sure the data’s well-protected, and understanding the basics of how the data’s stored in indexes inside those data files. Now, let’s start looking at performance, and let’s start by zooming really far out to think about how SQL Server…

“But NOLOCK Is Okay When The Data Isn’t Changing, Right?”

August 5, 2019

Brent Ozar

Development

I’ve already covered how NOLOCK gives you random results when you’re querying data that’s changing, and that’s a really powerful demo to show folks who think NOLOCK is safe to use in production. However, I’ve gotten a question from several users: But I’m querying data that isn’t changing – sure, OTHER rows in the table…

Using Implicit Transactions? You Really Need RCSI.

July 9, 2019

Brent Ozar

Locking, Blocking, and Isolation Levels, Videos

Implicit transactions are a hell of a bad idea in SQL Server: they require you to micromanage your transactions, staying on top of every single thing in code. If you miss just one little DELETE/UPDATE/INSERT operation and don’t commit it quickly enough, you can have a blocking firestorm. The ideal answer is to stop using…

Poll Results: Yep, Developers are Using Unmasked Production Data.

July 5, 2019

Brent Ozar

Development

Earlier this week, I asked where your developers are getting their development databases from. The poll results are in: 9% of development happens on the production database server 57% are copying the production data to another server, and using it as-is 31% are copying the production data, but then masking/scrambling private data before developers get…

What the Arrow Sizes in Query Plans Really Mean

June 21, 2019

Brent Ozar

Execution Plans

Precisely 80.3% of you are going to learn something in this post. Earlier this week, I asked what you thought the arrows in estimated and actual query plans meant. I asked you to just guess without doing any research, and here’s what you answered: There are a lot of different opinions, and I can see…