Below are answers to questions asked during the webinar. If you have additional questions, please contact Brian Flynn (brian.flynn@solarwinds.com) or Tom LaRock (thomas.larock@solarwinds.com). Thank you!
Q: What is the MSDN Sequential Read Ahead link?
A: http://blogs.msdn.com/b/craigfr/archive/2008/09/23/sequential-read-ahead.aspx
Q: What about SQL Servers running on VMware?
A: For picking apart storage problems related to an instance being on VM, you need metrics from the hypervisor. DPA is very good at correlating database IO problems caused by VMware.
Q: Which raid level (5 or 10) is best suited for SSDs?
A: I don't suggest any best practice answer specific to this. In my opinion, you treat it the same as RAID on HDD but note that you may not need to stripe across as many spindles for the same performance. This may mean that RAID-5 is perfectly acceptable on SSD where you might want RAID-10 for HDD.
Q: Is Procmon the same as Perfmon?
A: No, Procmon is a totally separate application you can download from Microsoft.
Q: Is fragmentation a significant issue when using SSD storage?
A: Fragmentation is still important on SSDs, but not for the same reason. Defragmenting is likely to reduce total number of reads and that is key to SSD performance. With HDDs, we still like having the reads be sequential b/c of the penalty for seeking a location on disk to read, but with SSDs, there isn't such a penalty... but there still is a penalty for total # of reads.
Q: Our SAN vendor is blaming our poor latency (as it shows in DPA) on SQL reading block sizes greater the 64k (which is not cached), up to 1 mb. What type of work in SQL triggers the larger block requests?
A: Read Ahead triggers the larger reads and read ahead is a good thing b/c it reduces total number of reads which is always a winner. I would push back on the SAN vendor, explaining that ultimately, we need the same data returned and if we design to target having small reads, we're going to have more reads which likely won't make the end users' experience better even if it makes a metric more favorable for their perspective. We don't optimize to make performance reports look good, we optimize for the end user. Ask them how to make the end users' experience better for the same use case. If they can make their point, design the solution to avoid read-ahead by getting fragmented and staying fragmented... but I just can't envision that being a winning strategy.
Q: Can this tool be used with a SQL server environment that runs SAP database?
A: Not sure which tool you are referring to, but if you are asking about DPA, yes! If you are talking about Procmon, yes!
Q: As you know, there could be other applications interacting with the disk that SQL is on. How do we gain visibility into that in a VMware environment?
A: I always try to avoid shameless plugs for our product, but DPA does this so well! DPA juxtaposes lots of metrics and we can show you a whole stack of metrics from VMware along side wait time or read latency. It's a great way to get the conversation started with a VM Admin that is denying VMware could be the issue. You can of course do this without DPA, but it is more tedious.
Q: We currently own DPA v8.0. When we upgrade to the new version will we lose the existing data?
A: Nope! :-D
Q: How does all this apply when you're using VMware with NetApp storage configured as a large Raid 6 that has been partitioned for drives? These new technologies seem to work differently in terms of filegroups and other traditional SQL technologies.
A: When you have abstractions like you are describing, the use of files and file groups can serve to muddy the water. In an environment like that, I don't add files and file groups to distribute load, but rather to have more control over where the workloads go. If your never ever going to do anything but put these files on the exact same array, you likely won't gain performance, but you might gain other benefits like predictability and managability.
Q: Can we just add disks to arrray to improve performance?
A: Adding disks to the array helps some performance use cases. I tend to describe the benefit as a concurrency benefit i.e. being able to do two reads at once, but you can't bank on which reads can be parallel… it ends up being more chance by which spindle each bit lands on.
Q: On the earlier discussion on reads being "sequential", my take is that each read operation that we see in ProcMon is sequential in itself (the read operation starts at the offset and continues sequentially for the length indicated) but they are NOT necessarily CONTIGUOUS, depending where the next block of data to read was located on disk. I hope that is correct and it might clear a bit of the potential confusion.
A: You may be touching on a nuance that is rather interesting. And that is, contiguous in what way? It is worth noting that when you use RAID, the array distributes your data across spindles and thus you can't assume that reads that are physically contigulous by their address on the volume. I do consider it a relatively safe assumption that you will be statistically more probable to have your design in more reads that are physically contiguous. Thank you for that note! VERY VERY ASTUTE! :-D
Q: Doesn't the read ahead read 8 extents at a time?
A: It can. Read ahead will read between 2 and 8 extents at a time.
