Press "Enter" to skip to content

Curated SQL Posts

SQL Saturday Orlando Notes

Published 2021-11-12 by Kevin Feasel

Andy Warren reflects on hosting the only in-person SQL Saturday in the United States this year:

We held an in-person SQLSaturday here in Orlando last weekend (Oct 30th). We didn’t organize one last year, there was just too much risk and too much uncertainty, so it felt good to return to something close to normal this year, even in scaled back fashion. I’ve got a lot of notes to share about how we ran the event this year!

The journey started at the end of 2020. We wrote up our plan for 2021 knowing there were a lot of unknowns, but hoping things would improve enough to resume doing the things we used to do as a local group and that included organizing a SQLSaturday. As this year has progressed attendance at our virtual meetings dropped, as did our enthusiasm for having them. Enthusiasm matters a lot when it comes to volunteer work and while I know many of you like the virtual format, it’s just not what I want to do. That narrowed the option list to having an in-person SQLSaturday or not doing one at all, not a great range of choices.

Read on for a lot of details. I appreciate how transparent Andy has always been with respect to running events like this and if you’re thinking about a SQL Saturday in 2022, definitely read Andy’s post.

Also, the event was small, but it was really nice to get to see people I hadn’t seen in years, so thank you, Andy, for putting on the show.

Comments closed

Updates to Azure Synapse Link

Published 2021-11-12 by Kevin Feasel

Aria Jelinek outlines the value of Azure Synapse Link:

New as of Ignite 2021, customers can optimize queries by setting custom partitions for their Azure Cosmos DB analytical store using keys that are commonly used as query filters. This compacts and optimizes the analytical data written to the partitioned store, resulting in better query performance even when working with a high volume of update or delete operations.

Azure Synapse Link is also now available for Azure Cosmos DB serverless accounts, expanding the integration to cover data from workloads with bursts of traffic or uncertain traffic patterns.

This post mostly covers the Dataverse and Cosmos DB integrations rather than the integration with SQL Server 2022.

One the whole, I like Azure Synapse Link for Cosmos DB and will probably like it for SQL Server 2022—maybe even a bit more. It does simplify the ELT process by taking care of the E and handling the first half of the L (landing into a staging table). Though if data’s going into a dedicated SQL pool, I do hope the people doing this understand that dedicated SQL pools are intended for Kimball-style data warehousing scenarios and there can be a considerable performance (and therefore price) hit if you simply replicate a bunch of stuff without subsequent transformation.

Comments closed

Optimization Tips with Inline Table-Valued Functions

Published 2021-11-12 by Kevin Feasel

Itzik Ben-Gan continues a series on table expressions:

This is the thirteenth and last installment in a series about table expressions. This month I continue the discussion I started last month about inline table-valued functions (iTVFs).

Last month I explained that when SQL Server inlines iTVFs that are queried with constants as inputs, it applies parameter embedding optimization by default. Parameter embedding means that SQL Server replaces parameter references in the query with the literal constant values from the current execution, and then the code with the constants gets optimized. This process enables simplifications that can result in more optimal query plans. This month I elaborate on the topic, covering specific cases for such simplifications such as constant folding and dynamic filtering and ordering. If you need a refresher on parameter embedding optimization, go over last month’s article as well as Paul White’s excellent article Parameter Sniffing, Embedding, and the RECOMPILE Options.

This was a really good series. If you haven’t seen the entries, set aside some time and check it out.

Comments closed

Version 12 of sp_WhoIsActive

Published 2021-11-12 by Kevin Feasel

Erik Darling answers the long-standing question “Who is active?” with “You is active!”:

– New parameter, @get_memory_info, that exposes memory grant information, both in two top-level scalar columns and a new XML-based memory_info column.

– Better handling of the newer CX* parallelism wait types that have been added post-2016

– A top-level implicit_transaction identifier, available in @get_transaction_info = 1 mode

– Added context_info and original_login_name to additional_info collection

– A number of small bug fixes

– Transition code to use spaces rather than tabs

Spaces rather than tabs? SQL should have tabs! But functional programming languages are great and they use spaces! I’m so conflicted!

Comments closed

SQL Server 2022 and Big Releases

Published 2021-11-12 by Kevin Feasel

Brent Ozar opines on an interesting topic:

The question, posed by Brent’s Tasty Beverage (nicely done) was:

My friends feel announcement from MS regarding SQL22 were only relatively small changes (since we didn’t see too much of multiple plans technically or demo), nothing groundbreaking or revolutionary. What are your thoughts?

Read on for Brent’s thoughts. I’ll say that I’m still expecting a few smaller surprises to come in as we get closer to CTPs.

Comments closed

Most Business Ideas Fail

Published 2021-11-11 by Kevin Feasel

Eric Colson, et al, have a humbling thought for us:

The introduction of data science into the business world has contributed far more than recommendation algorithms; it has also taught us a lot about the efficacy with which we manage our businesses. Specifically, data science has introduced rigorous methods for measuring the outcomes of business ideas. These are the strategic ideas that we implement in order to achieve our business goals. For example, “We’ll lower prices to increase demand by 10%” and “we’ll implement a loyalty program to improve retention by 5%.” Many companies simply execute on their business ideas without measuring if they delivered the impact that was expected. But, science-based organizations are rigorously quantifying this impact and have learned some sobering lessons:

1. The vast majority of business ideas fail to generate a positive impact.

2. Most companies are unaware of this.

3. It is unlikely that companies will increase the success rate for their business ideas.

Read the whole thing. It gives a lot of perspective to a difficult problem: there aren’t as many “free wins” in a business as you might expect. To paraphrase Adam Smith, there is a lot of ruin in a company…but that doesn’t mean you know what exactly it is or how exactly to fix it. Coming in with appropriate humility and a flexible mind (by which I mean a willingness to see reality even when it doesn’t comport to the mental model you’ve built over time) can help improve those odds.

Comments closed

Graphing Three or More Dimensions

Published 2021-11-11 by Kevin Feasel

Mike Cisneros takes on a challenge:

When we have three or more dimensions to show, how do you recommend we do it? I worry that my audience might not be able to make sense of it all.

This is a great question. As analysts we are often asked to consider multiple dimensions at once, and investigate complex relationships among these variables. In doing so, we may use visual analyses to explore and find patterns and outliers. The graph types we use to do this tend to be complicated and less intuitive than a simple bar chart or line chart. They might make sense to a trained observer, but to an unfamiliar audience, they’re at best confusing and at worst impenetrable. 

Click through for a few techniques, none of which directly involves 3D graphs, as those are really difficult for humans to understand in most circumstances.

Comments closed

Table-Valued Parameters and Dapper (.NET Core Edition)

Published 2021-11-11 by Kevin Feasel

Randolph West hits on a timely question:

A customer I’ve been working with for a while now has a monolithic ASP.NET MVC web application which we are porting to .NET Core 3.1 (and then almost immediately to .NET 6). One of our biggest changes was getting rid of Entity Framework and replacing it with Dapper, because performance is a feature.

To deflect the ire of EF Core aficionados out there, the answer is still no.

Dapper is a micro-ORM in that it does not do as much “magic” as Entity Framework. This necessitates more work at the data access layer, but we have the trade-off of speed.

I say this is timely because my team is working through this exact thing right now. For future reference, anticipating what my team is working on and writing a blog post which answers a question we have is an outstanding way of getting noticed here.

Comments closed