A Fine Slice Of SQL Server
Elizabeth Ricks asks and answers a question:
As the workplace shifts to more remote communication, a question we’ve been receiving frequently in our virtual workshops is “How many words should I put on my slides?”
The answer? It depends on how your audience is consuming the information.
Read on to get past the standard consultant’s answer of “It depends” and see upon what it depends.
Comments closedNagdev 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 closedHugo 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 closedMark 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:
–
Read-Host
–[Console]object methodsMost 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 closedJoey 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 closedAndrew 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 -dBut 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 closedGarry 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 closedLouis 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 closedKai Wähner walks us through the tradeoffs of sending large messages in Apache Kafka:
After exploring use cases for large message payloads, let’s clarify what Kafka is not:
Kafka is usually not the right technology to store and process large files (images, videos, proprietary files, etc.) as a whole. Products were built specifically for these use cases.
For instance, a Content Delivery Network (CDN) such as Akamai, Limelight Networks, or Amazon CloudFront distribute video streams and other software downloads across the globe. Or “big file editing and processing” (like a video processing tool). Or video editing tools from Adobe, Autodesk, Camtasia, and many other vendors are used to structure and present all video information, including films and television shows, video advertisements, and video essays.
There’s a lot of good advice in here. I think the best advice is essentially “don’t do this unless you need it” but I appreciate that Kai goes a lot further than that.
Comments closedDave Bland speeds up sp_helpdb:
When I run this on my computer, it usually takes between 500 and 1000 MS. More on this later.
Now let’s take a look what is happening behind the scenes with sp_helpdb. The first step is to populate a temporary table, #spdbdesc with database name, owner, when it was created and compatibility level. The code for the first step is below.
Watch Dave speed this up a bit.
Comments closed