Press "Enter" to skip to content

Month: October 2023

Fast Key Optimization in SQL Server

Published 2023-10-20 by Kevin Feasel

Paul White explains the performance benefits of fast key optimization and when you can get them:

SQL Server can be called upon to sort a variety of data types. To facilitate this, the sorting code normally calls out to a specific comparator to determine how two compared values should sort: lower, higher, or equal.

Although calling comparator code has low overhead, performing enough of them can cause noticeable performance differences.

To address this, SQL Server has always (since at least version 7) supported a fast key optimization for simple data types. 

Click through to learn which data types support fast key optimization and to gain a feeling of the performance impact.

Comments closed

Taking over a Personal Power BI Workspace

Published 2023-10-20 by Kevin Feasel

Olivier Van Steenlandt performs a hostile takeover:

The end user can save a Power BI report created in the Power BI Service in their own Personal Workspace but then they have no way to share it with their team. It remains a personal report/analysis.

When executing the analysis in Excel, they can distribute the Excel-file as they see fit.

The pitfall of personal workspaces in Power BI, from my point of view, is that if someone leaves the company, there isn’t a straight forward way for other end users to  take-over previously created analysis.

This is where the Power BI Administrator can be of importance.

Read on to see how a Power BI Administrator can gain access to a personal workspace and migrate reports out of it.

Comments closed

Using Data Contracts in Confluent Schema Registry

Published 2023-10-19 by Kevin Feasel

Robert Yokota shows us how to generate data contracts for streaming solutions:

A data contract is a formal agreement between an upstream component and a downstream component on the structure and semantics of data that is in motion. The upstream component enforces the data contract, while the downstream component can assume that the data it receives conforms to the data contract. Data contracts are important because they provide transparency over dependencies and data usage in a streaming architecture. They help to ensure the consistency, reliability, and quality of the data in event streams, and they provide a single source of truth for understanding the data in motion.

Click through for a sample application that uses data contracts.

Comments closed

ConstantCare Population Report for Fall 2023

Published 2023-10-19 by Kevin Feasel

Brent Ozar shares some percentages:

For the long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2023 version of our SQL ConstantCare® population report.

Out of the thousands of monitored SQL Servers, a whopping 44% are SQL Server 2019! That’s the highest percentage we’ve seen for any version in the 3 years that we’ve been doing this analysis.

Standard statistical sampling rules apply, though there is an interesting note in the comments about how EC2 instances break down in AWS by version of SQL Server, and the numbers are reasonably similar.

Comments closed

Query Compilation Time Matters

Published 2023-10-19 by Kevin Feasel

David Klee lays out an argument:

SQL Server query developers, listen up! Query execution time is not everything you should be worried about. You need to examine the parse and compilation time for each of your queries too.

Read on for the brunt of David’s argument. There are things you can do about query compilation time, starting with database design (normalize tables and include key constraints, include appropriate indexes, etc.) and continuing with query design (keep queries simple, limit use of functions, limit use of nested views, break complicated queries into multiple steps and use temp tables as intermediaries, etc.). One thing about compilation time, however, is that it doesn’t matter as much if you retain that plan for a while and reuse it a lot.

Comments closed

Case Sensitivity in Power BI

Published 2023-10-19 by Kevin Feasel

Kurt Buhler is going to raise my blood pressure this morning:

Most Power BI models are case-insensitive, meaning that “Bonk” is the same as “BONK”. However, Power BI data models can also be created as case-sensitive if you create a Direct Lake model in Fabric, or create a new model with external tools and enter a case-sensitive collation property. Two otherwise identical models which differ only in this case-sensitivity may produce different results, even though they’re using the same data, DAX, relationships, and tables.

It’s useful to know how case-sensitivity affects your model and its query results. You should also be able to identify and validate whether your model is case-sensitive. This is particularly important in the following scenarios:

Read on for those scenarios and how you can fix the problem of case sensitivity. My official stance on case sensitivity, by the way, is that applications should be case-insensitive on input but retain casing on output, so “dog” = “Dog” = “DOG” for sorting and querying, but if I saved “Dog” then that’s what should display.

Comments closed

Whitepapers for Oracle and SQL Server in Azure

Published 2023-10-19 by Kevin Feasel

Kellyn Gorman has been busy:

I’ve been pretty busy with work and travel, but I finally got an official Silk Github repository to publish a couple new white papers and sizing assessment worksheets for customer access.  These are primarily Oracle and SQL Server to Azure focused white papers, but I will be publishing ones on GCP next, to be followed by AI and other database platforms soon.

Click through for links to the documents.

Comments closed

(Near)-Real-Time Analysis with Microsoft Fabric

Published 2023-10-19 by Kevin Feasel

Reza Rad continues a series on Microsoft Fabric:

Microsoft Fabric offers a workload for real-time solutions. Real-time Analytics can be used for streaming data, such as the data coming from IoT devices. It can be used not only to ingest the data but also to analyze it and use it for other Fabric workloads, such as data science. In this article and video, you will learn what is Real-Time Analytics in Microsoft Fabric and how it works.

Read on for a detailed demo.

Comments closed

A Primer on A/B Testing for Engineers

Published 2023-10-18 by Kevin Feasel

John Mount performs some testing:

I’d like to discuss a simple variation of A/B testing in an engineering style.
By “an engineering style” I mean:

  • We will work a simulated example to see that the system works as claimed.
  • We will exhibit examples of problems before trying to fix them.
  • We will demonstrate all of the top level claims as calculations, and not delegate these to references.
  • We will leave fundamental math to the references, and not try to re-derive it.

In my opinion far too few A/B testing treatments check soundness, even on simulated data. This makes it easy for such articles to leave out important steps. If a relied on reference omits a step, the derived work may have to do the same.
We will implement the experiment design directly, instead of using a canned power calculator so we have a place to discuss some of the design issues in A/B test design.

This is an excellent dive into the topic and I highly recommend taking the time to read it.

Comments closed