Press "Enter" to skip to content

Curated SQL Posts

Standby Mode in Log Shipping

Published 2022-08-24 by Kevin Feasel

Garry Bargsley will not simply stand by:

Did you know that log shipped databases do not have to sit there collecting dust and not being used? However, there are certain situations where replication or Availability Groups are not the answer to the question of separating your reporting traffic.

You don’t need to build an actual data warehouse, you don’t want the complexity of Windows Cluster for Availability Groups, and you cannot use replication because your tables do not have primary keys.

We can use the long trusted Log Shipping technology and tweak a few settings and get a read-only copy of a database.

Click through to see how you can make use of those log shipped databases.

Comments closed

Variance in Parallel Query Performance

Published 2022-08-24 by Kevin Feasel

Joe Obbish takes on parallelism:

You may have noticed large variations in elapsed time for parallel queries while performing query tuning or observing a production workload. This post reviews some of the possible explanations for those variations. I’m testing on SQL Server 2022 RC0 but this behavior can be observed on all currently supported versions of SQL Server.

Joe has an interesting example (and my guess of how bad the performance would be was not too far off, though I did underestimate the difference), as well as several possible causes and mitigation strategies for slow parallel queries.

Comments closed

When Trivial Plans Hide Relevant Information

Published 2022-08-24 by Kevin Feasel

Chad Callihan wants the optimizer to sweat a bit:

When you execute a query, the SQL Server query optimizer determines potential query plans and decides which plan is the cheapest. It’s possible for a query to be so simple that there’s no need to bother weighing out different plans before returning results. In this case, SQL Server may use a trivial plan. Let’s look at an example of this plan and one reason why simple may not always be the best.

Click through for an example.

Comments closed

Granular Permissions in SQL Server 2022

Published 2022-08-24 by Kevin Feasel

Andreas Wolter notes some permissions changes:

In the last 3 years, the SQL Security team has put more emphasis on enabling customers to use SQL Server while adhering to the Principle of least Privilege (PolP). As part of that effort, all new features in the next SQL Server release: SQL Server 2022, can be controlled with more granular permissions. SQL Ledger is a good example of such a new feature. It comes with separate permissions for creating (ENABLE LEDGER) vs dropping ledger tables (ALTER LEDGER); a separate permission to view ledger content (VIEW LEDGER CONTENT) for auditing purposes; and another independent permission to generate a ledger digest (GENERATE LEDGER DIGEST).

Besides new features, a lot of effort also went into improving the existing set of permissions, namely by making them more granular. This has happened in 3 distinguishable areas:

Check out the changes they’ve made.

Comments closed

SQL Server 2022 RC0 Availabile

Published 2022-08-24 by Kevin Feasel

Ajay Jagannathan has a big announcement:

Continuing with our release cadence, we’re excited to announce the release of SQL Server 2022 Release Candidate 0. Since the first public preview in May 2022, anyone can download SQL Server 2022 RC0 to try the new features in this release.

The changelist doesn’t look enormous for this release and as we get closer to RTM, that set usually drops off a bit as code stabilizes in preparation for release.

Comments closed

EndOf in KQL

Published 2022-08-23 by Kevin Feasel

Robert Cain is the alpha, Robert Cain is the omega:

My post, Fun With KQL – DateTime Arithmetic, demonstrated how to do date / time math in Kusto. Then in my previous post, Fun With KQL – StartOf, we showed how to use the StartOf... functions, to calculate the beginning of time periods based on a date that is passed in.

If you’ve not read the previous post I’d highly encourage you to do so now, as there are parallels between it and the endof... functions we’ll discuss in this article, namely endofday, endofmonth, endofyear, and endofweek.

I’ve enjoyed Robert’s series but I really wanted this to be the last thing he covers so I could call it “EndOf the Line for KQL.” But because I don’t know if it really will be the last post in the series (other than maybe a conclusion post), I can only reference that momentary bit of cleverness here.

Comments closed

Building a Google Analytics Dashboard using RShiny

Published 2022-08-23 by Kevin Feasel

Pascal Schmidt builds a Shiny dashboard:

I participated in the R Shiny 2021 contest and published an application similar to the Google Analytics dashboard app. For that, I used the Google Analytics API and the Google Search Console API to pull my own data from my blog directly into the application.

The application uses the shinyauthr library because the dashboard can be adjusted for each user who has their own username and password. On the first page, there are some visualizations for page views, devices used, etc. On the second page, there is a time-series model that tries to predict my page views two months in advance.

In particular, this post covers a couple of the foundational pieces, with future posts getting into more detail on other components. H/T R-Bloggers.

Comments closed

Choosing between Neural Network Types

Published 2022-08-23 by Kevin Feasel

Jason Brownlee takes us through three common classes of neural network and explains when each is useful:

In this post, you will discover the suggested use for the three main classes of artificial neural networks.

After reading this post, you will know:

– Which types of neural networks to focus on when working on a predictive modeling problem.

– When to use, not use, and possible try using an MLP, CNN, and RNN on a project.

– To consider the use of hybrid models and to have a clear idea of your project goals before selecting a model.

Read the whole thing.

Comments closed

Azure Synapse Link for SQL Server 2022 and File Analysis

Published 2022-08-23 by Kevin Feasel

Kevin Chant digs into Azure Synapse Link for SQL Server 2022:

In this post I want to cover some file tests for Azure Synapse Link for SQL Server 2022 that I performed.

Because a while back I spotted something interesting whilst I was doing some initial tests for Azure Synapse Link for SQL Server 2022.

Which is when you add new data after the initial load that a new folder called ‘ChangeData’ appears in the storage account container. I noticed that the new file containing the insert was a comma separated value (csv) file. Whereas the table used for the initial load was a parquet file.

Is there a method to this madness? Click through to see Kevin’s tell-all story.

Comments closed

sp_prepare and Parameter Sensitive Plan Optimization

Published 2022-08-23 by Kevin Feasel

Erik Darling is a bit surprised:

I admit that sp_prepare is an odd bird, and thankfully one that isn’t used a ton. I still run into applications that are unfortunate enough to have been written by people who hate bloggers and continue to use it, though, so here goes.

When you use sp_prepare, parameterized queries behave differently from normal: the parameters don’t get histogram cardinality estimates, they get density vector cardinality estimates.

That part’s not the surprise. You’ll have to click through for that.

Comments closed