Optimizing Azure SQL Database, DevOps Collaboration, Invisible Indexes and more in Database Weekly (07/22/22)

Database Weekly for July 23, 2022 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  Query Tuning, Why Bother? There are simply tons of resources on query tuning. I’ve written, in whole or in part, several different books on the subject. There are other books too. Videos. Articles. Blog posts. Online classes. In-person classes. Probably millions, if not billions, of words on this topic. Further, there are more coming. I’m just going to say it. Why? A few of us, an exceedingly tiny few, will be able to spend the time. First, the time to learn how to gather query metrics, understand T-SQL, read execution plans, and understand how the query optimizer works within the SQL Server engine. Second, the time to practice these skills because you can’t just read a book, watch a video or attend a class and be able to do this. You will have to practice to get the skills down. Finally, the time to actually do the work. It’s not easy. It is time consuming. Here's the deal, most of your businesses, they don’t care. Oh, they want everything to run fast, of course. However, go off to training? No. You’re needed here. Spend time studying and practicing? No, we really need your time spent on building new servers or deploying new code or whatever else they’re going to say has priority. Make things faster? Absolutely. What’s that? How long? No. You can’t do that. More than this, most query tuning involves two things, because this is where the problems are, changing structures and changing code. For many, maybe even most, organizations, that is absolutely a non-starter. Maybe they’re running third-party software, so they can’t change the code, can’t change the structures. Maybe it’s ancient code put together at the dawn of the PC age by someone who retired 25 years ago and if it’s not broke, don’t even breathe on it hard. Maybe your development team refuses to work with you and you’ve got bad ORM code on top of an object-relational database (when you build your relational database out of objects instead of tables, nightmare). Whatever it might be, you’re not changing the code or the structures. With all this, what happens? You buy bigger hardware. You buy more hardware. You go up to the next service tier in the cloud. Heck, Microsoft comes to your rescue with adaptive and intelligent query processing (wonderful stuff, truly, but not an actual panacea). You’re simply not going to be given that time. So, why do so many people spend so much time on query tuning? Why are they always the most well-attended sessions at events, all over the world? What is it about this thing that far too many of us will never be able to do that we’re all spending insane amounts of time learning? I’m truly curious. If you know, please share. Grant Fritchey [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 [A day in the life of a DBA using SQL Monitor: in a 90 second video!]( We created a new, short video to show how a DBA can use SQL Monitor in their day to day work, including sharing reports with senior leaders. Watch it now. [DevOps Collaboration and Process Visibility in Flyway Developments]( A brief history of the DevOps movement and a discussion of the pivotal role of a tool like Flyway in the DevOps toolchain, when developing and delivering database changes. [Taming Database Documentation with Flyway and MySQL]( Database object documentation is essential for explaining to busy developers, and the wider business, the purpose of each object and how to use it. The solution presented in this article consists of a SQL script to allow developers to add comments to MySQL database objects, without affecting the database version, and a simple way to generate a documentation report, in JSON. The SQL script will execute automatically as a callback, during any Flyway Teams migration run, and the report will allow the team to spot any gaps quickly Administration of SQL Server [The SQL Server Transaction Log, Part 4: Log Records]( Previously I’ve talked about why logging is required and the architecture and circular nature of the log, so now it’s time to look at the real heart of the logging system—the log records themselves. [Update Job Step Across Multiple SQL Servers]( Logging into multiples servers to make the same update to the command of a job step can be time consuming. Thankfully, there are better methods. [Introduction to Network Trace Analysis Part 1: Asking Questions and Collecting Data]( Asking questions is just as important as running network traces. By clarifying who is talking we can cut out noise, clarifying how we get there helps us avoid rabbit holes, clarifying the language helps us understand what the rules are for their conversation. Azure Databricks, Spark and Snowflake [Snowflake Architecture – The Basics]( Snowflake has 3 important components. The Cloud services layer, centralised storage layer and the compute layer. Azure SQL [Oracle Database Service on Azure]( A technical perspective of Oracle's new database service for Microsoft Azure [Lesson Learned #225: Has an unresolved reference to object using Linked Server or External Table]( Presenting a stored procedure that will create dynamically the external table and/or view or synonym that is calling external tables and therefore causing the error message. [Optimizing Azure SQL Database with the Well-Architected Framework]( Design considerations, checklists, and detailed configuration recommendations for deploying optimal Azure SQL workloads. Azure Synapse (SQL Data Warehouse and Data Lake) [How to use CETAS on serverless SQL pool to improve performance and automatically recreate it]( You can use a serverless SQL pool to create a CETAS which will materialize the query results. It means a heavy query in which, for example, the results would be part of future large join with other queries or aggregations that can be consolidated for reporting purposes. [Database Project for use with Azure Synapse Link for SQL Server 2022]( How to create a database project for a database running in SQL Server 2022 which is the source for Azure Synapse Link Backup and Recovery [3 ways to verify that a backup has taken place]( Query msdb, use the Last database Backup property in SSMS or check the contents of the backup directory! Data Mining / Data Analysis [My plea for tick marks]( Tick marks and gridlines may seem trivial, but even minor changes can have a major impact on the readability of your graph. Data Privacy, Compliance, and Governance [Data Audit in the Age of Machine Learning: Goals and Challenges]( What does the growing “plug-and-play” analytical culture mean for data audits? Data Science [SQL: The Universal Solvent for REST APIs]( What if there were a way of reading from APIs that abstracted all the low-level grunt work and worked the same way everywhere? Good news! That is exactly what Steampipe does. It’s a tool that translates REST API calls directly into SQL tables. DevOps and Continuous Delivery (CI/CD) [Introduction to Testing a Flyway Development]( How to use Flyway Teams to run basic tests whenever it successfully executes a migration, checking that all the business processes supported by our database always produce the expected results. [Metrics that matter for IT organizations on an agile journey]( Measuring the wrong things is worse than not measuring anything. In this article, Mallika Gunturu explains the right things to measure for agile MDX/DAX [Rolling average with working days in DAX]( Rolling averages are a very common calculation used to smooth out charts. This article shows how to compute a rolling average taking into account only the working days. Oracle/PostgreSQL/MySQL/other RDBMS [Security in MySQL: Part Two]( There’s more to security in MySQL than user account privileges. In this article, Lukas Vileikis explains the other components of MySQL security. Performance Tuning SQL Server [Updates To sp_PressureDetector]( A few nice updates to Erik Darling's sp_pressuredetectorm including total physical memory in the server and additional CPU details. [Improve scalability with system page latch concurrency enhancements in SQL Server 2022]( In SQL Server 2022 we are introducing concurrent global allocation map (GAM) and shared global allocation map (SGAM) updates which will give SQL Server 2022 a big improvement for scalability of tempdb. [Updates to sp_QuickieStore]( Erik Darling adds new search functionality to his sp_QuickieStore procedure, making it even easier to dig into Query Store data to find queries to tune, [Storing sp_BlitzIndex to a Table Between Reboots]( How to store current index usage stats up to the midnight before a reboot of my SQL instances, for analysis. PowerPivot/PowerQuery/PowerBI [Power BI: Dynamically Removing Errors From Columns in M]( End users that are not trained in data governance but are actively involved in maintaining a data set can easily make data entry mistakes or create inconsistent data types within columns. [Datasets vs Datamarts]( Shortcuts are tempting and disguise themselves as cost-effective. If you’re a data analyst that doesn’t know or can’t afford any better, surely take the data source->dataset approach. Teo Lachev explains why this shortcut might not work out so well. [Field Parameters Using Measures]( A really cool way of implementing Field Parameters in Power BI reports. [Slowly Changing Dimension (SCD) in Power BI, Part 1, Introduction to SCD]( An introduction to the concept of SCDs and ways to handle them in Power BI. Reporting Services [SSRS Subscription E-Mail Error]( When you create a new subscription, the record is stored in the ReportServer databases Subscription table with the login account that created the subscription. If the account does not have an email address associated with it or does not have permission to send an email, your subscription will fail. SQL Server Security and Auditing [A Simple SQL Server Security Checklist]( A 12-point checklist of scripts to review your server’s current security posture. Software Development [The ultimate guide to web accessibility]( Accessibility ensures that everyone has a great experience on your site. Bikkani explains how to achieve web accessibility. T-SQL and Query Languages [A Useful Script to Delete Data from Multiple Tables]( Sometimes you need to delete data from multiple tables in a database. For example, you might have a multi-tenant database, and you need to delete all the data that belong to some tenant. [SSMS Put Pinned Tabs in their own Row]( Kenneth Fisher discovers a handy SSMS option to keep pinned tabs separate [SQL Server Pop Quiz: A Key Lookup Without the Index]( Brent Ozar sets a challenge: get a key lookup to show up in an execution plan without having an index seek or index scan operation on the same table [How to Add Invisible Indexes in SQL Server Enterprise Edition]( Every now and then, a client says, “We want to add indexes to this third party application, but if we do, we’ll lose support.” No problem – enter indexed views. [Introduction to new the SELECT WINDOW Clause in SQL Server]( Why the SELECT...WINDOW Clause should help make the WINDOW Operations more user-friendly [Turn your list into human-readable intervals]( If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.  [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -