Why Your Query Can’t Go Parallel, Table Variables Edition (2020-09-12 DBW)

 Database Weekly for September 12, 2020 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  Automation is a Key Skill for the Modern DBA This month we had [T-SQL Tuesday #130]( hosted by [Elizabeth Noble](. Elizabeth and I had some good talks about database development and DevOps last year, and I managed to convince her to host one of the blog parties. I had expected that people might focus on the software development side of automation, but many of the posts cover administrative topics. The recap is coming next week, and I look forward to it, but I shouldn't have been surprised. Good DBAs, as well as many sysadmins and Operations staff, have known that automation is important for years. It helps to ensure a smooth running environment and helps us cope with the volume of work that is thrust upon us. There were a few interesting posts. Greg Dodd talks about [the advantages for his employer]( when he automates things, which is important to think about. Spending time automating things can slow down the initial closing of tickets, but it pays dividends in the future. It's an investment, which is something to think about when you try to reduce repetitive work. Especially if your boss is concerned about the time taken to solve some tickets. One of the big advantages of DevOps, as well as general automation, is consistency. [Taoib Ali explains how he enforces trace flags]( with automation, and [Kevin Chant talks about SQL Server updates](. [Deepthi Goguri explains how to handle DBA work at scale](. These are all situations where a little automation is not only useful, but perhaps essentially to reducing mistakes and human error. As we move to a larger number of versions to support, a great variety of platforms, including the cloud, it's critical that a DBA not be required to click around in SSMS or connect to lots of systems to manage them. Learning to automate can produce some great blog posts for your brand, give you interesting conversation ice breakers at events (or on social media), and generate some stories that will impress interviewers. If you aren't sure how to get started, consider reading [Eitan's Laws of Automation](. It's a look at what to automate, why, and a few ideas on implementing changes. Steve Jones - SSC Editor [Join the debate, and respond to the editorial on the forums](  [Redgate SQL Prompt]( 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. Vendors/3rd Party Products [Database Development with GitHub]( How can you use GitHub to do team-based database development? This article proposes a process that splits development work into task-based GitHub branches, incorporates daily database builds and integration testing, and using Redgate tools to automate tasks such as provisioning, database scripting, and testing. [Why you Should Embed Monitoring Earlier in Your Database DevOps Cycle]( With insights from a recent Gartner report, Redgate’s Jamie Wallis looks at the benefits of tracking, and acting on, key metrics early in the DevOps process and how they can apply equally to database monitoring. Read more. [Data Masker with SQL Data Catalog Integration Automates Data Protection Process]( Data Masker for SQL Server now integrates with SQL Data Catalog (seamlessly with our new GUI) to let you fully automate your data privacy and protection process, from data classification through to masking. AI/Machine Learning/Cognitive Services [Pair Programming with AI]( In a conversation with Kevlin Henney, we started talking about the kinds of user interfaces that might work for AI-assisted programming. This is a significant problem: neither of us... Administration [Automate to replicate]( I found this question harder to answer than other T-SQL Tuesday challenges, simply because I love automation so much ??. I've always been a fan of automating deployments, from batch scripts back-in-the-day to modern Azure Pipelines. With the help of pipelines, automated testing now seems like a no-brainer. I'm a data engineer, so many of my automation challenges have been in the areas of process control and code generation, often using dynamic T-SQL – it's this last one that I'm going to talk about here. [T-SQL Tuesday #120 – Automated restores]( It’s September 2020, and it’s time for T-SQL Tuesday. Our host, this time, is Elizabeth Noble (b|t). And her theme of choice is automation. Not automating the work of others, our day to day job, but automating our own work. To limit the risk of error, to reduce the stress, and simply to avoid boredom from doing the same thing over and over again. [Failing SQL Agent Jobs – Part 2]( Welcome to Part 2 in the series about SQL Server Agent Job Failures. In this part you are going to learn how to build an HTML report with Failed... The... [Cannot drop the database because it is being used for replication.]( I have been doing more and more replication work lately, and when recently testing some replication work I needed to create and drop some test databases and re-test the... [Troubleshooting Performance issues like Microsoft Engineers Part 3]( If you don’t read the previous parts you can rea... [Resizing Tempdb (When TEMPDB Wont Shrink)]( Occasionally, we must resize or realign our Tempdb log file (.ldf) or data files (.mdf or .ndf) due to a growth event that forces the file size out of... The... [FILESTREAM v FileTable –#SQLNewBlogger]( Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I was really interested in the FILESTREAM technology... The... [4 Times to use the Page Life Expectancy Performance Monitor Counter]( Microsoft SQL Server has a much-maligned performance monitoring (perfmon) counter called Page Life Expectancy (PLE). What this counter monitors is how long SQL Server will expect to keep your... [T-SQL Tuesday #120 – Automated restores]( It’s September 2020, and it’s time for T-SQL Tuesday. Our host, this time, is Elizabeth Noble (b|t). And her theme of choice is automation. Not automating the work of... Azure SQL Database [Automating DBCC CHECKDB for Azure SQL DB with Azure Functions]( Warning, this is a longer post from me than usual. The title is a mouthful and so is this post. In the past I have linked to blog posts... The... Azure Synapse (SQL Data Warehouse and Data Lake) [Data Lakehouse & Synapse]( I am starting to see this relatively new phrase, “Data Lakehouse”, being used in the data platform world. It’s the combination of “Data Lake” and “Data Warehouse”. In this... The... Career Growth and Certifications [The Ars Technica ultimate buying guide for your home office setup]( From monitors to standing desks, we find the best ... [Upcoming Free Webcast: 500-Level Career Guide: Building Your Brand]( At SQLBits 2020 on Saturday, October 3 at 11:10AM Eastern, 8:10AM Pacific (calendar), I’m presenting a session called 500-Level Career Guide: Building Your Brand. Here’s the abstract: If you... Conferences, Classes, Events, and Webinars [SQLpassion Online Training about SQL Server on VMware]( (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance... The... [Why Monitoring is key to Database DevOps Success]( Join Grant Fritchey and our expert panel as they discuss how automated database monitoring enables organizations to faster detect and resolve deployment issues. [The future of DevOps: Fully Left-shifted Deployments with Version Control and Automatic Cloning]( Discover how Redgate's latest database DevOps innovations empower developers to code in the IDEs of their choice, version control database changes in plain SQL, and easily validate their changes against a masked copy of production as soon as they make the change. DMO/SMO/Powershell [dbatools.io = command-line SQL Server Management Studio: Start/Stop]( dbatools commands used in this post: Get-DbaServic... Data Privacy, Compliance, and GDPR [How to make classifying SQL Server data easier]( Classifying the data within an organization is not just something nice to do. It’s critical for complying with regulations such as The General Data Protection Regulation (GDPR), California Consumer... Database Design, Theory and Development [Nonclustered Primary Key – SQL in Sixty Seconds #119]( I will describe in detail a bit later on during th... DevOps and Continuous Delivery (CI/CD) [Zero Downtime Database Deployments are a Lie]( Many people assume that building proficiency at database development and operations leads to the ability to attain "zero downtime deployments." In this post I share why "zero downtime" is... ETL/SSIS/Azure Data Factory/Biml [#tsql2sday #130 – Automate your stress away – Getting more SSIS Agent Job information]( Automation T-SQL Tuesday was started by Adam Machanic (blog|twitter) is hosted by a different person each month. The host selects the theme, and then the blogging begins. worldwide, on... The... Excel [T-SQL Tuesday #130: Automate Your Stress Away – Magda]( Hi, I am Magda and this is my first blog post. I h... HA/DR/Always On/Clustering [Distributed Availability Groups–The Good, the Bad, and the Ugly]( I’m writing this post because I’ve been mired in configuring a bunch of distributed availability groups for a client, and while the feature is technically solid, the lack of... Hardware [Synology DS1520+ is the sweet spot for a home NAS and a private cloud]( My long love of Synology products is well-document... Performance Tuning SQL Server [Starting SQL: Missing Index Blind Spots]( Underfoot There are all sorts of things that might artificially keep the optimizer from suggesting a helpful index while you’re writing or tuning a query. But go figure, there are also... [Starting SQL: Why Wouldn’t An Index Help?]( Pennies From Redmond We’re spoiled over here in SQL Server land. When we run queries, the optimizer will suggest indexes that might help it run faster. I say might, because... [Computed Column Performance in SQL Server]( Computed columns may be the cause of hard to track down performance issues. In this article, Robert Sheldon reviews the issues and some ways to get around them.… The post... [Starting SQL: Why Your Query Can’t Go Parallel, Table Variables Edition]( SURPRISE! The important thing to understand about parallelism is it’s great when appropriate. Striking a balance between what should go parallel, the CPU usage it’s allowed, and what should stay... PowerPivot/PowerQuery/PowerBI [Conditional Merge in Power Query]( Doing a merge operation in Power Query is pretty simple, just click the Merge Queries button, select your tables and field(s) and away you go. But what if you want to merge Table A to a subset of Table B? You can do it, but you need to tweak your M code manually in the Advanced Editor. [Connecting to Access fails in Power BI Desktop]( You are ready to connect to your Access database, ... [NEXT LEVEL Power BI Date Picker!]( Revisiting the Power BI Date Picker. Patrick takes... [The M Behind The New Power Query Data Types In Excel]( The big news this week – at least for me – was the release of the new Power Query data types to the Excel insiders channel. You can read... Product Reviews and Articles [Database Continuous Integration with SQL CI and Jenkins]( Continuous integration (CI) is the process of ensuring that all code and related resources in a development project are integrated regularly and tested by an automated build system. Code... [Questions about SQL Change Automation that you were Too Shy to Ask]( Phil Factor takes on tricky SQL Change Automation questions about database builds, migrations, deployments and releases. The post Questions about SQL Change Automation that you were Too Shy to Ask... [Automation Ideas for T-SQL Tuesday #130]( This month we have another great T-SQL Tuesday topic, and again, a host that I pressured into writing the invitation. Elizabeth Noble (@SQLZelda) and I were talking DevOps last... The... SQL [Hands-On with Columnstore Indexes: Part 4 Query Patterns]( All queries run fast against columnstore indexes, right? In this article, Edward Pollack demonstrates some query patterns that don’t perform well and how to get around the issues. [Understanding SQL Server Recovery Models]( The database recovery model controls how a SQL Server database can be backed up and restored. In this article, Greg Larsen explains the three recovery models and what to think about when choosing a recovery model for a database. SQL Server Security and Auditing [Automate Credit Card Audits]( In this article, I have shown how to use the power... Software Development [Seamlessly Swapping the API backend of the Netflix Android app]( How we migrated our Android endpoints out of a mon... [First Steps with Blazor]( Blazor allows you to create client-side code with C# instead of JavaScript. In this article, Julio Sampaio gets you started with your first Blazor project.… The post First Steps with... [How We Ended up with Git]( Git is used by many teams for version control. In this article, Dino Esposito takes a look back at the history of source control and how git became the... T-SQL [Fundamentals of table expressions, Part 6 – Recursive CTEs]( This month I cover the logical treatment of recursive CTEs. I describe T-SQL’s support for recursive CTEs, as well as standard elements that are not yet supported by T-SQL. I do provide logically equivalent T-SQL workarounds for the unsupported elements. [“Not to write any procedure over 50 lines”]( In Joe Celko’s Stairway to Database Design series, he writes: The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) This seems... [“UPDATE, INSERT, and DELETE are not normally processed in parallel”]( Years ago, when troubleshooting performance, I stumbled across this Microsoft documentation on parallel query processing that says: Certain types of statements cannot be processed in parallel unless they contain... The Lighter Side [When will we see ordinary people going into space?]( Every week, the readers of our space newsletter, The Airlock, send in their questions for space reporter Neel V. Patel to answer. This week: How the average person can go to space.... [Upgraded Helmut, My Porsche 911 Targa]( It’s been a year since I bought Helmut, my 2019 ... [General Motors will engineer and build Nikola’s hydrogen pickup]( Nikola will use GM's batteries and fuel cells, giving GM $2 billion in stock. Tools for Development [Split Screen for SSMS Efficiency – SQL in Sixty Seconds #120]( I often struggle to remember the changes done to t... Virtualization and Containers/Kubernetes [T-SQL Tuesday #130]( My invitation to you is I want to know what you ha... XML, XPATH and XQUERY [How SQL Server stores data types: XML]( This week we’re looking at how the database engine stores the XML data type in SQL Server and Azure SQL Database. If you would like to read about storage of... The...  [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. 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 follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -