The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Increasingly, as database people, we are being asked to squeeze business insights out of survey data. Are surveys that easy? Heck, no. Are they accurate? It depends. Generally, you'd be better off staring at tea-leaves if you want to predict behaviour, attitudes and opinions.

Surveys are an aspect of scientific method that look easy, in much the same way that dressing up in a white coat, looking thoughtful, asking people to say 'Ah', and writing prescriptions illegibly, seems like something that anyone could do.

Surveys aim to draw general conclusions about the entire population from just a sample of it. This is perfectly legitimate, but to do this, each person or thing must both be selected randomly and be an accurate representation of the whole population. The better you do it, the lower the sampling error, but however hard you try, a sample selected randomly from a population will almost never be exactly the same as the entire population.

Even those who are trained in psychometrics and statistics blanch in terror at the prospect of conducting an accurate survey, because they are so easy to get wrong. Surveys also suffer from bias, caused by the way the sampling was done or the measurements taken. There are so many ways of unintentionally manipulating information from surveys that the unwary can get very badly misled by them.

Even if you are engaged in biological research or doing estimation, the gathering of data needs care and training. If it is about human behaviour or opinions, there is a minefield to get through. The idea that anything can be gained from randomly contacting people and asking questions, or even worse, trying to get them to fill in online forms, is wrong. Leave it to the Sunday magazines, along with horoscopes and fashion tips.

In a sense, a skewed sample distribution tells you more than no sample at all. For example, a survey conducted on Twitter will at least tell you whether people who use Twitter a lot like or dislike a policy or product. However, it would be foolish to extrapolate that those findings to make predictions about the general population, which is likely to contain many clusters of contrary opinion.

There are magic tricks we can try in order to 'clean' the data. We can combine several surveys, average them up, and apply formulas to correct consistent bias. In science, there is no way to heal bad data, and your colleagues will pillory you if you attempt it.

So, in the two buttons below, click 'yes' if you have faith in unscientific surveys, and 'no' if you don't. Yeah, just kidding.

Phil Factor.

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.

Webinars

Microsoft’s Agile Transformation Story - Learn how Microsoft adopted DevOps at scale in our free webinar on June 12th. Donovan Brown, Senior DevOps Program Manager in Microsoft’s US Developer Division, will share how Microsoft embarked on its own DevOps journey reducing a three-year release cycle down to three weeks. Sign up now....(more)

Virtualization and Containers

Increased SOS_SCHEDULER_YIELD waits on virtual machines - Paul Randall explains why, if a VM is frequently prevented from running for a few milliseconds or more, it could produce a set of wait statistics for a virtualized workload that appears to have lots of SOS_SCHEDULER_YIELDs, when in fact it’s actually a VM performance problem and the SOS_SCHEDULER_YIELD waits are really ‘fake’....(more)

Hyper-V: Getting Your Host And Guest Connected - How does one get SSMS connectivity between a Hyper-V host and guest?...(more)

Vendors/3rd Party Products

SQL Data Mask: masking configurations and reports - SQL Data Mask is the latest prototype to come out of the Foundry, Redgate’s research and development division. It copies your database while anonymizing personal data, and you can use it to mask your databases right now, free of charge. This blog post from Developer Santiago Arias explains what's new in the latest update....(more)

Have production-level PostgreSQL experience? - Redgate are doing some early stage research around PostgreSQL, and would really like to hear about your experiences. If you think you can help, check out their short survey here....(more)

How to implement a SQL formatting standard using SQL Prompt v8 - With ten programmers working on the same project, how do you agree on a standard style for formatting SQL code, and then implement it consistently? One way is through draconian rules, meeting after meeting, and the occasional sacrifice of a programmer to put the fear in those who remain. Another way is using SQL Prompt, Louis Davidson shows how....(more)

T-SQL

Reverse It - SQL Server has a REVERSE function that reverses any string value, but where would you use it? John Morehouse shows an example of using it to get file names for data or log files of a database...(more)

The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects - Technical debt is a real problem in database development, where corners have been cut in the rush to keep to dates. The result may work but the problems are in the details: such things as inconsistent naming of objects, or of defining columns; sloppy use of data types, archaic syntax or obsolete system functions. With databases, technical debt is even harder to pay back. Robert Sheldon explains how and why you can get it right first time instead....(more)

Tech News : Disaster Recovery

To Fly, To Serve, To Fry Your Servers - So, the story goes that an Ops engineer walked into a data center with the necessary pass, a cheery wave and a ‘good morning’. Shortly afterwards, he made history. Tony Davis reflects on the recent British Airways disaster....(more)

R Language

There is usually more than one way in R - In R (especially once you add many packages) there is usually more than one way to do anything. As an example we will talk about the common R functions: str(), head(), and the tibble package‘s glimpse()....(more)

PowerShell

The PowerShell hash table - Hash tables are an excellent way to store like key/value pairs for just about any kind of data. They are pretty easy to manipulate and provide a structured way of handling information in code....(more)

Anatomy of a PowerShell Advanced Function - PowerShell advanced functions provide modularity in automating system administration tasks. Advanced functions allow administrators to create reusable code snippets that can look and behave just like the in-box PowerShell cmdlets. In this article, I will show you how to structure an advanced function to transform your scripts and functions into reusable tools....(more)

PowerPivot/PowerQuery/PowerBI

Optimising Power BI data models - In-memory tabular models compress the underlying data to unique values during the load. This is important to understand and key to taking full advantage of this trick....(more)

Wrangling GotoWebinar Stats with Power Query: Part one - Eugene Meidinger needs to clean some attendance data for an online conference session using Power Query....(more)

Filtering Errors in PowerBI - just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive an expression error. Let’s build an example filtering errors in PowerBI....(more)

Create Power BI reports in Power BI Report Server - In this video, Adam walks through the steps to create your Power BI Report in order to publish it to Power BI Report Server. This involves downloading and installing Power BI Desktop optimized for Power BI Report Server and connecting to Analysis Services live connections....(more)

Performance Tuning SQL Server

SQL Server Monitoring: Creating your own metrics - The one and only important factor in monitoring is data. How much of x and how little of y? If you don’t know these values then you’re shooting in the dark. Microsoft has done a great job adding critical data points over the years; but, what happens when the data you need doesn’t exist? You create your own custom perfmon counter!...(more)

What is plan regression in SQL Server? - Plan regression happens when SQL Server starts using the sub-optimal SQL plan to execute some T-SQL query. Usually you will see that some T-SQL query is executing really fast, but then it gets slower without any obvious reason. In this post you will see how can plan regression happen....(more)

When does “physical reads” include read-ahead reads in SQL Server? - When you’re changing between different diagnostic tools in SQL Server, be aware that some of these tools include read-ahead reads in physical reads, and some don’t! Kendra Little explains....(more)

Top 5 Misleading SQL Server Performance Counters - Perfmon counters are an excellent tool for monitoring and sometimes troubleshooting Microsoft SQL Server. But some counters can get you into trouble, because they don’t mean what many people think. Learn what to look out for in the world of widely-used perfmon counters....(more)

NoSQL

SQL Graph, part I - Niko kicks-off a series dedicated to graph databases and engines, focused on the SQL Graph, that is the newest extension to the SQL Server engine,...(more)

ETL/SSIS/ELT

Download JSON Data with SSIS - SSIS does not have a built in source component for JSON data, so you’ll have to use the Script Component. ...(more)

Developing Metadata Design Patterns in BIML - You may have already experienced ETL Hell, where you have a large number of similar of SSIS tasks, and a small change, such as an alteration to the network topology, means that they all need to be altered with the correct connection details. Perhaps you should consider creating design patterns for all the standard components of integration tasks in BIML, and generating the SSIS packages from these? Amarendra walks you through the process....(more)

Database Design, Theory and Development

Why Your Biggest Query Plans Don’t Show Up in Some DMVs - SQL Server has three ways to get execution plans from the plan cache, and there's an important problem with sys.dm_exec_query_plan....(more)

SQL 101: Parallelism Inhibitors – Scalar User Defined Functions - Developers love to use scalar user defined functions inside of SQL Server because it lets them compartmentalize code and easily reuse it, which is a common goal in object-oriented programming, but it’s also a performance anti-pattern for SQL Server as this post demonstrates....(more)

Data Mining/Data Analysis

Machine Learning's Greatest Weakness is Humans - Modeling artificial intelligence on the human brain is modeling it on a flawed model. ...(more)

Statistics in SQL: The Kruskal–Wallis Test - A lot of things in life happen almost entirely by chance. You have to make a judgement whether the report you are creating shows sheer chance or whether it is due to some real difference. If you have a test that gives you a reasonable estimate of the likelihood that it happened by chance, then that will add a great deal of confidence to your judgement....(more)

Computing in the Cloud (Azure, Google , AWS)

The Future for Database Transaction Units - Pricing for MySQL and PostgreSQL on Azure is based on a combination of Compute Units (CUs) and IOPS, but Azure SQL Database still uses DTUs. Having finally adjusted to the idea of DTUs, Thomas La Rock suspects they may be going away....(more)

Career Growth

4 DBA Resume Anti-Patterns - Brent Ozar details some common DBA resume problems....(more)

Azure SQL Database

Azure Automation Methods - What are your options for automation when working with SQL Server in Azure?...(more)

Analysis Services / BI on the MS Stack

Power BI and Regular Expressions - Plenty of languages offer support for RegEx string searching and pattern matching but Power BI Desktop doesn't currently. This article shares a quick and simple way to enable the use of RegEx in Power BI....(more)

Redundancy, Consistency, and Integrity: Derivable Data - Database redundancy can wreak havoc with interpretation of analytics results, but it also poses consistency risks that can affect the correctness of the results themselves....(more)

Power BI visualization best practices - What is a dashboard? It is, or it should be, a visual display of the most important information needed to achieve one or more objectives, consolidated and arranged in a single screen. Does this definition corresponds to a Power BI dashboard? Not necessarily......(more)

AI/Machine Learning/Cognitive Services

Tuning Your DBMS Automatically with Machine Learning - OtterTune, a new tool that’s being developed by students and researchers in the Carnegie Mellon Database Group, can automatically find good settings for a DBMS’s configuration knobs. The goal is to make it easier for anyone to deploy a DBMS, even those without any expertise in database administration....(more)

Administration of SQL Server

SQLServer Checktable OLEDB Waits Part IV - A four-part series looking at the OLEDB wait type when running DBCC CHECKTABLE. ...(more)

Backup to Nul - Why would you want to trick SQL Server into thinking a backup was taken, but where the backup file is never actually created? You wouldn't generally, but Kenneth Fisher suggest one reason might be to if you want to test your maximum potential backup speed....(more)

Database Fundamentals #1: Install SQL Server - To get started with SQL Server, you need to install it. GRant Fritchey isn't going to teach you how to do a SQL Server install. Instead, he'll point you towards a couple of sources of information, and, give you some alternatives to installing SQL Server....(more)

Upgrading to SQL Server 2016, Part One - Have you ever been asked for your thoughts on upgrading a database server? If you’re like most data professionals, your response probably included something along the lines of, “it’s difficult” and “everything breaks.”...(more)

How Do I Find A String In Any Stored Procedure? - Unsure where a specific object is referenced or where you used a specific snippet of code you may want to reference or re-use? Read this article and find out how you can search all databases on a SQL Server instance to identify a string used in creating any stored procedure without paying for expensive commercial software....(more)

Identifying failed queries with extended events - You need to know what queries are failing on your production instances and why....(more)

Endpoint Owners – Back to Basics - You may have heard the term endpoints thrown around in technical discussion and wondered “what the heck is an endpoint?” Well, that is a good question. An endpoint in the simplest form is a connection or point of entry into SQL server....(more)

Import Export Wizard Mapping Files - The Import Export Wizard in SQL Server Management Studio should be the “easy way out”, but that's not always the case, as this post shows....(more)

6 reasons to version control your database - For most application developers, it’s unthinkable to work without version control; but it can sometimes be a different story when you ask about database version control. In a way, it’s understandable as database version control was, for a long time, seen as unfeasible. But now that’s no longer the case, it’s time database development teams caught onto the benefits....(more)

Query Store and Parameterization Problems - The query store gives us a novel way of identifying those queries that are causing performance problems when they are parameterized by SQL Server for reuse. Although it is relatively simple to ensure that certain troublesome queries avoid the problem, it is laborious to identify these queries. Additionally, Query Store gives us the means to fix the problem for groups of queries by means of plan guides without changing the DDL at all. Dennes Torres explains the details....(more)

How to Automatically Create and Refresh Development and Test Databases using SQL Clone and SQL Toolbelt - In order to be able to deliver database changes more quickly, there are several tasks that must be automated. It can be a daunting job to ensure that the whole team has the latest database build when there is a proliferation of copies, and the database is big. Phil illustrates a solution by taking a set of Redgate tools to show how they can be used together, via PowerShell, to build a database from object-level source, stock it with data, document it, and then provision any number of test and development servers with the database build, taking care to save any DDL changes to the existing copies of the database....(more)

.NET Related Articles

Control the Controller in ASP.NET MVC - Because of its obvious importance in coordinating the MVC application, we tend to be wary of being too adventurous with it, but there are some interesting features that can sometimes be exploited to provide for less common requirements. The controller in ASP.NET MVC core, for example, can be a plain-old C# class (POCO), making it easier to create file-based content on the fly. The DefaultControllerFactory class can be changed, and you can override the GetControllerType method with a custom factory to give you localized routes and URLs. Dino explores some of the interesting internals of ASP.NET MVC controllers....(more)