Development

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

Last Updated January 22, 2020

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…

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

Last Updated January 7, 2020

Brent Ozar

Execution Plans

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.

Last Updated January 6, 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

Last Updated December 22, 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

Last Updated December 16, 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

Last Updated November 19, 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.

Last Updated November 19, 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

Last Updated November 6, 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

Last Updated November 6, 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?

Last Updated November 17, 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?

Last Updated October 22, 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

Last Updated October 15, 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

Last Updated September 3, 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?

Last Updated September 5, 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

Last Updated August 11, 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?

Last Updated August 11, 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

Last Updated August 15, 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 My Data Isn’t Changing, Right?”

Last Updated January 6, 2022

Brent Ozar

Development, Locking, Blocking, and Isolation Levels

115

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.

Last Updated July 8, 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.

Last Updated 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…