Press "Enter" to skip to content

Curated SQL Posts

Using Index Column Order and Dynamic SQL to Fix Non-SARGable Queries

Published 2021-08-30 by Kevin Feasel

Erik Darling wraps up season 1 of Saving Sarge with a cliffhanger. First up, how setting up indexes to lead with SARGable columns makes sense:

Quite a bit, I find myself working with people who are able to change indexes, but unable to change queries.

Even making small, sane changes would nix their support, should they ask for it. I do sometimes have to laugh at the situation: if support were that great, they wouldn’t need me, and if we made the change, they probably wouldn’t need support.

Oh well, though. Keep me employed, ISVs.

Then, Erik takes a look at using dynamic SQL to solve one class of non-SARGable predicates:

The non-SARGABLE pattern that dynamic SQL helps you deal with is the the catch all query pattern, which can look like:

– col = @parameter or @parameter is null
– col = isnull(@parameter, col)
– col = coalesce(@parameter, col)

Or any similar variation of null/not null checking of a parameter (or variable) in the where/join clause at runtime.

Dynamic SQL allows you to build up the specific where clause that you need for the non-NULL set of search filters.

Stay tuned for the next thrilling episode of Saving Sarge. Same SARG-time, same SARG-channel.

Comments closed

E-Mailing a DBA upon Object Creation in System Databases

Published 2021-08-30 by Kevin Feasel

Thomas Williams has another script for us:

Next on the list to commission a SQL Server, is to create a trigger in the master system database to send an e-mail to the DBA when objects are created in the database.

In general I’d expect this never to happen – but if it did, I’d want to follow up. It’s possible to go even further and prevent creating objects in the master system database completely.

My experience is that about 5% of the time, people intentionally put objects into the master database, and 95% of the time, I…err, they…forgot to switch databases before running the script.

Comments closed

When APPEND_ONLY_STORAGE_INSERT_POINT is a Bottleneck

Published 2021-08-30 by Kevin Feasel

Paul Randal explains the APPEND_ONLY_STORAGE_INSERT_POINT latch and describes when and why this might be a problem:

Continuing my series of articles on latches, this time I’m going to discuss the APPEND_ONLY_STORAGE_INSERT_POINT latch and show how it can be a major bottleneck for heavy update workloads where either form of snapshot isolation is being used.

I strongly recommend you read the initial post in the series before this one, so you have all the general background knowledge about latches.

As always, read the whole thing.

Comments closed

Debugging Memory Access Violation Errors

Published 2021-08-30 by Kevin Feasel

Sean Gallardy explains access violations:

Access Violations (AV) are another common error that will cause SQL Server to take a memory dump. These can occur for a variety of reasons, and unlike the last one (Non-Yielding Scheduler) it’s quite the task, especially given public symbols don’t have structure data and offsets.

Read on to see what they are, what a dump file may include, and why they can be so difficult to debug.

Comments closed

Connecting to REST APIs via OAuth2 in Power BI

Published 2021-08-30 by Kevin Feasel

Chris Webb has an answer, but you may not like it:

There are a lot of articles and blog posts out there on how to handle OAuth2 authentication when connecting to REST APIs from Power Query in Power BI. However there is also a lot of confusion and contradictory information too so in this post I want to give you the definitive, Microsoft-endorsed answer to this question, which is:

If want to connect from Power BI to a REST API that uses OAuth2 authentication then you need to build a custom connector.

Read on for documentation showing how to implement and the big risk you’re taking if you don’t use a custom connector.

Comments closed

Estimating the Likelihood of an Underdog Winning at Soccer

Published 2021-08-27 by Kevin Feasel

Holger von Jouanne-Diedrich lays out the math for us:

The Bundesliga is Germany’s primary football league. It is one of the most important football leagues in the world, broadcast on television in over 200 countries.

If you want to get your hands on a tool to forecast the result of any game (and perform some more statistical analyses), read on!

What I would like is a tool which has SC Freiburg utterly dominating Bayern. Said tool may be more mythological than scientific (or at least a copy of Football Manager and a little bit of save scumming…), but I’ll take it.

Comments closed

Security Breach in Cosmos DB: ChaosDB

Published 2021-08-27 by Kevin Feasel

Nir Ohfeld and Sagi Tzadik discovered a flaw in Azure Cosmos DB:

Nearly everything we do online these days runs through applications and databases in the cloud. While leaky storage buckets get a lot of attention, database exposure is the bigger risk for most companies because each one can contain millions or even billions of sensitive records. Every CISO’s nightmare is someone getting their access keys and exfiltrating gigabytes of data in one fell swoop.

So you can imagine our surprise when we were able to gain complete unrestricted access to the accounts and databases of several thousand Microsoft Azure customers, including many Fortune 500 companies. Wiz’s security research team (that’s us) constantly looks for new attack surfaces in the cloud, and two weeks ago we discovered an unprecedented breach that affects Azure’s flagship database service, Cosmos DB.

Read on for details about the attack. Microsoft has already mitigated the issue by disabling the functionality necessary to pull off the attack. H/T Ben Stegink.

Comments closed

Multi-Cloud Pros and Cons

Published 2021-08-27 by Kevin Feasel

James Serra lays out some of the benefits and drawbacks of using multiple cloud providers:

A discussion I have seen many companies have is if they should be single-cloud (using only one cloud company) or multi-cloud (using more than one cloud company). The three major Cloud Service Providers (CSPs) that companies use for nearly all use cases are Microsoft Azure, Amazon Web Services (AWS), and Google Cloud Platform (GCP).

Without spoiling it too much, James is not really sold on the idea.

Comments closed

When in Doubt, Stop Counting

Published 2021-08-27 by Kevin Feasel

Chad Callihan looks at the SET NOCOUNT ON option:

You may have a stored procedure that completes in an acceptable amount of time for the dozen or so times a day it gets called. Maybe it returns results in a few seconds and that makes the users calling it happy enough that you can move onto more pressing matters. But what about a stored procedure being called millions of times a day? The definition of acceptable can be drastically different when you consider the speed and traffic that type of stored procedure produces. When every millisecond matters, it’s worth checking to see what your setting is for SET NOCOUNT.

Click through for a demo and what you can realistically expect from SET NOCOUNT ON. This works best with big loops, and incidentally, one pattern I like to use is to combine SET NOCOUNT ON with an occasional RAISERROR('%i iterations run...', 10, 1, @loopvar) WITH NOWAIT. That way, you can still see progress on the screen, but instead of printing results every single run, you might see one every 100 runs.

Comments closed

Paginated Reports in Power BI

Published 2021-08-27 by Kevin Feasel

Elayne Jones dives into paginated reports in Power BI:

Paginated Reports for Power BI offer pixel-perfect control over the format of each element of a report. They allow users to fine-tune each field of the report, such as text size, colors, spacing, and print layout, in a more precise way than using regular visuals in Power BI Desktop. Users can access Paginated Reports directly from workspaces in Power BI Service. Additionally, users can embed Paginated Reports directly onto a Power BI report page with the new visual option. This article will explain how to create a Paginated Report and how to configure the new Paginated Reports visual in Power BI Desktop. Please note that Paginated Reports require a Premium subscription. This tutorial is based on a fictional Sales Report.

If you’re familiar with SQL Server Reporting Services, you’ll find Power BI paginated reports simultaneously comfortable and confining—it’s much the same functionality as SSRS, but doesn’t feel as complete.

Comments closed