Press "Enter" to skip to content

Author: Kevin Feasel

Locks in Microsoft Fabric Data Warehouse

Twinkle Cyril enumerates the lock types in Fabric Data Warehouse:

Fabric DW supports ACID-compliant transactions using standard T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK) and enforces snapshot isolation across all operations. Locks in Fabric Data Warehouse are used to manage concurrent access to metadata and data, especially during DDL operations. Here’s how locking works:

Click through for a chart. The locking policy is a lot simpler than what we see in SQL Server and you can see a description of the pros and cons of that simpler approach.

Leave a Comment

Fun with SQL Firewall in Oracle

Brendan Tierney follows up on a SQL Firewall post:

In a previous post, we’ve explored some of the core functionality of SQL Firewall in Oracle 23ai, In this post I’ll explore some of the other functionality that I’ve had to use as we’ve deployed SQL Firewall over the past few weeks.

Sometimes, when querying the DBA_SQL_FIREWALL_VIOLATIONS view, you might not get the current up to-date violations, or if you are running it for the first time you might get now rows or violations being returned from the view. This is a slight timing issue, as the violations log/cacbe might not have been persisted to the data dictionary. If you end up in this kind of situation you might need to flush the logs to to data dictionary. To do this, run the following.

Click through for that command, as well as a few other scenarios and commands that may be of interest.

Leave a Comment

The Intricacies of COUNT()

Louis Davidson can easily get to 20:

I was reading LinkedIn posts the other day when I saw this blog about what was apparently an interview question about some forms of a COUNT aggregate function

This was apparently asked in an interview. What will each of these constructs do in a SQL statement:

COUNT(*) = ?
COUNT(1) = ?
COUNT(-1) = ?
COUNT(column) = ?
COUNT(NULL) = ?
COUNT() = ?

There’s one tricky bit in this set. Louis then takes it a bit further with CASE expressions and variables, so check out the post for the answers as well as those additional examples in T-SQL.

Leave a Comment

Evading Data Access Auditing in SQL Server and How to Stop It

Andreas Wolter brainstorms:

The methods that I will share here allow an attacker to either conceal his identity or even evade auditing completely.

Most of these commands require sysadmin privileges. However, if your goal is to audit every access to sensitive data, this typically means “all users”  –  not with an exception for administrators. Because of this, it’s important to understand these methods so you can make an informed decision about whether to include them in your auditing scope.

Some of these are wildly impractical, but they do work and Andreas has mitigations for each.

Leave a Comment

Extending Index Information across Availability Group Replicas

Aaron Bertrand extends a prior article:

In my previous tip, Managing Unused Indexes in SQL Server Availability Groups – Part 1, I showed how I use dynamic SQL to gather index usage statistics for a given table from all replicas in an availability group. Knowing the usage from all workloads is definitely better than focusing on only the primary or a single secondary. But what if I want to make more informed decisions, incorporating row counts, size, and index columns into this output?

Read on to see how you can incorporate this additional information.

Leave a Comment

Using Community Tools to Troubleshoot SQL Server Performance

Kevin Hill wraps up a series on what to do when “SQL Server is slow”:

This post will lean heavily on the First Responder Kit from Brent Ozarsp_WhoIsActive from Adam Machanic and others. They took what Microsoft provides and made them better. Hundreds or thousands of hours of work to make FREE things for you to use.

This is the most complex blog I’ve ever written. Your experiences may differ, and my code samples might have some bugs. Test first.

Kevin is trying to sum up a lot of material in a blog post, but this serves as a pretty good starting point for people who don’t know where to begin. One of the key takeaways from the post, and the entire series, is to have a plan in place. Regardless of whether you use what Kevin has as-is or make your own modifications, knowing what to do and how to do it will save a lot of time and energy when you need to act.

Leave a Comment

JSONL Support in Fabric Data Warehouse and Lakehouse now GA

Jovan Popovic announces that JSONL format support is now generally available in Microsoft Fabric:

The OPENROWSET function that can read JSONL format empowers you to easily read and ingest JSONL files – for example log files, social media streams, machine learning datasets, configuration files, and other semi-structured sources. With the versatile OPENROWSET T-SQL function, you can reference and query JSONL files as if they were tables, eliminating the need for manual parsing or complex transformation steps.

Read on to see examples of how to ingest and use data in the JSON Lines format.

Leave a Comment

Using Workspace Folders in Microsoft Fabric

Jon Vöge walks through a few folder strategies in Microsoft Fabric:

Every time I see a new Fabric Data Platform, I see a new way of using folders. Almost.

Ranging from no folders at all, to using folders to segregate item types, over folders for bronze/silver/gold layers, to even seeing setups of them being used for DEV/TEST/PROD.

I won’t claim all of these to be equally good. But the point is that there are many different approaches you may take.

Read on for a few strategies, including ones Jon would recommend avoiding.

Leave a Comment

Using Field Parameters in Power BI for Dynamic Views

Annamarie Van Wyk demonstrates how to use field parameters to slice data in Power BI:

If you’ve ever built a Power BI report and found yourself duplicating charts for daily, weekly, monthly, or yearly views — you’re not alone. It’s one of the most common (and frustrating) dashboard challenges: “Can we see this by day? Actually, make it by week. No wait — what about monthly?”

Instead of building five versions of the same visual, you can do it all with one — thanks to Field Parameters.

Read on to see how it all works.

Leave a Comment