unity connect - getting sql spinning with sharepoint - best practices for the backend

#UCO16

June 20th, 2016

Getting SQL Spinning with SharePoint - Best Practices for the BackendKnut Relbe-Moe, Advania Knowledge Factory

Office Server & Services MVP Nintex vTE (Virtual Technical

Evangelist) SharePoint evangelist, mentor,

speaker Migration Expert Educator for Glasspaper Microsoft Certified Trainer Chief Technical Architect with

Advania Knowledge Factory @sharePTkarm (Twitter)

Knut Relbe-Moe

knutrelbemoe@outlook.com

#UCO16

About me

BLOG

LINKED IN

TWITTER

EMAIL

MEMBER OF

MEMBER OF

WORKS FORJOB TITLE

Microsoft MVPOffice Servers and Services

Chief Technical Architect

https://linkedin.com/in/shareptkarm

knutrelbemoe@outlook.com

http://sharepointblog.no@shareptkarm

SharePoint Saturday Oslo

NIWUG

AWARDED

AWARDED

Nintex vTENintex Virtual Technical Evangelist

Thanx to @atomicvee

THANK YOU TO THE TEAM BEHIND

#UCO16

MS SQL Server is like the brain of your SharePoint environment

#UCO16

So if the brain works slow, everything else will be slow….

#UCO16

99.9% of SharePoint content stored in SQL Server Farm Configuration information stored in configuration db

Central Administration content stored in seperate content db

Most Service Applications have at least one db All Web Applications have at least one content db

#UCO16

How to fix this

Follow the guidelines in this presentation Read and understand this whitepaper from Vlad Catrienscu Maximizing SQL 2012 Performance for SharePoint 2013 WhitePaper

If you have to reinstall MS SQL server as a new instance with the correct settings.

#UCO16

Agenda Foundamentals 13 tips to get your SQL Spinning Summary

#UCO16

Foundamentals

#UCO16

SQL Server configurationFor SharePoint 2013:

• SQL Server 2008 R2 SP1• SQL Server 2012 (SP1 for BI)• SQL Server 2014 (SP2013 SP1 & April CU)

For SharePoint 2016:• SQL Server 2014• SQL Server 2016

Use a dedicated instanceRun the service with a Managed account•No specific permission

Use named instances\ SharePoint

#UCO16

Tip 1

#UCO16

Physical SQL Server will always outperform a virtual SQL server with the same specs, but what shall you choose.Pros : High availability because of the hypervisor, not because of clusteringEasier to scale up & downCan be easier to recover at Disaster Recovery

Cons:Very heavily loaded VMs have seen disk I/O issuesOver allocation of a virtual host. If you have too much running on a host, it's not just I/O that could be impacted.Harder to protect

Physical vs Virtual

#UCO16

Tip 2

#UCO16

DISKS

You should invest in as fast disk as possible If you run on a Virtual Server, consider physical disks for the SQL server

SSD or SAN If you have physical SQL server you should use at least 6 Disks:

1. TempDB 2. TempDB Logs 3. SharePoint Databases 4. SharePoint Databases Logs 5. OS 6. SQL binaries and other application Installs

#UCO16

DISKS• You have a farm where most of the content is only read and

downloaded

• Your users will be collaborating, uploading documents, content

Speed/Scenario Read Intensive Scenario (ex Public Website) Fastest Tempdb data files and transaction logs . Database data files . Search databases, except for the Search administration

database Slowest Database transaction log files

Speed/Scenario Write Intensive Scenario ( collaborative workspace)

Fastest Tempdb data files and transaction logs . Database transaction log files . Search databases, except for the Search administration

database Slowest Database data files

#UCO16

Tip 3

#UCO16

RAM & CPU

• Your SQL Server needs to have enough RAM to function properly.

• If your SQL Server runs only SharePoint, here are the minimum requirements Small

Farm Deployment (0-500GB of Data)

Medium Farm Deployment (501-1TB of data)

Large Farm Deployment (1-2TB of Data)

Very Large Farm Deployment (2-5 TB of Data)

Special Cases

Ram Required

8GB 16GB 32 GB 64GB 64GB+

CPU 4 4 8 8 8

#UCO16

Tip 4

#UCO16

DISKS

SQL server reads and writes 64k at a time So change your allocation size from the 4K default to 64K. This small change alone, will improve your SQL performance by up to 30%

Use chkdsk <drive>to Verify

Use Format to Configure:• Format <drive> /Q /FS:NTFS /A:64K

/V:<volume> /Y

#UCO16

Tip 5

#UCO16

Server Configuration – Collation SharePoint server 2013 is built to use Latin1_General_CI_AS_KS_WS. This is a quote from Microsoft to prove it:

“We support any CI collation for the SQL instance (for master, tempdb databases). However we recommend using Latin1_General_CI_AS_KS_WS as the instance default collation (master, tempdb databases).” CI – (Case Insensitive) A and a ARE treated as the same character. AS – (Accent Sensitive) a and á are NOT treated as the same character. KS – (Kana Sensitive) Japanese Hirakana and Katakana characters which look the same are

NOT treated as the same character. WS – (Width Sensitive) Single-Byte and Double-Byte versions of the same character are

NOT treated as the same character.

#UCO16

Server ConfigurationFor SharePoint 2013:

• SQL Server 2008 R2 SP1• SQL Server 2012 (SP1 for BI)• SQL Server 2014 (SP2013 SP1 & April CU)

For SharePoint 2016:• SQL Server 2014• SQL Server 2016

Use named instances\ SharePoint

Use a dedicated instance

Run the service with a Managed account•No specific permission

#UCO16

Server ConfigurationUse alias for the connection string• Client alias or DNS alias (preferred)

Good practice : every SP Content db must be created (and documented) by a DBA after a strict capacity plan

#UCO16

Server ConfigurationUse alias for the connection string• Client alias or DNS alias (preferred)

Good practice : every SP Content db must be created (and documented) by a DBA after a strict capacity plan

#UCO16

Server Configuration Backups Compression

#UCO16

Tip 6

#UCO16

Click to insert photo.

Max Memory

Thomas Larock from SQL Rockstar explains this:

SQL Server (and other database systems such as Oracle and Sybase) need to read data pages into their internal memory before they can be used. Of course your server needs memory to operate as well. When your database engine and your server are competing for the same memory resources, you get bad performance. You want your server and your database engine to be like dancing partners, and less like my kids fighting over the last cupcake.

Max Memory

#UCO16

Tip 7

#UCO16

Optimize TempdB Split across n locations

N = total number of physical cores > super myth All temp files need to have the same size

Size of largest content dB or 10 % of Total Content DB’s Size whichever is bigger

The fastest disk possible | Place on a different Drive than the Content Database

Simple Recovery mode Place on Different Drive Than Content Databases

#UCO16

Model Database

SQL Server uses the Model database as a template for creating new user databases

So therefor make sure that Model database have correct initial size and growth settings. The log should be around 25% of the initial size. The auto growth is your insurance policy if your current database size has been reached.

#UCO16

Script

#UCO16

Tip 8

#UCO16

Auto Create Statistics

Do not enable auto-create statistics on a server that hosts SQL Server and SharePoint Server

Enabling auto-create statistics is not supported for SharePoint Server

#UCO16

Tip 9

#UCO16

USE “DBA” created databases

Sizing Autogrowth DO NOT USE SIMPLE RECOVERY

Unless you understand the implications

#UCO16

Tip 10

#UCO16

Maintenance Plan

Create a maintenance plan to backup databases, rebuild indexes and checking the logical and physical integrity of your database

Make sure you don’t run backup when heavy timer jobs runs in the SharePoint environment, like midnight

#UCO16

Recovery mode

Model db : recovery model = full

Tempdb : recovery model = simple

SharePoint DB : recovery model ?

Contend DB = full Config DB= simple

Services App DBs= it depends :•http://technet.microsoft.com/en-us/library/cc678868.aspx

#UCO16

Tip 11

#UCO16

Script your SQL Server installation https://sqlserverfinebuild.codeplex.com/

#UCO16

Tip 12

#UCO16

Always install SQL Alias in SharePoint

Use alias for the connection string• Client alias or DNS alias (preferred)

C:\Windows\System32\cliconfg.exe

#UCO16

Tip 13

#UCO16

Using SQL Server Resource Governor to optimize Search Database usage Part of SQL Server Enterprise (since SQL 2008) Limits CPU and memory usage on some DBs (and IO in SQL Server 2014) like search DBs

Allow less CPU & mem & IO usage during work hours Allow more CPU & mem & IO usage during off hours

#UCO16

Summary

#UCO16

10 simple tips to maximize performancehttp://www.sharepointblog.no/?p=851. SSD Disk or better2. Formating disk with 64K3. Language corralation Latin_CI_AS_KS_WS4. Maximum memory5. Changed Sizes on Model database6. Default fill factor to 807. Switched of autocreate statistics8. Max Degree of Parallelism – set to 1 (it has to be)9. Changed Initial Size & Autogrow on temp database, and created extra temp

files for the amount of processors.10. Don’t do backups at midnight.Maximizing SQL 2012 Performance for SharePoint 2013 WhitePaper by Vlad Catrinescu

STAY TUNED FOR MORE GREAT SESSIONS

http://unityconnect.com/online

#UCO16

Questions?

Speaker picture

@shareptkarmOffice Server & Services

#UCO16

Thank You!Knut Relbe-MoeChief Technical Architect, Advania Knowledge Factoryknutrelbemoe@outlook.comshareptkarm@outlook.com