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

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

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.

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 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.

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...

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.

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.

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...

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...

If you don’t read the previous parts you can rea...

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...

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...

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...

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...

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...

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...

From monitors to standing desks, we find the best ...

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...

(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...

Join Grant Fritchey and our expert panel as they discuss how automated database monitoring enables organizations to faster detect and resolve deployment issues.

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.

dbatools commands used in this post: Get-DbaServic...

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...

I will describe in detail a bit later on during th...

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...

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...

Hi, I am Magda and this is my first blog post. I h...

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...

My long love of Synology products is well-document...

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...

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 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...

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...

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.

You are ready to connect to your Access database, ...

Revisiting the Power BI Date Picker. Patrick takes...

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...

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...

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...

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...

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.

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.

In this article, I have shown how to use the power...

How we migrated our Android endpoints out of a mon...

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...

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...

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.

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...

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...

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....

It’s been a year since I bought Helmut, my 2019 ...

Nikola will use GM's batteries and fuel cells, giving GM $2 billion in stock.

I often struggle to remember the changes done to t...

My invitation to you is I want to know what you ha...

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...