Press "Enter" to skip to content

Author: Kevin Feasel

Counting Open Lockers in R

Published 2021-02-05 by Kevin Feasel

Holger von Jouanne-Diedrich solves a riddle:

We are standing in front of 100 lockers arranged side by side, all of which are closed. One man has a bunch of keys with all 100 keys and will pass the lockers exactly a hundred times, opening or closing some of them.

On the first pass, he opens all the lockers. On the second pass, the man will go to every other locker and change its state. That means: If it is closed, it will be opened. If it is already open, it will be closed. In this case, he closes lockers 2, 4, 6… 98 and 100, because all doors were open before.

On the third pass, he changes the state of every third locker – that is, 3, 6, 9, … 96, 99. Closed doors are opened, open doors closed. In the fourth pass, every fourth locker is changed, at the fifth every fifth – and so on. At the last, the 100th, the man finally only changes the state of door number 100.

The question is: How many of the 100 compartments are open after the 100th pass?

Click through for one solution in R.

Comments closed

Sunflower Plots in R

Published 2021-02-05 by Kevin Feasel

Kenneth Tay takes a look at a sunflower plot:

A sunflower plot is a type of scatterplot which tries to reduce overplotting. When there are multiple points that have the same (x, y) values, sunflower plots plot just one point there, but has little edges (or “petals”) coming out from the point to indicate how many points are really there.

My first thought on it is that it’s too busy and doesn’t do its job of portraying a mass of data points very well. When you have just a few observations, then yeah, it’s not too bad. But once you have any reasonable amount of density on the plot, it’s better to use jitter and transparency (as Kenneth points out). H/T R-bloggers

Comments closed

Injecting a Tracer Token into Each Transactional Replication Publication

Published 2021-02-05 by Kevin Feasel

Steve Stedman has a script for us:

When working with SQL Server transactional replication, one of the common checks to see if things look healthy is to insert a tracer token into the replication data stream and see how long it takes to get to the subscribers.

This is a process that can be very tedious if you have multiple publications to check, as you need to go to the replication monitor, click on the publisher, then go to tracer tokens tab and insert a tracer token. Then wait to see the result. If you need to do this more than a couple times, you may be waiting for a while.

As a result, Steve has created a way to simplify this. Click through for that script.

Comments closed

Long-Term Backups on Azure SQL Database

Published 2021-02-05 by Kevin Feasel

Arun Sirpal takes us through a fairly new feature in Azure SQL Database:

There is a new (ish) interface to looking and configuring backups for your Azure SQL Database. This can be found within the settings section of the SQL Server.

As you can see, by default we have 7 days retention to allow for PITR – Point In Time Recovery, anything longer you will need to setup long term retention.

Click through to see how to set this up.

Comments closed

Scrolling Credits in Video Meetings

Published 2021-02-05 by Kevin Feasel

Rob Farley continues a thread:

The basic concept is that once you have a greenscreen application background in place, you can easily show text or drawings or whatever on the screen, superimposed over your webcam feed. I show how to do this in this post: http://blogs.lobsterpot.com.au/2021/01/30/presentation-trickery-online-glassboard-like-lightboard-but-using-just-free-software/. Scott Hanselman also made a video about it at https://youtu.be/-oaikJCR6ec, and used the idea to make scrolling credits, which is a really neat idea that everyone loves.

Click through to see how it works.

Comments closed

Indicators of Schema Issues

Published 2021-02-04 by Kevin Feasel

Erik Darling has a good list of schema-related issues:

Something is broken in the way that you store data.

You’re overloading things, and you’re going to hit big performance problems when your database grows past puberty.

Most of what he’s describing in this post is a failure of atomicity, which implies a failure to achieve first normal form. Mind you, all of these functions are perfectly reasonable as part of data loading, and many of them are perfectly reasonable in the SELECT clause of a query (though that’s still a sign of failure of atomicity), but once you start throwing them into the WHERE clause, we’ve got problems.

Comments closed

Shuffling Excel Worksheets and Changing Tab Colors with Powershell

Published 2021-02-04 by Kevin Feasel

Mikey Bronowski continues a series on using Powershell to modify Excel files:

In this part, we will work on an existing workbook that already has worksheets. If you want to learn how to add new worksheets using the Add-Worksheet have a look at this blog post.

In case you haven’t noticed in the script above we used -MoveToStart switch, that means all the new worksheets were added at the beginning.

Read on for examples around moving sheets to the front or end, moving sheets before or after other sheets, and changing the colors of different tabs.

Comments closed

Memory Grants: Query Memory and Workspace Memory

Published 2021-02-04 by Kevin Feasel

Deepthi Goguri continues a series on memory grant internals:

The memory grant for the above query is 183496/8=22937 pages of memory grant. The total available grant for the queries is 92442. We can run the above query 4 times to hit the total memory grant (22937*4=91748). We can use the SQLQueryStress tool to run the query and see the query memory grants. Let us run the query with 100 number of iterations and 10 number of threads.

This has been an interesting series so far and I look forward to seeing the rest of it.

Comments closed

Random Number Generation in T-SQL

Published 2021-02-04 by Kevin Feasel

Chad Callihan generates random numbers:

The first way to generate a random number is by using the SQL Server RAND function. This will return a float value. 

Both of the techniques Chad shows are examples of generating uniform distributions—distributions in which any value is just as likely as any other. There are plenty of places in which a uniform is great: drawing by lot is one of them. But when you’re generating artificial data, the results tend to look unrealistic because not many natural phenomena follow uniform distributions.

If you’re interested in generating numbers which tend to look more realistic when generating artificial data, I have a post on generating random numbers built on a normal distribution.

Comments closed

SQL Server Compression Q&A

Published 2021-02-04 by Kevin Feasel

Bob Pusateri has some follow-up Q&A after a session:

I was extremely fortunate to be able to present about data compression at the EightKB SQL Server internals conference last week. If you missed my talk in person, you can now view it, as well as the entire day, on YouTube!

I was able to answer many questions during the session, but there were a few still left after time ran out. I wanted to address them all, so here they are!

Click through for the video as well as some Q&A.

Comments closed