Partager via

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

Comments

  • Anonymous
    November 18, 2007
    There is lots of good information on how to optimize storage for SQL, perhaps too much. Some great articles

  • Anonymous
    February 06, 2008
    The SCCP SharePoint Models were just released via the SharePoint team blog about an hour ago. Let me

  • Anonymous
    February 06, 2008
    The SCCP SharePoint Models were just released via the SharePoint team blog about an hour ago. Let me

  • Anonymous
    June 27, 2008
    So.. you are an IT Manager and you have been presented with a SharePoint server topology based on a capacity

  • Anonymous
    August 19, 2008
    Space has been allocated on the SQL cluster to separate the Search DB and the Content DBs on different disks/LUNs. Good. Now how do I get MOSS to create the Search DB on the separate disk? I probably to pre-create the Search DB on the SQL Server and then point MOSS to that DB but how? Thanks in advance for your help.

  • Anonymous
    August 19, 2008
    Short of changing SQL's default location which is not ideal you can just create the database on the destination disk and then create the SSP specifying the database names you precreated. There's an article on precreating databases at http://technet.microsoft.com/en-us/library/cc262869.aspx

  • Anonymous
    August 22, 2008
    Thanks Michael. I'll try that. I assume that mean I'll have to delete the current SSP with STSADM and then re-create it? Thanks for the link to the technet article.

  • Anonymous
    August 22, 2008
    I missed that part. :-0 You can simply detach the databases and move the data and log to the appropriate location and reattach. Now that I think about, that's probably the easiest way to go. Hope this helps.