SQL Server

Just a short note – in SQL Server 2019, the setup process has a new MAXDOP tab: The hyperlink about MAXDOP configuration points here. I did a few quick test installs: 4 cores gave me a MAXDOP 4 8 cores offered MAXDOP 8 12 cores offered MAXDOP 6 Historically, the 0 default meant unlimited cores.…

I’m coming to SQL Saturday Salt Lake City in August!

May 9, 2019

I’m coming to Salt Lake City in August for SQLSaturday #887. On Friday, I’ll be teaching a one-day pre-con: Performance Tuning in 21 Demos – You’re a developer or DBA who wants to tune indexes and queries to make your application go faster. You don’t like learning by watching slides or reading white papers – you…

Are your CPU-intensive queries running slowly? Check your VM type.

May 2, 2019

When you build a VM for SQL Server in Amazon EC2, Azure VMs, or Google Compute Engine, it’s all too easy to overlook the CPU speeds that you’re getting. Most of the time, it doesn’t really matter because SQL Server isn’t usually CPU-bottlenecked: your queries are doing table scans with 16GB RAM, starving for IO throughput.…

Finding Froid’s Limits: Testing Inlined User-Defined Functions

April 11, 2019

Execution Plans, SQL Server 2019, T-SQL

This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…

1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

April 10, 2019

Adaptive Joins, SQL Server 2019

So far, I’ve talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don’t accurately show what happened. But so far, I’ve been using a simple one-table query – let’s see what happens when I add a join and a supporting index: Transact-SQL CREATE INDEX IX_OwnerUserId_Includes ON…

Parameter Sniffing in SQL Server 2019: Air_Quote_Actual Plans

April 9, 2019

Execution Plans, Memory Grants, SQL Server 2019

No Comments

My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them – kinda – with adaptive memory grants. However, the post finished up by talking about how much harder performance troubleshooting will be on 2019 because your query’s memory grant is based on…

Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants

April 8, 2019

Memory Grants, SQL Server 2019

This week, I’m demoing SQL Server 2019 features that I’m really excited about, and they all center around a theme we all know and love: parameter sniffing. If you haven’t seen me talk about parameter sniffing before, you’ll probably wanna start with this session from SQLDay in Poland. This week, I’m going to be using…

What Are You Going to Do About SQL Server 2008?

March 26, 2019

Last week, you used SQL ConstantCare® to analyze 2,396 SQL Servers across a variety of versions: Alas, poor SQL Server 2008 15% (362 instances) of SQL Server 2008 and 2008 R2 18% (438) SQL Server 2012 27% (650) SQL Server 2014 32% (777) SQL Server 2016 7% (169) SQL Server 2017 That means 15% of…

[Video] Demoing SQL Server 2019’s new Accelerated Database Recovery

March 4, 2019

Wouldn’t it be nice if rollbacks just finished instantly? Wouldn’t you love for startup times to be near-zero even when SQL Server crashed just as someone in the middle of a transaction? How much would you pay for all this? (Well, I’m a little afraid to ask that, since we don’t know yet whether this…

New Objects, Columns, and Messages in SQL Server 2019 CTP 2.3

March 3, 2019

Sure, there’s official documentation on what’s new in SQL Server 2019, but Microsoft’s notorious for slipping undocumented stuff in. Sometimes these new features become officially documented in subsequent preview builds, like CTP 2.2’s initial plumbing for Accelerated Database Recovery that went officially public in CTP 2.3, and other times they never see the light of…

SQL Server 2019 Result Set Caching?

March 3, 2019

Friday’s new CTP 2.3 added a few new fields to sys.databases: But the highlighted one has me all tingly inside, kinda like the first time I heard Winona Ryders use the word “chainsaw” in a sentence. (Bonus points for readers who get that reference.) I don’t have any answers yet, folks. I just have questions.

The “Guess the Next Release Date” contest continues…

February 4, 2019

Back in December 2017, when I posted the Guess the Next SQL Server Release Date Contest, you could leave your guess in the comments for a chance to win a Live Class Season Pass and a Recorded Class Season Pass. Here’s how the guesses so far have gone – and for this quick analysis, I’m only…

How to Think Like the Engine – 2019 Edition

January 25, 2019

Indexing, SQL Server, Videos

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos, you’ll learn how the SQL Server engine looks at your indexes and builds your query results. In 90 minutes…

Which Version of SQL Server Should You Use?

January 21, 2019

SQL Server, SQL Server 2016, SQL Server 2017, SQL Server 2019

Wait! Before you install that next SQL Server, hold up. Are you sure you’re using the right version? I know, management wants you to stay on an older build, and the vendor says they’ll only support older versions, but now’s your chance to make your case for a newer version – and I’m gonna help…

Fast Food Code

January 3, 2019

No Comments

Cheap and Easy We’ve all done it. We all know what and where it is, because we’ve probably left a comment for ourselves to go fix it later. It wasn’t me, I swear to myself. Either it was expedient, it seemed to work locally, it was fast on a small amount of data, or we…

38 Blog Posts We Couldn’t Write in 2018

December 31, 2018

No Comments

Behind the scenes here at Brent Ozar Unlimited, we’ve got a place where we jot down blog post ideas. Anybody from the company can add ideas, and anybody can pluck those ideas back out and flesh them out into blog posts. We’ve got a whopping 203 ideas sitting around. So I cleaned house and deleted…

Sniffed Nulls and Magic Numbers

December 18, 2018

I Sniff Your Milkshake Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine. I goes something like this: If this variable is passed in as NULL, substitute it with something else.…

Does low fill factor affect SELECT performance?

December 12, 2018

Tara Kizer

Sometimes good intentions lead to big problems. Imagine a system where page splits were thought to be causing a big enough performance problem that it made sense to change the fill factor setting of a lot of indexes. Now imagine you thought the fill factor setting was for how much free space you wanted when…

Have Availability Groups Gotten Easier Lately?

December 10, 2018

Always On Availability Groups, High Availability, SQL Server

Easy Lover I don’t blog a lot about AGs. If we’re being honest (and I do try to be honest with you, dear reader), I just like performance tuning topics way more. When new features get announced for AGs, some of you may ooh and aah, but not me. I Make A Face I don’t…

Tales From Overindexing: Too Many One Column Indexes

November 29, 2018