Press "Enter" to skip to content

Month: July 2019

Undocumented Commands Can Change

Published 2019-07-29 by Kevin Feasel

Thomas Rushton warns us that undocumented commands in SQL Server are liable to change without notice:

I don’t have every version available to test, but it does appear that the record indicating Containment State is mis-spelled in SQL Server 2012 as “dbi_ContianmentState”, and then corrected in SQL 2014 and later. It’s a good job I’m not relying on it for anything.

So, yes, don’t rely on undocumented functions – as they may change without notice.

If there’s a documented method, use that one. If the only method available is undocumented, you can still use it, but be sure to test it with each release—that is, major release, service pack, or cumulative update.

Comments closed

Using the Power BI Visual Header Tooltip

Published 2019-07-29 by Kevin Feasel

Prathy Kamasani gives us several good uses of the Power BI visual header tooltip:

When we look at data journalism posts, most of the times they have annotations, explaining what visual showing or talking about measures. Again most of these data storeys are used for paper. But in the digital world, we do see these annotations more interactively. It is nice to have this kind of lil annotations for everyday reporting as well, and Tooltip Icon can be used for that purpose. Another thing is using canvas space wisely, it is important, and having this kind of hint helps us on saving the canvas space.

Click through for instructions on how to enable this as well as smart ways to use them.

Comments closed

Automated Alert Emails

Published 2019-07-29 by Kevin Feasel

Max Vernon shows how you can use the SQL Server Agent to send automated e-mails on alerts:

SQL Server Agent provides a great mechanism for sending alerts via email in response to various events. Once you’ve setup Database Mail, and configured a SQL Server Agent Operator, you should add alerts for severe errors that affect the health of your SQL Server. Creating Alerts can be tedious, but automating Alerts is simple, with the easy code below that automates creating alerts in response to critical events. Automating alerts is important because it provides a standardized Alert configuration that can be used by all the SQL Servers in your organization.

Read on for the script.

Comments closed

SSMS 18.2 Available

Published 2019-07-26 by Kevin Feasel

Dinakar Nethi announces SQL Server Management Studio version 18.2:

We are excited to announce the release of SQL Server Management Studio (SSMS) 18.2. For this update, while we added some features, our focus was dedicated to fundamentals such as stability, reliability, performance, etc.

You can download SQL Server Management Studio 18.2 today.
Some of the new features in SQL Server Management Studio include:

– Intellisense/editor: Added support for data classification
– Query execution: Added a completion time in the messages to track when a given query completed its execution.
– ShowPlan: Added new attribute in query plan when the inline scalar UDF feature is enabled.

There are several bugfixes in there as well.

Comments closed

A Primer on Jenkins

Published 2019-07-26 by Kevin Feasel

Shubham Dangare gives us a quick walkthrough of setting up a Jenkins pipeline:

Jenkins Pipeline (or simply “Pipeline”) is a suite of plugins which supports implementing and integrating continuous delivery pipelines into Jenkins.

A continuous delivery pipeline is an automated expression of your process for getting the software from version control right through to your users and customers.

It provides an extensible set of tools for modeling simple-to-complex delivery pipelines “as code”. The definition of a Pipeline is typically written into a text file (called a Jenkinsfile ) which in turn is checked into a project’s source control repository

Click through for a demo.

Comments closed

Defining Intent Locks

Published 2019-07-26 by Kevin Feasel

David Fowler explains what an intent lock is and why it’s useful:

Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row.

Now what’s going to happen when another user decides to modify a bunch of rows? Now because of the number or rows involved in this modification, SQL is going to want to take out an exclusive page lock. Where’s the issue here?

Read on to learn what the issue is.

Comments closed

DAX Variables in Iterators

Published 2019-07-26 by Kevin Feasel

Kasper de Jonge explains how you can use a variable in the middle of an iterator:

As explained in the blog post the SUMX in this calculation will iterate over each row in the fact table which probably will have multiple currencies with different values for each date. The Min(FactExchangeRate[Factor]) will be evaluated for each currency and date and get the right value.

Now for those of you who have seen any of my sessions will ask why am I not using a variable as I always tell everyone to do so.

Click through for the answer and an example of where you can use a variable within an iterator.

Comments closed

Suspect Pages in msdb

Published 2019-07-26 by Kevin Feasel

Max Vernon explains what the suspect_pages table is in msdb:

When SQL Server detects corruption in a database, it reports that corruption immediately to the client who requested the data. But did you know SQL Server also stores the details about which pages have experienced corruption in the msdb database, in the suspect_pages table?

Read on to see the information you can get from this table, including a listing of what each event type means.

Comments closed

Testing ETL Pipelines

Published 2019-07-26 by Kevin Feasel

Ed Elliott has started a new series on testing ETL pipelines:

We test in production, this means we have monitoring and do things like have phased roll-outs using feature flags, or we roll-out to select customers first, prove it then roll it out to everyone else. Testing in production doesn’t mean hacking around getting some process to work. We don’t test “on production” (hacking), we test “in production” – while we are in production we are continually testing, and if anything goes wrong, we have alerts and can deal with it.

Testing pipelines feels difficult because there are so many moving pieces, but if you design for testability (e.g., being able to tee off samples of data, send test records through, etc.), things get easier.

Comments closed