Microsoft SQL 2014 disk performance

I'm running SQL Server 2014 Standard.  The SAN is an SSD disk array.

For the last couple days, I've been having IO issues - disk read / writes maxing out randomly.  Applications and the SQL Server itself become extremely sluggish and freeze up at times.

I'm getting many of these SQL Log warnings as well:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\templog.ldf] in database id 2

Performance Monitor tracking Avg. Disk sec/Transfer, Disk Bytes/sec, Disk Transfers/sec and Resource Monitor are all over the place.  Disk utilization seems to just randomly max out at times.

Thought I'd try posting this before calling Microsoft support.  Is this an indication of a hardware issue, SQL issue, database fragmentation?  Just not sure where to start with this.
GStonerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
How old is your SSD SAN? What disks are used?

Some SSDs are loosing promised performance parameters very quickly so I would recommend to test another SAN and/or test the SAN performance before calling MSFT support.
0
Ares KurkluSoftware EngineerCommented:
Is there an anti-virus running in the system?
0
Mal OsborneAlpha GeekCommented:
Also, how much RAM do you have? SQL is quite good at snaffling any memory it can find and using it as a cache, reducing the need to access the hard drive subsystem. SQL 2014 can use up to 128Gb.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GStonerAuthor Commented:
The SAN is a Dell Compellent SCv2020 with ten 960GB SAS Read Intensive SSD's.  It's been in use for 14 months.

Anti-Virus is not running on the server.

Regarding RAM:  This is a VM.  so that SQL does not consume all of the available resources, I have the max server memory set to 70GB, leaving 8GB free for the OS and any other use on this server.  I've had is set like that for 6 months without any problems.  As I type this, Task Manager show 36% of RAM in use.

I came into the office this morning and there were only a few people working, but we do have external vendors using a web application that hits this SQL server too.  Internal users were already complaining about poor performance.  I checked the SQL Job Activity Monitor and noticed that my full backup that starts at 5AM was still running at 8AM.  That job typically takes 20 minutes to complete.  Also, the scheduled transaction log backup was running at 8AM.  I manually stopped both, as I have had issues in the past with backup jobs getting stuck, causing server performance issues.  After stopping the jobs, performance improved slightly, but it's still sluggish and sqlserver.exe read and writes are very high.
0
GStonerAuthor Commented:
One thing I just found out...  A network admin installed Microsoft updates last Thursday to all of the servers.  This is also right around the time we started experiencing problems.  He also told me that he's been experiencing issues on some of the other servers, so we're going to roll those updates back to see if that is what's causing the problems.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
OK, to uninstall the last Windows updates is a good option. Then you may try Dell's diagnostic tool. But it means to put the Storage Controller into Maintenance Mode so some Dell assistance is highly recommended.
0
GStonerAuthor Commented:
After removing the updates that were applied last Thursday, everything seems to be back to normal.   I've attached a list of the updates. I'm guessing that it could have been the Intel LAN Adapter updates that were causing IO issues between SQL Server and the SAN.
0
GStonerAuthor Commented:
0
pcelbaCommented:
Yes, it could have been the cause. Was it the latest driver? Do you know your old/new driver version?

Intel drivers are available here: https://downloadcenter.intel.com/product/59679/Intel-Ethernet-Controller-I350-Series

But the question is whether to study it more deeply now...
0
GStonerAuthor Commented:
I found that the issue was with applied server updates.  After rolling them back, the problem was resolved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.