SQL Server

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

Execution Plans, Indexing, SQL Server

Master Plan Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti. Usually, there’s a view involved. Usually. Sometimes, there’s just really weird indexing. Here’s A Thing, Look At It Promises This is a query against one table. No views, no functions, no…

Psst – it’s holiday card time. Update your address.

November 19, 2018

Last year’s Christmas card We’re getting ready to send out our annual holiday cards to folks who shopped with us this year. If you’d like to get one, make sure you set up your billing address in your account to reflect your current address. Happy holidays!

What’s New in SQL Server 2019 CTP 2.1: Faster Functions

November 7, 2018

SQL Server 2019, T-SQL

A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the next preview (CTP2.1) of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here’s the documentation on it – let’s see how it works.…

Index Tuning Week: Fixing Nonaligned Indexes On Partitioned Tables

October 10, 2018