Press "Enter" to skip to content

Curated SQL Posts

Avoiding Temporal Coupling in Code

Yamini Bansal explains a common error in class and method design:

Temporal means time based. So, consider if the time(instant/order) at which one member of a class is called, affects the calling of another member. Or, if sequence of calling members of a class is something that should be kept in mind before using that class’s member then it means they are coupled.

Click through for an example. The basic concept is, I shouldn’t need to know that I must call setup method X() before I can take advantage of some useful method Y(). This is because a new person coming in might not realize that X() exists, will try to call Y(), and something breaks. Calling a method with a valid set of input parameters and having it immediately break is a sign of a dodgy API.

Comments closed

Hyperparameter Tuning in Azure Machine Learning

Dinesh Asanka takes us through hyperparameter tuning with Azure Machine Learning’s designer:

In the above experiment, both the previous model and the TMH included the model so that we can compare both models. In the above experiment, Tune Model Hyperparameters control is inserted between the Split Data and Score Model controls as shown. In the TMH, control has three inputs. The first control needs the relevant technique and, in this scenario, it is the Two-Class Logistic Regression technique. The second input needs the train data set and the last input needs the evaluation data set and for that, the test data set can be used.

Tune Model Hyperparameters control provides the best combinations and it will be connected to the score model. After the test data stream is connected to the score model, the output of the model is connected to the second input of the Evaluate model so that the previous model and the tuned model can be compared.

I’m not sure if there’s something handled internally in the Tune Model Hyperparameters component, but based on the pipeline images, I’d actually want two separate Split components so that I ended up with something more like 50-20-30 for training, hyperparameter testing, and validation. The first two pipelines appear to be 70-30-0 instead, and so can give you a false sense of confidence in model quality.

Comments closed

Auto-Generating Relative Links in Azure Data Studio Notebooks

Julie Koesmarno points out a new feature:

As you enrich your collection of notebooks (organized in a Jupyter Book, hopefully), you will likely want to link from one notebook to another notebook in the directory you are working on.

If you are familiar with markdown, you know that this process can be painful as you’d need to know where the target link is located and where it is located in relation to the notebook that you want to link from.

Luckily in Azure Data Studio v1.27.0, there is a new Insert Link button in the Text Cell that does the automatic translation “hard coded path” to “relative path” link. Check this out!

Click through for a demo. I like the idea as a way of preventing a common problem when sending artifacts somewhere: all of those hard-coded links to a network share I can’t access or a folder on somebody else’s laptop.

Comments closed

Extended Events without XML

Grant Fritchey shows how you can avoid working directly with XML in Extended Events:

One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped.

Look, I get it. I don’t like XML either. It’s a pain to work with. It’s actively difficult to write queries against it. If there weren’t a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let’s see how.

Click through for two such methods. Another thing I might note is that quite often, you only need to mess with the XML once to set up your session and once to set up how you’ll view and handle your results. If you’re constantly writing XPath queries from scratch to work with Extended Events, that’s by choice.

1 Comment

Cost versus Performance Optimization for SQL Server on VMs in Azure

Pam Lahoud takes a look at multi-constraint optimization:

So how do you get the best price-performance possible when configuring your SQL Server on Azure VM? In this blog, we’re going to cover three key aspects to right-sizing (and right-configuring) your Azure VM for SQL Server that are based on some common pitfalls customers face when migrating their on-premises workloads to Azure VM:

– Choosing the best VM series and size for your workload
– Configuring storage for maximum throughput and lower cost
– Leveraging unique to Azure features such as host caching to boost performance at no additional cost

One key point of the article is that there are several factors which can make a big difference in price and performance, but which you might not think about on-premises. It’s definitely worth taking the time to research this. It’s also a great example of how administrators are still important in a cloud-based world—having an admin who understands these settings and can get the most out of a given server can save a lot of money very quickly.

Comments closed

Finding Securables for a Database Role

Jack Vamvas answers a question:

Question: I  need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions  including the Permission,Grantor,Grant,With Grant and Deny.  And also the Securables – Schema,Name,Type

How can I get this information via t-sql?

The only downside in Jack’s query is that it enumerates the securables for the principal. But if the principal is part of an Active Directory group (or multiple groups), this becomes more difficult.

Comments closed

No Respect for NOCOUNT

Thomas LaRock notes an oddity in SQL Server Management Studio and Azure Data Studio:

Anyway, I spend time trying to debug what is happening. I am able to manually set NOCOUNT on and off inside of T-SQL and see a count of rows affected returned (or not). I check and recheck everything I can think of and feel as if I have lost my mind. I’m starting to question how I ever became certified in SQL Server.

I mean, it’s a simple configuration change. This isn’t rocket surgery.

So I do what anyone else in this situation would do.

I turn off my laptop and forget about everything for a few days.

I’d never used this particular style of setting NOCOUNT on for a user (I would always enable it by session using SET NOCOUNT ON), so I’m not sure when certain tools started ignoring the user-level setting, but read the whole thing for maximum intrigue.

Comments closed

Temp Tables and Nested Stored Procedures

John Morehouse takes us through a catch in creating temp tables inside nested stored procedures:

SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables.  One of the capabilities is to allow for stored procedures to call other stored procedures.  This is called “nesting” one stored procedure within the context of the calling stored procedure.  In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.

But wait! I’m not done yet!

You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure.  But there is a catch!

I’ve done the former (and more frequently, had to support when somebody else did the former), but I don’t think I’ve done the latter. Mostly because it seems like it’s an invitation for problems.

Comments closed

Deploying a Storage Solution to a Kubernetes Cluster

Chris Adkin continues a series:

Before we dive into deploying a storage solution to our Kubernetes cluster, we need to understand the basics of storage in the world of Kubernetes, which can appear to be both exotic and mysterious to the uninitiated. To dispel some confusion around Kubernetes and storage, the storage IO path is exactly the same as that with common garden vanilla variety Unix or Linux. The Kubernetes storage ecosystem introduces two extra things we need to concern ourselves with above and beyond conventional Unix/Linux storage, firstly there are some extra layers of abstraction between the physical storage and filesystems that pods use, what I like to refer to as . . .

Read the whole thing. And that was a particularly mean cut-off point on my part, if I do say so.

Comments closed