Press "Enter" to skip to content

Month: January 2019

Automation With Powershell Desired State Configuration

Published 2019-01-09 by Kevin Feasel

Jess Pomfret takes us on a journey to a desired state:

PowerShell DSC is a platform to support the concept of Infrastructure as Code (IaC).  It uses declarative syntax instead of the usual imperative syntax of PowerShell.  This means that you describe your desired state rather than the specific steps needed to get there.  There are two modes for DSC, push and pull, although pull mode offers more features and scalability, we’ll look at writing our configuration and using push mode for this blog post to keep it simple.

This post covers initial installation and some of the initial configuration, so check it out if you’re new to DSC.

Comments closed

A Docker-Based Sandbox For dbatools

Published 2019-01-09 by Kevin Feasel

Chrissy LeMaire takes us through using Docker to build a playground for learning the functionality inside dbatools:

I’ve long wanted to do this to help dbatools users easily create a non-production environment to test commands and safely explore our toolset. I finally made it a priority because I needed to ensure some Availability Group commands I was creating worked on Docker, too, and having some clean images permanently available was required. Also, in general, Docker is a just a good thing to know for both automation and career opportunities

Probably a little bit better to work on cmdlets you don’t know about in a sandboxed container rather than on production. Just a little bit.

Comments closed

Rowcount Shenanigans When Deleting In Batches

Published 2019-01-09 by Kevin Feasel

Denis Gobo takes us through a few issues you might run into when deleting data in batches:

I have always used WHILE @@rowcount > 0 but you have to be careful because @@rowcount could be 0 when your while loop starts

Let’s take a look at an example. This is a simplified example without a where clause..but let’s say you have to delete several million rows from a table with many more millions of rows and the table is replicated… in that case you want to batch the deletes so that your log file doesn’t fill up, replication has a chance to catch up and in general the deletes should run faster

Click through for a couple of issues you might run into other than the obvious one of “I’m scanning the entire table with every delete” if you don’t have indexing set up right.

Comments closed

Invoke-DbaDiagnosticQuery In dbatools

Published 2019-01-09 by Kevin Feasel

Andre Kamman walks through a particularly useful cmdlet in the dbatools package:

My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result. 
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!

Click through for a demonstration of this cmdlet and the useful output it generates.

Comments closed

R htmlTable Updates

Published 2019-01-08 by Kevin Feasel

Max Gordon has some updates to the htmlTable package:

Even more common than grouping columns is probably grouping data by rows. The htmlTable allows you to do this by rgroup and tspanner. The most common approach is by using rgroupas the first row-grouping element but with larger tables you frequently want to separate concepts into separate sections. Here’s a more complex example. This has previously been a little cumbersome to to counting the rows of each tspanner but now you’re able to (1) leave out the last row, (2) specify the number of rgroups instead of the number of rows. The latter is convenient as the n.tspanner must align with the underlying rgroup. 

I haven’t used this package before, but it does look interesting. H/T R-bloggers

Comments closed

Simulating Load With Powershell

Published 2019-01-08 by Kevin Feasel

Grant Fritchey builds a quick script to simulate load using PowerShell:

The idea is to be able to easily do one of several different things. By commenting out different sections of the code, I can change the general behavior. Most of the work is done in the  # Run forever section of the code.
First, I’ll randomly pick a modulus comparison. When that hits and the remainder is 0, then I randomly wait between 3 and 13 seconds. Clearly, any of these can be adjusted.
The query gets executed. Then, I have to options for dealing with the query in cache. I can clear cache on every execution. I’ve found this very useful when dealing with bad parameter sniffing (testing or generation). Or, I can use another random set of code to occasionally remove the procedure from cache.

Click through for the script and some more notes from Grant.

Comments closed

Installing ML Services With Python Support In SQL Server 2019

Published 2019-01-08 by Kevin Feasel

Rich Brenner walks us through installing SQL Server 2019 and enabling Python support:

First things first, you’ll want to choose your version of SQL Server. Python is available on 2017 and greater. For this demo I’ll be using SQL Server 2019 Developer Edition (CTP 2.2).
With 2019 CTP2.2 they’ve increased the requirement of your OS too, in my example I had a spare VM with Windows Server 2012 laying around but I needed to update this to Server 2016. Check the relevant documentation for the version you’re using.

Click through for a step by step guide with plenty of screenshots.

Comments closed

SOS_SCHEDULER_YIELD And Hypervisors

Published 2019-01-08 by Kevin Feasel

Jonathan Kehayias has an interesting article on generating SOS_SCHEDULER_YIELD waits due to CPU over-subscription on the host:

The test environment that I used for this is a portable lab I’ve used for demos for VM content over the last eight years teaching our Immersion Events. The ESX host has 4 cores and 8GB RAM and hosts three virtual machines, a 4vCPU SQL Server with 4GB RAM, and two 2vCPU Windows Server VM’s with 2GB RAM that are used strictly to run Geekbench to produce load.  Within SQL Server, I a reproducible workload that drives parallelism and is repeatable consistently, that I have also used for years in teaching classes.
For the tests, I first ran a baseline where the SQL Server VM is the only machine executing any tasks/workload at all, but the other VMs are powered on and just sitting there on the hose.  This establishes a base metric in the host and had 1% RDY time average during the workload execution, after which I collected the wait stats for SOS_SCHEDULER_YIELD.

From there, Jonathan starts cranking up the load on the application servers and sees what it does to SQL Server ready time. This is a great reason not to over-subscribe on CPUs on mission-critical hosts.

Comments closed

Data Professional Salary Survey Results Released

Published 2019-01-08 by Kevin Feasel

Brent Ozar has a new year of data professional salary results:


How much do database administrators, analysts, architects, developers, and data scientists make? We asked, and 882 of you from 46 countries answered this year. Y’all make a total of $84,114,940 USD per year! Hot diggety. (And at first glance, it looks like on average, y’all got raises this year.)

Download the 2019, 2018, & 2017 results in Excel.

Read on for some notes about the data and start playing around.

Comments closed

Emailing SSIS Errors

Published 2019-01-08 by Kevin Feasel

Peter Schott improves upon Kevin Hill’s script:


Recently, Kevin Hill (b | t ) posted on getting package errors from the SSIS catalog in a single query as opposed to clicking through the SSIS Reports and digging through pages.  I took that and ran with it a little bit. The first pass needed an additional index on the catalog to increase performance.  Kevin’s included that at the bottom of his query on the post above.  (You probably don’t need the included “message” column, though.)
I wanted to take this and run with it a little bit to report on all errors for a given folder within the last day, then e-mail that in an HTML formatted e-mail. To that end, I wrote up a quick stored procedure that should take the Folder or Package or Project name and a “to” e-mail address to send an e-mail through DBMail.

Click through for the script.

Comments closed