Bottlenecks on SQL Server performance
-
We have a BI-application that connects to input tables on a SQL Server 2022 Standard edition. This is installed on a Windows Server 2022 in Azure with the size Standard E32s v5, having 32 vCPUs, 256 GB RAM, 32 data disks and 51200 max IOPS.
The SQL Server is used for reading, updating and writing data through ETL-jobs, and as mentioned to directly connect from the BI-application to manipulate data, through ODBC-connectors.
When doing budgeting in the company, several users manipulate input tables in the BI-application, indirectly manipulating the SQL Server tables. Then again the SQL tables have triggers to perform logiq that recalculates affected data. We also have users reading data from tables and simply modifying this without any triggers. These tables are not used in the budgeting process. However we're experiencing that users manipulating these simple tables have huge delays when the budget process is ongoing. Meaning that the queries generated are being delayed because of other jobs on the SQL Server, even though the tables are not related.
We're struggling to find out why this is happening, and what we can do to make performance better. We recently upgraded the Windows Server that SQL Server is running on to double vCPUS, RAM and max IOPS, but it doesn't seem to have any effect.
Can someone please help us understand what could be the problem?
I have some hypotheses, but not enough knowledge to determine if it's actually the problem:
-Could it be that the SQL Server being Standard edition caps the performance, thus it could be solved by upgrading to Enterprise?
-It could not be deadlocks on the simple tables, since they are not affected by the budgetting tables, but could it be deadlocks on RAM-usage etc?
-Could it be limitations on the connection itself between our BI application and the SQL Server?
-Other things?
-
Could it be that the SQL Server being Standard edition caps the performance, thus it could be solved by upgrading to Enterprise?
Yes: Standard has a memory limit of 128GB & CPU limit of 4 sockets or 24 cores.
What transaction isolation level(s) are being used?
Have you examined execution plans and looked at blocking? Are you actually getting the advertised I/O?
It sounds like you may need separate databases for budgeting/reporting and ETL/CRUD.
-
Thanks for your answer!
But is this limit of 128 GB something else than the RAM on the server? We've set the SQL Server to max at 90 % RAM, and we see that it's using it fully:
I think the transaction isolation level being used is "ReadCommitted", but frankly I don't know a lot about this..
I've been trying to examine executions, but haven't gotten a lot out from it unfortunately.
Will splitting databases have anything to say? Isn't the limitations on the SQL Server/Server? All the input tables (including budgeting) are on the same database, but we have seperate databases for other things that's only affected by ETL-jobs.
-
We're having crippling bottlenecks in SQL Server 2022 as well. We're running enterprise edition, on prem. Everything looks awesome when there is little load on the system. As soon as we get up to normal load, application performance tanks and comes to a grinding slowdown. Even when this is going on, our monitoring tools show that memory is fine, cpu is fine, storage is performing acceptably. Metrics that would make me happy were the applications not performing so horribly. The only thing that is off is the RTT to the server. This is an OLTP system and the third party applications we're using perform a ton of small queries. One simple process that I was running takes about 1 second normally to complete within one of the applications, took over 2 minutes when we were under load yesterday. It runs about 350 small, sub millisecond queries. The queries themselves still showed the same duration/reads/etc., but it was agonizingly slow between each query. The network and systems engineers and admins can find no reason for the delays.
May I ask what CU you are on? I have a couple other servers on 2022 CU19, that I am not seeing the same issues with, although they have different types of loads.
-
We're on CU21.
Sounds a lot like you're having the same types of integrations with the SQL Server and the same problems as us.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply