Press "Enter" to skip to content

Month: April 2016

Polybase

Published 2016-04-28 by Kevin Feasel

Ayman El-Ghazali talks Polybase:

HDFS is a distributed file system that works differently than what we’re used to in the Windows OS side of things; the general principle is to use cheap commodity hardware that replicates data in order to account for availability and to prevent loss of data. With that in mind, it makes a great use case to store a lot of data cheaply for archiving purposes or can be used to store large quantities of data that been to be processed in large quantities as well.

For more information please visit: https://msdn.microsoft.com/en-us/library/mt143171.aspx

Now if you want to try it out for yourself, make sure you install the PolyBase Engine (from the SQL Server setup) and feel free to try the modified code sample below.

Polybase is, without a doubt, my favorite SQL Server 2016 feature.  I am excited to put this through its paces in a production environment.

Comments closed

Primary Keys On TVPs And Plan Forcing

Published 2016-04-28 by Kevin Feasel

Michael J. Swart notes that you cannot force query plans if you’re using a user-defined table type with a non-named primary key constraint:

When defining table variables, avoid primary key or unique key constraints. Opt instead for named indexes if you’re using SQL Server 2014 or later. Otherwise, be aware that plan forcing is limited to queries that don’t use these table variables.

Helpful advice when dealing with user-defiened table types.  Read the whole thing.

Comments closed

Registered Servers

Published 2016-04-28 by Kevin Feasel

Erik Darling shows how to set up registered servers in Management Studio:

And if you hip and hop over to the Connection Properties tab, you can set all sorts of nifty stuff up. The biggest one for me was to give different types of servers different colored tabs that the bottom of SSMS is highlighted with. It’s the one you’re probably looking at now that’s a putrid yellow-ish color and tells you you’re connected and that your query has been executing for three hours. Reassuring. Anyway, I’d use this to differentiate dev from prod servers. Just make sure to choose light colors, because the black text doesn’t show up on dark colors too well.

In team environments, I’m more a fan of Central Management Servers.

Comments closed

AcquireConnection Failures

Published 2016-04-27 by Kevin Feasel

Ginger Grant explains what to do when you get AcquireConnection errors:

AcquireConnection method call to the connection manager failed with error code 0xC0202009/0xC020801C

Try as I might, the only thing I was able to do after an hour was periodically change the error code from 0xC0202009 to 0xC020801C. Nothing I did worked. I created a new connection, created a new OleDB Source, changed the Run64BitRuntime to False in Configuration Properties in the Debugging Section of the project execution, set the Data Flow task DelayValidation from False to True. None of these various suggestions that I got from various websites worked at all.  I thought about changing the SSIS Service ID’s execution properties, but since I was running in Debug mode I determined that this would not make any difference, so I abandoned that idea. Nothing worked. The only thing I was able to do was change the error code, not eliminate it. I could log into SQL Server with the same ID and password in my package and run the simple query in the data flow task and return data.  I could preview the data, what I couldn’t do is execute the SSIS package.  Out of desperation I rebooted, which also did nothing.

SSIS security issues.  Gotta love ’em.

Comments closed

Infrastructure Trends

Published 2016-04-27 by Kevin Feasel

Glenn Berry reports on hardware trends:

Right now, you can purchase extremely capable, high performance server processors with physical core counts between four and twenty-two cores per processor. I am referring to the current 14nmIntel Xeon E5-2600 v4 (Broadwell-EP) and the 22nm Intel Xeon E7-8800 v3 (Haswell-EX) families that both use high bandwidth DDR4 memory.

On March 31, 2016, Intel released the 14nm Xeon E5-2600 v4 family (Broadwell-EP) for two-socket servers. This is a Tick release, building on the current Haswell microarchitecture that has up to 22 physical cores and DDR4 2400 support. This processor will work in existing model servers such as the Dell PowerEdge R730 with a BIOS update, which means that there will be less delay before they are actually available for sale.

A lot of companies aren’t interested in The Cloud, so it’s good to know that hardware vendors are keeping up with on-premises demands.

Comments closed

Increased NCI Key Size

Published 2016-04-27 by Kevin Feasel

Jos de Bruijn notes that non-clustered index keys may be larger in SQL Server 2016 and Azure SQL Database:

SQL Server 2016 and Azure SQL Database have increased the maximum size for index keys with nonclustered indexes. The new maximum key size for nonclustered indexes is 1700 bytes. The maximum key size for clustered indexes remains 900 bytes.

95-99% of the time, we don’t want to get anywhere near 900 bytes (much less 1700 bytes), but we all have that one edge case.

Comments closed

SARGable

Published 2016-04-27 by Kevin Feasel

Shane O’Neill explains what SARGable means and why it’s important:

So now that 1). we have our table and b). we have an index we can use, we can run the developer’s query and be SARGable right?

1
2
3
DECLARE @Year INT = 2016;
SELECT [Test_Date] FROM [dbo].[DateTest] WHERE YEAR([Test_Date]) = @Year;
GO

Nope! Table scan, ignores our Index and reads all 15M (too lazy for all the zeros) for a measely 127,782 rows! It’s not the slowest, taking around 3.960 seconds but still, we want SARGable!!!

Watch for the surprise twist at the end.

Comments closed

Azure Data Catalog Test Environment

Published 2016-04-27 by Kevin Feasel

Melissa Coates shows how to create a test environment for Azure Data Catalog:

Azure Data Catalog is a Software as a Service (SaaS) offering in Azure, part of the Cortana Intelligence Suite, for registering metadata about data sources. Check this post for an overview of Azure Data Catalog key features. (I’m a big fan of what Azure Data Catalog is trying to accomplish.)

There are a couple of particulars about Azure Data Catalog which make it a bit more difficult to set up a Demo/Test/Learning type of environment, including:

  • You are required to sign into Azure Data Catalog with an organizational account. Signing in with a Microsoft account (formerly known as a Live account) won’t work for Azure Data Catalog authentication, even if that’s what you normally use for Azure.

  • One Azure Data Catalog may be created per organization. Note this is *not* per Azure subscription – if your account has access to multiple subscriptions, it’s still one catalog per organization.

This method seems, shall we say, overly complicated.  Here’s hoping that the Azure Data Catalog team find a way to improve this experience.

Comments closed

Powershell + Bash

Published 2016-04-26 by Kevin Feasel

Max Trinidad shows how to integrate Powershell with Bash in Windows 10:

If it worth knowing that in order to get Bash, it’s a feature you need to installed it first. The following is the series of steps I use to enabled and install Bash on my desktop. And, after enabling Bash, I started using it under the PowerShell Console.

Apropos of this, I read a very interesting article by Alex Clemmer yesterday on how terrible the Windows command line is.  Powershell and Bash are way, way better for pretty much any purpose, other than perhaps experiencing masochism.

Comments closed