A SQL Server Upgrade Checklist, an SSIS Catalog Dashboard, and some cookies (Database Weekly 3/11/2019)

The Complete Weekly Roundup of SQL Server News In this issue: Virtualization and Containers - [How Your Hypervisor Can Impact Your CPU](#34819) Vendors/3rd Party Products - [Safely Deleting Clones and Images during Database Development and Testing](#34800) T-SQL - [Use Subquery with Bill of Materials](#34815) - [Concatenation in Transact-SQL](#34812) - [New Objects, Columns, and Messages in SQL Server 2019 CTP 2.3](#34807) - [Are nulls stored in a nonclustered index?](#34806) - [Commit or Rollback Dependent Transactions](#34805) - [Should I Replace My FOR XML PATH String Merges with String_agg?](#34803) - [Dipping into the Cookie Jar](#34799) - [SET Statistics IO Update in SQL Server 2019](#34795) - [CTRL + F in SSMS execution plans](#34793) - [Reduced recompilations for workloads using temporary tables across multiple scopes](#34792) - [Time Zones and Daylight Saving Time](#34788) - [Can deleting rows make a table…bigger?](#34785) - [Discovering Three or Four Part Names in SQL Server Database Code](#34781) PowerShell - [Failing a PowerShell Sql Agent step](#34787) PowerPivot/PowerQuery/PowerBI - [DAX and Conditional Formatting Better Together: Find The Biggest and Smallest Numbers in the Column](#34796) - [Easy unstacking of columns in Power BI and Power Query (and Excel)](#34794) - [DAX Optimizations: Write it like the DAX calls it](#34784) - [DAX 101: Computing running totals in DAX](#34783) - [Now You See Me! Use cases of ALL DAX Function in Power BI](#34782) Polybase/HDInsight - [PolyBase Revealed: Predicate Pushdown Does Not Include Strings](#34816) Performance Tuning SQL Server - [Wait Statistics in SQL Server – Performance Troubleshooting](#34821) - [Analyzing wait statistics on Managed Instance](#34814) - [The Curious Case of… faster disks causing more WRITELOG waits](#34810) - [Using Indexing To Solve Blocking and Deadlocking Issues](#34802) - [Video: How The Right Indexes Help SQL Server Make Better Use Of Memory](#34798) - [Troubleshooting THREADPOOL With The Plan Cache](#34786) NoSQL - [Querying CosmosDB with SQL Server 2019 Polybase Services](#34791) - [Introduction to SQL for Cosmos DB](#34780) Hardware Testing - [Assessing your disk performance and your needs: Collecting relevant data (Part 1)](#34817) Hardware News - [Glenn’s Tech Insights for March 6, 2019](#34808) ETL/SSIS/Azure Data Factory - [SSIS Catalog Dashboard](#34822) DevOps and Continuous Delivery (CI/CD) - [Approaching Zero: Table Changes](#34797) - [Approaching Zero: Stored Procedure Changes](#34790) Database Design, Theory and Development - [Fourth Order Properties: Association Relations vs. Foreign Keys](#34804) Azure SQL Managed Instance - [Sending resource alerts on Managed Instance using db_mail](#34818) Azure SQL Database - [system_health Extended Events in Azure SQL Database](#34809) Analysis Services / BI on the MS Stack - [SSAS Tabular 2019, Calculation Groups And Migration From SSAS Multidimensional](#34789) Administration of SQL Server - [Error Log Analysis Script](#34820) - [SQL Server Upgrade Checklist](#34813) - [Resumable Online Index Create and Rebuild Operations](#34811) - [Moving Data to a New Filegroup](#34801) [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 2019-03-11 [SQL Provision]( SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps With SQL Provisions virtual cloning technology, databases can be created in seconds using just 1MB of storage, and sensitive data can be anonymized or replaced with realistic data to ensure it is protected as it moves between environments. [Download your free trial]( [SQL Monitor]( How SQL Server monitoring benefits your whole organization SQL Server monitoring doesn’t just benefit your DBAs. In this new guide from Redgate, we take you through the different ways a robust monitoring solution has a positive impact across your organization, from your development teams to IT management, and from finance to your C-suite. [Download your free copy now]( [SQL Prompt]( Write, format, analyze, and refactor SQL fast 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 code analysis you get suggestions to improve your SQL as you type. [Download your free trial]( Editorial - Microservice Reality I used to think that I was rather a 'DevOps' sort of guy, until I was recently reproached for ignoring [microservices](. I'm not sure how microservice architecture became [part of the orthodoxy of 'DevOps']( but everyone seems to nod wisely whenever it's mentioned. It is still a 'cool' technology because it promises independent development and deployment, and easy reusability. For me, the most intractable problem with service-oriented architectures, in general, besides the obvious complexity, was the difficulty of maintaining long running transactions, and managing a [Two-Phase Commit]( [that involved more than one service](. In microservices, it would be a journey, and in SOA it was a[Saga](. The insidious nature of the problem is that it tends not to show itself in testing or development, and when it happens the cause is hard to pin down. In fact, the whole problem of managing state is hard, especially if it must survive network problems. Microservices don't so much represent an architecture as a design pattern for distributed services. [It provides no intrinsic solution to this two-phase commit problem](. It has been described as a technical solution to a man-management problem. With a 'monolithic' relational database acting as a coordinator, the whole problem disappears, as long as you avoid anything that tries to circumvent it. With Microservices, you can use the [Events](/[Choreography]( approach, which can work well with short Sagas but is inherently complex and hard to test. The other alternative is [Command/orchestration](. This involves a service whose only task is to act as coordinator and broker. This can work well, especially if it is all carefully monitored, but it begins to seem reminiscent of the moral tale of 'Animal Farm' where the revolutionary pigs began to look like the hated farmers, because as the tasks grow more complex, these orchestrators or coordinators begin to look like the monolithic databases that so many of the microservices visionaries like to condemn. My worst problem wasn't transactions, managing versioning, or providing resilient messaging, but explaining and communicating such a complex solution to a business that was considering it. At the start, it is fine: everyone likes the presentation where you show all the microservice boxes in PowerPoint, all running their well-bounded service, and intercommunicating as peers, as compared with the nasty old monolith. It is in describing the management of business processes that the anxiety in the room increases: expensive skill-sets, reliance on bleeding-edge technologies, expensive consultancy. The even scarier problem is that very few processes, even in well-established businesses, exhibit the dreamy clarity and autonomy of a microservice. Real processes are riddled with interdependencies that sometimes don't emerge until after launch. Your business sponsor may be pleasant and conscientious but unlikely to understand the naked reality of the typical business process in its full arcane horror. Microservices can work with social-media apps and well-funded green-field projects, and they work well to solve specific problems, but this pattern[isn't necessarily right for you](. 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. Virtualization and Containers [] [How Your Hypervisor Can Impact Your CPU]( - he performance of their SQL Server had degraded, and it appeared to be related to higher than normal CPU utilization in conjunction with symptoms of unresponsive user queries. The root cause was twofold—a third party hosting provider had overallocated virtual processors on the physical host where the virtual machine (VM) running SQL Server was residing, as well as a recent upgrade from a version of VMWare that was not patched for Spectre and Meltdown. ...[(more)]( Vendors/3rd Party Products [] [Safely Deleting Clones and Images during Database Development and Testing]( - This is the third article in a series that explains how to use SQL Clone, part of SQL Provision, plus a collection of PowerShell scripts, all with a shared configuration data file, to deploy, revert, customize, delete and refresh clones, for database development and testing work....[(more)]( T-SQL [] [Use Subquery with Bill of Materials]( - In this puzzle, we’re going to learn how to work with the AdventureWorks Bill of Materials table. Companies use a BOM (Bill of Materials) to itemize the products that are used or contained in another product. Solving puzzles is a great way to learn SQL....[(more)]( [] [Concatenation in Transact-SQL]( - SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses....[(more)]( [] [New Objects, Columns, and Messages in SQL Server 2019 CTP 2.3]( - Sure, there’s official documentation on what’s new in SQL Server 2019, but Microsoft’s notorious for slipping in undocumented stuff. To unearth it, Brent Ozar uses a linked server technique, joining between old & new versions of the product, and query the system tables for changes. ...[(more)]( [] [Are nulls stored in a nonclustered index?]( - When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it....[(more)]( [] [Commit or Rollback Dependent Transactions]( - Sometimes it is necessary to apply or fail multiple transactions together (all changes succeed or all changes fail). This can be the case when migrating data from a production/transaction table to a history (reporting) table. If the transactions are not ‘linked’, it is possible for one to succeed and the other to fail. ...[(more)]( [] [Should I Replace My FOR XML PATH String Merges with String_agg?]( - If you are looking for major performance gains, the answer is no. However, if you are using SQL Server 2017 and developing new code, you should consider using the new String_agg function that is available on SQL Server 2017 because code that uses this function is so much more readable and easier to write and maintain....[(more)]( [] [Dipping into the Cookie Jar]( - This month's T-SQL Tuesday is about finding and sharing cookies! Dipping into the Cookie Jar is about when the going gets tough and you don’t think you can handle anymore, then you think back about your accomplishments and take some sustenance from them. You dip back into that cookie jar and use whatever energy that provides to keep going....[(more)]( [] [SET Statistics IO Update in SQL Server 2019]( - Niko Neugebauer explains an enhancement that reveals why disabling automated statistics update before you perform a large ETL operation will improve the speed. Once finished you can run the statistics update with all available cores....[(more)]( [] [CTRL + F in SSMS execution plans]( - Let’s say we want to find all the operators that used the Posts table? Simply hit Ctrl-F and use Contains Posts, and use the arrows to go through the operators....[(more)]( [] [Reduced recompilations for workloads using temporary tables across multiple scopes]( - Joseph Sack demonstrates the improvement by comparing with the current behavior in SQL Server 2017 and prior....[(more)]( [] [Time Zones and Daylight Saving Time]( - AT TIME ZONE is great because it makes it easy to perform daylight saving time and time zone conversions in our queries. However, when using AT TIME ZONE for these types of conversions there are a couple key points to remember to ensure you are getting the correct results. ...[(more)]( [] [Can deleting rows make a table…bigger?]( - A case where deleting 10 million rows from a 7.5 billion row table with 5 indexes caused the indexes to get larger, not smaller. Why?...[(more)]( [] [Discovering Three or Four Part Names in SQL Server Database Code]( - Some of our databases have cross database dependencies by design. For example, a staging database may be accessible for loading code in the base database. Or we may have added a bolt on database, like a database of views or user space that we allow cross references to. In all cases however, we need to know what those dependencies are so we can handle them, and ferret out invalid ones....[(more)]( PowerShell [] [Failing a PowerShell Sql Agent step]( - If you're using PowerShell to move files around prior to importing them, then you'll want the PowerShell step to report failure properly, so the Agent logic will rerun the step....[(more)]( PowerPivot/PowerQuery/PowerBI [] [DAX and Conditional Formatting Better Together: Find The Biggest and Smallest Numbers in the Column]( - How to use DAX combined with conditional formatting to only highlight the biggest and the smallest number in a column in a table....[(more)]( [] [Easy unstacking of columns in Power BI and Power Query (and Excel)]( - How to unstack a column into a table....[(more)]( [] [DAX Optimizations: Write it like the DAX calls it]( - This article focuses on an optimization technique discovered when investigating why a data model’s calculation time could go from a couple of seconds to many minutes....[(more)]( [] [DAX 101: Computing running totals in DAX]( - This article shows how to compute a running total over a dimension, like for example the date....[(more)]( [] [Now You See Me! Use cases of ALL DAX Function in Power BI]( - Among all the functions in DAX; the behavior of ALL function still seems mysterious for many. Many people, don’t use it at all and end up writing a very complicated calculation for a scenario that only one simple expression can do the same job. ...[(more)]( Polybase/HDInsight [] [PolyBase Revealed: Predicate Pushdown Does Not Include Strings]( - Kevin Feasel finds out that you can’t use strings in a predicate pushdown operation in Polybase....[(more)]( Performance Tuning SQL Server [] [Wait Statistics in SQL Server – Performance Troubleshooting]( - There is a foundation of knowledge one must know to properly troubleshoot performance issues in SQL Server....[(more)]( [] [Analyzing wait statistics on Managed Instance]( - Wait statistics are information that might help you understand why the query duration is long and identify the queries that are waiting for something in database engine. In this post, I will show to you how to identify why the workload is waiting and what are the queries that are waiting on some resources....[(more)]( [] [The Curious Case of… faster disks causing more WRITELOG waits]( - Performance tuning is usually about reducing the incidence of waits and/or the individual wait times. Paul Randal explains the only example he can think of where you do something that increases performance and the wait type you were concerned about becomes more prevalent....[(more)]( [] [Using Indexing To Solve Blocking and Deadlocking Issues]( - A while back, I was having a conversation about a deadlocking issue and suggested that an index could perhaps help solve it. The reaction I got was along the lines of, ‘What, how can an index solve a deadlocking issue?’...[(more)]( [] [Video: How The Right Indexes Help SQL Server Make Better Use Of Memory]( - Exploring what's in the buffer pool for a table depending on the indexes you have....[(more)]( [] [Troubleshooting THREADPOOL With The Plan Cache]( - Erik Darling offers a query to help you investigate which queries contributed to THREADPOOL problems....[(more)]( NoSQL [] [Querying CosmosDB with SQL Server 2019 Polybase Services]( - How to configure SQL Server 2019 Polybase to connect Azure CosmosDB Mongo databases. ...[(more)]( [] [Introduction to SQL for Cosmos DB]( - This article by Adam Aspin reviews the Azure Cosmos DB SQL API from the perspective of the relational database developer. More specifically it will show you how to leverage your Structured Query Language skills to exploit the core possibilities of Cosmos DB as a NoSQL document database....[(more)]( Hardware Testing [] [Assessing your disk performance and your needs: Collecting relevant data (Part 1)]( - In this blog series, I will discuss about what to measure and how to measure it in order to assess the health of your disks and how to approach that touchy question: is this enough or do I need more?...[(more)]( Hardware News [] [Glenn’s Tech Insights for March 6, 2019]( - DRAM Prices Declining, Third generation AMD Ryzen Threadripper processors and more....[(more)]( ETL/SSIS/Azure Data Factory [] [SSIS Catalog Dashboard]( - The SSIS catalog includes built-in reports that show execution activity for current and historical operations, but they can only be viewed from within SQL Server Management Studio. Tim Mitchell has open sourced his set of reports created using SQL Server Reporting Services, which allow easy browser-based access to the activity in the SSIS catalog logging tables....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [Approaching Zero: Table Changes]( - Some table changes are trivial while others, like changing a data type, require a lot of foresight and planning. ...[(more)]( [] [Approaching Zero: Stored Procedure Changes]( - Kevin Feasel looks at different procedure modification scenarios and explains how we can make minimally disruptive changes....[(more)]( Database Design, Theory and Development [] [Fourth Order Properties: Association Relations vs. Foreign Keys]( - We have Building, Room, and Bed entities. Logically, if this is in the scope of some hypothetical hotel, then each one of those entities is dependent on their parent to exist ... you cannot have a bed without a room. Also, that room wouldn't exist without its parent, Building. So, why have I rarely seen this identifying relationship introduced?...[(more)]( Azure SQL Managed Instance [] [Sending resource alerts on Managed Instance using db_mail]( - One of the biggest issue that you might experience in Managed Instance is reaching storage limit or finding out that you don’t have enough CPU. In this case you would need to get the bigger instance; however, this is not instant operation. In this post, you will see how you can monitor resource usage and send email alerts if there is a risk that you might reach the limits....[(more)]( Azure SQL Database [] [system_health Extended Events in Azure SQL Database]( - The system_health Extended Events session is incredibly useful. Further, it’s running, by default, in every server you have under management that is 2008 or greater. Things are not the same in Azure though....[(more)]( Analysis Services / BI on the MS Stack [] [SSAS Tabular 2019, Calculation Groups And Migration From SSAS Multidimensional]( - Chris Webb regards the new Calculation Groups in SSAS Tabular 2019 as most important new feature in DAX since… well, forever. It allows you to create a new type of calculation, which in most cases will be a time intelligence like a year-to-date or a previous period growth, that can be applied to multiple measures....[(more)]( Administration of SQL Server [] [Error Log Analysis Script]( - SQL Server Error Logs show various aspects regarding the state of the SQL Server Instance. Error log analysis helps ensure nothing unexpected happens, and is useful for diagnosing existing problems....[(more)]( [] [SQL Server Upgrade Checklist]( - A SQL Server Upgrade Checklist is critical to the success of your SQL Server upgrade projects. Mike Walsh gives you a "template" from which to start....[(more)]( [] [Resumable Online Index Create and Rebuild Operations]( - Greg Larsen demonstrates resumable index operations available with SQL Server 2017 and 2019. This feature helps DBAs work around those small maintenance windows by allowing certain index operations to be paused and restarted again later....[(more)]( [] [Moving Data to a New Filegroup]( - Occasionally, you may need to move a table from one filegroup to another. Perhaps someone inadvertently created a table in the system filegroup, but company policy dictates never to do that. Perhaps you need to split data up between several filegroups to get better I/O performance. Either way, manually moving data from one filegroup to another can be a tedious, time-consuming, and error-prone process....[(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