Sometimes storage IOPS , latency and cpu usage can not say something about the reason of performance issue on MSSQL
We faced very interesting customer complaints about MSSQL server performance
Problem is related sp run very slow and the source should be the virtualization 😦
At the startup its looks like very common complaints and how storage and system admins do check the storage side like global read and write latency on storage , related sql server LUN based latencies , cache configurations , frontend (host latency) and backend (disk subsystem latency ) also on server site via Resource Manager check the cpu , ram usages and also disk latencies too ! Nothing wrong …
As a system guy , usually focused on sp and check the whats going on , how to tune the sp bla bla . By SQL admin some tunings done and its effect the overall process but its was not enough. Asked so much guys about how to deeply understand the issue , mostly the answer is use profile manager or check the execution plan, find the cost and try to do it better
Then we discover the how to see the Process and Resource Waits , problem is mostly described is about not often but problem could be indexes but mostly disk bottleneck which is looks like impossible storage point of view because we do not try only on the SAN, DAS with different level of raid levels and number of the disk almost have same results also MSSQL run on physical server too !
The last thing is move everything %100 SSD Volumes , wait times are decrease incredible and SUSPENDED things are start to happen less then before
Now you can say to me that really the problem is disk , san , raid , rpms or similar things but still i do not believe that the problem is non-SSD disks or arrays because there is no so much io , write latency any cache latency or SAN Network latency but single SSD solution somehow solve all things.
What i learned, its not enough to check MSSQL performance via only some very usual values like latency, cache and IOPS