SQL Server 2019: Batch Mode on Rowstore and Table Valued Deferred Compilation (Database Weekly 10/8/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [The single question to predict success for DevOps, and what it means for change control](#33315) - [How to achieve true DevOps by including the database](#33314) - [How to monitor hybrid and cloud-based SQL Server estates](#33313) - [An introduction to Compliant Database DevOps](#33312) T-SQL - [SQL Server 2019: Batch Mode on Rowstore](#33324) - [Dynamic SQL, NULL, and EXEC](#33309) Tools - [SSMS 18.0 public preview released](#33334) The Lighter Side - [I helped write a book! No, not THOSE books…](#33310) SQL Server on Linux - [How to Linux for SQL Server DBAs — Part 2](#33331) Security news and thoughts - [Facebook Says Security Breach Affected About 50 Million Accounts](#33308) - [When It Comes to Fantasy Football Today, Cyber Risks Could Sack Companies](#33306) - [Top 5 Ways for Analyzing Entitlements and Identifying High-Risk](#33305) Product Reviews and Articles - [Spotting Unauthorized Configuration Settings in SQL Server](#33311) PowerShell - [Checking Trace Flags with dbachecks, online docs and PSPowerHour](#33325) - [getting started with powershell](#33299) PowerPivot/PowerQuery/PowerBI - [Fun with DAX – Minesweeper](#33316) - [Power BI Cheat Sheet, Power Query in MS Flow, Home and more… (October 1, 2018)](#33298) - [Beyond the Basics: Report Design Considerations for Self-Service Users](#33297) Performance Tuning SQL Server - [The Good, the Bad and the Ugly of Table Variable Deferred Compilation Part 1](#33333) - [Extended Events Workbench](#33330) - [Adaptive Query Processing in SQL Server 2017](#33329) - [SQL Server Execution Plans, Third Edition, by Grant Fritchey](#33328) - [Plan Forcing in SQL Server](#33320) Microsoft News - [Microsoft Upgrades Surface Devices, Ups IPhone and Android Ties](#33321) - [Microsoft Further Commits to Ethical AI with New Humanitarian Program](#33303) - [Surface Hub 2 Conferencing System Set to Arrive in 2019](#33301) Internet of Things - [Can IoT Help End World Hunger? HPE and WEF Think So.](#33322) - [7 IoT Technology Highlights from Dreamforce](#33302) Database Design, Theory and Development - [Conceptual Modeling, Ontological Commitment, and the Data Model](#33319) Computing in the Cloud (Azure, Google , AWS) - [As the Need for Data Storage Rises, So Will the Use of Cloud Data Services](#33307) Career Growth - [Microsoft Fellow: Inject Social Responsibility into the Software Development Processes](#33300) Big Data - [Combining SQL + Python for an Improved Data Workflow](#33323) Azure SQL Database - [Sharding: Horizontal Scaling in Azure SQL Database](#33327) AI/Machine Learning/Cognitive Services - [Benefits of Artificial Intelligence in Healthcare: What's Possible? What's Next?](#33304) Administration of SQL Server - [Running a SQL Server Azure Container Instance in a virtual network](#33335) - [The SQL Agents’ view of SQL Server](#33326) - [How I Use a Mac but Work on SQL Server](#33318) - [What is a DBA anyway?](#33317) [Database Weekly - www.databaseweekly.com]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge SQL Server News for 2018-10-08 [Database DevOps](www.red-gate.com/dlmdemo?utm_source=dbw&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20181007-slot1) Database DevOps Demo Webinar Learn how to automate your database deployments alongside your app code in this free demo webinar. [Register now](www.red-gate.com/dlmdemo?utm_source=dbw&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20181007-slot1) [GDPR](www.red-gate.com/gdpr?utm_source=dbw&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20181007-slot2) How to make your SQL Server development GDPR ready Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. [Find out more about how our tools help with GDPR compliance](www.red-gate.com/gdpr?utm_source=dbw&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20181007-slot2) [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( Editorial - What Should You Look for in a SQL Monitoring Tool? Performance is a common reason to monitor SQL Server. The work day of a database administrator is often interrupted with unexpected calls about slowness in applications or reports. But, how does the performance today compare to the performance last week or last month? Can the root cause of the issue be traced to the database or is there something else in the environment causing the problem? Has the performance changed after a recent deployment? Performance is not the most important aspect of managing a SQL Server, but it is often what end users care most about. Slow applications can cause companies to lose customers or make workers less productive. Either way, it costs the organisation money when database performance is not optimal for critical systems. Windows Server and SQL Server have several built-in tools that can help DBAs figure out what is going on in real time. They can use extended events, perfmon, database management views (DMVs), traces, Query Store, and tools from the community such as sp_whoisactive to troubleshoot performance. If the issue continues after the DBA is notified of the problem, they may be able to find the root cause using these tools. Often, by the time the issue is reported, it’s too late, and they must wait until it occurs again. Wouldn’t it be better to know about an issue before the phone rings? A DBA’s trusted script library will grow over time as different types of issues come up, but analysing that data presents another challenge. How does this data help the DBA respond quickly to determine the root cause and solve the problem? A dashboard would helpful to tie information from many sources together so that correlations can be made. Building a useful dashboard takes time and effort that, once again, takes away from other important duties. While it is a difficult job to create a tool to monitor the performance of SQL Server, fortunately, there are monitoring tools available that can be used instead. By using a packaged monitoring tool, the DBA will spend less time figuring out which metrics to collect and writing those scripts, and more quickly getting to the root causes. While it is possible for a DBA to build a monitoring system, it will eventually end up being more expensive than purchasing a monitoring product. A monitoring tool should: - Connect server stress conditions, such as high CPU, memory, and I/O utilization, directly to long-running queries - Compare current performance to baselines - Analyse server waits - Connect long-running queries to waits - Detect blocking and provide easy visualization of the blocking chain - Alert on significant errors or warnings - Be easy to install, configure, and use - Allow the collection of custom metrics - Feature a dashboard that is intuitive and useful - Provide built-in and customizable reports - Pull together information from large estates, including Azure SQL Database, into one view The right monitoring solution will go a long way towards helping the DBA be proactive and spending less time “fighting fires.” Performance of critical systems directly impacts the bottom line, affecting customer loyalty and employee efficiency. Kathi Kellenberger from [SQLServerCentral.com]( » [Join the debate, and respond to today's editorial on the forums]( --------------------------------------------------------------- The Weekly News 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 [] [The single question to predict success for DevOps, and what it means for change control]( - In this 30 minute session, Microsoft MVP Kendra Little will share and discuss her three key insights from the 'Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report' webinar, and show why these insights make a compelling argument for rethinking your organization's change control process....[(more)]( [] [How to achieve true DevOps by including the database]( - As proven in the ‘The 2018 Accelerate State of DevOps Report’ including the database in DevOps initiatives greatly improves performance. In this webinar we will walk you through Redgate’s Database DevOps solution, showing reliable, scalable and repeatable processes for automating your database development and deployment....[(more)]( [] [How to monitor hybrid and cloud-based SQL Server estates]( - As organizations move to hybrid or cloud estates, the perception is that monitoring those servers gets more complicated, particularly if you have different servers on different platforms. In this webinar we will show you that it is quite easy to keep track of your databases and servers, regardless of where they are hosted, thanks to SQL Monitor....[(more)]( [] [An introduction to Compliant Database DevOps]( - Redgate’s Data Privacy and Protection Solution Specialist, Chris Unwin, will provide an overview of how a Compliant Database DevOps approach can help you balance the need to deliver software faster and cheaper, with the need to protect and preserve business critical data....[(more)]( T-SQL [] [SQL Server 2019: Batch Mode on Rowstore]( - In this post I’m going to look at the new feature of SQL Server’s 2019 Database Engine – Batch Mode on Row Store. I start with a quick introduction and then look at some curious details, that I have found playing with this feature so far. At the moment ......[(more)]( [] [Dynamic SQL, NULL, and EXEC]( - I've been a fan of dynamic T-SQL for a long time. I ran into something new, though, that threw me for a loop. It started with a stored procedure that builds a dynamic query string, based on a dozen or so input parameters that are all optional. If an ......[(more)]( Tools [] [SSMS 18.0 public preview released]( - We are very excited to announce the public preview of SQL Server Management Studio 18.0. SSMS has been ported to the VS 2017 Isolated Shell, which brings with it many improvements to look and feel and accessibility. This release expands platform support to keep up with the announcements at Microsoft Ignite last week and includes bug fixes and feature updates across the board....[(more)]( The Lighter Side [] [I helped write a book! No, not THOSE books…]( - Every now and again, someone asks if I write romance novels. Why? Well, let’s ask Bing who Kendra Little is…. To clarify, I am not married, I have dogs instead of children, and I prefer romances about strong women who bring SQL Servers to their knees. I ......[(more)]( SQL Server on Linux [] [How to Linux for SQL Server DBAs — Part 2]( - SQL Server DBAs are typically quite familiar with security and best practices in Windows but may not understand how security works in Linux. In this article, Kellyn Pot'Vin-Gorman demonstrates how to manage user accounts in Linux and why working while logged in as root is a bad idea....[(more)]( Security news and thoughts [] [Facebook Says Security Breach Affected About 50 Million Accounts]( - Facebook Inc. said it discovered a security breach earlier this week that affected almost 50 million accounts, the latest in a series of missteps that are undermining confidence in the company’s social network and business model. ...[(more)]( [] [When It Comes to Fantasy Football Today, Cyber Risks Could Sack Companies]( - With millions of players and billions of dollars involved, fantasy football today is rife with risk, Kaspersky warns. ...[(more)]( [] [Top 5 Ways for Analyzing Entitlements and Identifying High-Risk]( - There’s a great deal of risk associated with inappropriate or excessive rights, and when bad actors get into your network, they love to exploit these over-permissioned accounts. Identity analytics enhances your existing IAM investment by providing a ......[(more)]( Product Reviews and Articles [] [Spotting Unauthorized Configuration Settings in SQL Server]( - An attacker of SQL Server likes to be able to change the SQL Server configuration settings. In an ideal world, you will have left everything open for the intruder, but generally, every DBA reduces the surface of attack as much as possible. Why would ......[(more)]( PowerShell [] [Checking Trace Flags with dbachecks, online docs and PSPowerHour]( - It’s been a few weeks since i have blogged as I have been busy with a lot of other things. One of which is preparing for my SQL Pass Summit pre-con which has lead to me improving the CI/CD for dbachecks by adding auto-creation of online documentation, ......[(more)]( [] [getting started with powershell]( - Now that you’ve convinced your security team that PowerShell improves your security posture, it’s time to learn PowerShell if you haven’t already. Month of Lunches Hands down, the one book most people recommend for learning PowerShell is Learn Windows ......[(more)]( PowerPivot/PowerQuery/PowerBI [] [Fun with DAX – Minesweeper]( - The latest addition to my recent series of DAX based games is the classic Minesweeper game. This is the game where you are presented with a 9 x 9 matrix of squares. There are 10 hidden mines and you can either step on a square or place a flag where ......[(more)]( [] [Power BI Cheat Sheet, Power Query in MS Flow, Home and more… (October 1, 2018)]( - Reference vs Duplicate in Power BI; Power Query Back to Basics (@Rad_Reza) Power BI Cheat Sheet (@DaveRuijter) #PowerQuery everywhere :-) – now in #microsoftflow as well (@donsvenson) Premium Capacity Metrics app – query metrics now available Introducing ......[(more)]( [] [Beyond the Basics: Report Design Considerations for Self-Service Users]( - Your company rolled out self-service Power BI – how exciting! As a new user, you’re set up to start developing reports and sharing them with colleagues. You’ve probably gone through some basic training, so you know how to connect to data and create visualizations, ......[(more)]( Performance Tuning SQL Server [] [The Good, the Bad and the Ugly of Table Variable Deferred Compilation Part 1]( - As mentioned in the previous article, SQL Server 2019 cardinality estimations for a table variable are based on actual table variable row counts. Therefore, in SQL Server 2019, we should expect better estimations and better plans for queries that use table variables....[(more)]( [] [Extended Events Workbench]( - The Extended Events (or XEvents) feature has been part of SQL Server since 2008, but many database professionals struggle to get started using it. In this article, Phil Factor demonstrates several useful Extended Event sessions that measure just one thing in each. He then provides the code necessary to parse the resulting XML into something you can use....[(more)]( [] [Adaptive Query Processing in SQL Server 2017]( - As new versions of SQL Server have been released, the Query Optimizer has undergone many improvements. Adaptive Query Processing, new in 2017, represents a new direction. This set of features provides some flexibility for varying join types, multi-statement table valued functions, and memory grants. In this article, Robert Sheldon explains the three features that make up Adaptive Query Processing ...[(more)]( [] [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( - Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. My book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance, and so optimize your SQL queries, and improve your indexing strategy. You’ll also learn how to take advantage of the Query Store and the latest optimizations, Adaptive Query Processing....[(more)]( [] [Plan Forcing in SQL Server]( - Last month I was in Portugal for their SQLSaturday event, and I spent a lot of time talking about Plan Forcing in SQL Server – both manual and automatic (via the Automatic Plan Correction feature). I had some really great questions from my pre-con and ......[(more)]( Microsoft News [] [Microsoft Upgrades Surface Devices, Ups IPhone and Android Ties]( - In the six years since Microsoft began making its own devices, the company has taken the Surface from an ill-received convertible tablet to a well-reviewed line of devices with sales that topped $5 billion in the most recent fiscal year. ...[(more)]( [] [Microsoft Further Commits to Ethical AI with New Humanitarian Program]( - The tech giant announced a $40-million program to partner with humanitarian organizations on ethical AI. ...[(more)]( [] [Surface Hub 2 Conferencing System Set to Arrive in 2019]( - Microsoft demos Surface Hub 2 at Ignite with initial release planned for Q2 2019 and fully-featured version coming in 2020 ...[(more)]( Internet of Things [] [Can IoT Help End World Hunger? HPE and WEF Think So.]( - The prospect of the Internet of Things helping end world hunger may sound like a lofty goal, but HPE and the World Economic Forum are creating a step-by-step plan to make it happen. ...[(more)]( [] [7 IoT Technology Highlights from Dreamforce]( - Although not ubiquitous at this year’s Dreamforce event, IoT technology was a prominent theme. Here are seven examples spotted at the show. ...[(more)]( Database Design, Theory and Development [] [Conceptual Modeling, Ontological Commitment, and the Data Model]( - Revised: 10/1/18 We are culturally and linguistically conditioned to view the world as consisting of objects with properties[1]. Objects in a universe thereof that share common properties are of the same type and form a class, distinguishing them from ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [As the Need for Data Storage Rises, So Will the Use of Cloud Data Services]( - According to an EnterpriseStorageForum survey, companies are increasingly adopting cloud storage services to deal with petabyte-level data growth. ...[(more)]( Career Growth [] [Microsoft Fellow: Inject Social Responsibility into the Software Development Processes]( - According to Microsoft Technical Fellow Jeffrey Snover -- well-known for being the chief architect of PowerShell -- the thing developers need to be mindful of is not building a technology that best serves shareholders, but one that best serves stakeholders, ......[(more)]( Big Data [] [Combining SQL + Python for an Improved Data Workflow]( - Data teams have evolved a lot in the last five years. With radically cheaper storage and an exponential increase in data available for analysis, modern data teams are finding answers to questions that were recently considered impossible to even approach....[(more)]( Azure SQL Database [] [Sharding: Horizontal Scaling in Azure SQL Database]( - David has produced a talk all about Sharding. Or if you prefer horizontally scaling your Azure SQL database.You can view the talk below, its about 45 minutes long and looks at all the aspects of what you need to do to "shard" Azure SQL DatabaseIf you ......[(more)]( AI/Machine Learning/Cognitive Services [] [Benefits of Artificial Intelligence in Healthcare: What's Possible? What's Next?]( - Among the benefits of artificial intelligence in healthcare is accurate detection of dozens of eye diseases. It’s big news for deep learning and the medical industry, and there are many other applications of AI to come. ...[(more)]( Administration of SQL Server [] [Running a SQL Server Azure Container Instance in a virtual network]( - Up until now Azure Container Instances only had one option to allow us to connect. That was assigning a public IP address that was directly exposed to the internet. Not really great as exposing SQL Server on port 1433 to the internet is generally a bad idea......[(more)]( [] [The SQL Agents’ view of SQL Server]( - I recently came across a really odd issue with the SQL Agent, there were two agent jobs attached to one schedule one of the jobs was enabled and the other was not enabled – all pretty normal at this point but here is where it got weird. The disabled ......[(more)]( [] [How I Use a Mac but Work on SQL Server]( - Kind of a convoluted title, I know, but I get questions like this every now and then: Chris and I had a good conversation on Twitter, and I figured it’s time to post an updated version of how I work. I have a “fake job” – I’m a consultant. If you’re ......[(more)]( [] [What is a DBA anyway?]( - Some time ago we started a new series here, called Database Fundamentals. The very first post in that series asked what a database is. One of the major announcements at Ignite last month was that of SQL Server 2019 preview and major improvements to Azure ......[(more)]( --------------------------------------------------------------- Administrative To be removed from this list, please click [here]( Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can [change your profile]( or follow the instructions on the daily newsletter. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com