SQL Server 2019

Interesting Undocumented Tidbits in SQL Server 2019 CU2

February 19, 2020

SQL Server 2019

SQL Server 2019 Cumulative Update 2 is out, and Microsoft snuck in a few things that they didn’t mention in the release notes. Contained Availability Groups continue to get new investments. In the lead-up to SQL Server 2019’s release, we got a few presentations at various conferences where Microsoft folks said we’d get the ability…

SQL Server 2019 is out…now.

November 4, 2019

Brent Ozar

SQL Server 2019

After just one release candidate, Microsoft has decided it’s ready to go, apparently! Well, kinda: the official build in the release notes is 15.0.2000.5, and there’s already a “servicing update” to 15.0.2070.41. I’m not sure I wanna know what’s going on there. Anyhoo, SQL Server 2019 is available for download now. This also means it’s…

SQL Server 2019 Standard Edition Feature Limitations Are Out

October 17, 2019

Brent Ozar

SQL Server 2019

The features list by edition is out, and there’s a disclaimer at the top: This content is being updated for SQL Server 2019. The information in the article is not final. Which means if you’re really angry, and your company buys a lot of licenses, now’s your time to raise holy hell with your Microsoft…

[Video] What’s New in SQL Server 2019

October 14, 2019

Brent Ozar

SQL Server 2019, Videos

We must be getting pretty close to the official release of Microsoft SQL Server 2019. There are less than 80 days left in the year. (Interestingly, it’s been almost 60 days since Release Candidate 1 shipped – that’s unusually long for a delay between builds. Hmm.) With the clock ticking down, now’s a good time…

Setting MAXDOP During SQL Server 2019 Setup

May 23, 2019

Brent Ozar

SQL Server 2019

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.…

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

April 11, 2019

Brent Ozar

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

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…

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

March 4, 2019

Brent Ozar

SQL Server 2019, Videos

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

Brent Ozar

SQL Server 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

Brent Ozar

SQL Server 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

Brent Ozar

SQL Server 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…

Which Version of SQL Server Should You Use?

January 21, 2019

Brent Ozar

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…

What’s New in SQL Server 2019: Faster Functions

November 7, 2018

Brent Ozar

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 preview 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. Using the…

What’s New in SQL Server 2019 System Tables

September 24, 2018

Brent Ozar

SQL Server 2019

The free SQL Server 2019 preview download is out, and here are quite a few things to check out that aren’t documented yet: New System Objects Starting with new stored procedures: sys.sp_add_feature_restriction sys.sp_autoindex_cancel_dta sys.sp_autoindex_invoke_dta sys.sp_cloud_update_blob_tier sys.sp_configure_automatic_tuning sys.sp_diagnostic_showplan_log_dbid sys.sp_drop_feature_restriction sys.sp_execute_remote sys.sp_force_slog_truncation sys.sp_internal_alter_nt_job_limits sys.sp_rbpex_exec_cmd sys.sp_set_distributed_query_context sys.sp_set_session_resource_group sys.sp_showinitialmemo_xml sys.sp_xa_commit sys.sp_xa_end sys.sp_xa_forget sys.sp_xa_forget_ex sys.sp_xa_init sys.sp_xa_init_ex sys.sp_xa_prepare sys.sp_xa_prepare_ex sys.sp_xa_recover sys.sp_xa_rollback…

What’s New in SQL Server 2019’s sys.messages: More Unannounced Features

September 24, 2018

Brent Ozar

SQL Server 2019

The system table sys.messages holds error messages that SQL Server needs to return to end users. When a new build comes out, I love checking this table to see what features Microsoft has coded, but isn’t quite ready to announce yet. When SQL Server 2017 came out, that’s how I unmasked snapshot materialized views, a…

What’s New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

September 24, 2018

Brent Ozar

SQL Server 2019, T-SQL

For over a decade, SQL Server’s handling of table variables has been legendarily bad. I’ve long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation: Transact-SQL declare @VoteStats table (PostId int, up int, down int) insert @VoteStats select PostId, up = sum(case when VoteTypeId = 2 then 1 else 0 end), down…

What’s New in SQL Server 2019: Adaptive Memory Grants

September 24, 2018

Brent Ozar

Execution Plans, Memory Grants, SQL Server 2019

When you run a query, SQL Server guesses how much memory you’re going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you’re about to do, and doesn’t grant you enough memory. Say you’re working with…

Leaked: SQL Server 2019 Big Data Clusters Introduction Video

September 22, 2018

Brent Ozar

SQL Server 2019

Psst – you’re probably not supposed to see this yet, but look what @WalkingCat found: SQL Server 2019 Vision pic.twitter.com/zbjI28FYSu — WalkingCat (@h0x0d) September 21, 2018 What the video says Growing volumes of data create deep pools of opportunity for those who can navigate it. SQL Server 2019 helps you stay ahead of the changing time…