The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Occasionally, I have the DBA-equivalent of the tidy-nest instinct, the urge to remove clutter from my servers. Whenever I've installed SQL Server over recent years, for example, my subconscious mind signals to my right index finger to de-select the checkbox that opts in to sending SQL Server error reports to Microsoft. Their "Customer Experience Improvement Program (CEIP)" wants this data in order to understand what features we use and how, and so improve them. A laudable goal, but I'm a DBA who likes a SQL Server that focuses all of its attention on my user databases and processes, rather than spending valuable time gathering and sending data about my usage of them.

During a recent SQL Server 2016 installation, however, I couldn't find the familiar checkbox. Hmm, I mused, maybe all of that previous telemetry had taught Microsoft hat 98.9% of DBAs opt out of sending this data, so they gave up on the idea of collecting it!

Whether or not that 98.9% statistic is true (I made it up), it turns out that CEIP is far from gone. I was deep into a failover cluster installation when I noticed a "cluster resource" that was failing to start. I blinked a few times in disbelief; this "resource", sitting right there in my new Windows 2016 SQL cluster, turned out to be "SQL Server CEIP". It was failing to start because of a dependency on SQL Server itself, which in turn could not start because of a misconfigured TempDB location. As soon as I fixed SQL Server, CEIP fired right up and presumably began phoning home its telemetry data.

Later, during a spot of query tuning, I noticed, in the Query Store charts, a lot of "internal" queries cluttering up the usual list of user database queries. They looked suspiciously like extended events queries. Sure enough, when I opened up SSMS object explorer, there was a brand new built-in session called telemetry_xevents. While reviewing my wait statistics, I saw a lot more of these types of queries, all related, as it turns out, to CEIP.

I love the sound of telemetry, mainly because it reminds me of the movie Apollo 13, but what was all this doing here on my SQL Server 2016 server? I felt sure that I hadn't missed the opt-in checkbox, even if it was lurking in some different part of the installation. No, this telemetric data gathering is now on by default. For Standard and Enterprise Editions, you can turn it off by subsequently setting a registry-based policy. For Developer, Express and Evaluation Editions, you can't turn it off.

This gave me pause for thought. I like the way that Microsoft has gifted us, in Standard Edition of SQL Server 2016 SP1, many Enterprise Edition features. Was this decision the result of analyzing the data from CEIP? After all, much of the telemetry_xevents session is collecting data about these features. Could this data also have helped Microsoft understand the need, for example, to bring Power BI to SSRS on premises? If so, then I am grateful, and for now at least will try forget about it chugging along in the background, telemetrically sending its data to the space cloud.

Cheers,

Rodney Landrum (guest editor)

Rodney Landrum 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

Is your SQL Server remote? - If the answer is yes, this survey is for you. Redgate is looking at building solutions to the issues associated with remote SQL Servers. They want to know your pain points, and whether there's anything they can do to assist. If you think you could help the team out, please fill in their survey....(more)

T-SQL

Improve performance by replacing temp tables with memory optimized tables - Part 1 - There are many cases where you will be using temp tables, and many of us are trying to seek for performance improvement. Fortunately with SQL 2016, we now have a way to do it via memory optimized tables!...(more)

One way to get an index seek for a leading %wildcard - With a leading wildcard, a predicate is "non-SARGable" – just a fancy way of saying we can't find the relevant rows by using a seek. But what if SQL Server somehow knew about all of the possible portions of a string? The basic concept is that the engine has the ability to do point-style lookups on substrings, meaning you don't have to scan the entire table and parse every full value....(more)

How Joins Work - The SQL join operation is one of the most powerful and commonly used SQL operations, but little attention is paid to how the internal SQL engine breaks down the tasks of join operations. This post will explore the common algorithms that databases use to compute them, including nested loop, hash, and merge joins. ...(more)

Which Filegroup is that Partition Using? How Many Rows Does It Have? - Table Partitioning in SQL Server has a bit of a learning curve. It’s tricky to just figure out how much data you have and where the data is stored...(more)

Tech News : Disaster Recovery

SQL Server DBA’s Guide to the Gitlab Outage - There are so many amazing lessons to learn from this outage: transparency, accountability, processes, checklists, you name it....(more)

GitLab suffers major backup failure after data deletion incident - GitLab data was deleted accidentally followed by an apparent inability to restore the data from backups....(more)

Tech News : General Interest

HTTPS adoption has reached the tipping point - That's it - I'm calling it - HTTPS adoption has now reached the moment of critical mass where it's gathering enough momentum that it will very shortly become "the norm" rather than the exception ...(more)

SQL Server Security and Auditing

How safe is your data from theft? - Thomas La Rock offers five things you can be doing today to help secure your data and prevent your company from being named in a lengthy lawsuit....(more)

SQL Server on Linux

Installing latest PowerShell Core 6.0 Release on Linux just got easier! - Microsoft achieves its goal of enabling PowerShell installation through popular existing Linux package management tools like apt-get and yum....(more)

SQL Server News

SQL Updates Newsletter – January 2017 - January's round up of SQL Server news, releases, announcements issues, alerts, whitepapers and more. ...(more)

Software Development

Metaprogramming - The metaprogrammer was the designer, decision maker, and communication controller in a software development team. There is much evidence to suggest that this was an efficient method of organizing programmers to write software, but it’s a long way from the current Agile belief in consensus and discussion....(more)

Reporting Services

Keeping KPI Reports up to Date - KPI reports show cached data only. When the data changes, the KPI report will continue to show the same information unless you configure a cache refresh plan on the dataset. Follow these instructions so that the KPI data will refresh on a scheduled basis....(more)

Replication

Upgrading a Replication Topology to SQL Server 2016 - Amit Banerjee offers some guidelines for upgrading SQL Server replication environments to SQL Server 2016, and a support matrix for the major release versions for transactional and merge replication....(more)

PowerShell

PowerShell Time Saver: Automatic Defaults - PowerShell needs to be usable as an immediate scripting language by IT professionals who type in commands at a console, so there have to be language devices such as aliases that can make for terseness when appropriate....(more)

Query Plan Analysis with PowerShell - Trying to tune a large SQL batch process that had a loop, Mike Fal needs to capture all the executions within the loop and sum all the logical reads across a single batch execution....(more)

PowerPivot/PowerQuery/PowerBI

Introducing SUMMARIZECOLUMNS - This article explains how to use SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance....(more)

Performance Tuning SQL Server

Forced Plans and Compatibility Mode - Imagine you upgraded to SQL Server 2016 and kept the compatibility mode for your database at 110 to use the legacy Cardinality Estimator. At some point, you have a plan that you force for a specific query, and that works great. As time goes on, you do testing with the new CE and eventually are ready to make the switch to compatibility mode 130. When you do that, does the forced plan continue to use compatibility mode 110?...(more)

Parallel Execution on SQL Server 2016 - Joe Chang addresses some important questions in parallel execution: scaling versus degree of parallelism (DOP) on physical cores, Hyper-Threading, Compression and Columnstore....(more)

Optimize For Ad Hoc Workloads – Enable or not? - Mike Walsh explains the how and why” behind the setting. ...(more)

HA/DR/Always On/Clustering

Availability Group on SQL Server 2016 - A client with a single standalone SQL Server 2014 instance (Enterprise Edition) running on Windows Server 2012 R2, wants to make sure that if anything happens to the server, the website can immediately continue to operate as usual....(more)

Introduction to the Availability Group Dynamic Management Objects in SQL Server 2016 - Tim Ford introduces the Dynamic Management Objects (DMOs) useful in assessing the architecture and state of your AGs....(more)

DBA Tools

GitLab for SQL Server Projects - This post demonstrates the use of GitLab to build SQL Server projects. I use ReadyRoll for my SQL Server projects but SSDT projects could also be built with GitLab. ...(more)

Database Design, Theory and Development

Index Types: Heaps, Primary Keys, Clustered and Nonclustered Indexes - Kendra Little clarifies the concepts begin clustered and non-clustered indexes and Primary Keys....(more)

Outsmarting the DBMS: Analysts Should Beware - Attribute overloading, having one attribute represent multiple facts, is advocated in the name of flexibility, but the result is just the opposite....(more)

Data Access / ORMs

How to Check Database Availability from the Application Tier - A robust synthetic transaction to check database availability must include both a read and a write. To ensure that the storage subsystem is available, the write must not be cached, and must be written through to storage....(more)

Working with the BigInt Type in Node and SQL Server - Node.JS and SQL Server are a good match for creating non-blocking, event-driven database applications...but it is possible to get things wrong in converting SQL Server datatypes such as BigInt to native Javascript data....(more)

Computing in the Cloud

Playing with Azure, File Snapshot backups - I see File-snapshot backups like SAN snapshot backups, so they happen immediately and won’t take much space as they are connected to the original files, but let’s compare them with regular backups to see if that is true....(more)

Columnstore Indexes

ColumnStore Segment Elimination - A ColumnStore Index is internally subdivided into ColumnStore Segments. SQL Server stores a Minimum and Maximum Value internally for each segment so that it can perform Segment Elimination and only read those Segments that contain requested data. ...(more)

JSON data in clustered column store indexes - Clustered column store indexes (CCI) in SQL Server vNext and Azure SQL Database support LOB types like NVARCHAR(MAX), which allows you to store string with any size, including JSON documents with any size. In this post we will see one experiment that compares row-store and column store formats used to store JSON collections....(more)

Career Growth

What Makes a Good Online Presentation? - Pinal Dave and Adam Machanic: two very different presentation styles but both examples of presenters who have found their own voice, and are perfectly comfortable delivering sessions in their own voice....(more)

Analysis Services / BI on the MS Stack

Finding Out Which MDX Calculations Are Being Evaluated By Your Query In Analysis Services Multidimensional, Part 1 - Chris Webb aims to show how you can use a couple fo trace events to find out which MDX calculations are being evaluated when you run a query, which is of course going to be useful if you are trying to tune that query....(more)

Administration of SQL Server

APS Blocked Partition Switch - How to automatically kill any sessions that have been running over 5 minutes that are blocking a partition swap....(more)

Warning: Not a valid checkpoint file name - SQL Soldier investigates a series of error messages flooding the log files while a full backup process was running. ...(more)

Torn Pages and using DBCC PAGE - Uh oh: SQL Server detected a logical consistency-based I/O error: torn page ......(more)

Memory Grants and Data Size - In general, not having enough memory means reading pages from disk all the time, but it can have RAMifications down the line (GET IT?!), like queries not having enough memory to compile or run, and your plan cache constantly being wiped out....(more)

Solving SQL Connectivity issues: A new guided walk through just got published - A one stop shop for solving majority of connectivity issues that you may run into when working with SQL Server....(more)

Pesky Percent File Growth - When I have to support a third party application that automatically adds data files using percent instead of fixed size, it really irritates me. I got tired of seeing these new files show up on my daily exceptions report, so I decided to do something about it. This post explains what I did....(more)

What are LOGMGR_RESERVE_APPEND waits? - If you ever see these waits, look for databases using the simple recovery mode where the log is set to have zero or very tiny autogrowth....(more)

The five deadly sins of data management - As data professionals, we are often to blame for enabling our business end users to lust after BIG DATA, resulting in data hoarding leading to ROT (Redundant, Outdated, Trivial information). Don't just blindly collect: define objectives, build a recovery plan, define an archiving plan, think about security....(more)