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 !

Screen Shot 2014-05-08 at 01.28.20

 

Screen Shot 2014-05-08 at 01.30.06

 

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

VM

Posted on 08/05/2014, in MSSQL and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: