So 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 gsfssqlbackupstestdb01.bak tmpbackupstestdb01.bak
But when I try to restore from
- SAN
RESTORE DATABASE successfully processed 612466 pages in 369.425 seconds (12.952 MB/sec).
- 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 not connectivity issues, I am certain, the way MS SQL reads the files on 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
wait_type wait_time_s signal_wait_time_s resource_wait_time_s pct
SOS_WORK_DISPATCHER 9643760.19 114.83 9643645.35 98.2
PWAIT_EXTENSIBILITY_CLEANUP_TASK 174660.62 174660.62 0 1.78
BACKUPTHREAD 676.2 0 676.19 0.01
BACKUPIO 627.86 0.37 627.49 0.01
PREEMPTIVE_OS_LOOKUPACCOUNTSID 342.43 0 342.43 0
PREEMPTIVE_OS_AUTHENTICATIONOPS 298.48 0 298.48 0
PAGEIOLATCH_SH 116.55 0.04 116.51 0
PREEMPTIVE_OS_QUERYREGISTRY 28.7 0 28.7 0
SERVER_IDLE_CHECK 22 0 22 0
PREEMPTIVE_OS_WRITEFILEGATHER 13.71 0 13.71 0
PARALLEL_REDO_WORKER_WAIT_WORK 9.78 0.01 9.78 0
STARTUP_DEPENDENCY_MANAGER 5.11 0 5.11 0
IO_COMPLETION 5.02 0 5.02 0
PWAIT_ALL_COMPONENTS_INITIALIZED 3.75 0 3.75 0
PREEMPTIVE_OS_FILEOPS 3.41 0 3.41 0
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 different 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