I'm running into something that I'm having a hard time believing.
A client was hitting CPU issues during load testing, and they swore all things were equal between their SQL Server 2016 and 2019 environments. The 2019 box was having CPU pressure issues that didn't show up on the 2016 box. I've played this game before, and every time, the root cause has been different configurations between the two servers.
Last week's SQL Server 2019 Cumulative Update 9 snuck in some undocumented things.
We got two new DMVs for Always On Availability Groups, sys.dm_hadr_cached_replica_states and sys.dm_hadr_cached_database_replica_states. They have a subset of data from other AG DMVs:
Microsoft appears to have given up on patch documentation, and that's kinda scary for a product that costs thousands of dollars per core.
Yesterday's SQL Server 2019 Cumulative Update 6 launched with the worst documentation I've seen yet. It's been steadily going downhill, but CU6 represents a new low.
Thanks for coming today. I apologize for baiting you in here with a picture of toilet paper. That's not even in the US - that's from my Iceland trip, before I flew back here to the US. Look, we all make mistakes. That's not the point of this post. You see, I've been reading this…
You reported it here in the comments, and over at SQLServerUpdates, and in this DBA.StackExchange.com question.
The official word is in: yep, SQL Server 2019 Cumulative Update 2 breaks Agent jobs on some servers, causing them not to run.
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…
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.
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.
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 to take a 40-minute look at some of my favorite new features: deferred compilation for table variables, adaptive memory grants, adaptive joins, and air_quote_actual plans:
Just a short note - in SQL Server 2019, the setup process has a new MAXDOP tab:
The hyperlink about MAXDOP configuration points here.
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.
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:
[crayon-6aad33e40706f382742399/]
(Careful readers will note that I'm using a different reputation value than I used in the last posts - hold that thought. We'll come back to that.)
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 the last set of parameters used, not the current set.
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 the queries & discussion from that session as a starting point.
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 is an Enterprise-only feature, but dang, I sure hope not.)
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 day, like Snapshot Materialized Views.
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…
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…
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 hidden feature that sadly never got enabled.
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:
[crayon-6aad33e409239061842713/]
It puts a bunch of data into a table variable, and then queries that same table variable. On the small StackOverflow2010 database, it takes almost a full minute, and does almost a million logical reads. Here's the plan:
