Fri Nov 09, 2012 10:55 am
You are definitely memory constrained. If you fix that bottleneck, you might find your current storage is sufficient. To measure your current I/O, perfmon is the correct tool. To see if SSDs are going to be beneficial, you really need to fix the memory issue first so your storage isn't being hammered by paging. Then you could do some analysis on your disks to see if they are the bottleneck. Something to keep in mind is that the more memory you have for SQL, the more data is in memory and the less important the speed of your storage becomes. To see how your storage is performing, some important stats to watch in perfmon are:
1. Disk Reads/sec & Disk Writes/sec - These are your read & write IOPs.
2. Avg. Disk sec/Read & Avg. Disk sec/Write - These are how long your disks take to complete read & write operations.
3. Current DIsk Queue Length - This is how many operations are queued up (should be < 10 on average or you have problems, < 5 is good)
4. % Disk Read Time & % Disk Write Time - These should give you a good idea how much of your workload is writes vs reads and might give you better understanding about SSD durability for your application.
5. Avg. Disk Bytes/Read & Avg. Disk Bytes/Write - These will tell you how big your reads and writes are. Lot's of small reads and writes are really where SSDs shine vs HDDs.
For comparison, I have 50+ servers running on a SAN with 24 15k RPM SAS drives. These servers host a wide range of applications - Exchange, SQL databases, Oracle databases, domain controllers, terminal servers, SharePoint servers, web servers, file servers, etc etc. Right now, here are the averages for the last 10 mintues:
1. Disk Reads/sec & Disk Writes/sec - 1325, 550
2. Avg. Disk sec/Read & Avg. Disk sec/Write - 1.4, 1.6
3. Current DIsk Queue Length - 2.2
4. % Disk Read Time & % Disk Write Time - N/A
5. Avg. Disk Bytes/Read & Avg. Disk Bytes/Write - 28k, 12k
"I take sibling rivalry to the whole next level, if it doesn't require minor sugery or atleast a trip to the ER, you don't love her." - pete_roth
"Yeah, I see why you'd want a good gas whacker then." - VRock