Press "Enter" to skip to content

Day: July 28, 2025

Using R for Descriptive Statistics in Excel

Published 2025-07-28 by Kevin Feasel

Adam Gladstone shows off an Excel add-in:

The purpose of this series of posts is to demonstrate some use-cases for R in Excel using the ExcelRAddIn component (disclaimer: I am the developer of this add-in: ExcelRAddIn). The fundamental rationale for the add-in is that it allows access to the extensive R ecosystem within an Excel worksheet. Excel provides many excellent facilities for data wrangling and analysis. However, for certain types of statistical data analysis, the limitations of the built-in functions even alongside the Analysis ToolPak is not sufficient, and R provides superior facilities (for example, for performing LDA, PCA, forecasting and time series analysis to mention a few).

Click through for examples of how it all works. H/T R-Bloggers.

Leave a Comment

Ownership Chaining in SQL Server

Published 2025-07-28 by Kevin Feasel

Jon Russell breaks the chain:

Designing a reporting layer that protects sensitive data takes more than hiding tables behind a view. You must understand how schema permissions and ownership chaining interact, or a well‑meant deny can suddenly block your users—or worse, let them see columns you thought were private. The walk‑through below shows the entire life‑cycle of a common scenario:

  1. Build an HR table that holds confidential columns.
  2. Expose a summary view in a separate schema.
  3. Grant a reporting role access to the view but explicitly deny access to the HR schema.
  4. Break the ownership chain, observe the failure, diagnose the cause, and repair it by realigning ownership.

Read on to learn more. Note that this is all within a single database, so we aren’t talking about the cross-database ownership chaining setting. That setting being on immediately raises red flags for me.

Leave a Comment

Measuring Delay in Availability Groups

Published 2025-07-28 by Kevin Feasel

Matt Gantz wants to see how far behind we are:

Availability Groups are useful for creating Highly Available (HA) database systems, but that doesn’t mean they are entirely immune to performance problems. In busy systems, limitations in the infrastructure can introduce replication lag that is severe enough to affect database performance in ways that aren’t immediately obvious.

In SQL Server Availability Groups, the relevant difference between synchronous and asynchronous replicas comes down to how and when a transaction on the primary server is considered “committed”: Although it is easy to measure the lag between asynchronous replicas by using the dashboard in SSMS or by querying the DMVs (Dynamic Management Views), it takes more work to find the latency between synchronous replicas. This article explains how to measure that latency using internal performance counters, offering a simple technique for monitoring the cost of synchronous replication.

Click through to read the whole thing.

Leave a Comment

Making PostgreSQL Slower

Published 2025-07-28 by Kevin Feasel

Jacob Jackson takes on a unique challenge:

Everyone is always wondering how to make Postgres faster, more efficient, etc, but nobody ever thinks about how to make Postgres slower. Now, of course, most of those people are being paid to focus on speed, but I am not (although, if you wanted to change that, let me know). As I was writing a slightly more useful guide, I decided someone needed to try to create a Postgres configuration optimized to process queries as slowly as possible. Why? I am not sure, but this is what came of that thought.

I spent a few moments thinking about an equivalent sort of torture test on SQL Server, doing things like forcing CPU affinity through one core, monkeying with cost threshold for parallelism, and using trace flags to turn off different join optimizations (like, say, hash matches and merge joins, forcing everything to be nested loops). It’s a fun thought experiment.

Leave a Comment

Common Table Expressions in SQL Server and Materialization

Published 2025-07-28 by Kevin Feasel

Vlad Drumea is back to following Betteridge’s Law of Headlines and the people rejoice:

There’s this weird misconception floating around LinkedIn and reddit that SQL Server CTEs somehow store results in either memory or tempdb.

This is wrong and whoever states that CTEs store results either have no idea what they’re talking about or are intentionally trying to mislead people for engagement farming.

Click through for the proof of this.

As a quick note, you can materialize common table expressions in some relational database platforms like PostgreSQL, but SQL Server does not have that option.

Leave a Comment

Managing Point in Time Restores with SQL Server

Published 2025-07-28 by Kevin Feasel

Aaron Bertrand figures out available database restoration options:

In my last tip, Enhancing SQL Server Backups for Hundreds of Databases, I created a single, central backup catalog representing all the full, log, and differential backups across our environment. I demonstrated how I use it to build restore statements for any given database for validating the backup, performing DBCC checks, or to recover data at a specific point in time. But can this data help us in other ways, too? Let’s see how we can use this data for a point in time restore.

Read on for some neat queries you can run based on Aaron’s process in the original post.

Leave a Comment

Building a Test Bed for Page Latch Waits

Published 2025-07-28 by Kevin Feasel

Jared Poche generates some test data:

I’ve already posted a blog on page latch waits and some of the ways to minimize them, but I wanted to add some more on the issue. I wanted to test out some strategies to see how effective they are.

Setting up tests can be very difficult in some cases, and this attempt encountered a very unusual problem. At the risk of delaying the intended results, I wanted to point this out. It’s both interesting and frustrating to work with SQL Server for 20 years and run into new problems.

Read on to see what Jared tried, some interesting consequences, and a small fix that enabled a big problem.

Leave a Comment