Bypass SSDT Publish

Ed Elliott has just added another SSDT Dev Pack feature:

Open a document with a table valued function or procedure, click tools–> SSDT Dev Pack –> QuickDeploy or use the keyboard options in SSDT to make Tools.QuickDeploy to a key combination (I use Ctrl+Q, Ctrl+D but it is a little awkward) and your code will be deployed, any messages are added to the standard output window.

If the active document has anything other than stored procedures or table valued functions then they will not be deployed and also if you have more than one procedure of function, all of them in the active document will be deployed.

Ed is quickly becoming one of my favorite bloggers.  It seems like every other day, he has a new tool available for us.

JSON Leads To New Wave Of 1NF Failures

Jovan Popovic talks about storing JSON in SQL Server:

Instead of single JSON object you can organize your data in this “collection”. If you do not want to explicitly check structure of each JSON column, you don’t need to add JSON check constraint on every column (in this example I have added CHECK constraint only on EmailAddresses column).

If you compare this structure to the standard NoSQL collection, you might notice that you will have faster access to strongly typed data (FirstName and LastName). Therefore, this solution is good choice for hybrid models where you can identify some information that are repeated across all objects, and other variable information can be stored as JSON. This way, you can combine flexibility and performance.

Okay, we’ve hit my first major problem with JSON support:  rampant violation of first normal form.  You can create check constraints on JSON code, and that’s pretty snazzy I guess, but I know a better way to store relational data in a relational database system.  JSON support is great when you ask SQL Server to be a holder of text blobs, but this is begging for bad design decisions.

Highlight Expensive Queries

Ed Elliott has another tool in his SSDT DevPack:

When you enable the query cost for a document (I map it to the keys ctrl+k, ctrl+q) what the tool does is connect to a SQL Server instance and run the stored procedure using “SET SHOWPLAN_XML ON” so it isn’t actually executed but the estimated query plan is returned and the cost of each statement checked to see how high it is.

By default high statements must have a cost over 1.0 to be marked as high and anything over 0.2 is marked as a warning – you can override these with this in your “%UsersProfile%\SSDTDevPack\config.xml” :

You can quibble with the cost values but this is a really cool feature.

Storage Spaces Direct

Glenn Berry introduces us to Storage Spaces Direct:

One of the more exciting new features in Windows Server 2016 is called Storage Spaces Direct (S2D), which enables organizations to use multiple, clustered commodity file server nodes to build highly available, scalable storage systems with local storage, using SATA, SAS, or PCIe NVMe devices. You can use internal drives in each storage node, or direct-attached disk devices using “Just a Bunch of Disks” (JBOD) where each JBOD is only connected to a single storage node. This eliminates the previous requirement for a shared SAS fabric and its complexities (which was required with Windows Server 2012 R2 Storage Spaces and SOFS), and also enables using less expensive storage devices such as SATA disks.

This sounds like a technology with interesting potential, and not requiring SAS disks will make it more likely to be adopted.

DAX Performance

Bill Anton reminds me that I don’t know a thing about DAX:

As you can see, using DAX variables is a much better solution than using the aliases…the performance improvement is about the same, however, variables we can wrapped up in the calculated measures inside the model allowing us to take advantage of the performance gain with all tools (not just those allowing us to hand-craft the DAX queries).

The query used in this post is too simple to highlight the performance benefit (small data dataset, simple calculation)…but it did make it easier to cruise the query plans and SE requests. In reality, a better use case for highlighting the performance benefits of these optimizations is with a query that hammers the Formula Engine (FE).

Interesting stuff, even for someone with no knowledge of DAX.

BIML 101

Andy Leonard has a recording of his introductory session on BIML  He also has a series of links and resources.

If you use Integration Services at all, ever, then you want to learn BIML.  BIML:SSIS::Powershell:Administration.

SSRS Niceties

Koen Verbeeck talks about the Reporting Services overhaul:

And these are all awesome changes. But sometimes it’s the little things that make me smile.
For example, I was toying with SSRS 2016 CTP3.0 and I wanted to insert a new chart. Suddenly I noticed they had updated the little chart icons in the dialog:

Here’s hoping the final product ends up being what we all wanted back in 2008; if so, that’d be pretty awesome.

Telemetry XEvent

Kendra Little is sleuthing:

There’s something quite odd about this session. It has no target! The data isn’t being written to memory in the ring buffer or to a file or even a counter.

So I did a little testing. I right clicked the session and selected ‘Watch Live Data’ to see if I could consume the data flowing through in SQL Server Management studio even though it didn’t have a target. And then I ran this in another session:

It looks like this extended event is designed to track serious error messages.  I’m liking it and hope it sticks around in RTM.

Checking Object Compression

Richie Lee has a quick script to check which objects are compressed.  Given that I ended up needing to use this script within a day of his posting it (hey, I’m as lazy as anybody else…), I figured it was worth linking.

Data Science With Microsoft

Jan Mulkens has started an interesting series on data science using the Microsoft stack.  His first post is an overview of the products currently available:

But on a more serious note, I’m going to be crude to Microsoft here.
A long time ago, Power BI started as an over-hyped and underwhelming experience. Everyone saw the potential this Excel stuff had but I’m guessing the experience most people had was similar to mine. That is, Power BI back then was a disappointment because of what we were expecting.
The one good thing it did have at one point was PowerPivot.

Skip forward to august 2015.
The Power BI dream had suddenly come true!
Most of the things we were expecting in the past suddenly were there, in a web service AND a desktop application.
AMAZING!

From there, Mulkens shares a number of training materials:

Make Microsoft’s Virtual Academy your first or last stop when learning, but you should always pay it a visit!
It’s filled with incredible information broken down in some great free courses.

It seems that (at least some of) the closed edX.org courses are being placed on here, so you can follow up on them at your own pace.
Do be aware that you can’t receive certificates on Microsoft Virtual Academy.

This is an exciting time to jump into analytics.  Most of the material is free, and it’s easy to get VMs to practice, so the barrier to entry is low.

Categories

June 2017
MTWTFSS
« May  
 1234
567891011
12131415161718
19202122232425
2627282930