Press "Enter" to skip to content

Author: Kevin Feasel

Row Width And Snapshot Isolation

Published 2018-05-29 by Kevin Feasel

Kendra Little shows us the impact that row width has on snapshot isolation:

So I went to work to demonstrate row width impact on the version store — when only a tiny bit column is changed in the row.

Here’s how I did the test:

  • I created two tables, dbo.Narrow and dbo.Wide. They each each have a bit column named bitsy, along with some other columns.
  • I inserted one row in each table, but I put a lot more data into the row in dbo.Wide.
  • I allowed snapshot isolation on the database
  • I began a transaction in another session under snapshot isolation and left the transaction open (so version store cleanup wouldn’t kick in while I looked around)
  • I updated the bit column named bitsy for the single row in each table, thereby generating a row-version in tempdb for each table

The code I ran to test this is here, if you’d like to play around with it.

Read on for the results.

Comments closed

Tic-Tac-Toe In T-SQL

Published 2018-05-29 by Kevin Feasel

Riley Major implements Tic-Tac-Toe in T-SQL:

It turns out there’s a concept called bitmasking which can work a lot like this cardboard cut-out process. (Props to Dylan Beattie for his quick visual demonstration at NDC Minnesota which drove this point home.) First, you represent your game state with a bunch of bits (“OXOOOXXXX” yields “0100011110” for our example above, remembering that we’re padding that last 0 just to make the powers 1-based instead of 0-based) and then you represent your winning state with a bunch of bits (“0000001110” for our example winning state here). Now you use the magic of “bitwise math” to compare the two.

For our use, we want to find out whether our mask exposes the winning three bits. We want to block everything else out. With bits, to check if both items are true, you use “AND” (0 and 0 is 0; 0 and 1 is 0; 1 and 1 is 1). If we apply that “AND” concept to each bit in our game, it will squash out any values which don’t match. If what we have left matches the mask (fills in all of the space we can see through), then we have a match and a win.

The twist in all of this is that the end result doesn’t quite work as expected, but it was interesting watching the process.  That said, there’s a good reason why we don’t use T-SQL as a primary language for development…

Comments closed

Why You Should Read Gartner Critical Capabilities Reports

Published 2018-05-29 by Kevin Feasel

Jen Underwood explains the value behind Gartner Critical Capabilities reports, specifically the one for analytics and BI platforms:

Notably, the three Magic Quadrant Leaders except Tableau were ranked near the middle in all use cases. MicroStrategy, Birst, Sisense, TIBCO, YellowFin, Salesforce, SAS and a few other players excelled above the rest with high scores on this report. These results are a bit refreshing to see. Gartner Critical Capabilities scores seem to better align with Forrester’s rankings of Analytics and Business Intelligence Platforms and also my own understanding of several top offerings. I admit that I was surprised by these results. I was rarely – if ever – asked about several of the top scoring vendors over the past three years.

Read the whole thing, and then read the report.

Comments closed

Azure Data Factory V2 Pricing

Published 2018-05-29 by Kevin Feasel

Chris Seferlis gives us the details on how Azure Data Factory V2 pricing works:

2. Volume of data moved – this is measured in DMUs (data movement units). This is one you should be aware of as this will default to auto, which is basically using all the DMUs it can use and this is paid for by the hour. Let’s say you specify and use 2 DMUs and it takes an hour to move that data. The other option is you could use 8 DMUs and it takes 15 minutes, this price is going to end up the same. You’re using 4X the DMUs but it’s happening in a quarter of the time.

This is good to look at and do some comparisons since how many DMUs you’re using is where the bulk of your spend if going to be.

There are a few moving parts here, so the calculation is not trivial.  But Chris makes good sense of it all.

Comments closed

Power BI Color Palattes

Published 2018-05-29 by Kevin Feasel

Meagan Longoria helps us choose a color palette for Power BI reports:

A color palette is simply a collection of colors applied to the visual elements in your report. What we typically refer to as color is a combination of three main properties: hue (base color on the color wheel), intensity (brightness or gray-ness) and value (lightness or darkness). You can build an engaging and professional looking report with just 6 colors. It’s possible to have fewer colors or more colors, but 6 should cover many common visualization needs. If you are using more than 6 colors, you might want to check that you are optimizing engagement and cognitive load.

  1. Main color – default color on graphs

  2. Color 2 – used when multiple colors are needed in a graph or report

  3. Color 3 – used when multiple colors are needed in a graph or report and Color 2 has already been used

  4. Highlight color – a color used to highlight important data points to make them stand out from other points on the page

  5. Border color – a light color used for borders on tables and KPIs where necessary

  6. Title color – color used for visual titles and axis labels as appropriate

There’s a lot of good advice in here.

Comments closed

Finding Procedure Parameters Which Don’t Match Column Names

Published 2018-05-29 by Kevin Feasel

Shane O’Neill has a process to update procedures to make input parameter names match output column names:

I was asked to standardise stored procedures we use for common support cases before we hand them over to IT Helpdesk.

One of the comments that came back from the Helpdesk while testing was that the parameter names that they had to put values in for didn’t match what they saw in the application.

Luckily for me (or unluckily) the application was a third party developed one and they didn’t bother renaming the columns. So if the column is called create_date in the database then the application is going to show create_date.

However, if I created a parameter called DateCreated or even CreateDate, then they don’t want it.

Shane has a Powershell script which uses the Find-DbaStoredProcedure method in dbatools; click through to see it in action.

Comments closed

Probabilities And Poker

Published 2018-05-25 by Kevin Feasel

Steve Miller has a notebook on 5-card draw probabilities:

The population of 5 card draw hands, consisting of 52 choose 5 or 2598960 elements, is pretty straightforward both mathematically and statistically.

So of course ever the geek, I just had to attempt to show her how probability and statistics converge. In addition to explaining the “combinatorics” of the counts and probabilities, I undertook two computational exercises. The first was to delineate all possible combinations of 5 card draws from a 52 card deck, counting occurrences of relevant combinations such as 2 pair, a straight, or nothing in a cell loop.

Steve has made his notebook available for us.

Comments closed

Picking An Azure SQL Database Tier

Published 2018-05-25 by Kevin Feasel

Esat Erkec has various methods you can use to figure out your Azure SQL Database tier:

When we are beginning to think of migrating our on-premises databases to Azure SQL, we have to decide on a proper purchase model, a service tier, and a performance level. Before starting the Azure SQL migration process, we have to find logical and provable answers to the following questions:

  • Which purchase model is suitable for my apps and business requirements?
  • How much budget do I need?
  • Which performance level meets my requirements?
  • Can I achieve the acceptable performance of my apps?

The purchase model and service tiers will certainly affect the Azure bills. On the other hand, we have to achieve the maximum performance with the selected service tier.

It’s a good article with helpful tips for people thinking of moving to Azure SQL Database.

Comments closed

Gartner’s Cloud IaaS Magic Quadrant Changes

Published 2018-05-25 by Kevin Feasel

Bruno Aziza analyzes Gartner’s Magic Quadrant for Cloud Infrastructure as a Service offerings:

The first and most drastic change that occurred over the last year is the number of players that Gartner decided to highlight in its report: the number of vendors went from 14 to just 6 this year.  

Why is that?! Have the big become bigger and the small smaller?! Or has the space shrunk?   The latter is highly improbable.  All the contrary: earlier last year, forecasted that the highest growth in the cloud market would be coming from the sector this MQ covers: Gartner predicted that the cloud system infrastructure services would grow over 36% to reach $34B+ in 2017.

So, what gives?!

Read on to learn what gives.  As far as the rankings themselves go, I think it’s reasonable:  AWS and Azure can generally go head-to-head on features though Amazon does have the advantage.  Google is a distant third and the rest aren’t major players.

Comments closed