Press "Enter" to skip to content

Day: August 19, 2020

Fun with Benford’s Law

Nagdev Amruthnath covers a topic which brings me joy:

Benford’s Law is one of the most underrated and widely used techniques that are commonly used in various applications. United States IRS neither confirms nor denies their use of Benford’s law to detect any number of manipulations in income tax filing. Across the Atlantic, the EU is very open and proudly claims its use of Benford’s law. Today, this is widely used in accounting to detect any fraud. Nigrini, a professor at the University of Cape Town, also used this law to identify financial discrepancies in Enron’s financial statement. In another case, Jennifer Golbeck, a professor at the University of Maryland, was able to identify bot accounts on twitter using Benford’s law. Xiaoyu Wang from the University of Winnipeg even published a report on how to use Benford’s law on images. In the rest of this article, we will take about Benford’s law and how it can be applied using R.

The applications to images and music were new to me. Very cool. H/T R-Bloggers

Comments closed

The Row Count Spool Operator

Hugo Kornelis dives into another operator:

The Row Count Spool operator is one of the four spool operators that SQL Server supports. It counts the number of rows in its input data, and can then later return that same amount of rows, without having to call its child operators to produce the input again.

The Row Count Spool can be viewed as similar to Table Spool, but optimized for cases where the amount of rows is relevant but their content is not. Because the content of the rows is not relevant, the operator does not need to use tempdb to store its input in a worktable; it only has to keep a running count as it reads the input. The other two spool operators have different use cases: Index Spool is used to enable the spool operator to return specific subsets of the input multiple times, and the Window Spool operator is used to support the ROWS and RANGE specifications of windowing functions.

Read on to see where this might be useful and when it may appear.

Comments closed

Managing User Input and Creating Menus in Powershell

Mark Wilkinson wants a bit of user interaction:

Fully automated hands-off PowerShell scripts can be extermely useful for the DBA or System Administrator, but what if you need to get input from the user, or maybe you want to implement a menu system? Like most things related to PowerShell, you have a few options:

[Console] object methods

Most use cases are covered by Read-Host, but if you need something a little more flexible, the [Console] methods might be the way to go.

Read on to see how these work, as well as one way to create a menu.

Comments closed

Statistics Management with Azure SQL DB Serverless

Joey D’Antony takes us through stats management with the serverless tier of Azure SQL Database:

One of the only things platform as a service databases like Azure SQL Database do not do for you is actively manage column and index statistics. While backups, patches, and even integrity checks are built into the platform services, managing your metadata is not. Since Azure SQL Database lacks a SQL Sever Agent for scheduling, you have to use an alternative for job scheduling. 

Read on to learn about techniques as well as a few gotchas.

Comments closed

Decoding Helm Secrets with a kubectl Plugin

Andrew Pruski didn’t want to type that much:

The post goes through deploying a Helm Chart to Kubernetes and then running the following to decode the secrets that Helm creates in order for it to be able to rollback a release: –

kubectl get secret sh.helm.release.v1.testchart.v1 -o jsonpath="{ .data.release }" | base64 -d | base64 -d | gunzip -c | jq '.chart.templates[].data' | tr -d '"' | base64 -d

But that’s a bit long winded eh? I don’t really fancy typing that every time I want to have a look at those secrets. So I’ve created a kubectl plugin that’ll do it for us!

Click through to see the code, how you install the plugin, and how you use it.

Comments closed

Cleaning Up the SQL Server Error Log

Garry Bargsley does some spring cleaning:

If you are like me, you inherited variously configured SQL Servers when you took over as the DBA for your company. After almost two years, I have gotten all the standards in place where I feel that the environment is clean. One of the last things I accomplished was to standardize SQL Server Error Log configurations, Error Log Cycle schedules and cleaning up of old Error Logs.

To accomplish this there were several steps involved to get all SQL Servers into a unified set of configurations.

This is pretty easy to do and to script out.

Comments closed

Triggers and Isolation Levels

Louis Davidson walks us through a tricky problem:

Writing data integrity code in TRIGGER objects is generally is pretty simple. Typically you write a query to see “is there a row in inserted/deleted that is not set up as desired for the current transaction?” and no bad data is found, keep moving. And because MOST of the time, you would only use a TRIGGER object where transactions are quick coupled with the reality that a lot of the issues like I will describe would have to occur within milliseconds… concurrency issues seem to rarely occur. But…anything that isn’t impossible, is possible. Because of the rarity of the issue, if your code isn’t running a life of death system, when the anomalies happen, they are brushed off as “just something weird occurred”. 

This blog will cover one such weird occurrence that can happen when your trigger validation code queries another table in the default isolation level. You expect that your data is protected until your transaction ends, but it may not be.

Do read the whole thing.

Comments closed