Press "Enter" to skip to content

Author: Kevin Feasel

Group Managed Service Accounts

Published 2019-02-27 by Kevin Feasel

Jamie Wick explains Group Managed Service Accounts and uses Powershell to create them for use on a new SQL Server instance:

Service Accounts are a requirement for installing and running a SQL Server. For many years Microsoft has recommended that each SQL Server service be run as a separate low-rights Windows account. Where possible, the current recommendation is to use Managed Service Accounts (MSA) or Group Managed Service Accounts (gMSA
). Both account types are ones where the account password is managed by the Domain Controller. The primary difference being that MSA are used for standalone SQL instances, whereas clustered SQL instances require gMSA. In this post, we’re going to use PowerShell to create Group Managed Service Accounts, and then deploy them for use on multiple SQL servers that will be hosting an Availability Group.

Click through for more explanation as well as several scripts showing how to create and use them.

Comments closed

Performance Testing Scalar UDF Improvements

Published 2019-02-27 by Kevin Feasel

Wayne Sheffield tests out the scalar UDF performance improvements in SQL Server 2019:

This blog post will examine changes to the query plan and performance when Scalar UDF Inlining is occurring.

I have previously blogged about function performance – here and here. For a quick recap, the performance test ranks these function in duration. The order of the types of functions by duration is Inline TVF, Scalar UDF, and then finally a Multi-Statement TVF (MSTVF) – and the MSTVF is way behind the other two types of functions.

I’m using a Linux (Ubuntu) VM with SQL Server 2019 to perform these comparison performance tests. I use one database in the SQL 2019 compatibility level, and another one in the SQL 2017 compatibility level. I’m using the same performance test used in the previous blog posts.

Wayne finds a definite performance improvement, but not enough in my mind to start creating a bunch of these.

Comments closed

Learning More About Azure Data Lake Storage Gen2

Published 2019-02-27 by Kevin Feasel

Melissa Coates shares a compendium of links pertaining to Azure Data Lake Storage Generation 2:

A couple of people have asked me recently about how to ‘bone up’ on the new data lake service in Azure. The way I see it, there are two aspects: A, the technology itself and B, data lake principles and architectural best practices. Below are some links to resources that you should find helpful.

There’s a lot of good stuff there.

Comments closed

UNCOMPRESS Isn’t DECOMPRESS

Published 2019-02-27 by Kevin Feasel

Solomon Rutzky strives to solve the question, “What is the UNCOMPRESS function anyhow?”:

With no clear indications of what the UNCOMPRESS function does, we can at least pass in some simple values to see what comes back, and see if we can make sense of the output. For the following tests, please keep in mind that “8-bit” refers to the VARCHAR, CHAR, and TEXT (deprecated) datatypes. And, “16-bit” refers to the NVARCHAR, NCHAR, NTEXT (deprecated),and XML datatypes.

Read on as Solomon figures out what it does and how non-useful it is for anybody nowadays.

Comments closed

Selecting a List of Columns from Spark

Published 2019-02-26 by Kevin Feasel

Unmesha SreeVeni shows us how we can create a list of column names in Scala to pass into a Spark DataFrame’s select function:

Now our example dataframe is ready.
Create a List[String] with column names.
scala> var selectExpr : List[String] = List("Type","Item","Price") selectExpr: List[String] = List(Type, Item, Price)

Now our list of column names is also created.
Lets select these columns from our dataframe.
Use .head and .tail to select the whole values mentioned in the List()

Click through for a demo.

Comments closed

Microservice Communication Patterns

Published 2019-02-26 by Kevin Feasel

John Hammink shares a few ways that you can have microservices communicate with one another and argues that Kafka is a great platform for microservice communication:

Simply put, microservices are a software development method where applications are structured as loosely coupled services. The services themselves are minimal atomic units which together, comprise the entire functionality of the entire app. Whereas in an SOA, a single component service may combine one or several functions, a microservice within an MSA does one thing — only one thing — and does it well.

Microservices can be thought of as minimal units of functionality, can be deployed independently, are reusable, and communicate with each other via various network protocols like HTTP (More on that in a moment).

Read the whole thing. I have a love-hate relationship with these but it’s a pattern worth understanding.

Comments closed

Power BI IntelliSense For Python and R

Published 2019-02-26 by Kevin Feasel

David Eldersveld makes me wonder about the value of Power BI’s IntelliSense for R and Python:

If I type the letter a into the R Script editor, my code completion options are acts, always, and, and as. Power BI’s editor is not offering any IntelliSense options from a Python or R dictionary. Instead, it’s pulling from the text already in the editor. Note the comment in Line 1 and the inclusion of words beginning with the letter a — always, and, acts, as.

By comparison, the DAX editor contains a detailed function list and helpful annotations for code completion. Can we get something similar for R and Python? Not exactly… But there’s a workaround that I’m almost embarrassed to suggest. If you are a user who codes directly into the script editor, the following hack could be helpful. If you use the option to Edit script in External IDE, keep doing that and ignore the following guidance.

As-is, this is worse than no IntelliSense because at least with no IntelliSense, it’ll never steal a mouse click or keystroke. I wouldn’t expect RStudio level quality out of the gate but unless I’m missing something, that’s pretty bad.

1 Comment

Copying Filestream Data Between Tables

Published 2019-02-26 by Kevin Feasel

Paul Randal takes us through some limitations on copying Filestream data between tables:

I was asked last week whether it’s possible to create a table with a FILESTREAM column and then populate that column by copying FILESTREAM files from another directory in the FILESTREAM data container.

The simple answer is no.

Paul explains why this isn’t possible and then gives you an alternative which does work.

Comments closed

Eye-Friendly Palettes

Published 2019-02-26 by Kevin Feasel

Shannon Holck has shared a Power BI theme using a color-safe and easy to view palette:

Edward Tufte recommended use of soft colors that do not tire the eyes.  I’ve actually never read his books (yet), but a former boss of mine was a devout disciple and produced some beautifully soft color palettes.

Stephen Few, in “Show Me the Numbers,” reiterated Tufte’s color theories and recommended three sets of hues:

Light – for large shapes, e.g. bars
Medium – for small shapes, e.g. points
Dark/Bright – for calling attention to data

Click through for more including where you can get this Power BI theme. I’m not exactly the world’s biggest fan of the default palette so I’ll have to check this one out.

Comments closed

Blaming the Right Cardinality Estimator

Published 2019-02-26 by Kevin Feasel

Arthur Daniels helps us figure out which of SQL Server’s cardinality estimators your query used:

SQL Server 2008 is reaching end of support this year, so upgrading your SQL Server might be on your mind. One of the big changes when you upgrade your SQL Servers is upgrading the compatibility level, which by default will upgrade the cardinality estimator (CE).

This can change query performance, for better or for worse. This post won’t focus on whether it’s good or bad, but instead I want to show you how you can check to see what CE was used by your queries.

It’s not a 100% guarantee, but generally I’ve found the new estimator to be superior.

Comments closed