Has SQL Server Performance Improved? (2023-04-01 DBW)

Database Weekly for April 1, 2023 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  Has SQL Server Performance Improved? I saw a very interesting blog post this week from Brent Ozar that asked if [SQL Server 2019 More CPU-Intensive Than SQL Server 2016?]( You can read the post, but a client was having CPU issues and thought everything between their SQL Server 2016 and 2019 environments was the same. Brent decided to test this and found that on identical systems, he has queries taking more CPU on SQL 2019 than 2016. A few commenters tested as well, with similar results. Not everyone had similar results, but most did. You can get the scripts from the post and try it yourself if you have SQL 2016 and 2019 instances. One would expect that more recent versions would run faster, or use fewer resources, for equivalent data sets and hardware. I know that's not always the case, but it ought to be the case for lots of workloads. If not, then arguably the newer version isn't better. It likely isn't worth more money, and definitely needs more development work. This is my view of Windows 11, which seems to have returned to the habit of earlier Windows version of requiring and consuming more resources than its predecessor. I don't often benchmark or evaluate SQL Server version. I don't have to make those decisions, but I have seen SQL Server continue to improve on [the TPC-E benchmark](. However, this isn't necessarily the same hardware. In fact, across versions, it likely isn't. There could be more CPU consumed by the same queries, masked by hardware advances (and falling hardware prices). Is SQL Server using more CPU in newer versions? I'll let you see if that's the case on your systems. Even if it isn't, you might document some queries (in addition to Brent's) and record the results. That might help you decide when you upgrade. 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 [Why a monitoring tool is an essential tool for growing server estates]( As we continue into the era of supporting more and more database platforms in our data estates, self-monitoring goes from being a very difficult task to a truly daunting task. A monitoring tool can help DBAs keep track of things like drive space, licensing, and version end-of-support that can aid in budget discussions. Best of all, a software company offering monitoring tools will almost certainly keep up with the changes to the various performance monitoring options that all the RDBMS vendors support over time as new versions are released. [How to Automate Cross-Platform Database Development]( In order to focus on their primary task of developing databases, the development team need to automate as many as possible of the routine tasks that are essential for database delivery, such as testing, scripting, version control, documentation, code review, reporting and so on. This article gives some advice on how to do it, faced with the added challenge of needing to use several different relational databases. [Working with Flyway And Entity Framework Code First: An Overview]( This article presents an approach to database development and deployment that combines the strengths of Entry Framework Code First for .NET-driven development with the control and database versioning provided by Flyway's SQL migrations. It allows every database change to be reviewed and tested for integrity, performance, and stability in the same way as any application change. It should make a Database CI process much easier to sustain. AI/Machine Learning/Cognitive Services [Autonomous Driving Around London]( From SQLServerCentral Blogs There’s a video of Bill Gates taking a drive in ... [What if we could just ask AI to be less biased?]( From Technology Review Feed - Tech Review Top Stories This story originally appeared in The Algorithm, our weekly newsletter on AI. To get stories like this in your inbox first, sign up here. Think of a teacher. Close your... Administration of SQL Server [New Job -> Week 3 -> Creating Alerts]( From SQLServerCentral Blogs This is part of a series of new job blog posts. Yo... [Best way to monitor high worker thread on SQL Server]( From SQLServerCentral Blogs It is common to see high worker count issue on the... [Running backups on a SQL ServerÂ]( From Steve Stedman Running backups on a SQL Server is crucial for sev... [Truncating very large transaction log in full recovery mode]( From SQLServerCentral Blogs While I always configure transaction log backups f... [Steps to help keep your SQL Server running smoothly.]( From Steve Stedman SQL Server is a powerful and complex database management system that is used by millions of organizations worldwide to store, manage, and analyze their critical data. While SQL Server... [Accelerated Database Recovery enhancements in SQL Server 2022Â]( From Microsoft SQL Server Blog We are excited to share that there are several Accelerated Database Recovery enhancements in SQL Server 2022. The post Accelerated Database Recovery enhancements in SQL Server 2022 appeared first on... [7 Common SQL Server Transaction Log Myths]( From StraightPath Solutions SQL Blog SQL Server is a complicated piece of software, so it’s understandable that some of us have some misconceptions about what the transaction log is and what it does. This... [Using SQL Alerts to Spot Suspicious Activity in SQL]( From SQL Undercover SQL doesn’t really give us too many tools out of the box to allow us to spot when someone may be up to no good. We can look at... [Lesson Learned #340: Storage and Memory allocated by Columnstore Indexes]( From Azure Database Support Blog Today, we got a new question how much is the size used by a columnstore index table at storage level and memory usage.  TSQL to obtain the total number of... [Lesson Learned #339: WAITFOR wait type delay]( From Azure Database Support Blog Today, I worked on a service request that our customer got multiple wait stats with the text of WAITFOR.  The WAITFOR wait type doesn't indicate performance issues but will have... [What are all the values of the state_desc column in the sys.databases system table in SQL Server?]( From Steve Stedman The sys.databases table in SQL Server contains information about all databases in the current SQL Server instance. The state_desc column in this table represents the current state of the... [Find SQL Server LOB Columns]( From SQLServer-DBA.Com What are LOB columns in SQL Server ? How do I find certain columns in SQL Server? Azure Databricks, Spark and Snowflake [Watching Our Changes in Snowflake]( For those of you who have seen me speak at an event for SQL Server, you have probably seen my Windowing Functions presentation. (If not, you should – it could be life-changing!) [How to read and write XML files with Spark?]( From Hadoop in the Real World We will be using the spark-xml package from Databr... [I introduce you to the Data Generator by Databricks]( While attending the SQLBits 2023, I took part in André Kamman’s session about “Generate test data quick, easy and lots of it with the Databricks Labs Data Generator”. In this blog, I will share with you my insights about the DataBricks Data Generator library and I’ll give an example. [Watching Our Changes in Snowflake]( From Sherpa of Data For those of you who have seen me speak at an event for SQL Server, you have probably seen my Windowing Functions presentation. (If not, you should – it... Azure SQL [Lesson Learned #342: Slow communication between our applications and Azure SQL]( From Azure Database Support Blog We used to have service requests that our customer... [Capturing Query Metrics in Azure SQL Database | Data Exposed: MVP Edition]( From Azure SQL While Azure SQL Database is different than SQL Server in some ways, when it comes to gathering query metrics, a lot of the tools are the same. In this... Azure SQL Managed Instance [Private endpoints for Azure SQL Managed Instance]( From Azure SQL In this article we'll explain private endpoints, a new feature of Azure SQL Managed Instance currently in Public Preview. Private endpoints rely on Azure Private Link technology to establish... Career, Employment, and Certifications [Microsoft Learn: Programming Databases with Transact-SQL for Beginners]( From Azure SQL Have you ever wanted to learn more about programming databases for Transact SQL (T-SQL) but didn’t know where to start? Here at Microsoft, we have officially launched a video... [$335,000 Pay for ‘AI Whisperer’ Jobs Appears in Red-Hot Market]( From IT Pro - Microsoft Windows Information, Solutions, Tools These "prompt engineers" spend their day coaxing the AI to produce better results and help companies train their workforce to harness the tools. For many, a computer engineering degree is optional. Community Interests [Women in IT Face Continued Obstacles to Career Advancement]( From IT Pro - Microsoft Windows Information, Solutions, Tools Despite efforts to increase diversity, equity, and... [A Woman in SQL 2023]( From Deb the DBA When I started this blog back in 2016, I wanted to... [Women in Tech Discovery Day]( From Adatis Creating opportunities for women to return to work, learn, connect and develop careers in technology We are delighted to announce that Adatis, in partnership with Telefónica Tech, is running... Computing in the Cloud (Azure, Google, AWS) [Azure Migration Provides Time and Cost Savings]( From DCAC Read more about how DCAC was able to help Rev.IO i... [Storage Options and Database Services for Google Cloud Platform (GCP) - Part 2]( From MSSQL Tips This article provides an overview of the Google Cloud Platform GCP and looks at storage options and database services when using GCP. [Cloud Data Warehouse Landing Zone Discussion]( In this article I want to discuss some different layout options for a landing zone in a modern cloud data warehouse architecture. With landing zone, I mean a storage account where raw data lands directly from its source system (not to be confused with a landing zone to move a system or application into the cloud). Conferences, Classes, Events, and Webinars [Why should you invest in your team and go to SQLBits?]( From Purple Frog Systems A week or two back the entire Purple Frog and Powe... [Relational database design]( From SQL Server Fast Over nine years ago, early 2014, I published a vid... [On-Demand Webinar - Monitoring: The ROI of Build vs. Buy]( Missed our recent webinar on ‘Monitoring: The ROI of Build vs Buy’? Catch up on demand and find out the pros and cons to building a monitoring system versus purchasing one. [SQL School: Master SQL Server Joins with My Comprehensive Course]( From Steve Stedman Hi there, I’m Steve Stedman, and I’m excited to share with you my SQL Server course focused on SQL Server Joins, available at . As an experienced database professional,... DMO/SMO/Powershell [POWERSHELL PSOBJECTS – THINK OUTSIDE THE BOX!]( Working with PSObjects is essential to anyone using PowerShell. Especially, when there’s a need to extend the object to provide useful information. [(No) Fun With PowerShell – Disappearing Modules and OneDrive]( From Arcane Code (No) Fun With PowerShell - Disappearing Modules and OneDrive Data Privacy, Compliance, and Governance [Content Certification in Power BI: One Step Towards a Better Governance]( From RADACAD Governance is an important aspect of every application and system in organizations. A Power BI solution architecture needs good rules and processes set up for governance to ensure the... Data Visualisation ["Home experiments" and stacked bar graphs]( If someone asked you which graph would be best for visualizing the customer types for each account manager based on the table in the picture, what would you suggest? DocumentDB/Key-Value/Graph/other NoSQL Databases [How to automatically add timestamp to documents and find the latest document in Elasticsearch?]( From Hadoop in the Real World Elasticsearch used to add _timestamp field with the ingestion timestamp automatically to all documents that are being added to the index. Unfortunately, this was removed in […] The post How... Hardware [Kioxia and WD Unveil World's Fastest 3D NAND Flash Memory]( From Tom's Hardware US Kioxia's and Western Digital's 218-layer 3D NAND d... [Intel Roadmap Update Includes 144-Core Sierra Forest, Clearwater Forest in 2025]( From Tom's Hardware US Intel announced several updates to its Data Center and AI roadmap at its webinar today, including a 144-core Sierra Forest chip and the new Clearwater Forest processors that will... MDX/DAX [Different types of many-to-many relationships in DAX]( From Sqlbi The composite models in Power BI introduced a new ... [Optimizing fusion optimization for DAX measures]( From Sqlbi How to implement a DAX measure to run faster than what you get from the built-in horizontal fusion optimization. Oracle/PostgreSQL/MySQL/other RDBMS [How to perform join operation in BigQuery?]( SQL joins are used to combine columns from multiple tables to get desired result set. In a typical Relational model we use normalized tables, each table represents an entity (example: employee, department, etc) and its relationships and when we need to get data from more than one tables, for example employee name and employee department, we use joins to combine employee name column from employee table, department name column from department table based on employee number key column, which is available on both the tables. [A Beginners Guide to MySQL Replication Part 1]( From Simple Talk MySQL Replication is a process where data from one... [Build a Better WordPress Archives Page Using MySQL]( I recently wrote about a way to generate visual, interactive calendars in HTML by combining a calendar table with data from an events table. I started with SQL Server because that’s what I know, but I knew that wouldn’t be my final solution. In this post, I’ll show how I worked around that. But let’s start from the beginning, by starting from the end. [Are PostgreSQL memory parameters magic?]( Configuration parameters are the most mythical creatures in the world of PostgreSQL. [The SELECT Statement in Oracle]( The SELECT statement is used to retrieve information from a database. SELECT statements might be as straightforward as selecting a few columns from a single table. They can also be complex, with several columns and criteria and multiple tables. In this article, I will introduce the basic SELECT statement fetching data from just one table. Performance Tuning SQL Server [In SQL Server, Blocking Begets Deadlocking]( From Erik Darling Data Gateway Drug Most servers I look at have some leve... [A Quirk When Rewriting Scalar UDFs In SQL Server]( From Erik Darling Data Back To Business I think I have probably spent 500 hours of my life rewriting T-SQL Scalar UDFs to avoid all the performance problems associated with them. The obvious... [Is SQL Server 2019 More CPU-Intensive Than SQL Server 2016?]( From Brent Ozar Unlimited I’m running into something that I’m having a hard time believing. A client was hitting CPU issues during load testing, and they swore all things were equal between their... [SQL Server 2022: FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION]( From Erik Darling Data Mouthful SQL Server 2022 is kind of a boring release. I had high hopes that it would be a rich environment for new blog material, like other releases have... PowerPivot/PowerQuery/PowerBI [Build a Power BI Paginated Report Online]( From Excelerator BI Back in April 2022, I wrote an article called Poor... [(Livestream Replay) Power BI Governance (Why Should I Care) - with "Ásgeir Gunnarsson"]( From Havens Consulting ABSTRACT For many the mention of governance gi... [(Livestream Replay) Introducing TMDL for Power BI! - with Mathias Thierbach]( From Havens Consulting ABSTRACT An early look at the Tabular Model Definition Language (TMDL), soon to be released by Microsoft as a public preview as part of the Tabular Object Model (TOM)... [Power BI Adoption and More Q&A with Melissa Coates and Matthew Roche!]( From Guy in a Cube Join us live as we answer your questions about Pow... [Why Doesn’t Power Query Know If My Step Will Fold?]( From Chris Webb's BI Blog Why the "Might Fold" step indicator exists in Power Query [Using Virtual Network Data Gateways to Secure Access to Azure SQL in Power BI]( From Simple Talk Power BI and Azure are two related services, but with a considerable independence between them. Power BI share the Azure Tenant for security management, but in relation to networking,... Product Upgrades and Releases [Updates to sp_PressureDetector, sp_HumanEvents, sp_QuickieStore, And More!]( From Erik Darling Data Updates! If you use any of my stored procedures, I... [Multi-Column Distribution for Dedicated SQL pools is now available!]( From Azure Updates Multi-Column Distribution (MCD) is highly desirable for easing migrations, promotes faster query performance and reduces data skew. [Generally available: nconnect support for Azure Files NFS v4.1 shares]( From Azure Updates Azure Files NFS v4.1 share now support nconnect option. Python [Python Commands Cheat Sheet]( From IT Pro - Microsoft Windows Information, Solutions, Tools The Python programming language may be easy to lea... R Language [How fast do the files read in?]( I will demonstrate how to generate a 1,000 row and column matrix with random numbers in R, and then save it in different file formats. I will also show how to get the file size of each saved object and benchmark how long it takes to read in each file using different functions. [Little useless-useful R functions – Transforming dataframe to markdown table]( From TomazTsql Writing markdown documents outside RStudio (using the usual set of packages) has benefits and struggles. Huge struggle is transforming dataframe results into markdown table, using hypens and pipes. Ugghhh…... Security News and Issues [Microsoft Security Copilot Uses GPT-4 to Beef Up Security Incident Response]( From Dark Reading: Dark Reading News Analysis Microsoft's new AI assistant tool helps cybersecurity teams investigate security incidents and hunt for threats. T-SQL and Query Languages [SQL Server 2022: T-SQL Enhancements [Ep. 6] | Data Exposed]( From Azure SQL In this episode of Data Exposed with Anna Hoffman ... [GENERATE_SERIES : My new go-to to build a set]( From SQLBlog.org I show how GENERATE_SERIES makes for easier set bu... [Grievances While Working With RAISERROR In SQL Server]( From Erik Darling Data Use Case I know that in the world of professional error handling, most folks use THROW instead of RAISERROR to do things. In my world, I use RAISERROR in... Tools for Dev (SSMS, ADS, VS, etc.) [How to Change the Browser Used by SSMS for AAD Auth]( From DCAC Did you know that you can change the browser used by SQL Server Management Studio to authenticate using Azure Active Directory to a SQL database in Azure? I had... Virtualization and Containers/Kubernetes [Why i use a smaller docker image part I]( From SQLServerCentral Blogs What is Docker? Docker is a famous platform for building, shipping, and running container applications. What are containers? Containers are a way to package software in a portable and... The...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -