Press "Enter" to skip to content

Author: Kevin Feasel

LOBs and OPTION(RECOMPILE)

Published 2021-11-18 by Kevin Feasel

Paul White has a warning for us:

All that is fairly well-known. The point of this short post is to draw your attention to another side-effect of adding OPTION (RECOMPILE) — the parameter embedding optimization (PEO).

When PEO is used, SQL Server takes the value of any variables and parameters and embeds the runtime values in the query text, pretty much as if you had entered them by hand before compiling. This is often very useful for plan quality, but there is a potential drawback when large object types (LOBs) are in play.

Click through for the explanation and a simple demo.

Comments closed

Serverless SQL Pool CI/CD via GitHub Actions

Published 2021-11-17 by Kevin Feasel

Kevin Chant reminds me I need to spend more time with GitHub Actions:

I want to cover one way you can do CI/CD for Azure Synapse Analytics serverless SQL pools using GitHub Actions in this post. For various reasons.

For a start, in a previous post I wrote about how you can CI/CD for serverless SQL pools using Azure DevOps. So, I thought I would balance things out and show how you can do the same thing within GitHub.

In addition to this, there have been a few discussions about using GitHub Actions instead of Azure Pipelines within the Microsoft Data Platform community recently. For example, the topic came up during the DataWeekender conference.

With this in mind, I want to show how easy it can be to migrate an Azure DevOps pipeline to GitHub Actions.

Click through for the example.

Comments closed

Index Usage across Replicas

Published 2021-11-17 by Kevin Feasel

Jess Pomfret does the math:

Last week, I was working on a project to analyse indexes on a database that was part of an availability group. The main goal was to find unused indexes that could be removed, but I was also interested in gaining an overall understanding of how the system was indexed.

Unused indexes not only take up disk space, but they also add overhead to write operations and require maintenance which can add additional load on your system.  We can also use this analysis to look for a high number of lookups which could indicate we need to adjust indexes slightly.

Click through to see how you can connect together index usage stats from the primary and secondary replicas of an availability group.

Comments closed

Stats Q&A

Published 2021-11-17 by Kevin Feasel

Erin Stellato has a two-parter on statistics in SQL Server. Part 1 deals with questions on stats creation:

Last week I presented a session, Demystifying Statistics in SQL Server, at the PASS Community Summit, and I had a lot of great questions; so many that I’m creating multiple posts to answer them. This first post is dedicated to questions specific to creating statistics in SQL Server.

Part 2 deals with stats updates:

Last week I presented a session, Demystifying Statistics in SQL Server, at the PASS Community Summit, and I had a lot of great questions; so many that I’m creating multiple posts to answer them. This second post is dedicated to questions specific to updating statistics in SQL Server. Of note…I have a couple previous posts which also include helpful information:

Click through for lots of questions and lots of good answers.

Comments closed

Fill Factor and When It Matters

Published 2021-11-17 by Kevin Feasel

Raul Gonzalez has a confession to make:

I love SQL Server internals, I do and I just said it.

Why? because thanks to all the tools, documentation and community members that share their knowledge, folks like me can understand how a super complex piece of software like a relational database engine works (or at least a small part of it).

Click through for a discussion of fill factor and one area where Raul thinks it falls short. I’m not sure that I agree but would need to think about it to give a clear explanation as to why.

Comments closed

Auditing Data and Data Access Quiz

Published 2021-11-17 by Kevin Feasel

Kenneth Fisher has a pop quiz for us:

I was honored to speak at Pass Summit last week (Thanks again Redgate), and if you’ve ever been to one of my sessions you’ll know there is always a “quiz” at the end. I.e. a crossword puzzle. Well .. here is the puzzle itself, and attached (at the bottom) is the answer key.

I answered “c” for all of the questions and it worked out really well. When in doubt, Charlie out!

Comments closed

When MAX becomes TOP

Published 2021-11-16 by Kevin Feasel

Forrest McDaniel tries out a few aggregations:

I can’t tell you how tempted I was to call this MAXTOP. Thinking about it still causes stray giggles to bubble up, as I gleefully envision sharing this info with eager young DBAs at a SQL Saturday, then ushering them off to a session on parallelism. Thankfully I’m not that evil.

The real conclusion is that SQL Server is programmed to be very clever. Despite the cleverness though, details matter, and the cleverness often falls short. What’s that warm glow I feel inside? Ah yes, job security.

Definitely worth a read, and Forrest also shares the repro scripts.

Comments closed

Viewing Site-to-Site VPN Logs in Azure

Published 2021-11-16 by Kevin Feasel

Denny Cherry troubleshoots a site-to-site VPN issue:

Recently I needed to view the logs from an Azure Site to Site VPN to see why it wasn’t working as expected. When Azure Site to Site VPNs aren’t working as expected the GUI falls apart quickly for troubleshooting.

Log Analytics is where this problem gets solved. Log Analytics is going to allow you to see basically everything that the Azure Network Gateway is doing. Setting the feed up to Log Analytics isn’t as straightforward as it could be, but it is documented in this post.

Read on for some sample queries.

Comments closed