Getting Query Execution Statistics, Avoiding Excessive Sorts, and Is Transparent Data Encryption just security theatre? (Database Weekly 11/11/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Webinar: data protection & privacy in the world of DevOps](#30353) Virtualization and Containers - [dbatools with SQL on Docker and running SQL queries](#30360) T-SQL - [Coming in SQL Server vNext: Approximate_Count_Distinct](#30376) - [Partitioned Views, Aggregates, and Cool Query Plans](#30375) - [What's my transaction isolation level](#30370) - [How to Avoid Excessive Sorts in Window Functions](#30364) - [ROLLing UP Totals](#30356) SQL Server Security - [Is Transparent Data Encryption just security theatre?](#30357) SQL Server News - [SQL Server 2017 – Interleaved Execution](#30388) - [Making parallelism waits actionable](#30382) - [Big News In Databases — Fall 2017](#30371) R Language - [Multiple Output Datasets With R and SQL Server](#30384) PowerShell - [Comparing Agent Jobs across Availability Group Replicas with PowerShell](#30367) - [Scripting out all objects from a SQL Server database with PowerShell and dbatools](#30359) PowerPivot/PowerQuery/PowerBI - [Power BI Desktop – Query Reduction for Direct Query Models](#30365) - [Dynamic Row Level Security with Manager Level Access in Power BI](#30358) Performance Tuning SQL Server - [Getting Query Execution Statistics](#30389) - [How to Avoid Excessive Sorts in Window Functions](#30383) - [The Case of the Weirdly Long COLUMNSTORE_BUILD_THROTTLE Wait](#30381) - [Extended Events and Profiler: XEProfiler](#30373) - [Becoming a Query Performance Troubleshooting Expert](#30368) - [New Showplan enhancements](#30366) - [SQL Server 2017 - soft-NUMA limits MAXDOP?](#30361) DevOps and Continuous Delivery (CI/CD) - [SSDT How To Fix Error SQL17502](#30378) - [DevOps for Data Science – Defining DevOps](#30372) - [The four pillars of DevOps](#30354) Computing in the Cloud (Azure, Google , AWS) - [Azure Virtual Machine Developments for SQL Server Usage](#30363) Columnstore Indexes - [Surprise Delta Stores](#30362) Career Growth - [Does a Formal Degree Really Matter in Tech?](#30385) - [Hacking Speaker Idol](#30369) Backup and Recovery - [Configure TDE encrypted database in SQL Server AlwaysOn Availability Group](#30386) Azure SQL Database - [Microsoft Enabling Automatic Database Tuning in Azure SQL DB in 2018](#30387) - [Azure SQL Database: Ingesting 1.4 million sustained rows per second with In-Memory OLTP & Columnstore Index](#30374) Azure CosmosDB - [A few CosmosDB query limitations](#30377) Administration of SQL Server - [Checking SQL Server with Policy-Based Management](#30380) - [How to Test Your Corruption Alerts](#30379) [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 2017-11-13 [Redgate Hub]( Register now for SQL in the City Streamed Redgate’s popular SQL in the City Streamed virtual event takes place again this December. Wherever you are, tune in on Wednesday December 13 to watch some of the best-known speakers from the database world present the latest technologies and tools from Redgate. [Register free now]( [SQL Monitor]( Don’t just fix SQL Server problems, prevent them from happening SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. [Download SQL Monitor now and get a 14 day free trial]( [SQL Prompt]( Write, format, and refactor SQL effortlessly with SQL Prompt Writing SQL is 50% faster with SQL Prompt. Your SQL code can be formatted just the way you like it, you can create and share snippets with your team, and with tab coloring you’ll never deploy to the wrong environment again. [Download your free trial]( Editorial - Microservices in SQL Server It occurs to me that SQL Server is now the ideal way of implementing effective microservices. Yes, really. Over the years, I've rather changed my attitude to structured data and databases. I remain opposed to the idea of holding XML or JSON inside the base tables of databases, unless they are treated strictly as atomic. I particularly dislike seeing untyped XML in databases: it is stored inefficiently and is slow in use. However, we now have in SQL Server 2017 an excellent way of consuming or providing JSON data in a way that is application-friendly, without letting it poison our base tables. I can now smile sweetly at developers when they ask me to 'talk and understand' JSON in the interface between application and database. Not only is it OK with me, but it removes entirely any last lingering requests from developers to access database objects other than what is provided for them in their schema/interface. It is now perfectly possible for all their functions and procedures to consume and return JSON. Whatever one's philosophical stance to JSON, it has traction. As it is, effectively, JavaScript, it is ridiculously easy for the Web Developer to use. There is enough in JSON to allow us to do business with it. It is the data transport for microservices, and can be read 'natively' by PowerShell and any .NET compiled language. It underpins JSON-RPC and Asynchronous JavaScript and JSON (AJAJ). It is increasingly used for RESTful web services. It is the data transfer medium for most NoSQL Databases. The introduction of SQL Server 2017 gives us an opportunity for the first time to come out of our bunkers and cooperate with developers in data terms that make sense to them, without having to trouble them with the philosophical depths of relational theory. There need be no mismatch between the object-oriented world and the relational because we, as data people, can now do all the worrying about the translation in both directions, and obsess about both the data security and integrity. I'm hoping that next year we, as SQL Server data people, can come up with radical ways of making it quicker and easier to develop secure and scalable data applications on Windows and Linux, and change the old scary image of the database person as the grumpy curmudgeon of the IT department. With good JSON support in SQL Server now, it makes it all a lot easier to smile and nod at developers. Phil Factor. Phil Factor 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 [] [Webinar: data protection & privacy in the world of DevOps]( - Microsoft MVP and PASS President Grant Fritchey dispels the myth that database DevOps and compliance can't go hand in hand. After a brief look at how extending DevOps to the database lays solid foundations for data governance and compliance, the focus will then shift to guidance around the upcoming GDPR. Register now....[(more)]( Virtualization and Containers [] [dbatools with SQL on Docker and running SQL queries]( - Querying to show the version of the SQL running in the Docker Container....[(more)]( T-SQL [] [Coming in SQL Server vNext: Approximate_Count_Distinct]( - SQL Server vNext (2018?) will let you trade speed for accuracy. They’re working on a new APPROXIMATE_COUNT_DISTINCT....[(more)]( [] [Partitioned Views, Aggregates, and Cool Query Plans]( - Erik Darling on how partitioned views handle aggregates...[(more)]( [] [What's my transaction isolation level]( - If I use sp_executesql to run some dynamic sql - does it default the connection isolation level? If I change isolation level within the query, does it propagate back to the invoker? ...[(more)]( [] [How to Avoid Excessive Sorts in Window Functions]( - Like any tool, window functions come at a price and we must carefully evaluate if that’s a price we’re willing to pay. That price can be a sort operation....[(more)]( [] [ROLLing UP Totals]( - ROLLUP is used to provide additional totals for your aggregates while using GROUP BY. Steve Jones offers some simple examples of how you can use it....[(more)]( SQL Server Security [] [Is Transparent Data Encryption just security theatre?]( - One of the biggest arguments against TDE is that it doesn’t protect data in motion, which is a primary attack vector. It is thus considered nothing more than a checkbox item for the compliance officer’s security audit. However, Randolph West argues that TDE is very far from being just "security theater"....[(more)]( SQL Server News [] [SQL Server 2017 – Interleaved Execution]( - Interleaved Execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised estimates....[(more)]( [] [Making parallelism waits actionable]( - Microsoft have announced upcoming changes to parallelism waits, specifically splitting CXPACKET waits into an actionable wait (CXPACKET) and a negligible wait (CXCONSUMER)....[(more)]( [] [Big News In Databases — Fall 2017]( - Summarizing the most important database news from the last six months....[(more)]( R Language [] [Multiple Output Datasets With R and SQL Server]( - Using R, it's possible to scrape websites for data that's usable in SQL Server....[(more)]( PowerShell [] [Comparing Agent Jobs across Availability Group Replicas with PowerShell]( - How to use the Compare-Object a function available in PowerShell to compare SQL Agent Jobs across availability group replicas....[(more)]( [] [Scripting out all objects from a SQL Server database with PowerShell and dbatools]( - How to to automate the scripting of objects with PowerShell for easier source control integration....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power BI Desktop – Query Reduction for Direct Query Models]( - Previously, every change in a filter or slicer led to an immediate round trip to the data source which made some of those reports … let’s call it .. not perfect in the context of user experience. Beginning with the Nov2017 release some options for Query Reduction has been introduced....[(more)]( [] [Dynamic Row Level Security with Manager Level Access in Power BI]( - Reza Rad shows how to implement a dynamic row level security model where everyone will see their own data, but the manager will see all data....[(more)]( Performance Tuning SQL Server [] [Getting Query Execution Statistics]( - If you hate things that are slow, I bet you certainly want to know the reason for the slowness. So, if you are SQL Developer and your query run slow, you would prefer to have query execution statistics so that you can figure out what causing the query to run slow....[(more)]( [] [How to Avoid Excessive Sorts in Window Functions]( - Like any tool, window functions come at a price and we must carefully evaluate if that’s a price we’re willing to pay....[(more)]( [] [The Case of the Weirdly Long COLUMNSTORE_BUILD_THROTTLE Wait]( - A funny thing happened on the way to the filtered nonclustered columnstore demo… ...[(more)]( [] [Extended Events and Profiler: XEProfiler]( - Grant Fritchey points out the one giant issue outstanding, in the war between Trace Events (Profiler) and Extended Events. XML. ...[(more)]( [] [Becoming a Query Performance Troubleshooting Expert]( - Thomas LaRock explains why he is a huge fan of using “buckets” to help troubleshoot issues. Are all queries affected, or just a subset of queries affected?...[(more)]( [] [New Showplan enhancements]( - In SSSMS v17, we can now see per-operator statistics, such as CPU and elapsed time per thread. More recently, we have introduced overall query CPU and elapsed time tracking for statistics showplan xml, as well as the top 10 waits that the execution was waiting on....[(more)]( [] [SQL Server 2017 - soft-NUMA limits MAXDOP?]( - The plan was to set up a soft-NUMA node for each vcpu on a 16 vcpu VM, to evenly distribute incoming connections and thus DOP 1 queries over vcpus. But would leaving this soft-NUMA configuration in place after the specialized workload would result in DOP 1 queries whether I wanted them or not?...[(more)]( DevOps and Continuous Delivery (CI/CD) [] [SSDT How To Fix Error SQL17502]( - Ed Elliott tackles an issue that will have affected anyone who has a database project that they import into SSDT, where the database has some piece of code that references something that doesn't exist....[(more)]( [] [DevOps for Data Science – Defining DevOps]( - Buck Woody defines DevOps simply as: getting all parties involved in getting an application deployed and maintained to think about all the phases that follow and precede their part of the solution....[(more)]( [] [The four pillars of DevOps]( - DevOps isn’t just about tools. DevOps isn’t just about process. DevOps isn’t just about the people. DevOps isn’t just about culture. If you try and focus on one thing only, you’ll probably do it wrong and fail. You need to embrace all of the pillars. You wouldn’t build a house with only two walls and expect to have a wonderful experience the next time it rained....[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Azure Virtual Machine Developments for SQL Server Usage]( - Microsoft has a large number of different Azure virtual machine series and sizes available if you are interested in using their infrastructure as a service (IaaS) option for hosting SQL Server instances. One initial decision you will need to make is what series and size of Azure VM you want to use for your particular SQL Server workload....[(more)]( Columnstore Indexes [] [Surprise Delta Stores]( - Microsoft and many others will be quick to tell you that loading data into CCIs is much faster when you can bypass the delta store.This post contains all of the possible causes for delta store creation that I’ve found. ...[(more)]( Career Growth [] [Does a Formal Degree Really Matter in Tech?]( - Many professions require college degrees or even advanced degrees, professional board exams, and licensing, but being a developer or IT professional does not, at least not in the US. Specific educational requirements make sense in many fields. but it’s difficult for universities to keep up with the latest programming languages, and they don’t necessarily teach all the skills needed to be successful in tech....[(more)]( [] [Hacking Speaker Idol]( - What Thomas LaRock learned from PASS Summit Speaker Idol about good presentation skills....[(more)]( Backup and Recovery [] [Configure TDE encrypted database in SQL Server AlwaysOn Availability Group]( - TDE has been out there since SQL Server 2008 and it is widely used to protect data/log/backup files at rest. There still seems to be some confusion around the terms used, such as Service Master Key (SMK), Database Master Key (DMK), Certificate and Database Encryption Key (DEK), especially in the area how we backup, maintain those keys, when we should restore, re-create them....[(more)]( Azure SQL Database [] [Microsoft Enabling Automatic Database Tuning in Azure SQL DB in 2018]( - Microsoft's automated and AI-enabled database tuning technology will become the new default configuration on the Azure SQL cloud database in January 2018. ...[(more)]( [] [Azure SQL Database: Ingesting 1.4 million sustained rows per second with In-Memory OLTP & Columnstore Index]( - As Internet of Things (IoT) devices and sensors are becoming more ubiquitous in consumer, business and industrial landscapes, they introduce a unique challenge in terms of the volume of data they produce, and the velocity with which they produce it. ...[(more)]( Azure CosmosDB [] [A few CosmosDB query limitations]( - DocumentDB API for CosmosDB is also called SQL but although it looks like SQL and smells like SQL… it isn’t SQL. However, it’s easy to assume that what work in T-SQL or PSQL will work in DocumentDB SQL. Here are a few examples of what won’t work....[(more)]( Administration of SQL Server [] [Checking SQL Server with Policy-Based Management]( - Policy-Based Management, a feature of SQL Server, is a flexible tool that can help DBAs manage one or more SQL Server instances. It's used for monitoring and enforcing a standard set of policies for SQL Server throughout an organization. While there are many built-in conditions from which to choose, Dennes demonstrates how to set up a custom policy as well as a standard one. Viewing the policy status over many servers can be tedious, so he also explains how DBAs can evaluate the states of multiple servers with just one glance....[(more)]( [] [How to Test Your Corruption Alerts]( - You followed the setup checklist in our First Responder Kit. You ran sp_Blitz. You set up email alerts for common issues. You run CHECKDB as frequently as practical – weekly, or maybe even daily. But you just assume it’s all working......[(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 ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com