Press "Enter" to skip to content

Curated SQL Posts

Automatically Updating dbatools

Published 2018-05-07 by Kevin Feasel

Garry Bargsley gives us two ways to update dbatools on a schedule:

I have been using dbatools heavily since I was introduced to it.  I have automated processes and created new processes with it.  There are new commands that come out almost daily that fill in certain gaps or enhance current commands.  One way to stay current with these updates is to update your dbatools install frequently.

How better to do this than to have an auto update process that will run daily and get the latest dbatools version for you…

I have put together two ways of doing this based on your preferred method.  One is via a SQL Agent Job and the other is using a Windows Task Scheduler job.

Read on for examples of both techniques.

Comments closed

Changing Docker Named Volume Locations

Published 2018-05-03 by Kevin Feasel

Andrew Pruski answers an attendee question:

A few weeks ago I was presenting at SQL Saturday Raleigh and was asked a question that I didn’t know the answer to.

The question was, “can you change the location of named volumes in docker?”

This is one of the things that I love about presenting, being asked questions that I don’t know the answer to. They give me something to go away and investigate (many thanks to Dave Walden (b|t) for his help!)

Read on for Andrew’s answer.

Comments closed

Microsoft R Open 3.4.4

Published 2018-05-03 by Kevin Feasel

David Smith announces Microsoft R Open 3.4.4:

An update to Microsoft R Open (MRO) is now available for download on Windows, Mac and Linux. This release upgrades the R language engine to version 3.4.4, which addresses some minor issues with timezone detection and some edge cases in some statistics functions. As a maintenance release, it’s backwards-compatible with scripts and packages from the prior release of MRO.

MRO 3.4.4 points to a fixed CRAN snapshot taken on April 1 2018, and you can see some highlights of new packages released since the prior version of MRO on the Spotlights page. As always, you can use the built-in checkpoint package to access packages from an earlier date (for reproducibility) or a later date (to access new and updated packages).

David also spills the beans on when we’ll see MRO 3.5.0.

Comments closed

Let’s Not Talk About Timestamp

Published 2018-05-03 by Kevin Feasel

Randolph West hits us with a misnamed SQL Server data type:

It occurred to me that we haven’t covered the TIMESTAMP data type in this series about dates and times.

TIMESTAMP is the Windows Millennium Edition of data types. It has nothing to do with date and time. It’s a row version. Microsoft asks that we stop calling it TIMESTAMP and use ROWVERSION instead.

Much like DECIMAL is a synonym of NUMERIC, so too is TIMESTAMP a synonym of ROWVERSION. Please call it a ROWVERSION and pretend that TIMESTAMP doesn’t exist. Microsoft is deeply sorry for the confusion.

As I say, dates and times are hard.  But at least this is easy:  if you don’t use it, you won’t have problems with it.

Comments closed

Methods For Capturing Cardinality Estimate Statistics

Published 2018-05-03 by Kevin Feasel

Monica Rathbun gives us five methods for finding cardinality estimate values when running a SQL Server query:

A second option is to use statistics profiling. This was introduced in SQL Server 2014 and is easily set by using SET STATISTICS PROFILE ON orenable query profiling globally using DBCC TRACEON (7412, -1). This trace flag is only available in SQL Server 2016 SP1 and above.  Selecting from the dynamic management view (DMV) Sys.dm_exec_query_profiles you can do real time query execution progress monitoring while the query is running.  This option will return estimated and actual rows by operator.

Click through for the full set of methods.

Comments closed

Sign Those Stored Procedures

Published 2018-05-03 by Kevin Feasel

David Fowler shows how we can sign stored procedures to give users limited rights that they otherwise should not have:

One way that we can do this is by signing the procedure (you can also use this with functions and triggers) with a certificate or an asymmetric key.

In this post I’m only going to look into signing a stored procedure with a certificate but the same ideas can be applied for the other objects and with an asymmetric key.

So…

We’re going to create a certificate and sign our stored proc using that certificate.  We’ll then create a user based on the certificate and grant the new certificate user the appropriate permissions to run the stored procedure.

Every SQL Server DBA should know how to do this, but in my experience, it’s a small percentage who do.

Comments closed

Azure SQL Data Warehouse Generation 2

Published 2018-05-03 by Kevin Feasel

James Serra announces changes to Azure SQL Data Warehouse:

The changes in Azure SQL DW Compute Optimized Gen2 tier are:

  • 5x query performance via a adaptive caching technology. which takes a blended approach of using remote storage in combination with a fast SSD cache layer (using NVMes) that places data next to compute based on user access patterns and frequency

  • Significant improvement in serving concurrent queries (32 to 128 queries/cluster)

  • Removes limits on columnar data volume to enable unlimited columnar data volume

  • 5 times higher computing power compared to the current generation by leveraging the latest hardware innovations that Azure offers via additional Service Level Objectives (DW7500c, DW10000c, DW15000c and DW30000c)

  • Added Transparent Data Encryption with customer-managed keys

Those are some good improvements.  #2 in particular makes it possible for Azure SQL DW to be useful in a much larger number of environments.

Comments closed

Inside SQL Server 6.5

Published 2018-05-03 by Kevin Feasel

Brent Ozar reviews a blast from the past:

I picked up half a dozen used books about SQL Server 6.5, then spent a delightful weekend reading them. Seriously delightful – lemme tell you just how into it I was. Erika and I eat all weekend meals out at restaurants, but she saw me so happily curled up in my chair reading that she insisted on going out and getting tacos for us just so I wouldn’t have to get up. I was having that good of a time READING BOOKS ABOUT SQL SERVER 6.5. (Also, Erika is amazing. Moving on.)

To bring you that same fun, I wanna share with you a few pages from Inside SQL Server 6.5 by Ron Soukup, one of the fathers of SQL Server

It’s a great read.  My contribution to the Old But Good oeuvre is the Handbook of Relational Database Design by Candace Fleming and Barbar von Halle.  For my money, it has what I still consider the best primer on database normalization out there.  It also has a bunch of stuff that we should be glad we don’t do anymore, like figuring out specific file layouts for non-clustered indexes to minimize the number of disk rotations needed to retrieve a record of data.

Comments closed

Toward Interpretable Machine Learning

Published 2018-05-02 by Kevin Feasel

Cristoph Molnar shows off a couple of R packages which help interpret ML models:

Machine learning models repeatedly outperform interpretable, parametric models like the linear regression model. The gains in performance have a price: The models operate as black boxes which are not interpretable.

Fortunately, there are many methods that can make machine learning models interpretable. The R package imlprovides tools for analysing any black box machine learning model:

  • Feature importance: Which were the most important features?
  • Feature effects: How does a feature influence the prediction? (Partial dependence plots and individual conditional expectation curves)
  • Explanations for single predictions: How did the feature values of a single data point affect its prediction? (LIME and Shapley value)
  • Surrogate trees: Can we approximate the underlying black box model with a short decision tree?
  • The iml package works for any classification and regression machine learning model: random forests, linear models, neural networks, xgboost, etc.

This is a must-read if you’re getting into model-building. H/T R-Bloggers

Comments closed

Using map And flatMap In Scala

Published 2018-05-02 by Kevin Feasel

Shubham Verma explains the map and flatMap functions in Scala:

Consider two sets, A = {-2, -1, 0, 1, 2} and B = {0.5, 1, 1.5, 2.5, 4, 4.5, 5, 5.5} and a function          f: A => B

y = x ^ 2 + 0.5;  x is an element from set A and y corresponds to an element from set B, now we see that function f is applied to every element of set A but the result could be a subset of set B also.

So from the above text, we can draw the analogy that sets A and B can be seen as any collection in programming paradigm. Now what is “f”, so “f” could be seen as a function that takes an element from A and returns an element that exists in B, the point here to note is that, as scala promotes immutability whenever we apply map (or any other transformer) on some collection of type A, it returns a new collection of the same type with elements of type B. It would be helpful to understand it from the snippet below.

val result: List[B] = List[A].map(f: A => B)

So when a map operation is applied on a collection (here a List) of type A, with passing f as its argument it applies that function to every element of List of type A returns a new collection (again a List) of type B.

Read the whole thing.

Comments closed