Performance issues - Sql Server 2022 Standard Edition

Leonardo Azzari 20 Reputation points
2023-01-27T14:01:43.71+00:00

Hi,

I've been using SQL Server 2014 express to store data and interact with my C# applications on Windows 10 for different years.

I wanted to do some test with Sql Server 2022 before upgrading to that version so I've installed the Evaluation Edition (all default values on the installation) and eventually decide.

I've always known that there are Memory and Cpu Limits to the Express version, so I would expect that a newer version (2014 -> 2022) + no limits would cause a boost on query execution.

My test were done on the same Database, same computer and same source code. (.Net Framework 4.7.2 C#) with Linq queries for operations of inserting and selecting one record on a really large table (5 milion records and 29 columns).

The average time of execution for the query on the 2014 Express instance was 190ms

The average time of execution on the 2022 Standard (evaluation) instance was 300ms.

I'm not sure I wanna proceed to buy the SQL 2022 Standard on my future work computers if on the first tests, on simple queries, there's already a huge downgrade.

Is there something that specifically needs to be set instead of keeping SQL installation by default or in general could you suggest something?

Greetings,

Leonardo

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,648 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,817 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,315 questions
0 comments
Accepted answer
  1. CosmogHong-MSFT 23,321 Reputation points Microsoft Vendor
    2023-01-30T06:41:26.8466667+00:00

    Hi @Leonardo Azzari

    When testing the queries, it is suggested to choose several queries for testing with priority placed on queries that are:

    • Significantly faster on one server than on the other.
    • Important to the user/application.
    • Frequently executed or designed to reproduce the issue on demand.
    • Sufficiently long to capture data on it (for example, instead of a 5-milliseconds query, choose a 10-seconds query).

    If still exists significant performance difference between two servers, please refer to this article: Troubleshoot a query that shows a significant performance difference between two servers.

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.9K Reputation points MVP
    2023-01-27T22:54:07.01+00:00

    I would advice you not to draw conclusions on performance from a single query. While there are improvements to the optimizer in every release, there are always queries you get regression, because of bad luck or whatever.

    You say that you selecting a single row in one single table. In my opinion, neither 190 nor 300 ms is acceptable performance for a point lookup. It should hardly be a millisecond - assuming that you have the right indexes in place.

    Anyway, if you want a deeper analysis of this particular case, please share the query and the actual execution plan from both SQL 2014 and SQL 2022.

    And, oh, do you have SQL 2014 and SQL 2022 on the same hardware?