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

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.

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.

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.

There’s a video of Bill Gates taking a drive in ...

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

This is part of a series of new job blog posts. Yo...

It is common to see high worker count issue on the...

Running backups on a SQL Server is crucial for sev...

While I always configure transaction log backups f...

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

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

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

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

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

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

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

What are LOB columns in SQL Server ? How do I find certain columns in SQL Server?

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

We will be using the spark-xml package from Databr...

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.

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

We used to have service requests that our customer...

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

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

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

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.

Despite efforts to increase diversity, equity, and...

When I started this blog back in 2016, I wanted to...

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

Read more about how DCAC was able to help Rev.IO i...

This article provides an overview of the Google Cloud Platform GCP and looks at storage options and database services when using GCP.

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

A week or two back the entire Purple Frog and Powe...

Over nine years ago, early 2014, I published a vid...

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.

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 http://SteveStedman.com/joins. As an experienced database professional,...

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

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

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?

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

Kioxia's and Western Digital's 218-layer 3D NAND d...

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

The composite models in Power BI introduced a new ...

How to implement a DAX measure to run faster than what you get from the built-in horizontal fusion optimization.

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.

MySQL Replication is a process where data from one...

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.

Configuration parameters are the most mythical creatures in the world of PostgreSQL.

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.

Gateway Drug Most servers I look at have some leve...

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

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

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

Back in April 2022, I wrote an article called Poor...

ABSTRACT For many the mention of governance gi...

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

Join us live as we answer your questions about Pow...

Why the "Might Fold" step indicator exists in Power Query

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

Updates! If you use any of my stored procedures, I...

Multi-Column Distribution (MCD) is highly desirable for easing migrations, promotes faster query performance and reduces data skew.

Azure Files NFS v4.1 share now support nconnect option.

The Python programming language may be easy to lea...

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.

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

Microsoft's new AI assistant tool helps cybersecurity teams investigate security incidents and hunt for threats.

In this episode of Data Exposed with Anna Hoffman ...

I show how GENERATE_SERIES makes for easier set bu...

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

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

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