Press "Enter" to skip to content

Day: January 8, 2019

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

ARITHABORT And SET Options

Published 2019-01-08 by Kevin Feasel

Jason Brimhall explains an error message he received:

INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’. 
Verify that SET options are correct for use with 
indexed views 
and/or indexes on computed columns 
and/or filtered indexes 
and/or query notifications 
and/or XML data type methods 
and/or spatial index operations.
[SQLSTATE 42000] (Error 1934). The step failed.


Immediately I started looking at my creation scripts for the tables.  Nope, no XML indexes, no spatial indexes, no filtered indexes, no indexes on computed columns (not even any computed columns),  and no query notifications.

In Jason’s case, it was an oddity around SQL Agent jobs, but there are a few reasons this could pop up, and Jason explains some of the most common.

Comments closed