Press "Enter" to skip to content

Author: Kevin Feasel

Testability and Functional Code

Published 2022-01-28 by Kevin Feasel

I describe why the functional approach to writing code makes it testable:

Another important aspect of functional programming relevant to writing testable Python code is that functions should not have side effects.  In other words, functions take inputs and convert them to outputs; they don’t do anything else.  This approach is aspirational rather than entirely realistic—after all, saving to the database is a side effect, and most applications would be fairly boring if they offered absolutely no way to modify the data.  It just happens to be the case that our outlier detection engine can be close to side effect-free because we do not create files, save to a database, or push results to some third-party service.  With most applications, however, we do not tend to be so lucky.

Click through for an excerpt from the draft of an upcoming book as well as a bit of elucidation on key points. The specific language I’m talking about here is Python but the concepts apply to most languages.

Comments closed

Physical Read Double-Counting in Query Stats

Published 2022-01-28 by Kevin Feasel

David Alcock reviews the latest SQL Server 2019 cumulative update:

Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and love to have a look through the different fixes to see “Physical reads for read-ahead reads are counted incorrectly (two times) when you run queries. Therefore, the information in sys.query_store_runtime_stats and sys.dm_exec_query_stats shows incorrect values.”

Read on to see what this means and a quick test to see if it works as expected.

Comments closed

Row-Level Security and Parallelism

Published 2022-01-28 by Kevin Feasel

Jose Manuel Jurado Diaz hits on an issue with row-level security:

Today, I worked on a service request that our customer reported that running a complex query this is executing in parallel but having more than 2 vCores in Azure SQL Database this query is not using parallelism.

During the troubleshooting process we suggested multiple tips and tricks, but any of them made that Azure SQL Engine uses parallelism: 

Being on-premises versus in Azure turned out to be a red herring and the solution was something maybe even more difficult to spot than triggers.

Comments closed

Finding Performance Regressions

Published 2022-01-27 by Kevin Feasel

Angus Croll embraces the power of control charts:

Technically, “performance” metrics are those relating to the responsiveness or latency of the app, including start up time.

But TV devices also tend to be more memory constrained than other devices, and as such are more liable to crash during a memory spike — so for Netflix TV we actually care about memory at least as much as performance, maybe more so.

At Netflix the term “performance” usually encompasses both performance metrics (in the strict meaning) and memory metrics, and that’s how we’re using the term here.

Click through for a primer on how they use changepoint and anomaly detection to do this.

Comments closed

Temporal Tables and Azure DevOps Deployments

Published 2022-01-27 by Kevin Feasel

Rayis Imayev notes a problem with Azure DevOps deployments:

Here is one thing that still doesn’t work well when you try to alter an existing temporal table and run this change through the [SqlAzureDacpacDeployment@1] DevOps task, whether this change is to add a new column or modify existing attributes within the table. Your deployment will fail with the “This deployment may encounter errors during execution because changes to … are blocked by …’s dependency in the target database” error message.

Read on to see what causes this problem and what we can do to work around it.

Comments closed

Checking a SQL Server Database’s Master Key

Published 2022-01-27 by Kevin Feasel

Steve Jones is trying to find where he left his keys:

I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.

Click through to see how you can create a database master key as well as how to drop it if necessary.

Comments closed

Combining Transparent Data Encryption and Instant File Initialization

Published 2022-01-27 by Kevin Feasel

Tom Collins performs a test:

Instant File Initialization (IFI) enabled  on  SQL Server, for data files only, leads to faster  execution on  certain file operations, as  it bypasses the zeroing procedure while reclaiming disk space. Instead, disk content is overwritten as new data is written to the files.

But do these benefits continue  when Transparent Data Encryption (TDE) is enabled ?   

Click through for the answer.

Comments closed

January 2022 Updates for Azure Synapse Analytics

Published 2022-01-27 by Kevin Feasel

Saveen Reddy has an update for us:

You can now easily add data quality, data validation, and schema validation to your Synapse ETL jobs by leveraging Assert transformation in Synapse data flows. Add expectations to your data streams that will execute from the pipeline data flow activity to evaluate whether each row or column in your data meets your assertion. Tag the rows as pass or fail and add row-level details about how a constraint has been breached. This is a critical new feature to an already effective ETL framework to ensure that you are loading and processing quality data for your analytical solutions.

Read on for the full list of changes.

Comments closed

Using Proprietary Database Features

Published 2022-01-27 by Kevin Feasel

Lee Markum does some thinking:

I was recently thinking of SQL Server temporal tables and how there is a perspective that you shouldn’t use proprietary features of a product because it locks you into that product. I want to be your guide on this matter.

I agree completely with Lee’s take on this. I can count on one hand the number of platform migrations I’ve been a part of through the years. These are painful enough experiences even if you use 100% “portable” code because optimization patterns change. And then you get to the utterly absurd: indexes in Oracle and SQL Server behave differently (e.g., clustered indexes are a practical must in SQL Server and the equivalent to clustered indexes in Oracle is so rarely used that it’s not even worth talking about to most Oracle people), so are you going to avoid indexes so you can have “truly” portable code? If the answer to that question is “yes,” you are wrong.

I do understand that there are companies which create code bases which need to be installable on multiple platforms. In that case, I do understand trying to keep things as common as possible, especially for fairly simple apps. But this is also part of why vendor databases are, as a general rule, awful.

Comments closed