Press "Enter" to skip to content

Month: May 2021

Building a Better sp_help_revlogin

Published 2021-05-11 by Kevin Feasel

Eitan Blumin remembers:

Anyways, with that obvious answer out of the way, let’s talk about something more interesting, like sp_help_revlogin.

Remember sp_help_revlogin? It’s that stored procedure that Microsoft published more than 20 years ago, that never found its way into the SQL Server built-in system procedures. Microsoft still maintains that same KB page till this day (by “maintains”, I mean copy-and-pasting it from one place to another as they change their KB platforms).

Read on to understand what this is and several ways of doing the same thing better, including a new sp_help_revlogin2 that Eitan has put together.

Comments closed

The Mechanisms of Page Splits

Published 2021-05-11 by Kevin Feasel

Deepthi Gogrui takes us through page splitting in SQL Server:

Page splits can happen with the logical fragmentation and low page density causing the transactional log to be huge. Page splits are very expensive. As we learned from the previous post, page splits happens when SQL Server tries to insert a row and there is no more space on the page to fit in the page so page will split the page to give the space to fit it in that record. When SQL Server has to insert any rows in to the pages, first the free space on the page is checked at the header information of the page and if the record is within the mentioned free bytes, the record fits in. If the space is not contiguous within the page but there is total space to fit in the record, then the in-memory compaction of the page making the amount of free space on the page contiguous. This is not page split. Page split occurs when the space is not available on the page to fit in the record, in that case page split into half and the split point of the page is usually 50 percent each. Sometimes, the split can happen at the different point on the page is chosen by the storage engine as the obvious split point which is known as skewed page split. This is even more expensive when compared with the regular page split as this will create much larger transaction log.

What causes the page splits?

Read on for the full article.

Comments closed

When RCSI Is Not Enough

Published 2021-05-11 by Kevin Feasel

David Klee diagnoses an issue:

Basically, the use of the WITH (NOLOCK) query hint performs a dirty read, of which I’m sure you can find oodles of examples on the Internet about. Microsoft introduced RCSI in SQL Server 2005 to help you reduce the amount of blocks, and with RCSI exclusively in use, database readers no longer block other readers or writers. I love enabling RCSI wherever appropriate, as long as the TempDB database is monitored to make sure that RCSI’s version store is not causing any issues (and it usually is just fine). It also goes without saying that with RCSI enabled, you should work to remove the WITH (NOLOCK) query hints from your code, as RCSI is superseded by NOLOCK and can still invoke dirty reads.

But… this Client is still seeing serializable connections that will occasionally cause issues. What else can we check?

Read on for a few tips.

Comments closed

Visualizing Power BI Refresh in Real-Time

Published 2021-05-11 by Kevin Feasel

Phil Seamark doesn’t have time to wait:

I recently wrote an article showing how you can visualise a dataset refresh using Power BI. It was a pretty cool way to show some of the internal workings of what otherwise is a black box. The idea from my earlier article uses SSMS Profiler to run a trace against a database hosted in Azure AS, or Power BI Premium. Once the refresh is complete, you import the results of the SSMS Profiler trace into a Power BI report to analyse. The approach requires you to wait until the refresh is complete before you can start exploring the data.

Also recently, I had the opportunity work on some large models that took a long time to refresh. I wondered what might be required to update the earlier process to study the results while the refresh was underway. Does that make me too impatient? Here is what I ended up building.

Click through to enjoy the fruits of Phil’s impatience. This is quite the interesting solution, especially if you’re twiddling your thumbs and wondering if this refresh will ever wrap up.

Comments closed

Registering an SPN for SQL Server

Published 2021-05-11 by Kevin Feasel

Jack Vamvas explains how to register an SPN for SQL Server:

I received a question from a colleague asking how to register a Service Principal Name (SPN) for SQL Server. The specific situation relates to upgrades and new SQL Server instances deployed onto new servers\server names . 

Defining an SPN results in an Active Directory name – allowing a client to uniquely identify  the service instance.  The SPN is comprised of a service name with a computer and user account resulting in a Service id. 

Read on for the process.

Comments closed

Fun with MERGE and Deadlocks

Published 2021-05-11 by Kevin Feasel

Daniel Hutmacher walks us through another reason to avoid using the MERGE operator:

I recently ran into a curious deadlock issue. I have a process that performs a lot of updates in a “state” table using multiple, concurrent connections. The business logic in the application guarantees that two connections won’t try to update the same item, so we shouldn’t ever run into any locking issues. And yet, we keep getting deadlocks.

What’s going on here? Hint: it has to do with isolation levels and range locks.

Read on for the problem-causing query and a few ways to resolve the problem.

Comments closed

Understanding Confidence & Credible Interval Widths

Published 2021-05-10 by Kevin Feasel

John Cook takes us through the notion of confidence intervals and credible intervals:

Suppose you do N trials of something that can succeed or fail. After your experiment you want to present a point estimate and a confidence interval. Or if you’re a Bayesian, you want to present a posterior mean and a credible interval. The numerical results hardly differ, though the two interpretations differ.

If you got half successes, you will report a confidence interval centered around 0.5. The more unbalanced your results were, the smaller your confidence interval will be. That is, the confidence interval will be smallest if you had no successes and widest if you had half successes.

What can we say about how the width of your confidence varies as a function of your point estimate p? 

Read on to learn that answer.

Comments closed

Containerizing a Shiny App

Published 2021-05-10 by Kevin Feasel

Peter Solymos takes us through the process of running a Shiny app in a Docker container:

Docker provides isolation to applications. Images are immutable. Running multiple instances of the same image can serve many users at the same time. All these general advantages of containerized applications apply to Shiny apps too.

All the general advantages of containerized applications apply to Shiny apps. Docker provides isolation to applications. Images are immutable: once build it cannot be changes, and if the app is working, it will work the same in the future. Another important consideration is scaling. Shiny apps are single threaded, but running multiple instances of the same image can serve many users at the same time. Let’s dive into the details of how to achieve this.

Click through for a walkthrough. Containerizing these sorts of apps has been a boon for my team, as it lets us spin up appropriately-sized servers on the cheap. H/T R-Bloggers

Comments closed

Ditch the Powershell ISE

Published 2021-05-10 by Kevin Feasel

Jeffrey Hicks has a bit of advice:

Gladys’ big challenge, probably like many of you, is trying to break the habit of launching the PowerShell ISE. It doesn’t matter if you are running the ise alias at a PowerShell prompt or using a Start Menu shortcut. My idea is that if you can’t stop yourself from doing this, then why not have the ISE turn around and launch VS Code? I gave Gladys a few lines of code to put in her PowerShell ISE profile script that launched VS Code and then killed the ISE. I’ve since, refined that code and I thought I’d share it with everyone else.

There are a few assumptions in the process. First, is that when you installed VS Code, you included the option to include the application in your %PATH%. You can test this. At a PowerShell prompt run code. If VS Code launches, you are ready to continue.

Click through to see how.

Comments closed

Dynamic Date Formats in Power BI

Published 2021-05-10 by Kevin Feasel

Joe Billingham shows how to ensure that users see dates in the format most appropriate to them:

Which date format styles should we use if we are building a report that is being consumed internationally?

Remember, 01/12/2021 is December 1st or January 12th depending in which part of the world it is being read.

The decision may be taken from our hands if there is a company policy in place. If the company is based in the USA, for example, they may choose to use US formatted date fields as a standard for reporting across the entire business, however, if the field needs to be truly dynamic depending on the consumers location, the answer lies in this tool tip:

Click through to see how.

Comments closed