The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

With each recent version of SQL Server, Microsoft has added features that can be used to improve query performance with much less effort than traditional index and query tuning require. First was Query Store, introduced with 2016. When enabled, this feature allows you to easily find regression in query performance due to changing execution plans. You then can force a better performing plan for a particular query. In 2017, you can enable this feature to automatically use a better performing plan when one exists.

Along with forcing execution plans, another 2017 feature is called Adaptive Query Processing.  Adaptive Query Processing changes how queries are optimized in certain situations. (I was lucky enough to watch Bob Ward demonstrate this feature at SQLBits a few months ago and would really love for one of my customers to upgrade so I can see this feature in action on a production workload.)

One of the new optimizations involves multi-statement table valued functions. When I’m query tuning, the use of multi-statement table valued functions is a red flag for me. These are almost always a bad idea, especially when used in a join or cross apply, but they are often used by developers because of reusability. The biggest problem is that, before 2017, the optimizer always estimates the results of one of these functions to be exactly 100 rows. This affects the plan downstream of the function, including memory grants and join types (nested loop vs. hash mash, for example). Adaptive Query Processing solves this problem by using what Microsoft calls an ‘interleaved execution’ approach. Instead of just using the 100-row estimate, the function will be executed during optimization and then that information will be used to come up with the rest of the plan. Now, with a better row estimate, the plan will be more appropriate for the query.

Instead of multi-statement table valued functions always being quite the disaster in a query, they might perform better using this feature. I’ve seen so many situations where the function contains a while loop and conditional logic, and the optimization will not help this situation, but it is still quite an improvement over the 100 rows and done approach. As someone who has done quite a bit of query tuning over the years, I want to say to developers “Just don’t use them. No, keep using them – more query tuning work for me!”

Interleaved execution is just one of three optimizations available with Adaptive Query Processing in SQL Server 2017. The two others are ‘batch mode adaptive joins’ and ‘batch mode memory grant feedback.’ To learn more about these, take a look at this video from Joe Sack.

Adaptive Query Processing really adds a new level of intelligence to the optimizer. I can’t wait to see what else Microsoft has in store!       

Kathi Kellenberger 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

An introduction to distributed SQL Server monitoring - Join this upcoming webinar to discover the basics of distributed monitoring, and how it can help you keep on top of your entire SQL Server estate, regardless of how complex it becomes....(more)

Vendors/3rd Party Products

Monitoring Distributed SQL Servers using SQL Monitor - Many organizations are experiencing rapid expansion and diversification of their SQL Server estate to include Cloud, VMWare and other platforms, alongside traditional on-premise servers. This article explains the basic architecture and components of SQL Monitor, and then how to set up a distributed monitoring solution. ...(more)

The State of SQL Server Monitoring 2018 - Over 600 technology professionals who work in organizations that use SQL Server recently responded to our survey to discover the current state of SQL Server monitoring....(more)

T-SQL

Handy TSQL for Azure SQL Database - I do not always use the Azure portal to make database changes or to check for certain information. I use it a lot of for blogging purposes but for some tasks I rather just run code via SSMS – SQL Server Management Studio. Like what? Creating a database Very easy to create something like the below – a S2 database....(more)

Dates and Times in SQL Server: More functions you should never use - Previously we looked at four built-in functions to get the current date and time in SQL Server and Azure SQL Database using Transact-SQL (T-SQL). We identified that out of the options provided, SYSUTCDATETIME() is the recommended method because it relies on Coordinated Universal Time (UTC), and uses the DATETIME2 data type which has a much higher accuracy than the DATETIME data type....(more)

Choosing Between Table Variables and Temporary Tables - People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that both have their uses, and it’s easy to find examples where either one is quicker. In this article, Phil Factor explains the main factors involved in choosing one or the other, and demonstrates a few simple ‘rules’ to get the best performance....(more)

SQL Server Security

SQL Server Database Security and Source Control - Security in SQL Server, at its core, is a pretty simple topic (not in actually configuring and getting it right, but the mechanics of how security works.) There are server and database principals, and you can grant, take away, and deny rights to these principals to do pretty much anything at the server and database level. In this blog, I will take it as expected that you have a solid understanding of such things....(more)

SQL Server on Linux

SQL Server on Linux: What You Really Need to Know - SQL Server on Linux removes the barrier to entry for organizations that prefer Linux to Windows. The buzz about Microsoft bringing SQL Server to Linux has been building since the initial announcement, back in spring 2016. It seems you can't throw a CPU without hitting a blog post about SQL Server on Linux, as everybody scrambles to be the SQL Server on Linux expert. Here’s what you really need to know. ...(more)

Security news and thoughts

Intel Documentation Blamed for Multiple Operating System Security Flaw - A security flaw affecting four families of operating systems as well as other software seems to be the result of misunderstood Intel documentation. Anybody who's been involved with tech for a while has most likely come across the expression "RTFM" on more than one occasion. Usually delivered with a degree of snark, if not downright hostility, the initialism stands for "read the manual," with an added expletive added for good measure. ...(more)

PowerPivot/PowerQuery/PowerBI

Power BI – Part 1: Introduction - Power BI has been available for a number of years, but new functionality is added regularly. In this new series from Robert Sheldon, you will learn about the latest developments or get started with Power BI if you are new to it. Robert provides an overview of Power BI, the services and tools available in this article. ...(more)

Create Custom Visual with R and JSON- Part 4 - In the last Post, I have explained some parts of creating Custom visual such as how to create an icon, name of visual, and the how-to allocates fields to a custom visual. There are some other settings that need to be set up beforehand as well. the number ......(more)

Incremental Refresh with Power BI Premium - In this video, Christian Wade joined Adam Saxton to discuss Incremental Refresh with Power BI Premium. You can use Incremental Refresh with Power BI Premium to take your dataset beyond 1GB and avoid failures such as timeouts. Incremental Refresh with ......(more)

Revisiting Dynamic Web.Contents() - Thus far in my series of posts using hockey data to explore the subject of data analytics in Power BI (which began all the way back here), I have been focused on “using” the data, and was generally refreshing data whenever I opened the PBIX file. I didn’t ......(more)

Microsoft News : General Interest

Microsoft Is Said to Plan Low-Cost Tablet Line to Rival IPad - Microsoft Corp. is planning to release a line of lower-cost Surface tablets as soon as the second half of 2018, seeking a hit in a market for cheaper devices that Apple Inc. dominates with the iPad. ...(more)

Microsoft News

Microsoft Wins Lucrative Cloud Deal With Intelligence Community - The cloud agreement gives Microsoft more power to make its case to the Pentagon as goes up against competitors like International Business Machines Corp., Oracle Corp. and Amazon for the agency’s winner-take-all cloud computing contract for up to 10 ......(more)

Microsoft News : Security

Microsoft Makes Bid for IoT Devices with Linux and CASB - Microsoft Azure Sphere may provide a way for enterprises to ensure IoT device security deployments through long life cycles. The Microsoft Azure Sphere ecosystem comes with a control plane that manages IoT endpoint security, app download, authentication and attestation. The IoT app data flow can be controlled from on-premises and/or cloud-based communications, and doesn’t require data flow routing through Azure Sphere. ...(more)

MDX/DAX

Fun with DAX – Compressing Numeric Sequences to Text - A recent post on the Power BI community website asked if it was possible to compress a group of numbers into text that described the sequential ranges contained within the numbers. This might be a group of values such as 1, 2, 3, 4, 7, 8, 9, 12, 13: (note there are gaps) with the expected result grouping the numbers that run in a sequence together to produce text like “1-4, 7-9, 12-13”. Essentially to identify gaps when creating the text. This seemed like an interesting challenge and here is how I solved it using DAX....(more)

ETL/SSIS/ELT

Managing Business Logic - Encapsulating business logic into data movement and presentation is a critical part of a stable information management strategy. Too often, though, business logic is built and added late in the process, forcing it into whatever nooks and crannies are available. While this duct-tape approach sometimes works, it makes the resulting system difficult to maintain when the business logic is spread…...(more)

DevOps and Continuous Delivery (CI/CD)

How DevOps can automate the repayment of technical debt - Looking to DevOps automation to try to create the breathing room needed to break out of the vicious cycle of technical debt....(more)

2018 State of DevOps Report - Redgate is joining forces with DevOps Research and Assessment (DORA) and Google Cloud to support the pursuit of DevOps excellence. The research aim is to better understand the DevOps landscape, and to surface new findings that provide guidance for improvement in resource management, productivity, and quality of technology delivery teams. To have your say and be part of the research, simply take the survey - there are even some great prizes up for grabs! ...(more)

Deep Into Windows

OpenSSH is now Part of Windows! - Today is a big day! The OpenSSH client version 7.6p1 is now part of the Windows 10 operating system! Microsoft released Windows 10 Update 1803 and included in that release is the OpenSSH client, which is installed as part of the update. That’s right an SSH client as part of the Windows operating system by default! Also included with this update is the OpenSSH Server which is included as an Windows Feature on Demand...(more)

DBA Tools

A new update for mssql-cli, an interactive CLI, is now available - We have released our second major update for mssql-cli since our public preview announcement in December. You can view the public preview announcement here. mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is part of the dbcli community. See the install guide to download mssql-cli and get started....(more)

Getting Started with Mssql-cli Command-Line Query Tool - A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a sign of a successful product. After testing the SQL Operations Studio, I feel that it’s a promising tool for many developers, administrators, and DevOps specialists. In my opinion, the mssql-cli tool adds another feature to SQL Server in order to make it a leading database product....(more)

Database Design, Theory and Development

Data Architects and Data Modelers: The SQL/NoSQL Debate is Dead - Karen Lopez  says that when it comes to surviving as a Data Architect, “Hybrid is the future.” It’s no longer enough to speak only one language or stay attached to one set of technologies. According to Lopez, “purely relational (SQL) databases don’t ......(more)

Data Science

Elitist shuffle for recommendation systems - In today's high pace user experience it is expected that new recommended items appear every time the user opens the application, but what to do if your recommendation system runs every hour or every day? I give a solution that you can plug & play without having to re-engineer your recommendation system....(more)

Data Privacy and GDPR

WordPress 4.9.6 GDPR Compliance Demo Videos - In Tuesday’s upcoming WordPress 4.9.6, there are a couple of new features to help with GDPR compliance. First, you can create a privacy policy page: Second, you can export a user’s data and erase it: The current schedule is for 4.9.6 to go out this ......(more)

Data Governance and Your Customers: Five Pitfalls to Avoid - Organizations increasingly understand the importance of Data Governance, or the method of control that ensures data entered into a system meets a precise standard. In fact, the topic has been addressed extensively ......(more)

Conferences, Classes, and Events

SQL in the City Streamed – Compliant DevOps: June 20 2018 - Join Redgate’s livestream on June 20 and learn from Microsoft Data Platform MVPs, Steve Jones, Grant Fritchey and Kathi Kellenberger. The schedule will include sessions to help you learn how to demonstrate compliance with regulations such as the GDPR, HIPAA and SOX. Find out more and register for free...(more)

Computing in the Cloud (Azure, Google , AWS)

When is good enough, good enough? - Currently the project I’m working on is using Azure’s Infrastructure as a Service (IaaS) and we want to move to using Azure SQL Database on Platform as a Service (PaaS). The one thing stopping us doing that was cross database scripting, known in Azure terms as an Elastic Query. Before we agreed the move to PaaS I wrote a prototype elastic query which worked, if we need it, the capability is there....(more)

Big Data

Apache Hadoop 3.1: A Giant Leap for Big Data - Apache Hadoop 3.1, building on Apache Hadoop 3.0, is the core enabling big data technology as we march into the fourth industrial revolution. Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community. ...(more)

Azure SQL Database

Creating Azure SQL Managed Instance using ARM templates - Azure API enables you to create Azure SQL Managed Instance using ARM templates. These are JSON objects that contain definition of resources that should be created. You can send these objects to the Azure REST API to automate creation of Azure SQL Managed Instance. In order to create a new Azure SQL Managed Instance, you need to create ARM JSON request. An example of ARM JSON request is shown in the following script (the important part is under resources node):...(more)

AI/Machine Learning/Cognitive Services

Google, Microsoft Use AI to Tackle Corporate Email Inboxes - Google and Microsoft are embracing the idea that task management relies on communication, a lot of our task-based communication takes place in corporate email inboxes -- so why not use a workplace communication tool to complete those tasks? ...(more)

As Workloads Change, ‘Scale Up’ Augments ‘Scale Out’ in Cloud Data Centers - Machine learning and big data analytics are making hyper-scale data centers less and less homogeneous. Public cloud requires huge capital expenditure, not just for the massive scale of millions of servers in data centers, but also because those servers need to be replaced regularly as workloads become more demanding. ...(more)

How is Cognitive Computing Set to Reshape What We Call Computing? - The tech world is buzzing with the words like Machine Learning and Artificial Intelligence. But there is a new kid on the block— Cognitive Computing. Artificial Intelligence or AI is an umbrella term for ......(more)

Administration of SQL Server

dbachecks – Which Configuration Item For Which Check ? - I love showing dbachecks to people. It’s really cool seeing how people will use it and listening to their experiences. I was showing it to a production DBA a month or so ago and he said How Do I Know Which Checks There Are? OK you just need to runGet-DbcCheckand ......(more)

Do you need to update statistics after an upgrade? - There are a variety of methods we use for helping customers ... tl;dr Yes. Update statistics after an upgrade. Further, if you’re upgrading to 2012 or higher from an earlier version, you should rebuild your indexes (which will update index statistics, so then you just need to update column statistics)....(more)