Press "Enter" to skip to content

Day: March 29, 2021

Temp Tables and Nested Stored Procedures

Published 2021-03-29 by Kevin Feasel

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

Published 2021-03-29 by Kevin Feasel

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

Error Messages on SSDT Database Project Deployments

Published 2021-03-29 by Kevin Feasel

Chris Johnson has some advice if you’re hitting an error when deploying a SQL Server Data Tools database project:

Today I’d like to talk about three error messages you might see when deploying an SSDT database project, either through Visual Studio or via a dacpac and script. I’m going to focus here on what you see from inside of Visual Studio, but you will see similar errors returned when you deploy using a script and the reasons behind them will be the same.

Read on for Chris’s findings. These errors definitely aren’t a complete survey of possible messages, but they do hit some of the less obvious cases.

Comments closed

Finding Query Performance Issues with Query Store

Published 2021-03-29 by Kevin Feasel

Andrea Allred has a primer on Query Store:

So if I can’t modify or add indexes and I can’t change code, how do I get my query times to drop? Query Store to the rescue.

I love to tune queries. I feel so satisfied to see the times dropping on my server as I tune things. Recently, I have been tracking my Batch Requests per Second and my Instance Waits to see if I am making improvements when I tune. It has been awesome!

What I am going to show you today is how I dig into my query store to find those misbehaving queries and make their performance better.

Click through for some high-level tips on how to use Query Store.

Comments closed

Limitations with Control Flows in Azure Data Factory

Published 2021-03-29 by Kevin Feasel

Meagan Longoria has a list:

If you’ve been using Azure Data Factory for a while, you might have hit some limitations that don’t exist in tools like SSIS or Databricks. Knowing these limitations up front can help you design better pipelines, so I’m listing a few here of which you’ll want to be aware.

1. You cannot nest For Each activities.
Within a pipeline, you cannot place a For Each activity inside of another For Each activity. If you need to iterate through two datasets you have two main options. You can combine the two datasets before you iterate over them. Or you can use a parent/child pipeline design where you move the inner For Each activity into the child pipeline. Fun fact: currently the Data Factory UI won’t stop you from nesting For Each activities. You won’t find out until you try to execute the pipeline.

Click through for several other limitations and workarounds.

Comments closed

An Introduction to Azure Purview Studio

Published 2021-03-29 by Kevin Feasel

Rahul Mehta takes a look at Azure Purview’s current user interface:

Metrics in Azure Purview are integrated and reported using Azure Monitor. Click on the Metrics section and it would have a link to Azure Monitor, which would open in a new tab as shown below. From this interface, we can filter and split different data metrics, plot the same on different types of graphs, as well as build custom dashboards to visualize the metrics to evaluate performance. The available metrics depend on the features enabled in the Azure Purview account. Metrics may have different levels of granularity and this may lead to a significant amount of data. The data generated can be aggregated in these dashboards using the available aggregation functions.

Click through for a quick walkthrough.

Comments closed