Press "Enter" to skip to content

Month: November 2016

Columnstore On Standard Edition

Published 2016-11-22 by Kevin Feasel

Niko Neugebauer extends his Columnstore series with a post on what you can do with these indexes in Standard Edition:

Given the improvements and the availability of the of the programability surface for every edition (with some insignificant & logical limitations) that I have blogged about in
SQL Server 2016 SP1 – Programmability Surface for everyone!, I believe everyone using Microsoft Data Platform has rejoyced greatly. Of course, now everyone can have Columnstore Indexes on every SQL Server edition!
There are some noticeable limitations that were announced right from the start, such as the maximum size of the Columnstore Object Pool (you can find more information about it here – Columnstore Indexes – part 38 (“Memory Structures”)), but there are more limitations to the Standard Editions and inferior ones and it is extremely important to know them, to understand them in order to make the right decision – when your Business is ready/needed to upgrade to the Enterprise Edition of the SQL Server.

If you’re on Standard Edition and excited about using Columnstore, do read Niko’s post.  Columnstore won’t work as fast as it does on Enterprise Edition (gotta have a reason to upgrade) but based on what he’s shown thus far, Columnstore is still a good reason to upgrade to 2016 SP1 if you’re on Standard Edition.

Comments closed

Data Lifecycle Management

Published 2016-11-22 by Kevin Feasel

Jan Mulkens reviews three DLM workshops:

Before we continue, I’d like to point out that I was very skeptical about this training at first.
In general I’m not a fan of product specific training, especially not for a broad topic such as DLM.
You always risk just getting a sales person in front of you that wants to rant on about how great their product is.
If you’re unlucky you get comparisons to the leading competing products that apparently are the worst in the world.

Since these workshops are part of Redgate’s training program you can imagine my fear before entering them.
I think most people in the community know Redgate as a positive company that’s very active in the SQL Server Community.
I also don’t think I’ve ever seen something bad written about them, either on a blog or just on twitter.

It sounds like it went well overall, and that’s good:  understanding how to automate your administrative tasks and deployments is a critical part of modern data management.

Comments closed

Check Constraints On Date Ranges

Published 2016-11-22 by Kevin Feasel

Michael Swart has a script to check start versus end dates to ensure there is a valid check constraint for date ranges:

Don’t blindly run scripts that you got from some random guy’s blog. Even if that someone is me. That’s terribly irresponsible.

But this query may be useful if you do want to look for a very specific, simple kind of constraint that may match your business specs. These constraints are just suggestions and may not match your business rules. For example, when I run this query on Adventureworks, I get one “missing” check constraint for HumanResources.Shift(StartTime, EndTime) and when I look at the contents of the Shift table, I get this data:

If you wish non-blindly to run a script you got from that guy’s blog, click through for the script and more details, including a discussion of false positives.

Comments closed

Cross-Platform Database Restoration

Published 2016-11-22 by Kevin Feasel

Max Trinidad walks through restoring a SQL Server database to a new Linux instance:

So, before you start working or trying to restore your Windows SQL Databases to SQL Server on Linux, there are a few things you may need to do. This will involved installing components which are not installed by default when first build your Linux System.

One thing I had to say with confidence! You WILL find all your answer on the internet. Any of the Linux Distro forum have your resolution to any of the issue you may encountered. Also, most of the time you Linux system will tell you (or gives a tip) what do with missing dependencies.

Other than that, there are Linux Application Updater solution to help keep your system up-to-date.

This is an amazing time to be in the SQL Server space.

Comments closed

Scaling Kinesis Streams

Published 2016-11-21 by Kevin Feasel

Allan MacInnis shows how to scale Amazon Kinesis streams using the UpdateShardCount API call:

You also need to adjust the alarm threshold to accommodate for the new shard capacity automatically. For this example, update the alarm threshold to 80% of your new capacity (or 3200 records per second) by setting a CloudWatch alarm with an action to publish to a SNS topic when the alarm is triggered.

You can then create a Lambda function that subscribes to this SNS topic and executes a call to the new UpdateShardCount API operation while adjusting the CloudWatch alarm threshold. To learn how to configure a Cloudwatch alarm, see Creating Amazon Cloudwatch Alarms. For information about how to invoke a Lambda function from SNS, see Invoking Lambda Functions Using Amazon SNS Notifications.

This is pretty cool.

Comments closed

Pie Charts

Published 2016-11-21 by Kevin Feasel

Peter Ellis defends pie charts under very specific circumstances:

The usual response from statisticians and data professionals to pie charts ranges from lofty disdain to outright snobbery. But sometimes I think they’re the right tool for communication with a particular audience. Like others I was struck by this image from New Zealand news site stuff.co.nz showing that nearly half the earthquake energy of the past six years came in one day (last Sunday night, and the shaking continues by the way). Pie charts work well when the main impression of relative proportions to the whole is obvious, and fine comparisons aren’t needed.

Here’s my own version of the graphic. I polished this up during a break while working at home due to the office being shut for earthquake-related reasons:

Consider me in the lofty disdain camp.  That said, this is probably the best case scenario for a pie chart:  when looking at relative percentage of one dominant element versus the remaining set.

Comments closed

In-Memory Columnstore Updates

Published 2016-11-21 by Kevin Feasel

Niko Neugebauer looks at improvements in SQL Server 2016 SP1 with respect to adding columnstore indexes to memory-optimized tables:

With an incredible Service Pack 1 Microsoft has triumphantly announced that all editions (Standard, Web, Express and even Local) will get the most advanced programming capabilities of Columnstore, In-Memory, Database Snapshot, Compression, Partition & many others, plus that there are some incredible features for the T-SQL (CREATE OR ALTER) and Execution Plan details (Actual Information on processed Rows, used Trace Flags, etc),
but at the same time there are some quite important improvement under the hood that will make you want to use Service Pack 1 for SQL Server 2016 immediately. One of this features is the fast addition of the Columnstore Index to the Memory-Optimised tables. Let take it to the test by restoring a copy of the ContosoRetailDW free database:

These results look nice.

Comments closed

Two-Way T Tests

Published 2016-11-21 by Kevin Feasel

Mala Mahadevan shows how to write a two-way T test in R and T-SQL:

I can do the same calculation of T value using T-SQL. I cannot calculate p value from TSQL as that comes from a table, but it is possible to look it up. I imported the set of values into a table called WalkingSteps with two columns, walkerAsteps and walkerBsteps. For doing the math on T value the formula stated here may be useful. My T-SQL code is as below

The R code is a bit shorter, although the T-SQL code isn’t bad either.

Comments closed

The Risk Of Custom Indexes

Published 2016-11-21 by Kevin Feasel

David Klee shows the risk of creating custom indexes on third-party software:

The error file tells you specifically which indexes it does not like. The error file is found at:

C:\Users\(youraccount)\AppData\Local\Temp\vcsUpgrade\vcdb_req.err

You’ll find the error message towards the bottom of the document. My specific item was:

1 [42000](50000) [Microsoft][SQL Server Native Client 11.0][SQL Server]ERROR ! Extra indexes: VPX_EVENT.HFX_VPX_EVENT_Cover01; VPX_STAT_COUNTER.IX_VPX_STAT_COUNTER_STAT; VPX_TASK.HFX_VPX_TASK_Cover01;

For well-maintained third-party vendor software which doesn’t require you to add indexes to support the product at any scale beyond what a developer needs for basic testing, this isn’t an issue.  And if you ever find that piece of software, write the company a note of congratulations for being the first…

Comments closed

Solving Math Problems With T-SQL

Published 2016-11-21 by Kevin Feasel

Erik Darling shows the Collatz Conjecture in T-SQL:

The only problem in there that could really be written in SQL was the Collatz Conjecture. It states that when you take any number, and if it’s even you divide it by 2, and if it’s odd you multiply it by 3 and add 1, you’ll always eventually end up with 1. Fair enough. There’s probably a use for that out somewhere out there.

Writing it in SQL was super easy, of course. Just throw a CASE expression at it.

I think Erik is a little harsh on SQL as a mathematical language.  Yeah, there are other languages I’d go to first for many types of math problems (R, F#, and Python being examples), but SQL can solve quite a few mathematical problems.

Comments closed