The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This week's editorial is a guest post from Phil Factor.

Occasionally, I blink with amazement at some inscrutable code I’ve written and wonder what I’d consumed before writing it. Some beverages make you think you’re writing good code when it is lousy, whilst others make you write good code but forget to document it. In both cases, me-in-the-present roundly curses me-in-the-past. I’ve never found a stimulant such as coffee that makes you write good code as well as document it.

It helps, when picking up the pieces, to remember what type of tea or coffee you drank before an exacting programming task and drink the same stuff before you try to understand it. Beware, though: It depends. Sometimes, you need a different perspective on your code from the giddy moment of inspiration. If you write code under the influence of stimulants, you need to test it without, or vice versa. One old programmer advised me, a long time ago, “Never test code sober if you wrote it whilst sober”. The converse is, he suggested, also advisable.

Although, in an emergency, strong coffee is useful, we working programmers, whatever fancy names we give ourselves, only prosper by finding the means to deliver satisfactory code whatever our mood or location. Whether we are holed up in our quiet study at home, crouched on a plastic chair in an open-plan office whilst trying to ignore the ambient prattling, squatting on the floor of the server room, or surrounded by anxious chatterboxes on a site visit, we still have to deliver consistently reliable code: maybe not of our best. Perhaps that is the real value of the professional programmer. We leave the flashes of brilliance to the amateurs and academics: we just deliver consistent and reliable output whatever the background noise or discomfort. We all have our coping strategies to achieve this, but I’ve long since abandoned the technique of drinking too much tea, coffee or Jack Daniels.

Phil Factor from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Vendors/3rd Party Products

Redgate DLM Demo Webinar - Watch the recording of our latest DLM Demo webinar and learn how to improve your database change management process. See how Redgate DLM plugs into Subversion, TeamCity & Octopus Deploy so that you can automate your database changes alongside your application code....(more)

Database DevOps Research - Use SQL Server? Like to win $250 Amazon vouchers? Then you might be interested in filling out Redgate’s quick database DevOps survey. It should only five to ten minutes, and you'll also receive a copy of the research report once it's been completed....(more)

Tech News : General Interest

Big News In Databases - SQL Server on Linux, Parallel execution in PostgreSQL, and more. Markus Winand summarizes the really BIG news for databases, in 2016....(more)

Tech News : The Lighter Side

The Dangers of TechnoMarketingBabble - Everyone’s encouraged to be a content producer these days. It’s not marketing, it’s content! Where will it all lead? I, for one, start to worry that technical people will begin to take marketing content seriously....(more)

R Language

Extracting Tables from PDFs in R using the Tabulizer Package - How to extract a table from a pdf file in order to work with the table in R....(more)

PowerPivot/PowerQuery/PowerBI

Control the Interaction in Power BI Report - In this post you will learn how easy and useful is controlling the interaction between Power BI visual elements....(more)

Polybase/HDInsight

Reading Polybase Execution Plan Details - The Remote Query in a Polybase execution plan often contains interesting details....(more)

Running MapReduce Polybase Queries - What happens 'behind the scenes' when we run queries that perform MapReduce operations....(more)

Running Basic Polybase Queries On Hadoop - Defining “basic” queries as any query which does not generate MapReduce jobs, what happens is that all of the data is streamed over the wire to our SQL Server instance and stored in a temp table. ...(more)

Performance Tuning SQL Server

Fishing for wait types in WinDbg - Ewald Cress offers a crude and simple way to hunt for areas of SQL Server that may use a particular wait type. The only prerequisite is that you need to be willing and able to attach Windbg to SQL Server, and that you have public symbols loaded....(more)

In-Memory logging, much cooler than disk based - Discussing an important facet of why the In-Memory engine can outperform and scale better than the traditional engine, namely transaction log usage....(more)

Capacity Planning Using Performance Data - Capacity planning isn’t just figuring out how much disk space we need, it also involves the resources that a SQL Server instance must have available to handle the workload....(more)

Spills SQL Server Doesn’t Warn You About - Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one’s sure what they’re doing, but they’ve been taking forever to do it. One particular thing that irks me about them (table variables, now) is that they’ll spill their spaghetti all over your disks, and not warn you....(more)

How to Store Performance Counters Directly on SQL Server using TYPEPERF command - Without a proper baseline, it’s hard to say when and why the problem occurred. You also lose the ability to look over a period and analyze if a problem is really a problem, or if it is a normal environment behavior....(more)

How to Tune Indexes for a Stored Procedure - You’ve got an important stored procedure that you think needs index help– but it runs in environment with lots of other queries. How do you focus in and discover exactly what indexes need tuning for that procedure?...(more)

The DMV diaries: Worker, task, request and session state - Whizzing through the various ways in which the status of a running piece of work is exposed to us in sys.dm_os_workers, sys.dm_os_tasks, sys.dm_exec_requests, and sys.dm_exec_sessions....(more)

Database Clone - DBCC CLONEDATABASE creates a new database with the full schema plus all the statistics and meta data, but no data. Grant Fritchey explains why this might be useful for performance tuning....(more)

Determining What Happens When You Force an Execution Plan via the Query Store - When you force a query plan via the Query Store, you will need to track what happens: Sometimes the request to force a plan will fail, and you will want to know when and why. There are several ways of getting feedback, ranging from the built-in reports to using extended events. Enrico explains the details....(more)

NOSQL

The Dawn of NewSQL: Bridging the Gap Between Traditional RDBMS and NoSQL - NewSQL databases combine the distributed architectures typically seen in NoSQL systems with a multi-node concurrency scheme and unique storage mechanisms to maintain Atomicity, Consistency, Isolation, and Durability (ACID) guarantees....(more)

Microsoft News : General Interest

SQL Updates Newsletter – November 2016 - November's releases, whitepapers, tips, scripts and announcements from Microsoft....(more)

Events to attend

7 Things I Learned About Aurora at AWS re:Invent 2016 - Brent Ozar's takeaways from the conference, with regard Amazon Aurora, their homegrown relational database with MySQL compatibility....(more)

Announcing Group By: A New Kind of Free Community Conference - Let’s try something new. Let’s build a free event by the community, for the community, where Speakers submit abstracts publicly online, attendees suggest tweaks to the abstracts, the 5-7 top-voted sessions are picked. Brent Ozar explains how it will work....(more)

SQL in the City – it’s happening virtually, so mark your calendar as ‘busy’ on December 14/15 - Now only two weeks away, the sessions and speakers for SQL in the City Streamed have been both announced and confirmed. Find out the details of Redgate’s first ever free virtual conference in this blog post from Events Manager Annabel Bradford....(more)

Computing in the Cloud

I - Azure Functions take care of all the hosting, all the retry logic, all the parallelisation, all the authentication gubbins, all the monitoring for you. The only bits of code you really have to write is the important stuff – the code that implements the business process. ...(more)

How to Build Your First SQL Server Virtual Lab in Windows Azure - If you are a DBA who hasn't so far dived in head-first into using Azure, it is worth setting up an Azure 'Virtual Lab' environment the easy way, using a template. This will then allow you to experiment, try things out with SQL Azure, and get familiar with Resource Groups. Joshua shows how to build a virtual lab, from the ground up in the first of a series that aims to give you a grounding in Azure....(more)

Blogs : Developer Tools

Using SQL Tools with SQL Server on Linux - A new mssql extension for Visual Studio Code, SQL command line tools for Linux, New versions of SSMS, SSDT and SQL PowerShell with support for the SQL Server v.Next on Windows and Linux....(more)

Blogs : DevOps

Planning for Successful Data Management - It is the data, in particular, that sets Database Lifecycle Management apart from the mainstream of application delivery. Data entities, and the way that organizations understand and deal with them, have their own lifespan. If we neglect the management of data, we risk disaster for the organizations that use it. If we take data management seriously, databases become a lot easier....(more)

Blogs : DMO/SMO/Powershell

Lists With, or Without, Ranges in both T-SQL and PowerShell - Whether you are working in a procedural language like PowerShell or in T-SQL, there is something slightly bothersome about having to deal with parameters that are lists, or worse with ranges amongst the values. In fact, once you have a way of dealing with them, they can be convenient, especially when bridging the gulf between application and the database. Phil Factor shows how to deal with them....(more)

PowerShell – Working with Python and SQL Server - Windows Admin has the advantage with PowerShell as the Linux Admin has the upper hand with Bash/PHP/Python. Here’s where we can collaborate and work with each other....(more)

Blogs : Hardware

Building an Easy Button: SQL Server and Windows 10 IoT - Is it possible to wire up some sort of circuit or device that could interface with SQL Server? The short answer is: yes, you can. My project was ambitious as it was simple: create a button that, when pressed, backs up all of your databases on a given instance of SQL Server. And I’m going to show you how, one step at a time....(more)

Intel Xeon E7 Processor Generational Performance Comparison - Glenn Berry sums up and explains a recent Intel document describing the “performance” increases seen with the AsiaInfo ADB from moving from 2.8GHz Intel Xeon E7-4890 v2 (Ivy Bridge-EX), to 2.5GHz Intel Xeon E7-8890 v3 (Haswell-EX), and finally to 2.2GHz Intel Xeon E7-8890 v4 (Broadwell-EX) processors....(more)

Blogs : Reporting Services

Rotating Tables with Indicators in SSRS 2016 - When creating SSRS (SQL Server Reporting Services) dashboards, I like to use indicators to give clear and colorful data visualizations. In this post I am going to cover the use of indicators and demonstrate how to rotate your tables so that you can display the indicators horizontally....(more)

Blogs : T-SQL

Query Tuning 101: Debugging a procedure - I was recently asked to help tune a stored procedure that has been historically taking between 55 and 60 seconds to complete. Overall the code wasn’t too complex but getting to the root cause did surprise me a bit....(more)

Using OUTPUT with a Field List - Using OUTPUT to insert the outputed data into a table with an IDENTITY column. ...(more)

Date Math In The WHERE Clause - Erik Darling thinks that too many of us are still formatting our WHERE clauses poorly, still putting expressions around columns and comparing that output to a value, or another expression....(more)

Have you tried sp_ctrl3? - Daniel Hutmacher designed the sp_ctrl3 procedure to install on any dev environment that you use regularly. It provides a detailed overview of a specific database object definition, as well as copy-paste-friendly T-SQL code....(more)

Analysis Services / BI on the MS Stack

Reconciling Data Across Systems Using a Reconciliation Hub - In many enterprises, where there are a number of separate systems engaged in processing data, there arises the daunting task of checking and reconciling data as it flows between systems. Discrepancies in data must be detected, tracked and corrected as quickly as possible: there is no room for error in doing this. A Reconciliation Hub can provide the answer, as Rahul Gupta explains....(more)

Which Database Servers are hit from my Analysis Services Instance(s) - How to get a quick overview of Analysis Services (SSAS) to Relational Database (RDBMS) connections....(more)

Administration of SQL Server

SQL Server Event Handling: Event Notifications - Using Event Notifications to handle events asynchronously. It's more complex to handle events this way than it is with DDL triggers. Event Notifications require some additional objects, and related T-SQL scripts will generally be longer. Hopefully the example shown here will be usable as a framework to help you get started....(more)

Signs Your SQL Server is Running with Scissors (Dear SQL DBA Episode 24) - Does your team know what it’s doing with SQL Server? Learn what a consultant looks for when assessing a team, and signs that SQL Server may be badly configured....(more)

SQL Server & Containers – Part Three - Having created a custom docker image, this post explains how to push that custom image up to the Docker repository....(more)

The Future of the DBA - These days a DBA is no longer a car mechanic...we know all about how the engines work but we aren’t allowed to touch anything. We used to do backups and preventative maintenance, remember those days? Well, with SQL Server Managed Backup and Azure Query Performance Insight we aren’t needed for those things anymore....(more)

First steps with SQL Server on Linux - Klaus Aschenbrenner crosses the bridge, and describes his first ever SQL Server on Linux installation....(more)