Easy-to-read execution plans, an end to parameter sniffing pain, other impossible dreams (Database Weekly, 08/27/22)

Database Weekly for August 27, 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  Pick a Database, any Database… Having spent some years helping to define the IT strategy of a couple of well-known commercial organizations, you'd think I'd be able to tell you exactly the logical process by which an organization, or IT project, settles on their choice of database system. In fact, no. I've only rarely experienced an IT project where a rational decision was taken about the database product to use. Often it is difficult to detect anything more than a sort of 'group instinct'. Occasionally, it would be based on the existing skill set, or even the groundswell of opinion from the developers. Sometimes, it seemed to be the result of what we called, in code, the 'lobster lunch'. The higher echelons of the organization would return from a trip to a vendor, dazzled by a smooth marketing pitch and fine seafood, and announce the choice of database technology shortly after. This was often before deciding on the aims of the project, and its database requirements. Sadly, there is a wide difference between what the marketing people will tell you are the virtues of a relational database system and the aspects that appeal to the average commercial organization. In the broad view, fancy features hold little appeal to the IT manager, especially if they drift from the core purpose of a database. Instead, they value responsiveness of queries, ease of use and rapid delivery of applications. From this perspective, an expensive database system can be a lot cheaper on the budget of an IT project. Most of the considerable costs of maintaining a leading relational database system are incurred almost invisibly by the vendor in pursuit of performance, conformance, and reliability at scale; You are paying for the virtues you need rather than the ones that initially seem attractive. There are also other 'hidden costs' in purchasing a database system. For example, if the database system you're considering still uses arcane and inconsistent ways of implementing procedures and functions, then the extra development costs of building a database application can soon outweigh the up-front license costs. If you must hire 'consultant' developers to fix inexplicably slow query performance, you are soon losing control of your budget. You will quickly regret an injudicious purchase of a database system. It is a bit like buying a fancy-looking car at a knockdown price. It looks the part in the driveway, but what you're really buying is the hassle and distraction of frequent trips to the garage. Phil Factor Tony Davis [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 [Managing Datasets for Database Development Work using Flyway]( A generic way of exporting, deleting and loading data, for database development work. It uses Flyway Teams, a PowerShell framework, JSON files for storage and a table manifest to define the correct order of dependency for each task. It should help a team maintain datasets between database versions, as well as to switch between the datasets required to support different types of testing. [The Uses of Dependency Information in Database Development]( Dependency information will allow you to avoid errors during a database build or tear-down, by ensuring you create or remove objects in the right order. It will also help you to avoid future 'invalid object' errors, because it will allow you to check that no database alterations have introduced broken references, during Flyway migrations. Administration of SQL Server [How to Install SQL Server 2022 Release Candidate 0]( The first release candidate for Microsoft SQL Server 2022 is out, so let’s see what’s involved with installing it. [Some Interesting Questions And Answers Of Mine On Database Administrator’s Stack Exchange About SQL Server]( Erik Darling does a quick roundup on some of the more interesting Q&A he's taken part in on dba.stackexchange.com recently...and realizes he should get out more! [Log Shipping – Standby Mode]( How to use the long trusted Log Shipping technology, with tweaks to a few settings, to get a read-only copy of a database. Azure Databricks, Spark and Snowflake [First impressions of the web terminal in Azure Databricks]( On needing to investigate issues with mount points not appearing in Spark clusters, Kevin Chant discovered the web terminal for Azure Databricks. Azure SQL [Automated key rotation for TDE BYOK now available in preview for Azure SQL!]( When using TDE with Customer-Managed Key, one of the important responsibilities that customers need to perform on a regular basis is key rotation, that is, rotating the TDE Protector on the server by switching to a new key (or new version of the earlier key) from Azure Key Vault. Azure SQL Managed Instance [Arc Enabled Data Services 7: Azure SQL Managed Instance]( Warwick Rudd explores the differences between an installation of Azure SQL Managed Instance and Azure Arc-enabled SQL Managed Instance. Azure Synapse (SQL Data Warehouse and Data Lake) [File tests for Azure Synapse Link for SQL Server 2022]( Does Azure Synapse Link for SQL Server 2022 only create csv files, or is there a tipping point where it starts to create parquet files? Kevin Chant investigates. Computing in the Cloud (Azure, Google, AWS) [There’s hope, there’s a silver lining, show me my max server memory…]( Adding a read replica into a AWS RDS instance led to all sorts strange mssqlsystemresource-related errors and connection errors. The cause? Max Server Memory was set too high and stealing from the OS, causing memory to thrash and things to crash. JSON [OPENJSON Performance]( There are two options for using OPENJSON: with the default schema or with an explicit schema. There are performance implications for each, which I'll review with some examples. MDX/DAX [DAX Studio 3 released!]( This new, major version offers a new user interface and user experience, providing a modern look and feel without losing any of the features available in previous versions. [Why I’m Excited About The New DAX EvaluateAndLog() Function]( Chris Webb on why the new, as yet undocumented, EvaluateAndLog() function might be something to get excited about. Performance Tuning SQL Server [Too Many Plans for the Same Query Hash]( In today’s post, I’ll be providing a useful script to detect and troubleshoot when the query plan cache contains too many different plans for the same query hash, which could happen as a result of possible parameterization issues. [Trivial Plans Are Not Always the Best Plans]( Sometimes, aquery is so simple that the optimizer doesn't bother weighing out different plans before returning results; it just uses a trivial plan. But is it the best plan? [Different Ways To Parameterize Queries In SQL Server]( How to do parameterization properly, even when you're executing dynamic SQL. [How Microsoft Could Make Problems In Execution Plans Easier To Understand]( Look! Look! I am a Non-SARGable Predicate! [Why Parallel Queries are Sometimes Slow In SQL Server]( You may have noticed large variations in elapsed time for parallel queries while performing query tuning or observing a production workload. This post reviews some of the possible explanations for those variations. PowerPivot/PowerQuery/PowerBI [Azure Machine Learning Call API from Power Query]( How to call Azure ML Rest API inside Power Query, even if you don’t have Power BI Premium. [Long term storage of Power BI activity logs and statistics using Powershell]( Power BI gives you out-of-the-box statistics and end users’ activities. But the retention period is 90 days. Tomaz Kastrun provides a script that combines all workplaces into a single SQL Server table and extracts activity logs per day. And you can keep your logs for as long as you need them. [Calling The Power BI Enhanced Refresh API From Power Automate, Part 3: Incremental Refresh Options]( An examination of the two parameters in the Enhanced Refresh API for datasets that use incremental refresh: applyRefreshPolicy and effectiveDate. [Power BI Data Driven Subscriptions with Power Automate (Report Bursting)]( Let’s say you want to send a PDF version of a report to each of your store or department managers using a set of parameter values specific to each person. In the Power BI service that is not an option, but using Power Automate you can do this. Product Reviews and Articles [Book Review: SQL Server Advanced Troubleshooting and Performance Tuning]( This book aims to improve the performance of your SQL Servers, how does it fare? R Language [Passing a query string value to R Markdown]( A way to pass and read values from a query string in R Markdown, which works on a development computer with RStudio or on a Shiny server. SQL Server News [SQL Server 2022 Release Candidate 0 is now available]( Announcement of SQL Server 2022 Release Candidate 0, with a brief summary of new features and capabilities. SQL Server Security and Auditing [Transparent Data Encryption (TDE)]( In this post I’ll explain what TDE is, along with its use cases, and I’ll use a thorough demo to show how to implement it in a database and how it works [New granular permissions for SQL Server 2022 and Azure SQL to improve adherence with PoLP]( As part of recent efforts to make it easier for users to adhere to the Principle of least Privilege, all new features in SQL Server 2022, can be controlled with more granular permissions, including SQL Ledger. [Why is security important in a dev environment?]( You need to make sure a database application is written and tested, in your dev and test environments, with the same set of permissions it will have in production Security News and Issues [Efficient 'MagicWeb' Malware Subverts AD FS Authentication, Microsoft Warns]( The Russia-backed Nobelium APT has pioneered a post-exploitation tool allowing attackers to authenticate as any user. T-SQL and Query Languages [Explicit and implicit datatype conversions]( Did you know that 1/2=0? If you didn’t you could be in for some interesting surprises. [PSPO: How SQL Server 2022 Tries to Fix Parameter Sniffing]( SQL Server 2002 uses Parameter Sensitive Plan optimization (PSPO) to try to fix problems caused by parameter sniffing. Does it work? As a hint, Brent pronounces it as pss-poh... [Some Notes on sp_prepare And SQL Server 2022]( When you use sp_prepare, parameterized queries behave differently from normal and you often get inaccurate cardinality estimates. But this behaviour changes in SQL Server 2022. [What’s New in SQL Server 2022 Release Candidate 0: Undocumented Stuff]( Microsoft has an official list of what’s new in 2022 overall, but here I’m specifically focusing on system objects that might be interesting to script developers. [A simple lab to demonstrate the danger of NOLOCKs in INSERT statements]( The READ UNCOMMITTED isolation level, which the NOLOCK table hint activates, is not to be trusted when it comes to writing data. You are not always protected by error 1065 in cases where writes meet NOLOCK. Tools for Dev (SSMS, ADS, VS, etc.) [SSMS – How to get two rows of tabs]( If you spend much time at all in SQL Server Management Studio (SSMS), you’ve no doubt had trouble figuring out which query tab is the one you need. The "Save pinned tabs as separate row" option in SSMS can help. [Azure Data Studio August Release]( Support for Ledger Objects, Query Plan Viewer and Table Designer Updates and more Virtualization and Containers/Kubernetes [Microsoft continues its push to get more of its own services hosted on Azure. And Kubernetes is key]( Microsoft provides a rare glimpse into its more than 10-year effort to move its Office 365 and Microsoft 365 services to Azure.  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -