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