The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

There is a lesson for all of us in the recent Public Relations disaster for United Airlines, a lesson about Customer Service, and being so focused on the rules that we forget what the rules are there for.

Even if you've heard about what happened, you will probably have missed a couple of salient facts. Firstly, this is not a story about over-booking. The over-booking issue was resolved before anyone got on the plane. Secondly, whatever you may think of United Airlines, they're not directly responsible for how that poor man was taken off the plane. Whatever the rights or wrongs, when law enforcement shows up and asks you to comply, you can either comply or be "assisted" off the plane. The real issue began when four crew members showed up who needed to get to another location for a flight the next day. United immediately decided that they would exercise their legal right to inconvenience four customers already on the plane, and ask them to leave. When no one volunteered, United "randomly" picked four customers and told them they had to leave the plane, no choices. Three of them got up and walked off. The fourth, well, he's now famous.

Everything they did, as their CEO explained, was completely lawful, and 'by the book' according their company regulations. However, when the 'by the book' approach results in calling the law, because someone outside your organization doesn't understand your book, then you have failed utterly.

There is a difference between the law and our belief about what is right and proper. Most of us believe, incorrectly, that if we paid for the flight and are in the seat, we get to fly. Even if the law and the company regulations says otherwise, any service-oriented organization must make what's in the book, and why, clear to its customers in a way that doesn't involve threatening them with law enforcement. If that approach fails then they need to find another person to yield their seat, or provide some other added incentive. Playing it by the book isn't an adaptive approach.

Do we, as DBAs ever resort to 'playing it by the book', rather than find a solution that represents a happy compromise, and provides a service? How else have we become stuck with the old, but frighteningly accurate joke about DBAs: "What's a DBAs favorite word?" NO!

Are you that person? When developers ask about adopting MongoDB for certain applications, or for help setting up a CI process, is your stock answer to say "no", and to quote corporate policy at them? How about when a corporate policy is set that all VMs, regardless of use or intent, will have a 4 GB max memory allocation, even though SQL Server 2014's minimum memory allocation is, ta-da, 4 GB. That 300 GB database will be fine operating in 4gb of memory, right?

Sometimes, 'the book' gives you the right to say no. However, the fourth or fifth time you say no, aren't people going to start to bypass you? If I were a developer, I sure would. When you call law enforcement (management) down on them, maybe you get management's backing and maybe you don't, but do the developers any longer consider you a partner? In other words, do your customers believe that you are serving their interests?

We can become so focused on the rules that we forget that the rules are there to establish how we provide a service, to enable things to happen. Let's be the organization enablers, not the people who do things rigidly 'by the book'.

Grant Fritchey (guest editor)

Grant Fritchey 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.

Webinars

Extending DevOps Practices to SQL Server Databases - In this free demo webinar, Grant Fritchey and Arneh Eskandari will show how Redgate tools enable you to push and pull database changes in Git, then set up an automated database build and deployment process using TeamCity and Octopus Deploy....(more)

Virtualization

Impact of CPU Hot Add on NUMA scheduling - CPU Hot-Add is not compatible with vNUMA. If hot-add is enabled, the virtual NUMA topology is not exposed to the guest OS and this may impact application performance....(more)

T-SQL

SQL Server 2017: Unpublished Gems - Erik Darling uncovers some new stuff in SQL Server that they don't mention in the release notes....(more)

Table Column Differences with T-SQL and PowerShell - We've identified the tables with similar structure, but what about if we want to know which column names match exactly?...(more)

Is it ever worth adding indexes to table variables? - Erik Darling discovers that regular ol’ join simplification can help the optimizer 'optimize away' a table variable operator....(more)

Introducing Batch Mode Adaptive Joins - SQL Server 2017 and Azure SQL Database introduce a new set of adaptive query processing improvements, such as batch mode memory grant feedback, batch mode adaptive joins, and interleaved execution, to help fix performance issues that are due to inaccurate cardinality estimates. In this post, we’ll introduce batch mode adaptive joins....(more)

What are different ways to replace ISNULL() in a WHERE clause that uses only literal values? - If you have a query where ISNULL() is used in the WHERE clause to replace NULL values with a canary value for comparison to a predicate, what are ways to rewrite it to make it more efficient, and perhaps even SARGable?...(more)

SQLskills SQL101: Using DDL Triggers - Glenn Berry explains why he thinks it a shame that many people actually use Data Definition Language (DDL) triggers on their systems....(more)

Find Your Dark Queries - Most SQL Server monitoring solution relies heavily on the statistics of cached queries, but some queries will fall out of cache or don’t ever make it into cache. Those are the dark queries I’m interested in today....(more)

Ordered Set Functions: What's New--and Missing--in SQL Server vNext - SQL Server makes the first big step in supporting ordered set functions by introducing the long-awaited STRING_AGG function, which concatenates strings in a group based on optional desired order....(more)

SQL Server vNext: Interleaved Execution for mTVF - Investigating interleaved execution for Multistatement Table-valued Functions, which takes a part of a query that might be executed independently, executes it, and then reuses the result and the result cardinality to recompile and execute the rest of the query....(more)

How to Calculate Multiple Aggregate Functions in a Single Query - There are several ways to calculate multiple counts in a single query, but which is fastest?...(more)

Tech News : General Interest

Mandatory ISP data retention and the law of unintended consequences - Well, good one Australia, UK and whoever else has embarked on this hare-brained scheme, you've just made things a whole lot worse....(more)

Here's how Steve Ballmer is building a huge US government data repository - Former Microsoft CEO Steve Ballmer officially has launched a beta of his latest venture: A US government data repository known as USAFacts....(more)

SQL Server News

SQLCLR in SQL Server 2017 - Microsoft is recommending enabling “CLR Strict Security” in SQL Server 2017, which means users cannot create any CLR assemblies unless they have elevated permissions. This could be a breaking change anywhere you want to use dynamic CLR. ...(more)

Delivering AI with data: the next generation of Microsoft’s data platform - Joseph Sirosh announces the close integration of AI functions into databases, data lakes, and the cloud to simplify the deployment of intelligent applications. ...(more)

Graph Data Processing with SQL Server 2017 - Graph extensions are fully integrated in the SQL Server 2017 engine. This article introduces two new types of tables in SQL Server 2017 databases, Node and edge tables, as well as the new MATCH clause for pattern matching and navigation. ...(more)

SQL Server 2017 Community Technology Preview 2.0 now available - SQL Server 2017 CTP 2.0 adds a number of new capabilities, including the ability to run advanced analytics using Python in a parallelized and highly scalable way, the ability to store and analyze graph data, the Adaptive Query Processing family of intelligent database features and resumable online indexing....(more)

Software Development

The Age of Data and Software Development - Data drives, or should drive, all our decisions. Whether we’re deciding how high to set the cost threshold for parallelism, which query we want to tune, or even which product would serve us best, we should be making these decisions based on data. ...(more)

Replication

Transactional Replication and Stored Procedure Execution: Silver Bullet or Poison Pill? - Transactional Replication, by design, is prone to latency. It’s not a synchronous operation, like with database mirroring. The speed at which the distribution agent can enact each change at your subscriber(s) is a critical factor....(more)

R Language

Normal approximation to binomial distribution using T-SQL and R - Use of the binomial formula to calculate probabilities of events occurring in certain situations....(more)

Saving input and output with sp_execute_external_script using temporal table and file table (part #2) - G getting or capturing R code, once it gets to Launchpad, is tricky. So storing R code it in a database table or on file system seems a better idea....(more)

PowerShell

Creating Active Directory User Accounts with ADSI and PowerShell - The Active Directory module from RSAT is the way to go but sometimes you may want a bit more control. You also may have a need to roll your own tools. Today, I want to give you some ideas on how to create user accounts using LDAP and ADSI....(more)

Install-module DBATools - Thomas La Rock explains why, if you are just getting started in SQL Server administration, and want an easy way to learn some PowerShell, then dbatools.io is the place for you....(more)

Why VS Code Increases my Productivity - I have a Markdown document, a PowerShell script and a T-SQL script all in one Git repository and I can work on all of them and version control in one place....(more)

The PowerShell Disconnect - PowerShell continues to feel awkward and unintuitive for Dave Mason. Is prior development experience a hindrance to its adoption??...(more)

PowerPivot/PowerQuery/PowerBI

Power BI and Data Security - Row Level Security - Row level security is the ability to filter content based on a users role. There are two primary ways to implement row level security in Power BI – through Power BI or using SSAS. ...(more)

Using and optimizing DirectQuery in PowerBI and SSAS Tabular - Marco Russo explains the right and wrong reasons to use DirectQuery....(more)

SSISDB Reporting with Power BI - The SSISDB system database tracks many different metrics regarding the SSIS environment. With the popularity of Power BI Desktop, it becomes very easy to report and understand the Integration Services environment holistically without needing to spend large sprint cycles to implement....(more)

The DAX Unichar() Function And How To Use It In Measures For Data Visualisation - The DAX Unichar() function, which returns the unicode character associated with an integer value; so UNICHAR(65) returns the character A. You can have a lot of fun with this function in Power BI when you use it to return symbols that in turn can be used to represent data...(more)

Power BI, SSAS Multidimensional And Dynamic Format Strings - If you need to display formatted values in Power BI you will have to create a calculated measure that returns the value of your original measure, set the format string property on that calculated measure appropriately, and use that calculated measure in your Power BI reports....(more)

Performance Tuning SQL Server

When should I add an Index? - Kenneth Fisher offers his general rules of thumb for when and when not to add indexes....(more)

It’s not you, it’s me (I/O troubleshooting) - Before you go and spend a ton of money on new hardware, you should always examine your workload for unnecessary I/O....(more)

SQL Server Performance Baselining Reports Unleashed for Enterprise Monitoring - Parikshit Savjani describes which SQL Server metrics we need to collect to establish a performance baseline for a SQL Server instance, and then how to set up baseline reports....(more)

Index Maintenance and Performance (video) - They made their index maintenance job smarter, and their queries got slower in production afterward. Could the index maintenance have harmed performance? ...(more)

Are Bad Statistics Making My Query Slow? (video) - An important query is suddenly slow. Is it because statistics are out of date? This is tricky to figure out, and updating statistics right away can make troubleshooting even harder. Learn how to use query execution plans to get to the heart of the question and find out if stats are really your problem, or if it’s something else....(more)

HA/DR/Always On/Clustering

Troubleshooting High HADR_SYNC_COMMIT wait type with Always On Availability Groups - If transactions in the primary replica are slower than usual, and HADR_SYNC_COMMIT is unusually long, it means there is some performance issue in at least one Primary-Secondary replica data movement flow, or at least one secondary replica is slow in log hardening....(more)

Data Access / ORMs

SQL Server 2017 to add Python support - SQL Server 2017 will add Python as a supported language. Just as with the continued R support, SQL Server 2017 will allow you to process data in the database using any Python function or package without needing to export the data from the database....(more)

Computing in the Cloud (Azure, Google , AWS)

Blob Auditing for Azure SQL Database - While auditing features were available before in Azure, Blob auditing is a huge leap forward, especially in having more granular control over what audit records are captured....(more)

Career Growth

Wanna speak at the PASS Summit? Here’s how - The PASS Summit is the biggest SQL Server community event, and a lot of us speakers have “present at PASS” on their bucket list. it means getting in is legendarily difficult because competition is fierce. Brent Ozar shares what he's learned over the years about getting sessions accepted....(more)

How to Join the SQL Server Community Chat in Slack - Brent Ozar brings to our attention the Chat rooms at SQLcommunity.Slack.com....(more)

Bugs/Patches for SQL Server

Cumulative Update #5 for SQL Server 2014 SP2 - The 5th cumulative update release for SQL Server 2014 SP2 is now available for download at the Microsoft Downloads site....(more)

SQL Server 2014 SP2 Cumulative Update 5 - Microsoft has released SQL Server 2014 Service Pack 2 Cumulative Update 5, which is Build 12.0.5546.0. There are 24 hotfixes in the public fix list....(more)

Administration of SQL Server

SQLskills SQL101: Restoring to an earlier version - Is it possible to attach or restore a database to an earlier version of SQL Server? The very simple answer is: No. Paul Randal explains why....(more)

Will ‘Optimize for Adhoc Workloads’ Save Memory? - It might, depending on how many single-use execution plans are in memory after your instance has been up and running for a while. However, a better approach might be to monitor the number of single use plans in cache, and investigate and act accordingly if that number starts spiking upwards....(more)

Trace Flag 4199: No Per-Session Override if You Enable it Globally - If you enable optimization trace flag 4199 globally for all sessions, can you then disable it per-session? Kendra Little explains that that’s NOT how it works....(more)

SQL Agent and the hairiest Dateadd in town - An Agent job is scheduled every minute and takes pretty much exactly 55 seconds. On SQL 2014 it runs every minute, but on SQL 2016, every 2 minutes. Why?...(more)