At first I thought SQL was under high utilization, but now I'm thinking perhaps the SAN is under high utilization from other VMs, causing our disk IO capacity to diminish.
January 31, 2022 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 1, 2022 at 9:34 pm
At first I thought SQL was under high utilization, but now I'm thinking perhaps the SAN is under high utilization from other VMs, causing our disk IO capacity to diminish.
- Am I interpreting correctly that the disk IO performance during this peak (in stats) is bad? Avg Disk sec/Read the average is .159 which is 159 MS, and I have learned that anything over 50 MS is getting bad. Thoughts?
- Also, the Disk Reads/sec looks to also be very high, an average of 2,101 reads per second is the average. Does this mean that the SQL server is demanding these reads and shouldn't this number be lower than 50 also (Some people interpret this stat to be MS, but to me it sounds like reads per second, not time per read).
What do the disk statistics look like in VMWare at the host and VM level?
How many different, and what type. of SCSI adaptors are configured for this VM? Do these charts represent the entire VM, or a single drive?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
It's a little hard to tell just from the graphs if you have an issue or not. Hopefully, if you've got a SAN, you've got a SAN/Storage admin that can look at the performance and tell you what kind of performance you're getting and help you figure out what's going on with disk. SQL Server does keep some pretty good statistics about how much it is waiting on storage. In case you haven't seen some of the ways you can look at those metrics, here's an article that will give you some additional things to look at.
https://www.sqlshack.com/how-to-analyze-storage-subsystem-performance-in-sql-server/
With that said, I would take a step back for a second. In the scheme of server performance, disk is always going to be one of your slowest parts. How much memory is in the system? How is your indexing? Both of these things can make a difference in how much you have to go back to disk for data. A lot of times, adding memory to your server is cheaper than making changes to the SAN or spending a lot of time index tuning. If you can't add memory, or if your active data is much bigger than the memory you can get, then indexes are going to be very important for the server. For example, you might have a 200 GB table that doesn't fit in memory, but if you can put an index in that satisfies your query and does fit in memory, you're not going to have to go back to disk for your query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply