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

Mohamed 1 Reputation point
2022-05-20T12:21:34.587+00:00

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
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
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-05-23T21:40:36.257+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 100.9K Reputation points MVP
    2022-05-20T21:40:24.303+00:00

    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.

  3. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2022-05-23T07:44:03.56+00:00

    Hi @Mohamed ,

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