SharePoint Jump Start Bill Baer Senior Product Marketing Manager Microsoft Brian Alderman Chief Executive Officer MicroTechPoint Course Topics Tuning SQL Server 2012 for SharePoint 2013 Jump Start ID: 591547
Download Presentation The PPT/PDF document "Tuning SQL Server for" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Tuning SQL Server for SharePoint Jump Start
Bill Baer | Senior Product Marketing Manager, Microsoft
Brian Alderman | Chief Executive Officer |
MicroTechPointSlide2
Course Topics
Tuning SQL Server 2012 for SharePoint 2013 Jump Start
01 | Key SQL Server and SharePoint Server Integration Concepts (50 minutes)
Dedicated Server or Instance; SQL Database &
database files;
ldf
to
mdf
checkpoint process; Recovery model (Simple vs. Full)
02 | Best Practices: SQL Server Database Settings (50 minutes)
Model Database;
TempDB
(Location);
Initial size,
Autogrowth
, and Collation; Recovery model
03 | Server Settings for SQL Server (50 minutes)
Default Database File Locations;
Memory Settings; MAXDOP
04 | SQL Server and SharePoint Availability (50 minutes)
Avoiding large log files; Backups and Location of Backups;
SQL Server 2012
AlwaysOn
; Best PracticesSlide3
Module Agenda
SQL Server Instance Settings
Maximum Memory (Per Instance)
Default File Locations
Collation
MAXDOP
Operating System SettingsSlide4
SQL Server Instance SettingsSlide5
Default Maximum Memory SettingSlide6
SQL Server Memory Configuration
Maximum Memory Setting
SQL Max Memory =
TotalPhyMem
- (
NumOfSQLThreads
*
ThreadStackSize
) - (1GB * CEILING(
NumOfCores
/4))
NumOfSQLThreads
= 256 + (
NumOfProcessors
*
- 4) * 8
ThreadStackSize
= 2MB on x64 or 4 MB on 64-bit (IA64)
(* If
NumOfProcessors
> 4, else 0)Slide7
SQL Server Default File Locations
Must Restart SQL Server InstanceSlide8
SQL Server Collation SettingSlide9
Max Degree of ParallelismSlide10
Modifying SQL Memory SettingsModifying Default DB LocationsSlide11
Operating System SettingsSlide12
Operating System Settings
NTFS Allocation Unit Size
64K is optimal, 4K = 30% Performance Penalty
Use
chkdsk
<drive>Verify
Use Format to Configure:
Format <drive> /Q
/FS:NTFS /A:64K /V:<volume> /YSlide13
Verifying Operating System SettingsSlide14
Summary
Modify Memory Settings to Control Memory Usage by SQL Server
Change Default File Locations
Leave MAXDOP at 1 for SharePoint
Verify Operating System Set to 64KB Allocation Unit SizeSlide15