• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13
  • Last Modified:

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.
0
GStoner
Asked:
GStoner
2 Solutions
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now