Press "Enter" to skip to content

Category: Versions

Distributed Replay Deprecated in SQL Server 2022

Brent Ozar starts the wake:

For SQL Server 2022, Microsoft deprecated Distributed Replay.

The idea behind the feature was that you’d capture a trace against your production environment, set up another environment for load testing or QA testing, and then replay that exact same workload against it. You’d be able to measure which queries got better or worse, and how.

The reality was a complete mess. It was a giant pain in the rear to set up and use, to the point where I got frustrated with it within a few hours and asked my peers about their experiences with it. I got back a string of four-letter words – everybody really struggled to get it across the finish line. Over subsequent versions, Microsoft made token efforts to improve it, but never really gave it the love it required.

Yep, I can concur. What we wanted was a simple button-click (or easy-to-navigate UI) that let you capture “What does a real production workload look like?” and then the ability to re-run it elsewhere, like on new hardware. What we got was indeed a mess.

I don’t fully agree with Brent’s argument that the right answer is to build app-level testing. If everything was architected and developed for this, then yeah, that might be a better answer. But unless you’ve built all relevant applications around APIs (so they can be programmatically invoked rather than trying to do everything via Selenium) and have put in the legwork necessary to track and re-run calls, I think you end up with an even bigger mess—especially if there are multiple applications working with the same database. I do agree that this is a hard problem regardless of the path you choose.

Comments closed

Query Store Hints in SQL Server 2022

Erik Darling has thoughts:

When you’re dealing with untouchable vendor code full of mistakes, ORM queries that God has turned away from, and other queries that for some reason can’t be tinkered with, we used to not have a lot of options.

In SQL Server 2022, Query Store gains a new super power: you can add hints to queries without intercepting the code in some other manner.

There are a couple of useful hints which won’t be available but Erik seems mostly upbeat about what is there.

Comments closed

Parameter Sensitivity Plan Optimization and Monitoring Scripts

Erik Darling gives us a warning:

You can read the full documentation here. But you don’t read the documentation, and the docs are missing some details at the moment anyway.

– It only works on equality predicates right now

– It only works on one predicate per query

– It only gives you three query plan choices, based on stats buckets

There’s also some additional notes in the docs that I’m going to reproduce here, because this is where you’re gonna get tripped up, if your scripts associate statements in the case with calling stored procedures, or using object identifiers from Query Store.

It’s not a deal-breaker but it does make things a lot harder for tool writers, as Erik points out. Hopefully there’s some way to tie this all together before GA.

Comments closed

Creating a SQL Server 2022 Learning Environment

Marlon Ribunal gets us started with a Docker container:

Maybe you want to get your hands dirty with the bells and whistles of the latest iteration of SQL Server, but you don’t have an extra bare metal or Azure or GCP based VM. Well, you’re in luck because Microsoft just released container images for SQL Server 2022.

Here are few steps to get you started with SQL Server 2022:

At this point, it’s quite easy to give new versions of SQL Server a try, even when they’re in preview. That said, some of the features make it to containers later so you might want to spin up a virtual machine and install it if there’s something you can’t get right now in the container.

Comments closed

Backups to S3 in SQL Server 2022

Anthony Nocentino tries out backup to S3 in SQL Server 2022:

In s3 object storage, a file is broken up into as many as 10,000 parts. In SQL Server, the each part’s size is based on the parameter MAXTRANSFERSIZE since this is the size of the write operation performed into the backup file. The default used for backups to s3 compatible storage is 10MB. So 10,000 * 10MB means the largest file size for a single file is about 100GB. And for many databases, that’s just not big enough. So what can you do…first you can use compression. That will get more of your data into a single file.

This right here is the pain. Anthony shows a few ways to extend this number but there’s still a hard cap on maximum backup size, one we don’t have on-premises.

Comments closed

Finding Queries with Missing Index Requests in SQL Server 2019

Erik Darling shows off a nicety in SQL Server 2019 and later:

Note that this script does not assemble the missing index definition for you. That stuff is all readily available in the query plans that get returned here, and of course the missing index feature has many caveats and limitations to it.

You should, as often as possible, execute the query and collect the actual execution plan to see where the time is spent before adding anything in.

Read on for the script.

Comments closed

SQL Server 2022 Public Preview on Linux

Amit Khandelwal has notes on SQL Server 2022 on Linux:

In continuation of last week’s announcement of SQL Server 2022 public preview, we are pleased to announce availability of SQL Server 2022 on Linux/Containers for public preview. Here are the details for getting started with the SQL Server 2022 public preview packages on Linux/Containers.

As usual, the officially supported distributions are Red Hat Enterprise Linux and Ubuntu.

Comments closed

Azure Synapse Analytics May 2022 Updates

Ryan Majidimehr lays out some updates for Azure Synapse Analytics:

Serverless SQL pools let you query files in the data lake without knowing the schema upfront. The best practice was to specify the lengths of character columns to get optimal performance. Not anymore!  

Previously, you had to explicitly define the schema to get optimal query performance. In this case, the column countries_and_territories is defined as varchar(50):  

There are some interesting updates in this month’s release, including the public preview of Azure Synapse Link for SQL, which connects to Azure SQL DB and SQL Server 2022.

Comments closed

Obscure Changes in SQL Server 2022

Aaron Bertrand has a three-parter on obscure changes in SQL Server 2022. First up we have some new information:

You can get the marketing blitz from just about anywhere, and the What’s New documentation for the bigger hitters from the technical side.

But what about the changes that aren’t on the highlight reel at Build and aren’t getting all the attention from the media blitz? I’m a details person, so I get a lot of insight looking around at the little, non-headline-generating things that have changed. I’ve shown before how to sneak a peek under the hood, and I’m going to do it again today:

Then we have feature selection changes:

You will notice some changes in the Feature Selection screen. Some of the options have been consolidated; for example, you now get R, Python, and Java with MLS, instead of picking. One item has been added: SQL Server Extension for Azure. Unfortunately, this option is checked by default in the CTP 2.0 version of setup (click to enlarge):

Finally, we have execution plan changes:

As a former technical product manager for Plan Explorer, I can’t help but snoop around in what has changed in the XSD for showplan. Even though I am not the best person to actually analyze what those changes mean, and even though changes in XSD don’t necessarily reflect changes the engine can produce right now – these usually lay the groundwork for engine changes that will happen later.

There’s quite a lot of information available for those willing to dig, as Aaron shows.

Comments closed

Extended Events in SQL Server 2022

Tom Zika checks out some extended events:

It has been announced today (2022-05-24) during the MS Build event. The blog post includes a download link. Unfortunately, the Docker container is not quite ready yet.

Anyway, because I’m a #TeamXE, I had to check out if there are any new goodies there. So, I took an XE event list from Microsoft SQL Server 2019 (RTM-CU16) and the new one from Microsoft SQL Server 2022 (CTP2.0) and compared them.

There are a lot of new events—click through to see how many.

Comments closed