Press "Enter" to skip to content

Author: Kevin Feasel

Getting SQL Server Context Settings

Published 2017-11-01 by Kevin Feasel

Frank Gill has a script to find current Query Store context settings:

The following query will return the hex and integer value for each row in the table (NOTE: Query store must be enabled for the database to return values):

USE YourQueryStoreDatabase;

SELECT set_options, CONVERT(INT, set_options) AS IntSetOptions
FROM sys.query_context_settings;

The set_options value represents a bit mask, with each binary digit representing a specific set option. The full list of values can be found here. I created stored procedure ReturnSetOptions to take the IntSetOptions from the query above and return the set options represented. The code for the procedure is listed below.

Read on to get a script which breaks the bitmask field into human-readable results.

Comments closed

Using Powershell To Write To A File

Published 2017-11-01 by Kevin Feasel

Adam Bertram shows how to write to a file using Powershell:

Set-Content is one of those core PowerShell cmdlets that I can’t do without. I still remember using VBscript before we could use PowerShell to write to a file. I remember always trying to remember what kind of object I needed to use and the method name. Was it FileSystemObject, FileObject or what? It was a pain! Also, even when I did recall the method name was CreateTextFile, I’d always forget to add True as the second argument.

Here’s an example of the monstrosity I’m talking about.

Click through to see how easy writing is with Powershell.

Comments closed

Locale Problems With SSAS

Published 2017-11-01 by Kevin Feasel

Koen Verbeeck diagnoses an awful Analysis Services error:

This really didn’t make any sense. However, in one of the Discover Begin/End events, the same number appeared again: 8192 (this time explicitly marked as locale identifier). Hmmm, I had problems with weird locales before. I dug into my system, and yes, the English (Belgium) locale was lingering around. I removed it from my system and lo and behold, I could log into SSAS with SSMS again. Morale of the story: if you get weird errors, make sure you have a normal locale on your machine because apparently the SQL Server client tools go bonkers.

Worth reading the whole thing.  And also maybe just using en-US for all locales; at least that one gets tested…

Comments closed

Deciding Which Indexes To Tune

Published 2017-11-01 by Kevin Feasel

Jeff Schwartz gives us some guidance on finding good candidate indexes for tuning:

Index usage and tuning metrics became available on SQL Server 2005 with Dynamic Management Views and Functions, which will be discussed later. However, the meanings and significance of index DMV/DMF metrics are still not well understood by many despite only minor additions over the years. Specifically, the following list contains a synopsis of the topics that the author has observed to be the most salient index-related issues:

  1. Identifying

    • Queries that need an index to function efficiently
    • Which indices, if any, are used by a query (and how they are used, e.g., randomly or sequentially)
    • Tables (and their indices) that merit evaluation and potential adjustment
    • Indices that duplicate functionality of others

  2. Understanding when

    • A new index is truly needed and what improvement can be anticipated
    • An index can be deleted without harming performance
    • An index should be adjusted or added despite the query not producing any missing index warnings

  3. Understanding why having too many indices results in

    • Inserts and updates taking too long and/or creating blocking
    • Suboptimal query plans being generated because there are too many index choices

  4. Knowing Database Engine Tuning Advisor (DTA) pros & cons

Jeff starts with the basics of indexes, followed by some general strategy.  This promises to be the first of several posts on the topic.

Comments closed

Making a Shiny Dashboard

Published 2017-10-31 by Kevin Feasel

Anish Sing Walia walks us through creating a dashboard using Shiny:

Shiny is an amazing R package which lets the R developers and users build amazing web apps using R itself. It lets the R users analyze, visualize and deploy their machine learning models directly in the form of the web app. This package lets you host standalone apps on a webpage or embed them in R markdown documents or build dashboards and various forecasting applications. You can also extend your Shiny apps with CSS themes, htmlwidgets, and JavaScript actions. Shiny lets us write client-side front-end code in R itself and also lets users write server-side script in R itself. More details on this package can be found here.

I recently learned Shiny and started developing a web application using it.And since then I have been in love with it and have been using it in each and every data science and analytics project. The syntax is super easy to understand and there are lots of amazing articles and documentation available for you to learn it and use it. I personally had a background of developing full-stack web applications using HTML, CSS and javascript and other JS based scripting languages so I found the syntax easy.

I keep meaning to learn Shiny and someday I will, just to prove to my intern that she’s not the only one here who can…

1 Comment

Reverting Database Changes

Published 2017-10-31 by Kevin Feasel

Alex Yates talks about database rollback in the event of a code release failure:

A thorny topic. Rolling back code is easy. Normally you can just redeploy the old binaries and you’re done. Databases are more difficult – because data. The existence of persistent data has two significant consequences:

  1. You can’t just redeploy the entire database – you need to create a script to migrate your existing database from the existing (undesirable) state back to the old (desired) state. This probably isn’t an automated process.

  2. You need to think about new data that has entered the database since the deployment. Do you want to keep it? Do you need to process it to get it into an older format? (E.g. if you need to undo a column split.)

Alex has some helpful tips for structuring database changes.  Me, I just never look back…which is actually one of the strategies Alex talks about.

Comments closed

Kaggle Data Science Report For 2017

Published 2017-10-31 by Kevin Feasel

Mark McDonald rounds up a few notebooks covering a recent Kaggle survey:

In 2017 we conducted our first ever extra-large, industry-wide survey to captured the state of data science and machine learning.

As the data science field booms, so has our community. In 2017 we hit a new milestone of reaching over 1M registered data scientists from almost every country in the world. Representing many different backgrounds, skill levels, and professions, we were excited to ask our community a wide range of questions about themselves, their skills, and their path to data science. We asked them everything from “what’s your yearly salary?” to “what’s your favorite data science podcasts?” to “what barriers are faced at work?”, letting us piece together key insights about the people and the trends behind the machine learning models.

Without further ado, we’d love to share everything with you. Over 16,000 responses surveys were submitted, with over 6 full months of aggregated time spent completing it (an average response time of more than 16 minutes).

Click through for a few reports.  Something interesting to me is that the top languages/tools were, in order, Python, R, and SQL.  For the particular market niche that Kaggle competitions fit, that makes a lot of sense:  I tend to like R more for data exploration and data cleansing, but much of that work is already done by the time you get the dataset.

Comments closed

Error Handling On SQL With Linux

Published 2017-10-31 by Kevin Feasel

Anthony Nocentino explains Linux error codes and systemd behavior for SQL on Linux:

Now in the output above, you’ll notice a bolded line. In there, you can system that systemd[1] receives a return code from SQL Server of status=1/FAILURE.  Systemd[1] is the parent process to sqlservr, in fact it’s the parent to all processes on our system. It receives the exit code and immediately, systemd initiates a restart of the service due to the configuration we have for our mysql-server systemd unit.
What’s interesting is that this happens even on a normal shutdown. But that simply doesn’t make sense, return values on clean exits should return 0. It’s my understanding of the SHUTDOWN command, that it will cause the database engine to shutdown cleanly.

On the development side, there aren’t many differences between SQL on Linux versus SQL on Windows (aside from things which haven’t yet made the move); on the administration side, there are some interesting differences.

Comments closed

Handling Late Arrivals In SSIS

Published 2017-10-31 by Kevin Feasel

Peter Schott shows us a pattern for dealing with late-arriving dimension members in SQL Server Integration Services ETL packages:

The general steps are

  1. Set up your source query.

  2. Pass the data through a Lookup for your Dimension with the missing results routed to a “No Match” output.

  3. Insert those “No Match” rows into your Dimension using a SQL task – checking to make sure that this particular row hasn’t already been inserted (this is important).

  4. Do another lookup using a “Partial Cache” to catch these newly-inserted members.

  5. Use a UNION ALL transform to bring the existing and late-arriving members together.

Click through for more information and a helpful package diagram.

Comments closed