Press "Enter" to skip to content

Category: Versions

Undocumented Goodies in SQL Server 2022 RC0

Published 2022-08-25 by Kevin Feasel

Brent Ozar digs through some internals:

Microsoft has an official list of what’s new in 2022 overall, but here I’m specifically focusing on system objects that might be interesting to script developers like you and I, dear reader.

New stored procedure sp_get_table_card_est_and_avg_col_len – I assume the “card” refers to statistics and cardinality, not Hallmark. SQL Server has historically struggled with memory grants because it uses datatype size to budget memory grants, and bigger-than-necessary data types (like NVARCHAR(MAX)) have led to larger-than-necessary grants. It’d be cool if this proc was a down payment to mitigate that problem, but I have a sinking feeling it has to do with external data sources. I would tell you more about it, but when I run it, I get:

Read on for the full list.

Comments closed

SQL Server 2022 RC0 Availabile

Published 2022-08-24 by Kevin Feasel

Ajay Jagannathan has a big announcement:

Continuing with our release cadence, we’re excited to announce the release of SQL Server 2022 Release Candidate 0. Since the first public preview in May 2022, anyone can download SQL Server 2022 RC0 to try the new features in this release.

The changelist doesn’t look enormous for this release and as we get closer to RTM, that set usually drops off a bit as code stabilizes in preparation for release.

Comments closed

sp_prepare and Parameter Sensitive Plan Optimization

Published 2022-08-23 by Kevin Feasel

Erik Darling is a bit surprised:

I admit that sp_prepare is an odd bird, and thankfully one that isn’t used a ton. I still run into applications that are unfortunate enough to have been written by people who hate bloggers and continue to use it, though, so here goes.

When you use sp_prepare, parameterized queries behave differently from normal: the parameters don’t get histogram cardinality estimates, they get density vector cardinality estimates.

That part’s not the surprise. You’ll have to click through for that.

Comments closed

SQL Server 2019 CU 16 and Log Shipping

Published 2022-08-03 by Kevin Feasel

Lee Markum notes a change in SQL Server 2019 CU 16:

As a data professional managing SQL Servers, you need to be thinking about Windows and SQL Server patching.

You want to stay up to date, but you also have to be careful because sometimes staying fully up to date comes with the risk that a new patch might break a feature you are using. This is possibly the case for SQL Server 2019 CU 16. If you’re using Log Shipping on a database that has TDE enabled and compression is enabled on those backups, then you need to take heed.

I’ll walk you through what can happen and what I’ve seen work to resolve the problem.

This is the tricky part of getting rid of service packs: when I think cumulative update, I don’t think “Here’s a thing that could break backwards compatibility with other SQL Server instances which have not been patched.” Read on to see an example of this in action.

Comments closed

Azure Active Directory Authentication in SQL Server 2022

Published 2022-08-01 by Kevin Feasel

Mirek Sztajno has an interesting announcement:

Enabling Azure AD authentication opens access to the Azure cloud identity system. Azure AD is used by many cloud services and unifies all local authentication mechanisms used by Microsoft products providing one central identity repository and authentication management system available to different platforms, including Azure SQL and SQL Server on-premises. The variety of available authentication methods including single sign-on (SSO) and multifactor authentication (MFA), provides strong security support in the authentication area for different services used internally by Microsoft and by external customers. Azure AD authentication is the recommended authentication method for Azure SQL and SQL Server.

Looks like it does require Azure Arc, which has a fairly small per-instance monthly charge. Click through for the details. That said, you will be able to use this feature on-premises and in other clouds, not just in Azure VMs.

Comments closed

Azure Data Studio July 2022 Release

Published 2022-07-28 by Kevin Feasel

Timi Oshin announces a new set of updates:

The Query Plan Viewer feature continues to add functionality with this release of Azure Data Studio. There are several UX improvements users may notice: the icon to enable the capture of an actual plan has been updated, operator selection is now noted with a solid green line, and the plan labels are updated in the Properties window when plans are compared and the orientation is toggled from horizontal to vertical, and back.  We have updated the Command Palette to make it easier to find the commands for execution plans, and while the CTRL + M command still enables actual plan capture for a query window, it no longer executes the selected query (or queries) in the window. 

It’s not a huge release in terms of new functionality but there are some improvements to the query plan viewer and its core Visual Studio Code implementation.

Comments closed

SQL Server 2022 CTP 2.1 Released

Published 2022-07-28 by Kevin Feasel

Ajay Jagannathan has a good announcement:

Continuing with our release cadence, we’re excited to announce the release of SQL Server 2022 Community Technology Preview 2.1. Since the first public preview in May 2022, anyone can download SQL Server 2022 CTP2.1 to try the new features in this release.

CETAS and delta table support are nice additions for PolyBase, ones I’ve really wanted on-premises. We also have the official releases of APPROX_COUNT_DISC() and APPROX_COUNT_CONT(), which I can confirm are “good enough” in terms of closeness and way faster than doing COUNT(*). If you don’t need exact numbers (and outside of certain financial or legal scenarios, once you get into the millions or billions, you usually don’t need a precise number, just a sufficiently good estimate).

Comments closed

SQL Server 2012 Migration Plan

Published 2022-07-18 by Kevin Feasel

Lee Markum says farewell to SQL Server 2012:

Today is end of support for SQL Server 2012. May it rest in peace.

Migrating a SQL Server can be a lot of work. There are so many things to think about. It’s a pain.

It is a pain but Lee does have a few tips on how to get started with a migration plan. And as you get closer to present-day SQL Server (remember: there were 4 versions of SQL Server released after 2012 and we’re getting another one this year), being able to set up distributed Availability Groups for version migration can make life a lot easier for you.

Comments closed

Ordered Columnstore Indexes

Published 2022-07-14 by Kevin Feasel

Joe Obbish and Erik Darling tag team on this one. First, Joe looks at the details of what the CCI ordering process does:

The sort for inserting into an ordered columnstore is a DML request sort. It appears to use the same internal mechanism as the sort that’s added for inserting into partitioned columnstore tables. The difference is that the data is sorted by the specified columns instead of a calculated partition id. In my testing, the sort appears to be a best effort sort that does not spill to tempdb. This means that if SQL Server thinks there won’t be enough memory then the data will not be fully sorted. Parallel inserts have an additional complication. 

And Erik has a messy work-around:

Anyway, I decided to dig in and see what was going on behind the scenes. Which of course, means query plans, and bothering people who are really good at debuggers.

Most of the problems that you’ll run into in SQL Server will come from sorting data.

Whenever I have to think about Sorts, I head to this post about all the different Sorts you might see in a query plan.

Definitely read both posts.

Comments closed

Summarizing Data & AI Summit Announcements

Published 2022-07-05 by Kevin Feasel

Zach Stagers hits the high notes:

One of the biggest cheers of the keynote was that Delta is being fully open sourced! Databricks continue to share their incredible work to help drive our industry forward. Delta already has wide adoption, but with the open sourced version now being levelled up to the same standard as the ‘proprietary’ one, this should help cement it as the default choice for lake-based storage.

There were some announcements of things to come with Delta too, such as a optimised deletes and updates by removing single rows instead of having to completely rewrite the file. It’ll be really interesting to see how this works, and just how much it boosts performance.

Read on for more notes on several big announcements.

Comments closed