Press "Enter" to skip to content

Author: Kevin Feasel

Upgrading Servers in an Availability Group

Published 2019-10-18 by Kevin Feasel

Thomas Rushton has a checklist for upgrading servers connected by an Availability Group:

This is a checklist put together and followed for an upgrade of a pair of physical SQL Server 2012 servers which hosted a single availability group of several terabytes of data with minimal downtime.

The availability group was configured with synchronous commit and automatic failover.

Minimizing downtime here is great, but it’s not automatic: you still need to do work on your end to get this right.

Comments closed

Making the SQL Server 2019 Edition Cut

Published 2019-10-18 by Kevin Feasel

Brent Ozar noticed an update to Microsoft’s “what’s in which edition” document:

The most important stuff:
– Standard Edition is still capped at 128GB RAM.
– Accelerated Database Recovery is in Standard Edition.
– Automatic tuning, batch mode for row store, adaptive memory grants, adaptive joins, and memory-optimized TempDB are Enterprise Edition only.

Click through for more of Brent’s thoughts. Of interest to me is that PolyBase control nodes may now run in Standard Edition. It makes me wonder if they’ll cap that feature somehow, where you get N nodes maximum in a Standard Edition scale-out group, or if it’s uncapped like Enterprise Edition.

Comments closed

AGs and Redo Latency

Published 2019-10-18 by Kevin Feasel

David Fowler explains that even in synchronous mode, there can still be redo latency for Availability Groups:

There’s a misconception that if you have a synchronous replica, that replica has to commit any transactions that are passed to it by the primary before the primary can then commit those same transactions. This ensures that there is no possibility of data loss and that the data would always be available on the secondary.

With this misconception comes our problem.

Read on to understand the full issue.

Comments closed

Scala Views

Published 2019-10-17 by Kevin Feasel

Girish Bharti takes us through a performance-tuning technique in Scala:

We all know the power of lazy variables in Scala programming. If you are developing the application with huge data then you must have worked with the Scala collections. Some mostly used collections are List, Seq, Vector, etc. Similarly, you must be aware of the power of Streams. The streams are a very powerful tool for handling the infinite flow of data and streams are powerful because of there lazy transformations. As we know most of the Scala collections are strict so applying an operation on immutable collections creates a new collection. The size of the collection can be huge in the big data world. So, what if you have to apply a lot of transformations to the collection? Is there a way to handle collections in a lazy way? What if you can find a way to apply operations on your usual collections lazily? In this blog, we will be talking about the Scala views and how to use them.

Read the whole thing.

Comments closed

Delta Lake to Become an Open Standard

Published 2019-10-17 by Kevin Feasel

Michael Armbrust and Reynold Xin have exciting news about Delta Lake:

At today’s Spark + AI Summit Europe in Amsterdam, we announced that Delta Lake is becoming a Linux Foundation project. Together with the community, the project aims to establish an open standard for managing large amounts of data in data lakes. The Apache 2.0 software license remains unchanged.

Delta Lake focuses on improving the reliability and scalability of data lakes. Its higher level abstractions and guarantees, including ACID transactions and time travel, drastically simplify the complexity of real-world data engineering architecture. Since we open sourced Delta Lake six months ago, we have been humbled by the reception. The project has been deployed at thousands of organizations and processes exabytes of data each month, becoming an indispensable pillar in data and AI architectures.

Read on to see what this means for Delta Lake.

Comments closed

Benchmarking JSON Query Times

Published 2019-10-17 by Kevin Feasel

Silvano Coriani compares different options for loading and querying JSON data in Azure SQL Database:

Storing and retrieving data from JSON fragments is a common need in many application scenarios, like IoT solutions or microservice-based architectures. These fragments can be persisted in a variety of data stores, from blob or file shares, to relational and non-relational databases, and there’s a long standing debate in the industry on what’s the database technology that fits “better” for this task.
 
Azure SQL Database offers several options for parsing, transforming and querying JSON data, and this article doesn’t pretend to provide a definitive answer to that debate, but rather to explore these options for common scenarios like data loading and retrieving, and benchmarking results to provide a clear indication of how Azure SQL Database will perform manipulating JSON data.

Read on for the results.

Comments closed

Query Folding with Power BI Dataflows

Published 2019-10-17 by Kevin Feasel

Matthew Roche shares a few important points about Power BI dataflows and query folding:

In a recent post I mentioned an approach for working around the import-only nature of Power BI dataflows as a data source in Power BI Desktop, and in an older post I shared information about the enhanced compute engine that’s currently available in preview.

Some recent conversations have led me to believe that I should summarize a few points about dataflows and query folding, because these existing posts don’t make them easy to find and understand.

Read on for those points.

Comments closed

Fixing Key Lookup Problems

Published 2019-10-17 by Kevin Feasel

Erik Darling has a couple techniques for mitigating key lookup-related performance problems:

They’re one of those things — I’d say even the most common thing — that makes parameterized code sensitive to the bad kind of parameter sniffing, so they get a lot of attention.

The thing is, most of the attention that they get is just for columns you’re selecting, and most of the advice you get is to “create covering indexes”.

That’s not always possible, and that’s why I did this session a while back on a different way to rewrite queries to sometimes make them more efficient. Especially since key lookups may cause blocking issues.

Read on to see what you can do when a covering index isn’t a viable option.

Comments closed

Training, Validation, and Test Data Sets with SAS Viya

Published 2019-10-16 by Kevin Feasel

Beth Ebersole takes us through creating training, validation, and test data sets using SAS Viya:

Training data are used to fit each model. Training a model involves using an algorithm to determine model parameters (e.g., weights) or other logic to map inputs (independent variables) to a target (dependent variable). Model fitting can also include input variable (feature) selection. Models are trained by minimizing an error function.

For illustration purposes, let’s say we have a very simple ordinary least squares regression model with one input (independent variable, x) and one output (dependent variable, y). Perhaps our input variable is how many hours of training a dog or cat has received, and the output variable is the combined total of how many fingers or limbs we will lose in a single encounter with the animal.

Read on for some good notes, including the difference between mean squared error and average squared error.

Comments closed