Press "Enter" to skip to content

Curated SQL Posts

Maximum Number of Actions on an Extended Event

Published 2023-02-28 by Kevin Feasel

Jonathan Kehayias hits the limit:

Did you know that there is a limit to the number of actions you can add to a single event in Extended Events? While I was playing around with Trace Flag 9708 for my previous blog post, one of the things I wanted to try to see was whether it would be easy to determine the impact of adding too many actions to a single event. I picked a single event, in this case wait_info, and I checked all of the actions for the event in the UI, and tried to create the event session. I was surprised when I got a validation error back so I clicked on the details link and got the following:

Session validation for the alter operation failed. (Microsoft.SqlServer.Management.XEvent)

Read on for more details and to see the limits. Applying Swart’s 10% Rule to this, you’d say 3 actions for any given event, which is probably a little bit low but in the neighborhood of what I’d call reasonable for a given XE.

Comments closed

Rolling Your Own Serverless SQL Pool Database Project

Published 2023-02-28 by Kevin Feasel

Kevin Chant doesn’t let the lack of support for a product limit him:

In this post I want to share how I created a homemade serverless SQL Pool database project.

Because I know people are keen to work this way right now. Mostly due to the comments I received when I covered how to deploy a dacpac to a serverless SQL pool.

By the end of this post you will know how I created a database project for it. Plus, how you can deploy the contents of the database project with Azure DevOps. I also share plenty of links along the way.

Though Kevin did run into some challenges trying to hack in a solution, so it’s not quite as useful as you’d first hope.

Comments closed

Removing Diacritics with Power Query

Published 2023-02-28 by Kevin Feasel

Chris Webb gets rid of them scribbles what you sometimes find on perfectly good letters:

…then the output is “un garon trs g Nol”. As you can see, removing all the characters leads to unreadable text. Instead, what you have to do is find all the letters with diacritics (accents and other glyphs that can be added to characters) and remove the diacritics. Doing this may be ungrammatical and make it harder to understand the meaning of the text but in most cases the text will still be readable.

The bad news is that there is no straightforward way to do this in Power Query, and indeed there is no straightforward way to do this at all because there are no hard-and-fast rules about what to replace a letter with a diacritic with: should “ö” become “o” or “oe” for example? My first thought was to create a big lookup table with all the rules of what to replace each character with in, similar to the approach taken here for solving this problem in Google Sheets. Building a comprehensive lookup table would be gigantic task though.

Chris does take a different approach, though do read the comments because there are scenarios in which a simple removal of the diacritic can lead to a not-so-subtle alteration of the phrase.

Comments closed

Route Planning in Postgres

Published 2023-02-27 by Kevin Feasel

Mark Litwintschik plans a journey:

I recently came across a transit route feed aggregator called Transitland. They list feeds from 2,500 operators in 55+ countries around the world. Among these feeds is one for FlixBus, a 12-year-old coach service provider. Below is a route map of their European destinations.

In this post, I’ll import their feed into PostgreSQL, build visualisations of their routes and plan a bus trip from Vienna to Oslo.

Read on for the process.

Comments closed

Scripting Drop Statements for Redundant Indexes

Published 2023-02-27 by Kevin Feasel

Eitan Blumin deals with a clone problem:

This article published by Brent Ozar is very informative about redundant/duplicate indexes, what they mean, why they’re bad, and what should be done with them.

Also, a few years ago, Guy Glantser published a post about dropping redundant indexes. It’s very useful for finding all redundant indexes within all tables in a specific database.

But what both of these articles are missing – is the ability to easily generate Drop/Disable commands for these redundant indexes.

Additionally, what if there are “similar” indexes that are only “partially” redundant, and therefore it’s not enough to simply drop one of them? Otherwise, some queries may suffer a negative performance impact.

Click through for the article and be sure to pay close attention to the important note, which I’ll summarize: “kind of redundant” doesn’t always mean redundant.

Comments closed

Diagnosing a Resource Semaphore Wait Issue

Published 2023-02-27 by Kevin Feasel

Jose Manuel Jurado Diaz finds excessive resource semaphore waits:

Today, we got a service request that our customer reported that they query are taking too much for their execution. The main wait stats found was RESOURCE_SEMAPHORE and I would like to share with you my lessons learned here. 

We executed this query to find out the queries and check the resource semaphore wait type. 

Click through for the queries and diagnosis.

Comments closed

Thoughts on the New Power BI Accessible Themes

Published 2023-02-27 by Kevin Feasel

Meagan Longoria is moderately pleased:

Everyone’s vision is a little different. It is rare (impossible?) that a color theme is accessible for everyone. For instance, while many people with color vision deficiency have trouble distinguishing red and green hues, others have trouble distinguishing blue hues. So when we optimize to accommodate one condition, we may make things more difficult for another condition. This happened with the change in accent color in Power BI Desktop from yellow to teal. Changing to teal increased color contrast, which was great for people with low vision, but it caused new issues for some people with color vision deficiency.

While I am very happy to see these new color themes, I hope everyone understands that they aren’t just generally accessible for all uses. As mentioned in the blog post, they specifically have better color contrast to achieve a contrast of at least 3:1, which is the contrast recommended by WCAG for non-text content.

Read the whole thing. There’s a delicate balancing act between having a complete color scheme and satisfying a variety of needs. It sounds like this theme doesn’t quite cut it, though hopefully there will be some improvements in the future.

Comments closed

Performing a Detailed Code Review of a Stored Procedure

Published 2023-02-27 by Kevin Feasel

Aaron Bertrand has 99 problems and this stored procedure is 40 of them:

I’ve been at this for a while now, and have a very particular set of rules and coding conventions that I follow when writing and, more importantly, reviewing T-SQL code. I often perform code reviews and thought it would be fun to frame this exercise: a completely fictitious stored procedure hits my desk, I’ll reject it of course, and enumerate the reasons why. The procedure might pass review in a lot of shops, but not in mine.

Click through and give it a try. Aaron has outdone himself with this and I got angry with him about 2/3 of the way through the procedure. That’s how you know it’s a good example.

Comments closed

Calculating Log Likelihood Ratios with jeva

Published 2023-02-23 by Kevin Feasel

Peter M.B. Cahusac takes us through a jamovi package:

Ever wanted to try doing an evidential analysis? You may have found it difficult to find a statistical platform to do it. Now there is the jamovi module jeva which can provide log likelihood ratios for a range of common statistical tests.

Imagine for a moment that we wish to carry out a statistical test on our sample of data. We do not want to know whether the procedure we routinely use gives us the correct answer with a specified error rate (such as the Type I error) – the frequentist approach. Nor do we want to concern ourselves with possible a priori probabilities of hypotheses being true – the Bayesian approach. We need to know whether a statistic from this particular set of data is consistent with one or more hypothetical values. Also, let’s say that we weren’t happy with how much data we had collected (a familiar problem?), and just added more when convenient. Welcome to the likelihood (or evidential) approach!

Read on for an explanation and how to try jeva out.

Comments closed