Joe D’AntoniPSSUG

13-Nov-2013

8 Things to Do After You Install SQL Server

@jdanton TwitterBlog/Slides/Scripts joedantoni.wordpress.comSolution Architect, Anexinet

About Me

SQL Server’s Default Installation Rules are BAD!!!

Let’s Fix It!!

Overview

Set Max Memory• The default setting for max server memory is

2147483647 MB (2.1 Petabytes!!!)• If this setting is not changed SQL Server will

attempt to grab all of the memory on the box• This can lead to paging of the Windows O/S

• Best Practice is to allocate 80% of memory to SQL Server• The one exception is very large memory

servers—Windows generally needs about 6-8 GB to run comfortably

• Minimum Memory doesn’t need to be set except on VMs

Configure MaxDOPDefault setting is 0 which

uses all available processors in parallel query execution

This can lead to CXPACKET and Scheduler waits

Best Practice For servers > 8 CPUs =

MAXDOP=8For servers < 8 CPUs =

MAXDOP 0 to nSharepoint MAXDOP=1

Initial Size and Autogrowth are way too small initially

There is no right number—base on roughly how big your databases will be

Definitely, change autogrowth to remove percentage growth and go with fixed value

Goal is to avoid file system fragementation

Change Model File Sizes

Change Model Recovery Model• By default—Model is in full

recovery mode• Typically I set to simple—if a

database needs to be in full recovery mode, set it manually

If the number of logical processors < 8 then number of TempDB Files = number of CPUs

If logical processors > 8, then number of TempDB Files = 8If contention continues add

files in multiples of 4 All TempDB files should be

the same size and have same autogrowth settings

Add Files to TempDB

Create SQL Agent Alerts for Critical Errors• Ensures you get notified when something bad

happens on your server• Know that problems are happening before

your users do• Can tie alerts to actions and/or pages

Find out the current Service Pack and Cumulative Update level (sqlserverbuilds.blogspot.com)

Patch your server—no time like install time

Patch SQL Server

Script your Installs• Don’t use the GUI• Automate for consistency, and

speed• You should still QA—this

process is dependent on things like having standard disk letters

• Download at my blog

Do this stuffAutomate and RepeatYour Servers will love you

Slides joedantoni.wordpress.comTwitter @jdantonEmail jdanton1@yahoo.com

Summary