Comunidade Portuguesa de SharePoint

XXXª Reunião Presencial

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• Best-Practices for Administration & Service Application

Databases

1SQL Installation Best-Practices

• General Considerations• CPU• Memory• Disk• Network

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

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)

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

Memory ConsiderationsCombined Size of Content Databases

Recommended RAM

Small Farm 8 GB

Medium Farm (<1TB) 16 GB

<2 TB 32 GB

>2 TB to <5 TB 64 GB

Important: If using SQL Mirroring you may needExtra RAM.

Disk Considerations

• Disk Performance is measured in IOPS (Input/Output Operations Per Second).

• If Disks have different IOPS values prioritize:1. Temp DB2. Database Transaction Logs3. Search Database4. Database Data Files

* In a heavily read-oriented portal site, prioritize data over logs.

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.

Disk Considerations (cont)Device IOPS Interface

7200 RPM SATA ~90 IOPS SATA II

10k RPM SATA ~130 IOPS SATA II

10k RPM S.A. SCSI ~140 IOPS SAS

15k RPM S.A. SCSI ~180 IOPS SAS

10k RPM SATA (queue depth 24)

~280 IOPS SATA II

• Use a Disk IO measuring tool like:• SQLIO• SQLIOSIM• IOMETER

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.

1Demo:Using SQLIOSIM to measure Disk Performance

Comments:Download SQLIOSIM and instructions on:

http://support.microsoft.com/kb/231619

2Best-Practices for Content Databases

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.

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)

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

Estimating GrowthInput Value

Number of documents (D) 100,000Calculated by assuming 2,000 users times 50 documents

Average size of documents (S) 250 KB

List items (L) 150.000

Number of non-current versions (V) 2Assuming that the maximum versions allowed is 10

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

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

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

Comments

3Best-Practices for Administration & Service Application Databases

Best-Practices for Administration & Service Application Databases

• We will use the following scale:

Best-Practices for Administration & Service Application Databases

Database Size Read/Write Scale Comments

Configuration Small Read-intensive Up No significant growth

Central AdminContent Small Varies Up No significant

growth

Content DBMedium-Large

200GB Recommended

VariesCollab – Write

Doc.Man – ReadAdd Other Content

DB’sScale up to 1 TB or

more only on Historical

Usage and Health Large – Extra Large Write-Intensive Up Dedicated Disk or Spindle

Application Registry Small Read-Intensive Up No significant

growth

Subscription Settings Small Read-Intensive Up, or <> Service

AppsCreated by Powershell

Secure Store Medium Read/Write Ratio Up, or <> Service Apps

Best-Practices for Administration & Service Application Databases

Database Size Read/Write Scale Comments

State Medium Varies Add other by PowerShell

Infopath & Visio Use

Web Analytics Staging Medium Varies Scale Out Size ~ # of Reports

Web Analytics Reporting Large / Extra Large Varies Up, or <> Service

AppsSize depends on retention policy

Search Administration Small /Medium Read/Write Ratio Up, or <> Service

AppsFit RAM Dedicated

Server

Search Crawl Medium/Large Read Intensive Add other DB Dedicated Server

Search Property Large/Extra Large Write Intensive Add other DB 1/3 Fit RAMDedicated Server

Profile Medium/Large Read Intensive Up, or <> Service Apps

Growth - Users & News Feeds

Best-Practices for Administration & Service Application Databases

Database Size Read/Write Scale Comments

Synchronization Medium/Large Read / Write Ratio Up, or <> Service Apps

# of Users / Groups & Ratio

Social Tagging Small / Medium Read Intensive Up, or <> Service Apps

# of Ratings, Tags & Notes

Managed Metadata Medium Read Intensive Up, or <> Service Apps Amount Metadata

Word Automation Small Varies Up, or <> Service Apps

No significant growth

PowerPivot Application Small - Up No significant

growth

Performance Point Small Read Intensive Up, or <> Service Apps -

FAST Search Administration Small Read Intensive Up No significant

growth

@Artur Santosartur.santos@rumos.ptwww.rumos.pt

Thank you for sponsoring!