Development

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

November 5, 2019

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

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

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

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…

Fully optimized plan

Why Query Plans Can Look Different on Different Servers

October 9, 2019

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

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…

Brent getting caffeinated

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

August 26, 2019

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…

Plan Stitch

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

August 19, 2019

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

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…

Plan Properties

DBA Training Plan 11: The Basics of Executing a Query

August 14, 2019

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…

NOLOCK

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

August 5, 2019

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

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…

Brent Reading Book

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

July 5, 2019

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

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…

Poll: What Do You Think The Arrow Sizes in Query Plans Mean?

June 18, 2019

Execution Plans

Don’t read the documentation – just guess. What do you think the arrow sizes in query plans mean in SSMS 17/18? After you take the poll – just guess – then you can see what other people think. Friday, I’ll share a demo you can use to prove the answer.

Unforced parameterization

Forced Parameterization Doesn’t Work on Partially Parameterized Queries.

June 12, 2019

Over and over again, I run into applications that don’t parameterize their queries, and as a result: Compilations/sec is nearly as high as Batch Requests/sec CPU is high due to all those compilations Plans rarely last for more than a few hours in the cache Monitoring tools that track resource-intensive queries are useless because every…

What’s Better, CTEs or Temp Tables?

June 6, 2019

I get this question a lot, so let’s set up an example with the Stack Overflow database. My query’s goal is to: Find the top 5 Locations List the users who live in those top 5 Locations, alphabetized by their DisplayName There are a LOT of ways I could write this query, but for the…

Scalar function execution plans, SQL Server 2017

User-defined scalar functions suck – even when they don’t access data.

May 28, 2019

The performance of scalar functions sucks hard. Let’s see it in action using the Stack Overflow database – any size will work. I’ll set things up first in case you want to follow along: Transact-SQL USE StackOverflow2013; GO ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; GO EXEC DropIndexes; GO EXEC sp_configure 'cost threshold', 5; EXEC…

9 Tips for Faster SQL Server Applications

May 21, 2019

Wanna make your SQL Server applications go faster? Here are 9 simple tips that will give you a better chance for success. They’re not carved in stone, but they’re a good place to start. Query Design Tips 1. Code for readability first. The easier your query is for humans to understand, the more likely SQL…

Why Order Isn’t Guaranteed Without an ORDER BY

May 15, 2019

Execution Plans

Start with the Stack Overflow database, with no nonclustered indexes. If you’ve been playing around with demos, run my DropIndexes proc just to clear things out, which leaves the clustered indexes in place. Say you run this query: Transact-SQL SELECT TOP 100 Id, DisplayName FROM dbo.Users; 12 SELECT TOP 100 Id, DisplayName FROM dbo.Users; By default,…

Menu