Troubleshooting SQL Server Queries using Actual Execution Plans (2024-02-03 DBW)

Database Weekly for February 3, 2024 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  More Private Clouds Last year the [Basecamp people made a big deal about leaving the cloud]( (and I wrote [a bit about their learnings](. I still see record earnings for cloud vendors and no shortage of Redgate customers continuing to move workloads to the cloud, so maybe not everything thinks the cloud is bad. However, I do also see some movement away from the cloud, which might be back to some sort of data center, though usually not on-premises at a company. More often than not, companies that move away are still renting VMs or space from someone else who manages the core infrastructure (building, power, cooling, network, etc.). I saw an article this week that said [we may see more private clouds in 2024]( which I think is likely. Even as companies may leave the cloud, or reconsider their plans to move, I do think that the idea of platforms and services where users can get more done in a simpler way is growing. Why was the cloud attractive? Often because someone didn't need to go through corporate procurement or red-tape to get a server stood up. They could purchase a database in minutes as opposed to opening a ticket and waiting on someone to triage it, open it, work on it, take a break, finish their work, and finally notify the requestor. I've been doing some work on [Platform Engineering]( which felt like a new area (and new titles) that were rebranding, but I am coming to see these as an internal way to provide services, templates, and more so that the individual developer, DBA, or other tech professional doesn't need to know everything about creating or managing the service. Many of the platform services might be built in the cloud, but they could easily be built on local (ish) hardware the company owns (or rents). In fact, I've seen a few presentations about companies that have built a customized platform that makes software development and deployment easier than ever before, while ensuring corporate standards around security, DevOps, and even production operations are followed. I think that as new tools have been written to allow people to configure, create, and manage more systems at scale, the idea of building a platform of sorts for your development or operations teams might be more feasible. I think this is still more likely at large organizations, but I learned a long time ago about the value of self-service for customers, especially internal ones, in creating efficiencies. I'm happy for most people to request and deploy the things they need, with some reasonable limits. I'd certainly prefer that they use my template for SQL Server databases (with sa disabled, Windows auth required, backups set, and more) than click-click-click the installer and fumble around learning how easy it is to poorly configure a SQL Server. There is value in the cloud, but you should carefully decide if there is value for your organization in the cloud, if you should continue with a simpler data center, or if you might benefit from creating a platform for your IT grou;. Steve Jones - SSC Editor [Join the debate, and respond to the 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. Vendors/3rd Party Products [Troubleshooting SQL Server Queries using Actual Execution Plans]( For SQL Server 2019 onwards, SQL Monitor can now show the actual execution plan, complete with runtime statistics, for expensive queries that were running over any period of investigation. This means you'll not only know how SQL Server executed your poorly performing query but also see accurate runtime data. Any big discrepancies between estimated and actual row counts will be revealed, and SQL Monitor will highlight any warnings and offer recommendations. [Integrating Redgate Monitor Alerts with New Relic]( This article demonstrates how to use a script notification, in PowerShell, to automatically send database alerts raised by Redgate Monitor as incidents to New Relic. This simple integration makes it easy to feed the detailed diagnostics that only a specialized database monitoring system such as Redgate Monitor provides, and the DBA team relies on for a rapid response, into a single, organization-wide incident management system. AI/Machine Learning/Cognitive Services [Data in AI Evolution: The Roles of Relational Databases and Data Lakes]( From DBAKevlar AI has become a buzzword synonymous with the futur... [System Message (Prompt) Example for Safety – ChatGPT]( From tsmatz In this post, I'll briefly show you risk mitigation architecture for adversarial prompting and prompt's example for safety. [AI in 2024: The Other Side]( From Dataversity I’ve made a lot of predictions about how AI will change programming. Hacking code will be less important than understanding problems, we’ll have better tools for generating code, higher-level skills... Administration of SQL Server [Search for queries with Query Store GUI]( From SQLServerCentral Blogs Hey folks, long time no write! Today I want to bri... [Understanding and Using xp_sqlagent_enum_jobs in SQL Server]( From SQLServerCentral Blogs Understanding and Using xp_sqlagent_enum_jobs in S... [How to Find Where Your Databases Reside In The File System]( From SQLServerCentral Blogs How to Find Where Your Databases Reside In The Fil... [DBAs, Give Git a Try – Getting Started]( From SQLServerCentral Blogs One of the really interesting things over the last... [SQL Server 2022 Cumulative Update 11]( From Glenn Berry On January 11, 2024, Microsoft released SQL Server... [Query Store: How to open multiple instances of the 'Tracked Queries' dashboard]( From SQLServerCentral Blogs At the beginning of this week, I shared how you can Search for queries with Query Store GUI . Today I want to share something, Query Store related, that bugged... The... [sp_snapshot – The easy way to take database snapshots of one of more databases – Version 2.0]( From SQL Undercover Presenting you with an updated version of our sp_snapshot procedure, to easily create database snapshots. This new version adds more flexibility to the procedure, allowing you to specify the... Azure Databricks, Spark and Snowflake [Moving towards the DataFrame API using the Spark Connect gRPC API in .NET]( From AgileSQL - Ed Elliot All Spark Connect Posts Code Goal of this post So ... Azure SQL [Darling Data SQL Server Stored Procs in Azure SQL Database]( From SQLServerCentral Blogs Erik Darling, founder of Darling Data, has created... [Save money with Arc SQL Server licensing - what you need to know | Data Exposed]( From Azure SQL If you are used to traditional licensing options and pay for software assurance, you may wonder why you would ever use pay-as-you-go billing. In this episode of Data Exposed... Azure SQL Managed Instance [How to create linked servers from SQL managed instance using Entra Authentication (part 1)]( From Azure Database Support Blog Lately, we have seen some customers interested on ... [More vCore options for SQL MI Business Critical for better price/performance and flexibility]( From Azure SQL Recently, we announced a number of Azure SQL Manag... Career, Employment, and Certifications [Who’s Hiring in the Microsoft Data Platform Community? February 2024 Edition]( From Brent Ozar Unlimited Is your company hiring for a database position as ... [Four simple facts about the DP-600 exam]( From Kevin Chant Reading Time: 3 minutes In this post I cover four simple facts about the DP-600 exam. I want to do this post because there has been a lot... Conferences, Classes, Events, and Webinars [What would you like to see at Summit 2024?]( What challenges would you like help with in 2024? What speakers would you like to present? Take part in our survey, and your input will ensure Summit delivers the most relevant topics and speakers to the data community. As a thank you for taking part you’ll have the chance to opt-into a prize draw to win a free 3-day pass* to Summit 2024! The survey closes February 19. [BI Training with Eugene Meidinger]( From SQLServerCentral Blogs I’ve known Eugene Meidinger (SQLGene) for a numb... [THAT Conference 2024–Austin]( From SQLServerCentral Blogs This week I attended THAT Conference in Round Rock... [(Livestream Replay) Fabric Lakehouse Hell...or Glory?!? - with Tom Martens]( From Havens Consulting DESCRIPTION During the last months, we have heard a lot about Microsoft Fabric and how it will change the analytical landscape. How it will help solve the most demanding... DMO/SMO/Powershell [Running Powershell for SQL Use* in Parallel - POshRBAR no more.]( This is one that I should have learnt way earlier than I actually did if i'm being completely honest. One of my colleagues wrote a cool function for multithreading, and although this isn't it, it did made me look into how it all worked. [How To Use PowerShell To Create PDF Files]( From IT Pro - Microsoft Windows Information, Solutions, Tools PowerShell excels at exporting data, but it lacks native support for creating PDF files. Here are three techniques to overcome this limitation. Data Visualisation [sketch your data]( From Storytelling with Data I’m a huge proponent of low-tech planning prior ... [#SWDchallenge: recommended reading]( From Storytelling with Data When I started using graphs early in my career and... Data Warehousing [Data Mesh Topologies]( From SQLServerCentral Blogs As a follow up to my blog Common Data Mesh excepti... Database Design, Theory and Development [Content Management System Architecture]( A Content Management System (CMS) is a software application that allows users to handle digital content on a website without specialized technical knowledge. It provides an intuitive interface for non-technical users to add, edit, and organize content such as text, images, videos, and other multimedia elements. DocumentDB/Key-Value/Graph/other NoSQL Databases [Getting Started with MongoDB]( MongoDB is a scalable database management system that stores data as documents in a collection, rather than as rows in a table, such as you’d find in a relational database system. A document is an object made up of one or more field and value pairs that contain related data. A field’s value can be one of a variety of data types, including arrays or embedded documents. Support for such data types eliminates the need to create the type of complex joins used in relational database. Editorial [Autonomous Databases?]( I was talking to some people today about the concept of Autonomous Databases. The idea is that the database platform can do most, if not all, of the work of storing and managing data by itself. MDX/DAX [Differences between DATEADD and PARALLELPERIOD in DAX]( From Sqlbi The different results of DATEADD and PARALLELPERIO... [Power BI Tips: Excluding Top N with DAX]( From Guy in a Cube Someone asked how to exclude Top N results within ... Microsoft Fabric ( Azure Synapse Analytics, OneLake, ADLS, Data Science) [Data MVPs and Microsoft Fabric Super Users in the Era of AI | Data Exposed: MVP Edition]( From Azure SQL Taking a step back in this episode of Data Exposed... [Use Source Control with your Power BI Report in Microsoft Fabric!]( From Guy in a Cube People have been asking for source control with Po... [Office Hours: PowerBI & Fabric Q&A with Eugene Meidinger]( From Brent Ozar Unlimited Today’s Office Hours has a different topic. Euge... Oracle/PostgreSQL/MySQL/other RDBMS [Francesco Tisiot: Load StackOverflow's StackExchange data in Postgresql®]( From Planet Postgres I recently found about the StackOverflow dataset i... [Adam Hendel: Automate vector search in Postgres with any Hugging Face transformer]( From Planet Postgres Sentence transformers have revolutionized text processing in machine learning and AI by converting raw text into numerical vectors, enabling applications like vector similarity searches and semantic search. With pg_vectorize,... [Francesco Tisiot: How to load JSON data in PostgreSQL with the the COPY command]( From Planet Postgres You have a JSON dataset that you want to upload to a PostgreSQL table containing properly formatted rows and columns... How do you do it? All the main... Performance Tuning SQL Server [Indexing SQL Server Queries For Performance: Fixing Blocking and Deadlocking]( From Erik Darling Data Okay, Look Before I go on about how adding an inde... [What is Join Elimination?]( From Callihan Data SQL Server can be pretty smart when it comes to avoiding work it’s not required to do. One instance of this is the concept of join elimination. Join elimination... PowerPivot/PowerQuery/PowerBI [Avoiding HIDDEN Visual-Level Filters with Field Parameters]( From Havens Consulting Video by: Reid HavensLearn how to hide all the vis... [Scientific Calculations in Power BI - Cube, Square, Logarithms, Powers, Inverse and more]( From MSSQL Tips In this article, we look at options to use scienti... [CI/CD & DevOps for Power BI… Are We There Yet?]( From Paul Turley's SQL Server BI Blog In my view, projects and teams of different sizes have different needs. I described DevOps maturity as a pyramid, where most projects don't require a sophisticated DevOps implementation, and... Product Reviews and Articles [Integrating Redgate Monitor Alerts with New Relic]( From Product learning – Redgate Software This article demonstrates how to use a script noti... [Friday Flyway Tips–Navigating in Flyway Desktop]( From SQLServerCentral Blogs Flyway Desktop changed the navigation and a few people were surprised. I was as well, so I decided to do a short tip on the changes. This is part... The... Product Upgrades and Releases [Released: Microsoft.Data.SqlClient 5.2 Preview 5]( From MS SQL Server Blog Microsoft.Data.SqlClient 5.2 Preview 5 has been re... [General Availability: Read replica in special regions with Azure Database for PostgreSQL - Flexible Server]( From Azure Updates Read replica now available in Microsoft Azure oper... [General availability: Azure SQL updates for late-January 2024]( From Azure Updates General availability enhancements and updates rele... [General Availability: Server logs for Azure Database for PostgreSQL - Flexible Server]( From Azure Updates Enhance your database monitoring with the new serv... [General availability: Azure Cosmos DB for PostgreSQL Customer-managed keys (CMK)]( From Azure Updates Customer-managed keys allow you to use your own ke... [Generally Available: Support for Cross Region Restore for PostgreSQL backups]( From Azure Updates Cross region for Backup vault using Azure Backup i... [Visual Studio Code extension update for Azure Kubernetes Service (AKS)]( From Azure Updates You can use VS Code extension update for AKS to in... [Generally Available: Continuous model monitoring in Azure Machine Learning]( From Azure Updates Azure Machine Learning model monitoring is now generally available. R Language [Prevent dropping from non-occuring levels using dplyr]( 1 Load packages 2 Problem 3 Solution 4 Reproducibility [How to Extract Month from Date in R (With Examples)]( Greetings fellow R enthusiasts! Today, we’re diving into a fundamental task: extracting the month from a date in R. Whether you’re new to R or a seasoned pro, understanding how to manipulate dates is essential. We’ll explore two popular methods: using base R and the powerful lubridate package. So, let’s roll up our sleeves and get started! SQL Server on Linux [Video: Active Directory and SQL Server on Linux]( In this video, we will configure SQL Server on Linux to use Active Directory and enable Windows authentication, whether you’re connecting from Windows or Linux. Software Development [The art of good code review]( This post is an extension to a talk I gave recently at work. It was arranged at short notice and the audience were experienced engineers, so I kept it brief and tried not to be patronising. But the feedback afterwards was quite positive and there were some questions too, so here's the extended version for anyone interested. [Feature Flags in Data Projects]( From Simple Talk My motivation for writing this summary was an inte... [Why Test-Driven Development? (Part 2)]( From Simple Talk As discussed in Part 1, by segregating our focus on developing code that behaves as expected from the focus on developing code that will be well structured, we have... T-SQL and Query Languages [Optimising DISTINCT Clauses using EXISTS]( From Andy Broadsword The DISTINCT clause in a query can help us quick... [bit Obscene Episode 1: What Developers Need To Know About Transactions]( From Erik Darling Data bit Obscene Episode 1: What Developers Need To Know About Transactions In the inaugural episode, Joe Obbish and Erik Darling talk about how developers use and abuse transactions, and... Tech News [Why Private Clouds May See a Resurgence in 2024]( From IT Pro - Microsoft Windows Information, Solutions, Tools Expect trends such as AI and the economic turbulence that is affecting organizations' spending to drive an increased interest in private cloud. [Big 3 Cloud Providers Continue to Outperform as AI Drives Growth]( From IT Pro - Microsoft Windows Information, Solutions, Tools AWS, Microsoft, and Google continue to point toward AI as a driver of growth, but there is more to cloud gains, including security and partnerships. The Lighter Side [A New Word: Tarrion]( From SQLServerCentral Blogs tarrion – n. an odd interval of blankness you feel after something big happens to you bt before you feel the resulting emotional reaction – stunned by a sudden... The... Virtualization and Containers/Kubernetes [COPY vs ADD in Docker: A Detailed Comparison]( From Darren Gosbell [MVP] - Random Procrastination When setting the stage for smooth Docker container... [The Docker debug command]( From DBA From The Cold In the latest version of Docker Desktop a new comm...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -