SQL Server Import From Excel And TLS 1.0

Chris Thompson has an important notice if you import data into Excel from SQL Server:

As you may know, TLS 1.0 is being deprecated due to various known exploits and will no longer be PCI compliant as of June 30th, 2018 (see PCI DSS v3.1 and SSL: What you should do NOW below).   You may also know that Microsoft has provided TLS 1.1/1.2 patches for the SQL Server Database Engine (2008+) as well as the client connectivity components (see TLS 1.2 support for Microsoft SQL Server below). What you may NOT know is that there is a popular feature in Excel to import data from SQL Server. See the screen print below from Excel 2016.

The problem with this feature lies in the fact that this menu option will, by default, leverage SQLOLEDB.1 as the OLE DB provider when connecting to SQL Server. This provider is an older MDAC/WDAC provider (see Data Access Technologies Road Map below) that comes built into the Operating System (including Windows 10) but DOES NOT support TLS 1.1+. So, if you have SQL Servers that have TLS 1.0 Server disabled, you will no longer be able to use this feature. You will receive an error similar to the one below. You will also receive the same or similar error if you have existing workbooks that use this feature and attempt to refresh those workbooks.

Chris has a workaround for current versions of Excel and notes that future versions will hide this particular import option behind a legacy wizards menu.

Columnstore Deadlocking

Kendra Little shows us a scenario in which querying columnstore metadata during table updates can lead to a deadlock:

I was playing around with a nonclustered columnstore index on a disk-based table. Here’s what I was doing:

Session 1: this session repeatedly changed the value for a single row, back and forth. This puts it into the delta store.

Session 2: this session repeatedly counted the number of rows in the table, using the columnstore index.

With my sample data in this scenario, I found I frequently generated deadlocks.

Read on for more.

Concatenation Truncation

Kevin Feasel



Adrian Buckman walks through one of the more annoying aspects of building large strings in SQL Server:

So there I was building this massive VARCHAR(MAX) string and concatenated at various points in my code were Database names of the datatype NVARCHAR(128).
The interesting part was that I was expecting SQL server to use my largest data type – the VARCHAR(MAX) and just concatenate the NVARCHAR(128) values into it
this was not the case – what actually happened was my string of  VARCHAR(MAX) characters being truncated down to an NVARCHAR(4000)!

There is a reason for this and its all to do with Data Type Precedence in this case the NVARCHAR is preceding my VARCHAR unless of course I explicitly convert the NVARCHAR to a VARCHAR.

Read the whole thing.

Performance Improvements In SQL Server 2017

Bob Ward goes over some of the performance improvements introduced in SQL Server 2017:

Consider for a minute all the built-in capabilities that power the speed of SQL Server. From a SQLOS scheduling engine that minimizes OS context switches to read-ahead scanning to automatic scaling as you add NUMA and CPUs. And we parallelize everything! From queries to indexes to statistics to backups to recovery to background threads like LogWriter. We partition and parallelize our engine to scale from your laptop to the biggest servers in the world.

Like the enhancements we made as described in It Just Runs Faster, in SQL Server 2016, we are always looking to tune our engine for speed, all based on customer experiences. Take, for example, indirect checkpoint, which is designed to provide a more predictable recovery time for a database. We boosted scalability of this feature based on customer feedback. We also made scalability improvements for parallel scanning and consistency check performance. No knobs required. Just built-in for speed.

One of the coolest performance aspects to built-in speed is online operations. We know you need to perform other maintenance tasks than just run queries, but keep your application up and running, so we support online backups, consistency checks, and index rebuilds. SQL Server 2017 enhances this functionality with resumable online index builds allowing you to pause an index build and resume it at any time (even after a failure).

I saw the performance improvements in 2016 and am looking forward to the ones in 2017.

Ribbon Charts

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.

Recognizing Wood Knot Images

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.

SQL Server Memory Usage Myths

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.

Understanding Alert Methods

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.

Running Powershell Tasks With SQL Agent

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.

SSIS In Azure

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.


September 2017
« Aug Oct »