A Fine Slice Of SQL Server
Hasan Savran won’t settle for just one date:
GENERATE_SERIES function is introduced in SQL Server 2022 version. It is a simple function that generates a series of numbers within a given interval. It requires compatibility level 160.
The natural use for it is to generate a set of numbers, but as Hasan shows, you can easily turn that into a set of dates or dates and times.
Comments closedKate Smith points out a new feature in SQL Server 2022:
In SQL Server 2022, we introduced a new feature called DOP feedback. This feature will look at any parallel query and determine if it might perform better with a lower degree of parallelism than currently being used. For example, perhaps 16 threads will perform better than 20 if there are a lot of waits on other threads. It will test out the new degree of parallelism and, either decide that this was a good change and keep the 16 threads, or it will revert to previous levels of parallelism and go back to 20 threads. If the new degree of parallelism is good, then this optimization is persisted inside the query store and will be applied appropriately to a query for future executions.
Read on for an overview of how it works and what protections are in place to keep it from going completely bonkers. Well, more completely bonkers than what you already have.
Comments closedHugo Queiroz provides an overview of data virtualization options in SQL Server 2022:
SQL Server 2022 now supports CSV, Parquet, and Deltafiles stored on Azure Storage Account v2, Azure Data Lake Storage Gen2, or any simple storage service (S3)–compliant object storage—the last as an on-premises offering or in the cloud. Finally, SQL Server 2022 can now use
Create External Table as Select(CETAS), together with commands likeOPENROWSET,Create External Table(CET), and all the new T-SQL enhancements. SQL Server 2022 is a powerful data hub.
The post doesn’t get too deep into the topic, though a search here will find you links to articles with concrete examples.
Comments closedHugo Queiroz shows off something new in SQL Server 2022:
Backup and restore to simple storage service (S3)–compatible object storage is a new feature introduced in SQL Server 2022 that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.
There are some differences from other backup operations, so you should definitely read up on it before using it. One interesting side benefit I got to try out recently is that Pure Storage’s FlashBlade product has an S3 API, allowing you to use that interface for backup/restore operations as well as data virtualization.
Comments closedJeff Hill has a script for us:
In the world of PowerShell, some things move super fast while others may stay the same for years. If the module you are using does what you want with no issues, you may not think to see if a newer version is available. The PSReadline module is a good example. The version I was on worked great and it did what I wanted. I had no idea there was so much more that had been done.
Click through for a script which checks the Powershell Gallery for updates, though it does not force install those updates.
Comments closedGarry Bargsley rolls the dice:
In my experience, two options exist to get the desired result. One, create a new server, install the latest supported version of SQL Server, and migrate your data. Two, upgrade SQL Server on the existing server.
There are pros and cons to each of these options. My preference is to go with option number one as it allows you more flexibility in your migration plan. However, many smaller shops might not have the hardware resources for this option, so they are forced to option number two.
In-place upgrades have improved considerably, though certain ancillary services (like Machine Learning Services) have breaking changes between versions, so you may be forced into the first route regardless.
Comments closedGlenn Berry summarizes what people are doing with SQL Server 2022:
Back on September 5, 2022, I posted the invitation for T-SQL Tuesday #154 Invitation – SQL Server 2022, which was due on Tuesday, September 12, 2022. I ended up getting eleven blog post responses that I am aware of (including mine). Thank you to everyone who participated! This post will be my T-SQL Tuesday #154 Recap.
Here are the blog posts for #T-SQL Tuesday #154, in alphabetical order by author.
Glenn goes the extra mile by including author photos as well.
Comments closedBrent Ozar surveys the SQL Server landscape:
Ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates, hardware sizes, or numbers of databases? Let’s find out in the summer 2022 version of our SQL ConstantCare® population report.
Out of the 3,151 servers sending in data recently, the most popular version of SQL Server is still 2016:
Every time I link to this, I say the same two things: one, that it’s important to keep in mind that this is necessarily a biased sample (in that it includes the subset of organizations which has people who are familiar with Brent’s work, spends enough money to get ConstantCare support, and has a need for this support); and two, that I appreciate this sample. Yes, it’s biased and not necessarily indicative of the broader market but it’s also useful information, especially seeing trends over time.
Comments closedParameter sniffing is a notorious problem for Microsoft SQL Server because it tries to reuse execution plans, which doesn’t work out well for widely varying parameters. Here’s a primer for the basics about how it happens.
SQL Server 2022 introduces a new feature called Parameter Sensitive Plan optimization. I’m not really sure why Microsoft capitalized the first three letters sometimes and not others, or why they only abbreviate it as “PSP optimization” – as long as we’re abbreviating, why not call it PSPO? Optimization is the word in there with the most syllables – why the hell would we leave that one as the one that isn’t abbreviated?
Because I make things go faster for a living, we’re gonna call it PSPO. You’ll see how I pronounce it later, and why.
Erik Darling has also spent a lot of time talking about this. And at this point, I don’t know that how many (if any) of Brent’s critiques get fixed before RTM.
Comments closedTimi Oshin has another month’s worth of updates for us:
The SQL Database Projects Publish dialog has been enhanced with a breadth of options including excluding object types, data definition language (DDL) trigger behavior, index rebuild behavior, and more. These options are available after clicking the Advanced button and a description for each option appears at the bottom of the panel. With the availability of these options, you can now publish a SQL project or generate the publish script with more precision for the requirements unique to your environment.
This is something I’ve missed from VSCode / Azure Data Studio that I could do 15 years ago in Visual Studio.
Comments closed