Press "Enter" to skip to content

Author: Kevin Feasel

Ribbon Charts

Published 2017-09-29 by Kevin Feasel

Reza Rad explains what ribbon charts are and why it’s good that they’re now available in Power BI:

Ribbon Chart shows bigger value in each column at the top, then the next value comes after. Look at the sales amount value for female and male in 2005 and 2006. In 2005, Female (Black) had more sales than Male. However, in 2006, Male (Green) generated more revenue than the female, so it is on the top for the 2006 column.

The main benefit of the ribbon chart is in this re-ordering, so it’s easier to tell which categories are largest in a specific time period.

Comments closed

Recognizing Wood Knot Images

Published 2017-09-28 by Kevin Feasel

Bob Horton and Vanja Paunic walk through a lumber grading scenario with Microsoft R Server:

Here we use the rxFeaturize function from Microsoft R Server, which allows us to perform a number of transformations on the knot images in order to produce numerical features. We first resize the images to fit the dimensions required by the pre-trained deep neural model we will use, then extract the pixels to form a numerical data set, then run that data set through a DNN pre-trained model. The result of the image featurization is a numeric vector (“feature vector”) that represents key characteristics of that image.

Image featurization here is accomplished by using a deep neural network (DNN) model that has already been pre-trained by using millions of images. Currently, MRS supports four types of DNNs – three ResNet models (18, 50, 101)[1] and AlexNet [8].

This is a practical example of how to use image recognition to facilitate machine learning.

Comments closed

SQL Server Memory Usage Myths

Published 2017-09-28 by Kevin Feasel

Eric Blinn has a few myths regarding memory usage in SQL Server:

My VM administrator says that I’m not using all the memory I asked for.  In fact, 70% of it is idle at any given time.  We’re going to return that memory to the resource pool to better utilize it on other VMs.

The VM administrators are not lying or misinterpreting what is on their screen.  The metrics displayed on their management tool (Microsoft Hyper V Manager or VMWare vSphere Client) are lying to them.  When the VM management tool is checking on memory activity it asks the OS.  The OS only knows that this memory was allocated to SQL Server a long time ago and that it hasn’t moved since.  It appears to the OS to be stagnant and unused and this is what it reports to the VM management tool.  However, this memory, once allocated to SQL Server, is in the domain of SQLOS which is likely very actively using this memory in a way that is largely invisible to the OS, and by extension, the VM management tool and eventually the VM administrators themselves.  The VM tools are not yet smart enough to ask SQLOS what it is going on with the memory and report falsely that the memory is not being effectively utilized.

Worth reading, particularly if your sysadmins are trying to free up some of that “unused” memory.

Comments closed

Understanding Alert Methods

Published 2017-09-28 by Kevin Feasel

Monica Rathbun implores us to understand where we’re sending our alerts:

Today I ran into something on a client server I unfortunately see too often.  The DBA goes through the trouble of configuring and setting up alerts\operators but doesn’t really understand what the options in the configurations mean. So unfortunately, that means they take the CYA (cover your ass) approach and they check all of them. Now, not only have I seen this with alerts but also with things like security configurations as well. My advice is to always in to take a second and research what each option is before you check the little boxes, especially when it comes to security. Always follow the rule of less is more.

In the example below the administrator enabled alerts for an operator using the CYA approach. They checked email, pager, and netsend.

The E-mail option is probably the only interesting one anymore; if you need paging, integrating with something like Pagerduty (or one of its competitors) is the better call nowadays.

Comments closed

Running Powershell Tasks With SQL Agent

Published 2017-09-28 by Kevin Feasel

Chrissy LeMaire shows how to run Powershell cmdlets from SQL Agent:

By default, only members of the sysadmin role are allowed to create jobs with the CmdExec Job Step, but adding non-sysadmins as principals to the CmdExec proxy works as well.

So here are the steps that I use to schedule my tasks:

  • Create a Windows-based Login in SQL Server

  • Ensure dbatools is available to the account

  • Create a SQL Server Credential

  • Create the Agent Proxy

  • Create the PowerShell .ps1 file

  • Create the Job and Job Step

Chrissy walks you through step by step, making the whole thing easy.

Comments closed

SSIS In Azure

Published 2017-09-28 by Kevin Feasel

Richie Lee reports that SQL Server Integration Services is now available as a service in Azure:

I’ve written about it elsewhere in greater depth, but here are the headlines:

  • It makes use of SSIS Scale Out, which was released as part of SQL Server 2017.

  • Although it is based on SSIS Scale Out, you can’t actually configure SSIS Scale Out to run on the instance. If this confuses you then read my in-depth post.

  • SSISDB is installed in either SQL Azure or on a Managed Instance.

  • You don’t have to create Integration Services Catalog/SSISDB yourself; it is done for you. So that annoying key management is no longer a problem.

Richie’s got more to say on the topic, so check out the highlights and then his in-depth post.

Comments closed

Effective Permissions In SQL Server

Published 2017-09-28 by Kevin Feasel

Daniel Hutmacher has a helper procedure to assist you in understanding who has what effective rights on a SQL Server box:

Principals, permissions and securables can all inherit each other. A principal could for instance be a group or a role, and will confer its permissions on to its group/role members. One permission can imply a number of other permissions – SELECT, for instance, requires you to also have VIEW DEFINITION rights to the object. Securables are also arranged in a hierarchy, with the server owning databases, which in turn own schemas that own objects, and so on.

To make things even more complicated, if you have multiple conflicting permissions (DENY and GRANT), the strictest rule applies, meaning that the effective permission is DENY.

Read on to get his procedure.  For my money, the best method to get these details is to query sys.fn_my_permissions() but that requires that you be able to impersonate the user whose permissions you want to see.

Comments closed

Listing SQL Server Options

Published 2017-09-28 by Kevin Feasel

Kenneth Fisher breaks apart the @@OPTIONS bit flags:

There are a fair number of options settings. ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER, etc. Each session has its own set of configurations. They are initially set based on the user settings system configuration, then the various connection programs (SSMS for example) can override that, then the various SETcommands can override that.

Now personally I prefer to keep my settings to the default to eliminate confusion but they do get changed occasionally. Just as a for example, when you generate a script from SSMS it typically includes a bunch of SET ON and SET OFF commands. And if you turn on a setting that was already on, then turn it off and the end, well, your setting has changed unexpectedly. My original intent for this post was to create a stored procedure that would let you save the current settings and restore them. Unfortunately, I ran into a scope problem. I can find the current settings: @@options. I can break down the integer value using a script from here (just in case the post should disappear before this one does here is the code from the article)

Read on for the list, including things like ANSI_NULLS, ANSI_PADDING, and XACT_ABORT.  I probably pay less attention to these than I should and just have a habit of setting the few most important settings for my environment atop every procedure definition.

Comments closed

A Power BI Report To Track Power BI Changes

Published 2017-09-28 by Kevin Feasel

Julie Smith has found the only way to keep up with Power BI changes—using a Power BI report:

Microsoft makes major improvements via monthly and sometimes weekly releases to Power BI. In my time working on Power BI projects at Innovative Architects, I have found that the only way to stay on top of the frenetic pace of Power BI’s improvements is to closely follow its blog and other social media feeds.

This page is a Power BI report (how meta right?) built off of the content from Microsoft’s Power BI blog. I have set it up to run daily and refresh. Please let me know in the comments if you feel that the data has become stale. As part of building this report, I have added some curated slicers–things like PBI Service, PBI Desktop, Gateways, Connectors– and blogging content tags such as Contest or Webinar. Keep in mind that this is my best effort and there is no guarantee on the accuracy of my tagging. I provide this as my gift to you “as is” with no expressed or implied warranty.

Click through to see the report.

Comments closed

A Cheat Sheet For Purrr

Published 2017-09-27 by Kevin Feasel

Mara Averick has a list of resources for learning more about purrr:

Purrr royal decree (ok, I’ll stop with the 🐱  puns now), the purrr 📦  now has its very own official RStudio cheat sheet: Apply Functions Cheat Sheet

The purrr package makes it easy to work with lists and functions. This cheatsheet will remind you how to manipulate lists with purrr as well as how to apply functions iteratively to each element of a list or vector. The back of the cheatsheet explains how to work with list-columns. With list columns, you can use a simple data frame to organize any collection of objects in R.

So, I thought we’d celebrate with a bit of a purrr 🐦  tweet roundup:

Purrr is one of those libraries I know I need to learn more about, and this looks like a good starting point.

Comments closed