question

Mohamed-6884 avatar image
0 Votes"
Mohamed-6884 asked ErlandSommarskog answered

SQL Server on Linux with Enterprize Evaluate Version - Very Slow Response for Heavy Load of API Request

SQL Server on Linux is very slow when there is huge API request but the same procedure is running very fast in SQL SERVER on windows

Problem statement :

SQL server on Linux response time is high on load hence API timeout.

More Information:

Connection pool configured : 100
Connection method from IIS to Linux Server : Native Client (SQL Client)
No of request / sec : around 80 to 100 / per sec.
Stored procedure execution time : around 2 sec / request

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

Have you enabled Query Store? If not. do that in both environments. Then run your tests and collect the information to compare performance of the queries in same environments. Pay particular attention to Reads. From what you say, we can expect Duration to be widely different. But if it is only a matter of the two environments being different as such, the number of reads would be the same.

On the other hand, if the number Reads is a lot higher on the Linux box, I am inclined to think that it is an issue with query plans being different, rather that different performance as such of the environments.

Of course, I am here assuming that you really the databases in the two environments are copies of each other. If they are not, that alone can explain the difference.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog Thank you so much for your explanation. I tried with Query Store. I can't see much difference in read and write.

Answering your question, yes we have same database in both Linux and Windows. When we do load test with 100 API request, application connected to Windows response in 12 seconds for 100 requests, but it takes 2 minutes in Linux for 100 request.

0 Votes 0 ·

Seems like you should take a look at the query plans, and see if you can figure out why there is a difference. Maybe statistics are not the same in the two databases?

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered DanGuzman commented

Hi @Mohamed-6884,

Please compare the execute plan of the same procedure in Linux and windows. Then you may find the difference.

In addition, suggest you refer to below MS document to adjust your Linux configuration. This article provides best practices and recommendations to maximize performance for database applications that connect to SQL Server on Linux.

Performance best practices and configuration guidelines for SQL Server on Linux


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



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your reply, we compared the execution plan but could not find any difference, and one more information, both Linux and Windows are responding in the same time if we send only 1 API request, but Linux response is very slow when we send 100 API request at the same time.

and also we followed the MS document of best practices (the same link you provided) and verified already.

0 Votes 0 ·

@Mohamed-6884 , if plans are the same, I suggest your compare infrastructure and SQL configuration. Do the the Windows and Linux machines have the same number of CPUs? Are values from sys.configurations the same for both?

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

It seems that when you said I can't see much difference in read and write, I managed to read this as I can see..., which prompted my response to look at query plans. (Hm, I wonder what Dan will have to say about me missing a not also when reading...) Sorry for the confusion.

I think one thing that would be very interesting to look at is the wait statistics. Before you start a load test, run this:

DBCC  SQLPERF ("sys.dm_os_wait_stats", CLEAR)

When the test has completed, run

SELECT TOP 20  * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC

You may have to filter out benign waits that are unrelated to the tests, but we'll see.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.