Press "Enter" to skip to content

Curated SQL Posts

Selective Column Replication in Postgres

Published 2024-02-19 by Kevin Feasel

Semab Tariq shows how to replicate a limited number of columns:

Selective column replication, a feature of logical replication in PostgreSQL, enables the selective transfer of data changes from one database to another. This feature offers flexibility by addressing bandwidth and storage optimization concerns. It allows users to choose specific columns to replicate rather than replicating entire tables, ensuring that only essential data is transferred. This selective approach reduces network traffic and storage space required for replication while maintaining data consistency across databases.

In this blog, I will demonstrate the process of replicating specific columns from a table using logical replication. Additionally, we will see how you can create multiple subscribers connected to a single publisher to replicate data across multiple locations.

Replication seems to be a lot easier to set up in Postgres than SQL Server, though check out the FAQ at the end for a few kicks in the pants around Postgres replication.

Comments closed

Upcoming SSMS 20 Connection Security Changes

Published 2024-02-19 by Kevin Feasel

Erin Stellato announces an “above-the-fold” change:

In SSMS 20 we have made changes to the connection dialog.  In the screenshot below, you will see the Login and Connection Properties pages for the connection dialogs for SSMS 19 and 20 compared side-by-side.

Moving the Connection Security section to the Login tab (and it presumably showing up without needing to select Options) is a good change. It’s also interesting to see SSMS support Mandatory encryption, a part of Tabular Data Stream (TDS) 8.0 that Azure Data Studio supports but SSMS (and most other SQL Server-related things) don’t yet.

Comments closed

Cannot Create Property ‘groupid’ On String

Published 2024-02-16 by Kevin Feasel

Barney Lawrence fixes a problem in Azure Data Studio:


This is a solution to a bug I’ve encountered in Azure Data Studio when trying to create new connections. that leads to an error message of cannot create property ‘groupid’ on string ‘.

File this post under “things I couldn’t find a neat answer to in Google so I figure I’d best create a page for it”.

Read on for Barney’s answer as I lament how far downhill Google has gone as a search engine.

Comments closed

The Importance of Source Control for DBAs

Published 2024-02-16 by Kevin Feasel

Steve Jones explains that Git isn’t just for developers:

Git has become a fantastic tool for me, and many other technologists, over the last ten years. It’s almost ubiquitous in most of my clients, and so many people are comfortable with it. Many others aren’t, which is why I started a Git series for DBAs (and other Ops people) on my blog.

Quite a few people asked me why I recommend git over a file share for storing code that a team of Ops people or DBAs might use. Why isn’t a global file share a better choice in an organization? I think I have a few good reasons, but if you disagree, let me know in the discussion for this piece.

There are some annoyances around Git but good UI tools minimize a fair amount of the pain and the benefits are huge.

Comments closed

Avoiding Time Intelligence DAX Functions in DirectQuery Mode

Published 2024-02-16 by Kevin Feasel

Marco Russo and Alberto Ferrari skip the slow stuff:

Calculations that use the DAX time intelligence functions mostly retrieve data at the day level, performing the required aggregations in the formula engine. By avoiding time intelligence DAX functions, you can force DAX to produce more optimized queries for your specific calculations.

DirectQuery over SQL and VertiPaq require the same patterns to optimize time intelligence calculations, even though the reasons are different. In VertiPaq, we try to stay away from DAX time intelligence functions to avoid large materialization at the day level. With SQL, materialization does not always happen because Tabular tries to push the grouping down to SQL. Still, time intelligence calculations often result in complex queries, and it is better to avoid the complexity by using simpler DAX code.

Check out the performance difference.

2 Comments

Checking SQL Server Connectivity with Powershell in Parallel

Published 2024-02-16 by Kevin Feasel

Rod Edwards builds a script:

The chances are that you have other systems monitoring your SQL servers already, so this task isn’t required at all. However, sometimes a quick ‘knock on the door of SQL’ to confirm a response isn’t a bad thing as a sanity check.

So, building on that, we’re going to use the same technique to essentially perform a sweep of our estate (with a few bells and whistles added) to give us a colour coded quick view of service status like below, with a little bit of additional info.

Click through for the script and explanation.

Comments closed

Troubleshooting a Slow Deletion

Published 2024-02-16 by Kevin Feasel

Aaron Bertrand has an admission:

Before looking at the code path, the query, or the execution plan, I didn’t even believe the application would regularly perform a hard delete. Teams typically soft delete “expensive” things that are ever-growing (e.g., change an IsActive column from 1 to 0). Deleting a user is bound to be expensive, because there are usually many inbound foreign keys that have to be validated for the delete to succeed. Also, every index has to be updated as part of the operation. On top of that, there are often triggers that fire on delete.

While I know that we do sometimes soft delete users, the engineer assured me that the application does, in some cases, hard delete users.

Click through for the full story and a minor bout of self-petard-hosting. I’m as guilty as anyone else of jumping to conclusions, and this is a good reminder to go through the process even when you think you know the answer.

Comments closed

Troubleshooting a Problem with sp_send_dbmail

Published 2024-02-16 by Kevin Feasel

Shane O’Neill applies the Sgt. Schultz defense:

I don’t like posting about issues unless I fundamentally understand the root cause. That’s not the case here. A lot of the explanation here will be hand-waving while spouting “here be dragons, and giants, and three-headed dogs”, but I know enough to give you the gist of the issue.

Click through for a fun story about Kerberos and behavioral changes after absolutely nothing happened.

Comments closed

Working with Date Sequences in R

Published 2024-02-15 by Kevin Feasel

Steven Sanderson isn’t satisfied with a single date:

In the world of data analysis and manipulation, working with dates is a common and crucial task. Whether you’re analyzing financial data, tracking trends over time, or forecasting future events, understanding how to generate date sequences efficiently is essential. In this blog post, we’ll explore three powerful R packages—lubridate, timetk, and base R—that make working with dates a breeze. By the end of this guide, you’ll be equipped with the knowledge to generate date sequences effortlessly and efficiently in R.

Click through for several ways to generate date sequences, including weekly sequences.

Comments closed

Weirdness with Aggregation

Published 2024-02-15 by Kevin Feasel

Erik Darling digs into a problem. Part 1 sets up the scenario:

Here’s the query plan, which yes, you’re reading correctly, runs for ~23 seconds, fully joining both tables prior to doing the final aggregation.

I’m showing you a little extra here, because there are missing index requests that the optimizer asks for, but we’ll talk about those in tomorrow’s post.

The wait stats for this query, since it’s running in Batch Mode, are predictably HT-related.

Part 2 covers those missing indexes:

I’ve taken a small bit of artistic license with them.

The crappy thing is… They really do not help and in some cases things get substantially worse.

Maybe it’s because it’s early and I’m trying to compile things in my head rather than actually trying it out, but it seems like a combo of CTE + CROSS APPLY or a pair of CROSS APPLY statements could work better (especially with a good index), assuming that join doesn’t need to be in place. Given the query as it is, with two MAX() aggregations and no GROUP BY clause, that could be an avenue for improvement, though one I have not actually tested. Nonetheless, read both of Erik’s posts.

Comments closed