Press "Enter" to skip to content

Author: Kevin Feasel

Antipattern Queries Extended Event in SQL Server

Published 2023-02-06 by Kevin Feasel

Bob Ward enumerates some anti-patterns SQL Server can guilt you over:

If a query uses certain antipatterns, it will be detected during query optimization.  For both SQL Server and Azure SQL (internally on by default), if these antipatterns are detected when optimizing the query, and the query_antipattern event has been added as part of a running extended event session, the output will be captured.  The output will contain the relevant capture fields configured for the extended event session, allowing one to quickly identify which queries contain these antipatterns and are, therefore, prime candidates for tuning.

Read on for more information about this extended event, which is new to SQL Server 2022. I haven’t used this yet, so the two caveats I’m about to give are speculative in nature…though when has that ever stopped me? Caveat the first: just because something shows up as an anti-pattern doesn’t mean it needs to be fixed. There can be good reasons why you have chosen what is normally a less-efficient path. Caveat the second: just because something doesn’t show up as an anti-pattern doesn’t mean it’s fine. These are likely directional and my guess is that SQL Server will be fairly conservative in its estimation of what constitutes an anti-pattern so that you don’t get a lot of false positives.

Comments closed

Rotating TDE Certificates

Published 2023-02-06 by Kevin Feasel

Matthew McGiffen switches out a certificate:

In terms of encryption, Key Rotation is the process of replacing your encryption keys on a periodic basis. This is considered good practice and is required by many security certifications.

In practice, if you had to rotate/replace the key that is used to encrypt your data then that would be an intensive activity requiring all your data to be decrypted with the old key before being replaced with the new. This could also create a vulnerability where data sits in an unencrypted state during the process.

Instead, see what SQL Server does by reading Matthew’s blog post.

Comments closed

Power BI Report Source Redirection

Published 2023-02-06 by Kevin Feasel

Dan English re-binds some reports:

This is a follow up to the post Azure Analysis Services (AAS) Migration to Power BI Premium Experience that I did back in December. In that post I mentioned the following towards the end of the post –

If you have existing Power BI reports using the AAS live connection you are able to use the report rebind API to point the report to use the Power BI dataset. The one thing to note as pointed out in the documentation is that as of now if you need to modify the report layout you would need to do that directly in the service. That portion of the migration process is still being worked on and hopefully should be updated to resolve the issue so that you will be able to download the redirected report from the service, modify it, and then publish the report back to the Power BI service.

In this post I want to touch on this a bit more and provide some follow up based on work that has taken place since that post on the migration rebind (redirection) process.

Dan shows the process and also tells us about a gotcha along the way.

Comments closed

Dealing with Imbalanced Class Data for Image Classification

Published 2023-02-03 by Kevin Feasel

Alexander Billington needs more beta carotene:

Image classification is a standard computer vision task and involves training a model to assign a label to a given image, such as a model to classify images of different root vegetables. A big problem with classification is bias, and the models favouring a particular image class above the others. A common cause of this can be dataset imbalance, and it is often hard to spot as a model trained on an imbalanced dataset can often still have good accuracy. E.g. if there are 1000 images in the test dataset, 950 potatoes and 50 carrots and the model predicted all 1000 images to be potatoes it would still have 95% accuracy. This is also an example of why more metrics than accuracy should be considered… but let’s leave that discussion for another day.

Click through for several techniques you can use to balance out classes, with a focus on image classification. Undersampling is almost always a no-go for me, though I am much fonder of the other techniques.

Comments closed

Multi-Class Classification in PyTorch

Published 2023-02-03 by Kevin Feasel

Adrian Tam does some iris categorizing:

Now you need to have a model that can take the input and predict the output, ideally in the form of one-hot vectors. There is no science behind the design of a perfect neural network model. But you know one thing, it has to take in a vector of 4 features and output a vector of 3 values. The 4 features corresponds to what you have in the dataset. The 3-value output is because we know the one-hot vector has 3 elements. Anything can be in between, and those are known as the “hidden layers” since they are neither input nor output.

Click through for the full tutorial.

Comments closed

Flink 1.16.1 Release

Published 2023-02-03 by Kevin Feasel

Martijn Visser announces Apache Flink version 1.16.1:

The Apache Flink Community is pleased to announce the first bug fix release of the Flink 1.16 series.

This release includes 84 bug fixes, vulnerability fixes, and minor improvements for Flink 1.16. Below you will find a list of all bugfixes and improvements (excluding improvements to the build infrastructure and build stability). For a complete list of all changes see: JIRA.

We highly recommend all users upgrade to Flink 1.16.1.

Read on for the release notes, including links to all of the closed tickets.

Comments closed

Azure Load Testing Now GA

Published 2023-02-03 by Kevin Feasel

Darryl Taft provides an overview of a now generally available service:

Moreover, Azure Load Testing collects detailed resource metrics to help you identify performance bottlenecks across your Azure application components. You can automate regression testing by running load tests as part of your CI/CD workflow.

Azure Load Testing also creates monitoring data using the Azure Monitor service, including application insights and container insights, to capture details from the Azure services.

It’s available in 11 regions, including the best region of all (East US) and the second-best region of all (East US 2).

Comments closed

Migrating from Elasticsearch to Azure Data Explorer

Published 2023-02-03 by Kevin Feasel

Bhaskar Kakaraparthy does a logging switcharoo:

This article is an extension to an existing article to migrate data from Elastic Search to Azure Data Explorer (ADX) using Logstash pipeline as a step-step-step guide.  In this article, we will explore the process involved in migrating data from one source (ELK) to another (ADX) and discuss some of the best practices and tools available to make the process as smooth as possible.

Using Logstash for data migration from Elasticsearch to Azure Data Explorer (ADX) was a smooth and efficient process. With the help of ADX output plugin & Logstash, I was able to migrate approximately 30TBs of data in a timely manner. The configuration was straightforward, and the data transfer with ADX output plugin was quick and reliable. Overall, the experience of using ADX output plugin with Logstash for data migration was positive and I would definitely use it again for similar projects in the future.

Read on to see how.

Comments closed

Database Constraints in Postgres

Published 2023-02-03 by Kevin Feasel

Grant Fritchey does some data modeling:

PostgreSQL supports constraints much like any other database management system. When you need to ensure certain behaviors of the data, you can put these constraints to work. I’ve already used several of these in creating my sample database (available articles publicly on GitHub, in the CreateDatabase.sql file). I’ll explain those as we go. The constraints supported by PostgreSQL are:

Read on for the list, which includes one constraint which doesn’t have a direct analog in SQL Server.

Comments closed