Kevin Feasel – Page 1624

Infrastructure Trends

Published 2016-04-27 by Kevin Feasel

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

BimlExpress

Published 2016-04-26 by Kevin Feasel

Catherine Wilhelmsen introduces us to BimlExpress:

BimlExpress is a free Visual Studio add-in for working with Biml in your SSIS projects. It allows you to add and edit Biml files, generate SSIS packages from Biml, and the code editor is fully featured with syntax coloring, error highlighting and intellisense.

If you are already using BIDS Helper, you will see that BimlExpress works the same way and includes all the same Biml features as in BIDS Helper – just with a new and improved code editor. No more squiggly red lines, yay!

For normal Biml purposes, I see some benefit. But to me, the biggest benefit is when presenting: a common question which comes up during Biml talks is, “Why does Intellisense say you have so many errors?” A superior code editor is well worth the download.

Comments closed

Transforming Cursors

Published 2016-04-26 by Kevin Feasel

Mickey Stuewe has a post in which she transforms a cursor into a set-based procedure:

His approach was to use a cursor to cycle through all the columns in the provided table, analyze each column, determine the new data type, and store the information in a table variable. After the cursor was completed, the data in the table variable was written to a permanent table for the next process to use.

This approach isn’t necessarily bad. If you are only running it infrequently and you needed to write this stored procedure quickly, then it’s fine. But if this type of stored procedure needs to be run frequently, then it should be rewritten.

Set-based code tends to be easier to read and more compact than cursors, so even without the performance improvements they bring, there are benefits.

Comments closed

DISTINCT Windows

Published 2016-04-26 by Kevin Feasel

Daniel Hutmacher looks at ways to get windows of distinct elements from a table:

Probably the most common distinct aggregate is COUNT(DISTINCT). In some respects, it’s similar to the windowed function DENSE_RANK(). DENSE_RANK(), as opposed to ROW_NUMBER(), will only increment the row counter when the ordering column(s) actually change from one row to the next, meaning that we can use DENSE_RANK() as a form of windowed distinct count

This is a very interesting approach to the problem. Read the whole thing.

Comments closed

Power BI: Dynamic Chart Titles

Published 2016-04-26 by Kevin Feasel

Chris Webb shows how to generate dynamic chart titles using Power BI:

But what about if you want the chart title to change depending on what is selected? For example, you might be using slicers or filters to allow a user to choose which days of the week they want to see data for. In that situation you might want to add a title that shows which days of the week have actually been selected; this would be particularly important if the report uses filters, or if the report is going to be printed. Unfortunately the built in Title Text property can’t be used to display dynamic values but in this blog post I’ll show you how to solve this problem using DAX.

The solution isn’t trivial, but it is cool.

Comments closed

“Broken” Left Joins

Published 2016-04-26 by Kevin Feasel

James Anderson reminds you to check those WHERE clauses:

We have said that a NULL value for s.DateOfSale is not in the range we are interested in. This means the rows with NULLs in the s.DateOfSale column (our employees yet to make a sale) will be filtered out. It will also filter out employees with sales in months other than March. We have converted the LEFT JOIN into an INNER JOIN.

James’s fix is to move the filter to the join clause, which eliminates the implicit inner join. When I see a condition like this in a code review, the first question on my mind is whether the correct fix is James’s fix or whether the developer really meant to do an inner join. There’s a potential performance gain from using an inner join over a left outer join (due to being able to drive from either table and thus having a larger number of potential execution plans) if it turns out you really do want to filter all rows and not just making the join criterion more specific.

Comments closed

Author: Kevin Feasel