Performance comparison

Bob sql 476 Reputation points
2023-03-20T06:06:32.6166667+00:00

Hi All,

We are trying to compare UAT performance vs Prod performance. The UAT is having much better performance.

Production:

========

Prod has 30 databases.

One of the database database is2.5TB.

Its 3 node Alwayson setup. Two synchronous replica in one region and one asynchronous replica in a different geographical reason.

server resources:

16 CPUs

256 GB RAM

230 GB set as MAX SERVER MEMORY.

server settings:

name value

cost threshold for parallelism 5

max degree of parallelism 1

max server memory (MB) 486592

optimize for ad hoc workloads 1

At database level, maxdop is set to 4.

UAT:

====

UAT has only 4 databases.

Its a standalone server. We clone the db to UAT and change the recovery model to SIMPLE.

server resources:

16 CPUs

256 GB RAM

230 GB set as MAX SERVER MEMORY.

server settings:

cost threshold for parallelism 5

max degree of parallelism 0

max server memory (MB) 235520

optimize for ad hoc workloads 0

At database level, maxdop is set to 4.

Environment:

SQL Server 2017 EE

Looking for some clarifications and suggestions.

Is that a fare comparison comparing UAT to Prod?

Will there be any performance gains choosing database recovery model to SIMPLE over FULL recovery model?

Only 1 database is active most of the time in UAT? But in prod 40 databases are active.

Cheers,

Bob

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,690 questions
Accepted answer
  1. Bjoern Peters 8,781 Reputation points
    2023-03-20T13:56:11.9566667+00:00

    Hi Bob,

    It is hard to tell why your environments perform differently; so many aspects could be an issue...

    Like index fragmentation, like non updated stats, like different storage, nonoptimal stored exec plans, and freeing cache regularly on one system...

    First, I would like to ask if you stated some things differently:

    Both environments have 256GB RAM

    Prod - "230 GB set as MAX SERVER MEMORY" and "max server memory (MB) 486592"

    UAT - "230 GB set as MAX SERVER MEMORY" and "max server memory (MB) 235520"

    What kind of value is that second PROD value? Where does it come from, or is it just a "Copy&Paste" error?

    maxDoP on database level - is it set to 4 on ALL databases or just on one?

    If it is set only on one or some databases... UAT maxDoP on the instance was set to 0, and CTfP = 5 means a lot of queries (if not restricted on db level) will run in parallel, which might be a boost depending on your workload.

    Prod is set to a single thread, overridden by the db level set to 4

    That might be the biggest impact of why UAT is performing better.

    RecoveryMode Simple/Full might be an issue have your storage for the transaction log file performs really badly and is the IO bottleneck

    You should look at the WAITSTATS on both environments, then check which statements aren't performing differently on both environments, and then dive deeper into why there are differences... maybe it is the storage or a config issue.

    Let us know ;-)

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2023-03-20T22:11:37.61+00:00

    As Björn says that are so many "it depends" here. For all I care, the answer could simply be that there is less load on UAT.

    What measurement do you use to conclude that UAT performs better?

    cost threshold for parallelism 5

    While 5 is the default, I think best practice these days is to set it 30 or 50.

    0 comments
  2. Seeya Xi-MSFT 16,436 Reputation points
    2023-03-21T03:12:45.8266667+00:00

    Hi @Bob sql ,

    Comparing UAT to Prod based solely on the hardware and server settings is not a fair comparison. There are many other factors that could contribute to the difference in performance such as workload, query patterns, data volume, network latency, and other environmental factors.

    Changing the database recovery model from FULL to SIMPLE can provide some performance gains, as it reduces the amount of logging and checkpointing required, but it also comes with the trade-off of reduced data recoverability. The choice of recovery model should be based on the specific needs of the application and the level of risk that is acceptable for data loss.

    About cost threshold for parallelism, please refer to this:

    https://vroomperformance.com/cost-threshold-for-parallelism-and-maxdop/

    Best regards,

    Seeya

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

    0 comments