2

I am new to Linux and our client decided to use SQL Server on Linux. As soon as we started testing, we ran into performance issues.

The server in question is a physical machine configured with a SSD local disk and a "natively" attached Storage. I don't know what a quoted "natively" actually mean here. Linux guy's exact words below

the storage is "natively" attached to the server

The storage area network is also built on top of SSD and I copied a 4.7 GB backup file from there over to local SSD within a second.

cp \gsfs\sql\backups\testdb01.bak \tmp\backups\testdb01.bak

But when I try to restore from

  1. SAN

RESTORE DATABASE successfully processed 612466 pages in 369.425 seconds (12.952 MB/sec).

  1. Local Disk

RESTORE DATABASE successfully processed 612466 pages in 26.248 seconds (182.295 MB/sec).

The database files are also stored on the SAN and while SAN to physical machine has no connectivity issues, I am certain, the way MS SQL reads the files on the SAN has something to do with the slowness.

The database size is not big enough and the database is usually idle based on wait statistics

enter image description here

I want to understand why SQL Server is restoring from SAN slower than local file system. I would understand if the difference was little, but currently, the difference is dramatic. Since our data files and log files are also on SAN, we need to ensure disk is read/written on as fast as possible.

  • Most of settings are set as default
  • network packet size is 4096
  • No antivirus installed
Improve this question
6
  • Anything in the sql error log ? DO you have antivirus enabled ?
    – Stephen Morris - Mo64
    Mar 3, 2021 at 15:30
  • @StephenMorris-Mo64 - I have confirmed from Linux admin that there is no antivirus on the server.
    – Shamvil Kazmi
    Mar 3, 2021 at 16:50
  • Do you happen to have a SQL Server on Windows that you could ran that same test just to be sure the problem is due to using Linux? (I'm not a Linux admin either and I'm not trying to defend it, just a test) What version of SQL Server are you using?
    – Ronaldo
    Mar 3, 2021 at 23:36
  • Have you read through the performance recommendations from Microsoft and ensured you're adhering to them?
    – HandyD
    Mar 4, 2021 at 4:49
  • @Ronaldo 90 % sql servers are based on Windows OS currently, we never faced this problem.
    – Shamvil Kazmi
    Mar 4, 2021 at 7:47

1 Answer 1

Reset to default
0

I quit the job, so I don't know what they did about it. Before I left, it was decided that a SAN admin will investigate if their storage was slow or if they were facing other challenges.

Closing this question as there is no way for me to track the issue any further.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.