Hi All,
We are trying to compare UAT performance vs Prod performance. The UAT is having much better performance.
Production:
We have this one big database with 2.5TB.
Its 3 node Alwayson setup. Two synchronous replica in one region and one asynchronous replica in a different geographical reason.
Prod has 30 databases.
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.
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
So, when we talk about comparing performance between something like a UAT system and a production system, we've got to get more specific.
Are you saying, for a given query, against an identical data set, with identical parameters, that performance is better in UAT than production? Or are you saying overall workload is radically less in UAT, therefore things are faster, than production? We have to get those specifics in order to understand what's going on.
Also, at least from your rundown, you have differences that are going to lead to different behaviors, completely negating comparing these two things. First up, max dop. 1 in prod, 0 in UAT. That means that some queries in UAT which need parallel execution are going to get it, whereas prod gets none, ever, because of the max dop (by the way, this is a bad approach in most circumstances instead, change the cost threshold to a higher number). Same for optimize for ad hoc. Simple recovery mode changes behaviors again compared to prod. These differences lead to differences in behavior so we're not comparing apples to apples.
The key is understanding first, where are things slow, and this is generally solved by capturing query metrics (I recommend Extended Events and/or Query Store). Then why they're slow, which involves a lot more, wait statistics, server metrics, execution plans, etc.. Then you can arrive at what to do about it. However, as much as possible, you have to compare like to like to arrive at real understanding.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2023 at 4:56 pm
In most cases (there are exceptions to everything) the following settings absolutely suck on any server...
cost threshold for parallelism 5 -- General advice here is to start at 50 and adjust.
max degree of parallelism 1 -- If you actually do need "1", you seriously need to spend some time fixing the code.
MAXDOP of 4 or 8 is usually the "sweet spot" for a lot of applications. I use "4" in most cases and have compared it with 8 on my production servers. The issue for me is that performance challenged code appears to have to do less work when limited to 4. We had multiple 6 hour jobs drop to 2. YMMV.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2023 at 6:39 pm
> Two synchronous replica in one region <<
The prod server must wait for the replicas to be in sync. That could also be adding to delay in prod.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply