GStoner
asked on
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
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.
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
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.
Is there an anti-virus running in the system?
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found that the issue was with applied server updates. After rolling them back, the problem was resolved.
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.