1 / 27

Comunidade Portuguesa de SharePoint

XXXª Reunião Presencial. Comunidade Portuguesa de SharePoint. DD/MM/YYYY. Optimizing SQL Server for SharePoint 2010. Agenda. SQL Installation Best-Practices for SharePoint General CPU Memory Disk Network Best-Practices for Content Databases

allayna
Download Presentation

Comunidade Portuguesa de SharePoint

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. XXXª Reunião Presencial Comunidade Portuguesa de SharePoint DD/MM/YYYY

  2. Optimizing SQL Server for SharePoint 2010

  3. Agenda • SQL Installation Best-Practices for SharePoint • General • CPU • Memory • Disk • Network • Best-Practices for Content Databases • Best-Practices for Administration & Service Application Databases

  4. 1 SQL Installation Best-Practices General Considerations CPU Memory Disk Network

  5. General Considerations • Have different separate disks for: • System Paging File • Data Files • Log Files • Temp DB • Backups • Assign to the Service Accounts the “Lock Pages in Memory” permission • Disable Auto-Create Statistics • Set MAX DEGREE OF PARALLELISM to 1

  6. CPU Considerations • Assign as many CPU’s as you can • Count Dual-Core processors as 2 • Count each processor that supports Hyper-Threading as 1 • Max Worker Threads • Logical CPU’s<=4 – 512 • Logical CPU’s>4 - 512 + ((logical CPUS’s - 4) * 16)

  7. CPU Considerations w/SQL Mirror • Principal Server: • 1 global thread • 2 threads per mirrored DB • Mirror Server: • 1 global thread • 2 threads per mirrored DB • 1 thread for each group of 4 Cores, if more than 4 • Witness Server • 2 global threads

  8. Memory Considerations Important: If using SQL Mirroring you may need Extra RAM.

  9. Disk Considerations • Disk Performance is measured in IOPS (Input/Output Operations Per Second). • If Disks have different IOPS values prioritize: • Temp DB • Database Transaction Logs • Search Database • Database Data Files * In a heavily read-oriented portal site, prioritize data over logs.

  10. Disk Considerations (cont) • Sharepoint supports all types of storage: • Direct Attached Storage (DAS) • Storage Area Network (SAN) • Network Attached Storage (NAS) • Only for Content Databases using Remote Blob Storage • Sharepoint must ping in 1ms, and receive the 1st byte of data in 20ms • If SAN cannot garantee this use DAS.

  11. Disk Considerations (cont) • Use a Disk IO measuring tool like: • SQLIO • SQLIOSIM • IOMETER

  12. Network Considerations • Web Servers & Application should have TWO GIGABIT NICs, one for client requests, the other DEDICATED for SQL Server. • If using Mirror, use dedicated GIGABIT NICs on each SQL Server for that.

  13. 1 Demo:Using SQLIOSIM to measure Disk Performance Comments: Download SQLIOSIM and instructions on: http://support.microsoft.com/kb/231619

  14. 2 Best-Practices for Content Databases

  15. Best Practices for Content Databases • Use multiple data files for content databases • Only create files in the primary filegroup for the database. • Distribute the files across separate disks. • The number of data files should be less than or equal to the number of core CPUs. Count dual core processors as two CPUs for this purpose. Count each processor that supports hyper-threading as a single CPU. • Create data files of equal size.

  16. Best Practices for Content Databases • Avoid raising the size of content databases above 200 GB • Create Multiple Databases if necessary. • Site collections per content database: • 2000 recommended • 5000 maximum • A site collection should not exceed 100 GB unless it is the only site collection in the database, or used for historical purposes (Read-Only)

  17. Best Practices for Content Databases • Set the database autogrowth value to a fixed number equal to avg. growth in a week /month to minimize the number of file increases. • Maintain a level of at least 25 percent available space across disks to allow for growth and peak usage patterns

  18. Estimating Growth Database Size: (((100.000*2))*250KB)+((10KB*(150.000+(100.000*2) Database Size: ~50GB

  19. Features that influence the size of content databases • Recycle Bins • Until a document is fully deleted from both the first stage and second stage recycle bin, it occupies space in a content database. • Auditing • Estimate the number of new auditing entries for a site, and multiply this number by 2 KB • Office Web Apps • Create a separate Content Database for Office Web Apps cache. • http://blogs.msdn.com/b/jjameson/archive/2011/03/03/installing-and-configuring-office-web-apps-on-sharepoint-2010.aspx

  20. 2 Demo:Manually Creating a Database & Adding it to a Web Application Comments

  21. 3 Best-Practices for Administration & Service Application Databases

  22. Best-Practices for Administration & Service Application Databases • We will use the following scale:

  23. Best-Practices for Administration & Service Application Databases

  24. Best-Practices for Administration & Service Application Databases

  25. Best-Practices for Administration & Service Application Databases

  26. @ Artur Santos artur.santos@rumos.pt www.rumos.pt

  27. Thank you for sponsoring!

More Related