Press "Enter" to skip to content

Curated SQL Posts

Setting CPU Affinity (Correctly)

Published 2023-01-16 by Kevin Feasel

Taiob Ali does something out of the ordinary:

Setting CPU affinity in SQL Server is not a task you do every day. Rarely are there use cases when you need to do that. I had a recent requirement to do it. We plan to replace a physical server with half of its current CPU. Primarily due to faster CPU and workload moved off of SQL Server to other cloud services. To test, we needed to set the CPU affinity mask in one of our non-production servers. In the research, I learned about the side effect of setting CPU affinity mask, which is nicely explained in this ( by Klaus Aschenbrenner) and this (by Adam Denby) blog post.

Click through to learn more about the process.

Comments closed

Avoiding Dynamic Data Sources Error with OData.Feed

Published 2023-01-16 by Kevin Feasel

Chris Webb avoids an error altogether:

In my last post I showed how, in many cases, you can avoid the “dynamic data sources” error with OData data sources by taking advantage of query folding. That’s not always possible though and in this post I’ll show you how you can use the Query option of the OData.Feed function to do so instead.

As always, Chris provides some nice detail and good examples.

Comments closed

Using the Native Pipe in R 4.1+

Published 2023-01-13 by Kevin Feasel

Michael Mayer shows off the native R pipe:

What does the pipe do? It puts the object on its left as the first argument into the function on its right: iris %>% head() is a funny way of writing head(iris). It helps to avoid long function chains like f(g(h(x))), or repeated assignments.

In 2021 and version 4.1, R has received its native forward pipe operator |> so that we can write nice code like this:

Tying pipe syntax all back together, the magrittr pipe %>% was (as I recall) built with the F# pipe |> in mind. In R 4.1 and later, the built-in pipe is |>, as is right and natural in this world. Regardless, do check the comment before trying out this code, as it appear to work for R 4.2 and later, though not 4.1.

Comments closed

Writing Tests with shinytest2

Published 2023-01-13 by Kevin Feasel

Russ Hyde continues a series on shinytest2:

Here, we will write a simple shiny app (as an R package) and show how to generate tests for this app using {shinytest2}. As discussed in the previous post, {shinytest2} tests your app as if a user was interacting with it in their browser. The tests generated are application-focussed rather than component-focussed and so give some overall guarantees on how the app should behave.

This post is slightly more technical than the last, and assumes that the reader is comfortable with creating and unit-testing packages in R, and with shiny development in general.

Click through to see the code, as well as plenty of explanation.

Comments closed

Reviewing the Power BI Admin Portal

Published 2023-01-13 by Kevin Feasel

Reza Rad looks at administrative options:

In the world of Power BI, there are some configurations in the Desktop tool and some on the Service. One of these critical configurations is the Tenant Settings of the Power BI administrator panel. Tenant settings have a list of highly important configurations across your Power BI tenant. If you miss configuring the settings properly, it may result in leaking the data, authorizing people who should not be authorized to see reports and many other catastrophic scenarios. In this article and video, you will learn the configurations available in Tenant settings and the recommended options for each. If you want to learn more about Power BI, read the Power BI book from Rookie to Rock Star.

Click through for a video, as well as a detailed description of what’s available in the admin portal.

Comments closed

Troubleshooting High I/O Usage on Azure SQL DB

Published 2023-01-13 by Kevin Feasel

Etienne Lopes troubleshoots a strange issue:

After the downsizing (to GeneralPurpose: Standard-series (Gen5), 2 vCores) occasionally there were timeouts in the application for a very specific task (the command timeout property in the application was set to 30 seconds). Other times the very same task would execute immediately, as it should always, since the underlying query was actually quite simple: a SELECT to a single, although large table (58 GB) but with a predicate that would always result in a perfect index seek to return never more than 300 rows. Furthermore each time there were timeouts, there were also momentary I/O spikes up to 100%:

Read on to learn more about what caused this problem and how Etienne was able to resolve it.

Comments closed

Defending (Certain) Bad Practices

Published 2023-01-13 by Kevin Feasel

Aaron Bertrand considers the trade-offs:

For the first T-SQL Tuesday in 2023, Raul Gonzalez invites us to talk about cases where we have knowingly implemented worst practices.

Well, I have done it a lot. Most of the posts in my bad habits series are cautionary tales based on my own “learning the hard way.” There are always trade-offs with doing something correctly – maybe proper design is less efficient, or takes longer to write, or has to pass more checks. Over time, though, you start getting a feel for where it makes sense to cut these corners, and where it doesn’t.

Read on for some practical examples around Stack Overflow.

Comments closed

Business Continuity with Arc-Enabled Data Services

Published 2023-01-13 by Kevin Feasel

Warwick Rudd continues a series on Azure Arc-Enabled Data Services. Part 11 covers high availability:

So far in this series of posts, you have been able to deploy and configure your newly provisioned Azure Arc-enabled SQL MI environment. Out of the box you get High Availability without having to do or implement anything.

The Recovery Time Objective (RTO) that is achievable with Azure Arc-enabled Data Services is dependent on the tier you choose to deploy. But regardless of that, this post is only concerned about informing you what you get out of the box with this technology.

Part 12 turns to disaster recovery:

In the previous post, we introduced you to how Azure Arc-enabled SQL MI provides High Availability based on the tier you have deployed.  If your environment requires disaster recovery, regardless of the tier level you have deployed, Azure Arc-enabled Data Services covers the job for you.

Read on to learn more about what options are available and what you need to do.

Comments closed

Saving Time in Power BI

Published 2023-01-12 by Kevin Feasel

Allison Kennedy shares a few tips:

I myself still refer back to that blog post whenever I start a new project where there isn’t a date table already in the data source. Below are a few of my other favorite resources that I visited most frequently in 2022.

Click through for the list of posts and the helpful tips they contain therein.

Comments closed