Press "Enter" to skip to content

Category: Versions

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

Azure Synapse Analytics June 2022 Updates

Published 2022-06-30 by Kevin Feasel

Ryan Majidimehr has some updates for us:

Fuzzy matching with a sliding similarity score option has been added to the Join transformation in Mapping Data Flows. You can create inner and outer joins on data values that are similar rather than exact matches! Previously, you would have had to use an exact match. The sliding scale value goes from 60% to 100%, making it easy to adjust the similarity threshold of the match. 

Read on for the full list of updates.

Comments closed

Query Store in SQL Server 2022

Published 2022-06-29 by Kevin Feasel

Melody Zacharias gives us a heads up on what’s new with Query Store:

The SQL Server team has improved on Query Store for 2022 again and made some great improvements for SQL 2022. Query Performance was originally introduced as a flight recorder for your queries. It uses a system that gathers query performance data and gives you insights into your work loads over time. In 2022 it is being used to build and expand new capabilities in intelligent query processing.  To allow this to work well and be accurate, Query Store is now enabled by default for new databases. In addition to providing hinting support, it will facilitate the ability to build new intelligent query processing scenarios and improve performance.

Read on for a list of improvements you’ll see in the product.

Comments closed