SharePoint and Storage

  • Article

There is lots of good information on how to optimize storage for SQL, perhaps too much. Some great articles to read for those interested:

https://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx

https://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

 

 

Doron Bar-Caspi is working on SharePoint specific document for public consumption, but if you want my cliff notes version- here you go:

 

  1. Design storage properly before deploying SharePoint. Deploy the fastest storage you can afford, but in the following order: temp > logs > search > data. This typically means more spindles (disks) per array. As general guidance, Raid 10 typically offers better random I/O performance and is therefore a good choice for temp and logs. Raid 5 may be fine for content database files. MOSS search may require extreme numbers of spindles in order to remove bottlenecks. (we deploy 10 to 20 spindles in Raid 10 just for search)
  2. Remember that SAN doesn't always equate to great performance. DAS can offer good performance too. iSCSI may even work for some. Especially with 10Gig/E. We are deploying DAS/SAS throughout our deployments because it offers good bang for the buck.
  3. In my experience SCSI offers better performance than SATA and is generally more reliable. We use SATA for backup as it performs well for sequential write operations such as backup or logging.
  4. When building storage arrays, make sure to use your vendor best practices to maximize performance. This is most important when determining stripe size. The vendors do a good job of making sure their stripe sizes maximize performance for different RAID configurations.
  5. Never use the Windows UI to build disks. Use diskpart.exe and create partitions with offsets of at least 64K. I've had good luck with 64K offsets on Raid 5 and 128K offsets on Raid 1 or Raid 10. (if you are curious about how to use offsetting. This is part of the diskpart create partition command. When creating a partition use the parameter align=offset value in Kilobytes. Example, "create part primary align=64." This example creates a single primary partition with an offset of 64K)
  6. Isolate temp database onto it's own spindles.
  7. Isolate temp log and data file onto different spindles.
  8. Create a temp data file for every processor core on your server. Maximize temp database performance by spreading these files across different spindles; however, you can get gains just by creating more files on the same spindles.
  9. Isolate database data files from logs.
  10. MOSS search is a major consumer of disk I/O. Isolate onto dedicated spindles.
  11. Don't use out of the box data and log file size and growth options. Growing frequently robs performance and creates fragmentation. Consider setting your model DB data and log file sizes to 1GB or more. Set autogrowth to grow in 1GB increments or more. This is most important for the temp database. Caution - Don't grow your model DB too large or it will take SQL forever to come online after startup. That's because the temp database is created from the model database at start up. It's takes a long time to create a 25GB temp database at startup. I learned this the hard way. :-)
  12. Feel free to deploy multiple content databases to the same spindles. It's unlikely that all content databases will require I/O at the same time. Move data files when bottlenecks appear.

 

All deployments are different. After deploying with the above guidelines, monitor storage for bottlenecks and adjust as necessary. Use the following perfmon counters to identify bottlenecks:

Logical Disk > Current Disk Queue Length - (AKA Disk Queuing) This counter indicates the number of outstanding disk requests currently queued. The lower the better. If you see this counter consistently exceed 20, it may indicate a bottleneck.

Logical Disk > Average Disk sec/Read - (AKA Read Latency) This is how long it takes to read from the chosen disk. The counter value is in seconds. The lower the better. An unburdened disk will read in less than 10 milliseconds.(.010) If you consistently see this counter exceed .020 it may be a problem. Especially for temp and log files.

Logical Disk > Average Disk sec/Write - (AKA Write Latency) Same as above, except for disk writes. Again more than .020 may be a problem.

Typically, these counters will indicate a problem together. If you see high queuing in conjunction with bad latency, consider alternative stripe sizes, offsets, Raid configurations, or as a last resort, adding more spindles to the array.

BTW.. JoelO has a good article on storage for SharePoint: https://blogs.msdn.com/joelo/archive/2007/09/12/sharepoint-disk-allocation-and-disk-i-o.aspx