Press "Enter" to skip to content

Curated SQL Posts

Traipsing Through commondelete

Ewald Cress focuses on a function named commondelete to explain reference counting and an elegant use for vftables:

Notionally we can imagine a global portfolio of active memory allocations, each chunk uniquely identified by its starting address. When we want memory, we ask the global memory manager to lend us some from the unused pool, and when we’re done with it, we hand it back to that memory manager, who carefully locks its internal structures during such operations, because we should only access mutable global state in a single-threaded manner, and…. Oops. No, no, double no. That is not how SQL Server does things, right?

Okay, we know that there are actually a variety of memory allocators out there. If nothing else, this avoid the single bottleneck problem. But now the question becomes one of knowing which allocator to return a chunk of memory to after we’re done with it.

As usual, this is a deep and interesting blog post from Ewald.

Comments closed

Adding Powershell Job Steps To Existing SQL Agent Jobs

Rob Sewell uses Powershell to add a Powershell job step to a set of existing SQL Agent jobs:

I put all of our jobs that I required on the estate into a variable called $Jobs. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file and of course you can add more logic to filter those servers as required.

$Jobs = (Get-SQLAgentJob -ServerInstance $Servers).Where{$_.Name -like '*PartOfNameOfJob*' -and $_.IsEnabled -eq $true}

Of course to add a PowerShell Job step the target server needs to be SQL 2008 or higher. If you have an estate with older versions it is worth creating a SMO server object (you can use a snippet) and checking the version and then getting the jobs like this

Click through for the process.

Comments closed

Powershell In Bash: Cursor Issues Fixed

Max Trinidad points out that the latest alpha for Powershell in Bash has fixed an annoying display issue:

Finally, the fix we all been waiting for has arrived with the newest release of PowerShell version v6.0.0-alpha.17. Now, when you installed the Ubuntu 16.04.1 version of PowerShell 6 in Bash on Windows 10, it works this time.

Thanks God! No more crazy cursor screen issues going on when you type commands and/or display results.

Max also shows how to install the latest version of Powershell in Bash on Ubuntu in Windows 10.

Comments closed

Biml And ADF, Part 2

Meagan Longoria builds Azure Data Factory pipelines using BimlScript:

The great thing about Biml is that I can use it as much or as little as I feel is helpful. That T-SQL statement to get column lists could have been Biml, but it didn’t have to be. The client can maintain and enhance these pipelines with or without Biml as they see fit. There is no vendor lock-in here. Just as with Biml-generated SSIS projects, there is no difference between a hand-written ADF solution and a Biml-generated ADF solution, other than the Biml-generated solution is probably more consistent.

And have I mentioned the time savings? There is a reason why Varigence gives out shirts that say “It’s Monday and I’m done for the week.”

Click through for the script.

Comments closed

U-SQL Custom Python Libraries

Saveen Reddy explains how to build a custom Python library and use it with U-SQL:

First, let’s talk about “zipimport”. Thanks to the adoption of PEP 273 – Python had the ability to import modules from ZIP files since Python 2.3. This ability is called “zipimport” and is a built-in feature of the Python’s existing import statement. Read the zipimport documentation now.

To review the basics.

  • You create a module (a .py file, etc.)

  • ZIP up the module into a .zip file

  • Add the path to the .zip file to sys.path

  • Then import the module

Read on for the step-by-step process.

Comments closed

SSAS Extended Events

Eugene Polonichko shows how to use Extended Events with SQL Server Analysis Services:

Cubes require frequent monitoring since their productivity decreases quite often (slowdowns during query building, processing time increment). To find out the reason of decrease, we need to monitor our system. For this, we use SQL Server Profiler. However, Microsoft is planning to exclude this SQL tracing tool in subsequent versions. The main disadvantage of the tool is resource intensity, and it should be run on a production server carefully, since it may cause a critical system productivity loss.

Thus, Extended Events is a general event-handling system for server systems. This system supports the correlation of data from SQL Server which allows getting SQL Server state events.

If you’re already familiar with relational engine XEs, this isn’t much of a stretch.

Comments closed

Dockerizing Windows Applications

Elton Stoneman has a five-step process for turning your Windows app into a containerized application:

1. Choose Your Base Image

Docker images for Windows apps need to be based on microsoft/nanoserver or microsoft/windowsservercore, or on another image based on one of those.

Which you use will depend on the application platform, runtime, and installation requirements. For any of the following you need Windows Server Core:

  • .NET Framework apps
  • MSI installers for apps or dependencies
  • 32-bit runtime support

For anything else, you should be able to use Nano Server. I’ve successfully used Nano Server as the base image for Go, Java and Node.js apps.

This is good reading if you’re thinking of offering a containerized version of your product.

Comments closed

Easy Code Refactoring Opportunities

Bert Wagner shows a few easy things you can do to clean up C# code:

1. Clean up formatting

The overall format of your code is what makes it possible to quickly navigate to areas of interest. Consistent indentation, line breaks, and patterns help programmers skim large chunks of code. Take the following sloppily formatted code for example:

Read on for the rest.  This has analogues in every language:  the goal is to create simple, concise, easily scannable, and human-readable code which also correctly solves the relevant business problem.

Comments closed

Auditing Login Attempts

Cedric Charlier shows how to use server audits to track failed and successful logins (and logouts):

The core issue is that we have many users and logins on our databases and we have huge doubt their respective needs. The root cause is identified: sometimes, for a short period of time, we’re making exceptions to our own rules and let a few other friend projects access to our DEV database. On some other cases, we’re connecting our own solution in DEV environnement to the QA environnement of another solution. Why … planning, data quality issue, … we’ve valid reasons to do it … but these exceptions should be removed as soon as possible. And you know what? People forget. Nowadays, on our largest solution, we have 20 users but only 7 of them are expected and documented … other should be removed. But before executing this cleanup, we’d like to be sure that these users are not effectively used by other solutions. If it’s the case, we’ll need to update first the configuration of the corresponding solution.

Click through for a few scripts to show how to set this up as well as how to query the audit log.

Comments closed

Analyzing The Ramones

Salvino Salvaggio uses R to analyze The Ramones:

Musical purists always reproached the Ramones for knowing a couple of chords only and making an excessive use of them. Data show that the band knew at least… 11 different chords (out of too-many-to-bother-counting possibilities) although 80% of their songs were built on no more than 6. And there is no evidence of a sophistication of the Ramones’ compositions over time.

It’s a fun analysis with all the R code attached.  This fun analysis, however, includes n-gram analysis, sentiment analysis, and token distribution analysis.

Comments closed