Press "Enter" to skip to content

Curated SQL Posts

Moving Stack Overflow to Azure

Published 2022-12-28 by Kevin Feasel

Aaron Bertrand gets into the whats and wherefores:

Like many companies, Stack Overflow is trying to get out of the business of running our architecture in our own data centers; instead, we want to offload some of the more mundane parts of system administration to a cloud service offering like Azure.

I’m going to cut to the chase for the purpose of this article and concede we’ve already decided on Azure for the majority of our infrastructure and, most importantly to me, our databases.

Click through to learn what their plan is and why Aaron & co went that particular route.

Comments closed

Avoiding Problems with DAX Window Functions

Published 2022-12-28 by Kevin Feasel

Jeffrey Wang shares a few tips to avoid issues with window functions in DAX:

Several people had reported running into errors when trying the window functions on fact tables. Let’s look at an example by first adding a calculated table of three columns to the model that is defined by the following DAX expression, and then, add all three columns to a table visual as shown in Figure 1.

Read on for more. This is an area where expected behavior does differ from what you get with window functions in T-SQL.

Comments closed

Interpreting Linear Models with SHAP

Published 2022-12-27 by Kevin Feasel

Michael Mayer answers a question:

XGBoost models are often interpreted with SHAP (Shapley Additive eXplanations): Each of e.g. 1000 randomly selected predictions is fairly decomposed into contributions of the features using the extremely fast TreeSHAP algorithm, providing a rich interpretation of the model as a whole. TreeSHAP was introduced in the Nature publication by Lundberg and Lee (2020).

Can we do the same for non-tree-based models like a complex GLM or a neural network? Yes, but we have to resort to slower model-agnostic SHAP algorithms:

Read on for examples of those algorithms and an example of interpretation and analysis.

Comments closed

Row-Level Security and Data Migration

Published 2022-12-27 by Kevin Feasel

Forrest McDaniel shares an interesting case of using row-level security:

This was the situation I found myself in earlier this year – our company had absorbed another, and it was time to slurp up their tables. There were a lot of decisions to make and tradeoffs to weigh, and we ended up choosing to trickle-insert their data, but make it invisible to normal use until the moment of cutover.

The way we implemented this was with Row Level Security. Using an appropriate predicate, we could make sure ETL processes only saw migrated data, apps saw unmigrated data, and admins saw everything. To give a spoiler: it worked, but there were issues.

I would not have thought of this scenario. And given the difficulties Forrest & crew ran into, it might be for the best…

Comments closed

Procedures for Reviewing SQL Server Security

Published 2022-12-27 by Kevin Feasel

Lee Markum continues a series on SQL Server security. Part 2 looks at sp_DBPermissions:

Again, the internet is awash with scripts and options so let me give you a place to start.

Kenneth Fisher’s sp_DBPermissions

From the comments in the stored procedure, “This stored procedure returns 3 data sets. The first data set is the list of database principals, the second is role membership, and the third is object and database level permissions.”

Part 3 reviews security options in sp_Blitz:

Open source provides a lot of fantastic scripts and software. One of the more popular ones is the First Responder Kit. In that collection of scripts is sp_Blitz. This script is great because it provides a lot of information. The script results can also be problematic, because it provides so much information. On my little SQL Server I use for testing scripts and writing demos, sp_Blitz returned 68 rows across 8 priority levels or categories of issues. Again, wonderful if you want to see all the things! But, what if you’re trying to focus on a particular type of issue, like security? What do you do then?

Comments closed

Finding Data Factory Objects in Synapse Studio

Published 2022-12-27 by Kevin Feasel

Kevin Chant pulls out the magnifying glass and compass:

In this post I want to cover where you can find Azure Data Factory objects in Synapse Studio. I want to do this post for a couple of reasons.

First reason is that at the start of the year I published a post on how to automate a Data Factory pipeline migration to an Azure Synapse Analytics workspace using Azure DevOps.

Even though I showed one way that you can automate the migrations of a Data Factory to a Synapse workspace I did not show where you can view them in Synapse Studio.

Second reason is because I keep telling everybody they can use the same pipelines in Azure Synapse Analytics but the objects can be found in different places.

Read on for the two places to find Data Factory objects.

Comments closed

Degenerate Dimensions and Power BI DirectQuery

Published 2022-12-27 by Kevin Feasel

Chris Webb provides a warning:

A few weeks ago my colleague Dany Hoter wrote a post on the Azure Data Explorer blog about how using columns on fact tables as dimensions in DirectQuery mode can lead to errors in Power BI. You can read it here:

https://techcommunity.microsoft.com/t5/azure-data-explorer-blog/to-star-or-not-to-star-more-about-data-modeling-in-power-bi/ba-p/3689933

In the post he mentioned that he could reproduce the same behaviour in SQL Server, so I thought it would be good to show an example of this to raise awareness of the issue because I think it’s one that anyone using DirectQuery mode on any data source is likely to run into.

Read on to understand what might lead to (expected) errors and what you can do about it.

Comments closed

Troubleshooting Memory Constraints in SQL Server

Published 2022-12-27 by Kevin Feasel

Ajay Dwivedi is running low on memory:

Memory Pressure on SQL Server is very common and is presented as Memory Grant Pending metric in the above dashboard screenshot. In my previous blog Troubleshooting SQL Server With High Memory Grants, I discussed what actions should be taken in order to get the server out of constant memory grant issues in real-time.

For long-term resolution, there are various metrics to be validated before making any decision.

Read on for several tips to help you decide the best course of action.

Comments closed

Working with R in AML v2

Published 2022-12-23 by Kevin Feasel

Tomaz Kastrun ends the advent of Azure ML on a downer:

R language and Azure Machine Learning SDK for R was deprecated a year ago (end of 2021). But R can be still used for training and deployment by using Azure Machine learning CLI 2.0!

Furthermore, R language can be used in Machine Learning Designer, for data preparation, data wrangling and statistical analysis.

You can work with R but they make sure everything is more difficult.

Comments closed