101 Stupid Things Your
Colleagues Do When Setting
Up A SQL Server
Matt Slocum – Who am I?
• Senior DBA with a passion for SQL Server
– Experience with SQL Server v6.5-2014
– Specialize in server setup, replication/DR, and performance
• MCITP: DBA SQL Server 2005 & 2008
• MCSE: SQL 2012 Data Platform
• Rochester PASS SSUG President
• SQL Saturday Rochester Coordinator
Goals of this session
• Review some hot-spots and common mistakes that can be
addressed before releasing an instance of SQL Server into
production
• Installing SQL Server properly is not just about running the
SQL Server setup wizard properly, we will review the entire
infrastructure stack
– Server hardware – Storage – Virtualization – Operating System – SQL Server configuration/optionsGoals of this session
• Like so many things in SQL Server “Your mileage may
vary” or “It depends”
• You will need to research and test what options are best
for you and your environment.
Colleague #1 – Edmund
• Hardware Engineer
• Easily distracted (oooo,
SHINY!), but he’ll never make
the same mistake twice
• In his spare time he enjoys
sharing kingdom leadership
with his siblings
Physical Server Hardware – Go green
• Run with default BIOS power saving settings
– or
• Turn on/increase power saving settings
– All servers have power saving features and an energy saving profile is sometimes enabled by default
– Saving energy means slowing down the CPU (and bus/memory speeds as well)
• Be sure to check the BIOS for power saving features and turn
them off to get the full performance of your hardware
Physical Server Hardware – Save Money
• Repurpose old hardware
– This might be an option for non-production servers (provided that performance is not a requirement), but you really don’t want your production servers to suffer
• Use non-Intel CPUs
– In the past, AMD had some great CPUs that actually outperformed Intel CPUs (especially math computing)
– Since the Nehalem CPUs, Intel processors can do more work per core than any other manufacturer
– Competition is a good thing – both companies are striving to improve their CPUs with every generation
Physical Server Hardware – CPUs
• Order highest core count CPUs
– As the number of CPU cores increases the speed decreases
• Each individual thread will take longer on slower cores
– More cores means more expensive SQL licensing (2012+)
• However, fewer cores means fewer concurrent threads
– Research available processors and see what fits your budget (licensing costs) and needs (# of cores)
• Disable Hyper-threading (HT) on Nehalem+ CPUs
– Enabling HT increases the number of concurrent SQL threads which usually results in more transactions/sec
Physical Server Hardware – Memory
• Order just enough RAM
– RAM is one of the most reasonably priced upgrade options
when ordering a server – more is better
• Order slower memory
– 800/1066MHz should be enough for everyone, right?
– Slower memory will perform … slower
– Order the fastest RAM that the server supports
• Populate all memory sockets (maybe)
– Memory speed is typically throttled back when all memory
slots are filled – see previous point
Colleague #2 – Eustace
• Storage/SAN Administrator
• Under a lot of pressure to
strike a balance between
cost/performance/capacity
• In his idle time he accidentally
turned himself into a dragon.
Thankfully, the whole
experience changed him for
the better.
Physical Server Hardware – Save Money
• 1 Gb iSCSI connection to storage
• Create arrays with only SATA (or NL-SAS) drives
– Great for capacity, but horrible for performance – 7200 RPM just isn’t enough for SQL Server
– All flash or hybrid flash arrays are best, but arrays of 15K SAS/FC drives can work for moderate IO workloads
• A single drive (or RAID 0) provisioned for storage
– One drive to rule them all – and take everything down when it fails
• New clustering feature introduced in SQL Server 2012/2014
– Put your TempDB on local storage (like local SSD drives) – Ensure redundancy (RAID 1 – mirror is great for local SSD)
Storage – Why are slow arrays bad?
• EMC has this exact same slide in their deck for their XtremIO flash arrays, as do some other storage vendors
Storage – RAID Types/SAN
• What RAID type should you use for attached storage?
– It depends on what you need and what you can afford – A hot-spare is highly recommended
– Test your hardware with the different options and see what works best
• IOMeter and SQLIO/SQLIOSIM are both great tools to test your IO
• Additional considerations for a SAN
– It is surprisingly easy to set up a SAN incorrectly
– IO load for an application server is very different than the IO load for database server and the LUNs should be tuned appropriately
– Be sure to consult with your SAN vendor or manufacturer to verify what configurations should be used
Storage – Creating Data Partitions
• Format drives with default sector
size (4KB)
– SQL Server interacts with the storage in extents (1 or more per IO operation)
– 1 Extent = 8 x 8KB data pages = 64KB – SQL Server can read and write in
multiple extents in one operation
• Up to 2MB in a single IO operation
– Matching sector size to extent size makes IO more efficient
• This can vary by storage vendor, be sure to verify optimal sector size with hardware manufacturer
Storage – Capacity Planning
• Know your enemy load and plan accordingly
• Performance Monitor is your friend
– Transfers per second (read, write, and total)
– Average disk seconds per transfer (read and write)
• Multiply by 1000 to get milliseconds
– Current disk queue length (total)
– Average disk queue length (read, write, and total)
– Disk % Idle Time
– Bytes per read and bytes per write
Colleague #3 – Tumnus
• Virtualization Engineer
• A bit shy and inexperienced
with the systems he supports,
but he tries his best
• Found out the hard way that
the evil queen can turn you
into a stone statue
Virtualization – Facts
The facts:
• A virtual machine is not as fast as the raw hardware
it’s running on.
• Modern virtual kernels only incur ~3% (or less)
performance hit if configured properly
• Additional licensing/maint costs for virtual platform
If your SQL Server load is large enough, you may be
better off just clustering physical servers
Virtualization – Performance Hit
• Configure the OS setting in the VM configuration
to an OS other than the VM is running
– Performance issues/degradation due to VM kernel
optimizations incorrect for the installed OS
– Vmware/Xen – You may not be able to deploy a new VM
from an existing VM or a template
– VMware – You may not be able to upgrade the
tools/drivers
Virtualization – Host Memory Commitment
• Over commit memory - provision more RAM than you have in
the virtual host (VMware only)
– In VMware, once there is host memory contention, VMware will deploy the balloon driver which starts “consuming” memory in the virtual guests.
• The OS will start shelling data in memory to the page file (on disk)
– If host memory contention is critical, VMware will also start paging to disk, but it’s not as smart as the OS in the VM
• Hyper-V and Xen don’t allow RAM over-provisioning
– However, if you have a host failure without enough RAM on the remaining hosts, one or more VMs will remain down
Virtualization – Host CPU Commitment
• Over over-commit CPU resources
– Very easy to do and can yield poor performance
– When more CPUs are needed than are available the VMs have to wait for CPU resources
• VMware – CPU Ready %
– Lower is better – 5% or higher is considered bad
• Hyper-V (2012) – CPU Wait Time
– Measured in nanoseconds (ns) – you need to calculate the percentage of Wait Time vs Total Time – lower is better
• Allocate all CPUs for use with VMs (default setting)
– Reserve CPU0 for the virtual Kernel to prevent contention with the VMs
Virtualization – Resource Reservations
• It’s very easy to overlook resource reservations
– You can set the minimum CPU/RAM resources for your
most important servers so that their performance is
Virtualization – NUMA
• Don’t forget to enable/configure NUMA
– Non-Unified Memory Architecture (NUMA)
• Banks of RAM are associated with a physical socket and is managed directly by that CPU (1 CPU+RAM = 1 NUMA node) • Fully supported in VMware, Hyper-V, & Xen
• Not always enabled by default at the VM level • When configured, the VM is then NUMA aware
– When NUMA is not configured, VMs can cross host NUMA
nodes resulting in RAM latency
– In VMware, if you enable hot-add memory and/or hot-add
CPU for a VM, NUMA support is disabled
Virtualization – Drivers/Tools
• Use default virtual devices
– Default devices (SCSI controller & NIC) are typically emulated for compatibility, but this results in additional overhead/latency – VMware – The ParaVirtual SCSI controller should be utilized for
all drives that require low latency and high throughput
– VMware – The vmxnet3 NIC is currently the best option (v5.5)
• Be sure to install the latest tools/drivers on the guests to take
advantage of the latest advances/improvements
– Allow the VMs to directly interact with host hardware
Colleague #4 – Reepicheep
• Systems Engineer
• Sometimes forced to take
action before he has time to
properly examine the situation
• In his spare time, he has taken
up fencing and he’s quite
Windows OS – Permissions
• Grant no permissions to user that SQL services Run As
– Two key permissions in Group Policy Editor:
• Lock Pages in Memory
– Prevent the OS from paging SQL data in memory to disk
• Perform Volume Maintenance Tasks
– Allows Instant File Initialization
– Both are located in same location in GPEdit
• Computer Configuration Windows Settings Security Settings Local Policies User Rights Assignment
– You can grant these rights to Network Service (default Run As option for SQL Server), but it is best-practice to run the services under a domain or local user that is not a member of the
Windows OS – AntiVirus
• Install antivirus with default options
– Antivirus needs to be configured to exclude folders
• SQL Server binaries
• All (user & system) database data and log file folders
• Do you even need antivirus software installed on your DB
server? – It depends!
• If network traffic and user access is restricted to the server:
– Probably not
• If anyone can access the server from anywhere:
Windows OS – Drives
• Use default or vague volume labels
– Drive volume labels should be descriptive
• Describe type of data on that drive
• Include the drive letter (or mount point) in volume name
• Overlook local paths for clustering
– In SQL 2012 and higher, you can put TempDB on local storage (like mirrored SSDs)
• The same drive path needs to exist on all nodes
• The path will not be created when installing additional nodes in the cluster, nor will the installer verify that the drive letter even exists
Windows OS – Swap File
• Use the default page file size
– Windows Server 2003 - 2008 R2 by default will create a swap file equivalent to the memory in the server
• Incredibly inefficient when memory > 12GB
– Windows 2012 and 2012 R2 are much better at managing the page file, but you still need to manage their size
– Set page file to 8 GB and increase if needed (if proper memory reservations are in place, it shouldn’t need to be larger)
Colleague #5 – Lucy
• SQL Server DBA
• New to the job and lacks the
maturity of a seasoned DBA
• Hard worker even when others
don’t believe in her
• She found an entire kingdom
through a wardrobe in a “spare
oom”
SQL Server – Installation
• Install All Features with Defaults
– All binaries and system & user databases all end up on C:
– If the C: drive fills up and TempDB is unable to grow, SQL will crash
SQL Server – Installation
• Installing to the root is acceptable for the instance
SQL Server – TempDB Files
• Default single TempDB data file
– Multiple files recommended
– Old-school thought 1 file per CPU
– Current recommendation ¼ - ½ the
total number of CPUs/cores
(possibly much less)
– Robert Davis & Paul Randal both
have great blog posts on TempDB
file contention
SQL Server – Memory Configuration
• Utilize default Maximum memory
– SQL Server will consume ALL available physical memory – The OS will then swap data in memory to disk (ouch)
– Leave enough headroom for OS and any background processes
• Utilize default Minimum memory
– SQL Server will make a poor performance issue even worse by giving up almost all of its memory
– Set this equal to Max Memory or Max Memory minus 10-25%
Recommended (only SQL Instance)
Total RAM Max Memory
1 GB 512 MB
2-8 GB 75% total RAM 8-32 GB 85% total RAM 32-64 GB 90% total RAM >64 GB 92.5% total RAM
SQL Server – CPU Configuration
• Run with default CPU Affinity
SQL Server – Parallelism Configuration
• Utilize default Cost Threshold for Parallelism
– The value of 5 is a very old standard
– This will cause fairly insignificant queries to use parallel plans – Recommended to set this in the 35-50 range
– Test this in your environment as workloads vary greatly
• Utilize default Max Degree of Parallelism
– This will cause all parallel queries to utilize all CPUs (even if they cross NUMA nodes) severely impacting performance
– Microsoft recommends a maximum of the lesser of 8 or # of physical cores in 1 CPU
-http://support.microsoft.com/kb/2806535
SQL Server – Growth Settings
• Stick with the default Auto-Growth settings
– SQL 2005-2014 default auto-growth settings:
• Data Files – 1 MB • Log Files – 10%
– Data file will become extremely fragmented on disk
– Log file growth size will not be consistent resulting in large disparity in VLF size (Kimberly Tripp blog)
– Edit the properties of the Model database as appropriate – Fixed growth (adjust as necessary for your environment):
• Data Files – 100 MB - 4 GB • Log Files – 16 - 256 MB
SQL Server – Growth Settings
• Enable Auto-Shrink
– Huge IO performance hit
– If auto-growth is enabled, your database may
grow and shrink repeatedly
SQL Server – Maintenance Jobs
• Maintenance jobs? We don’t need no stinking
maintenance jobs!
– Whether using the default DB Maintenance plans or
creating your own jobs, you need to:
• Back up databases • Update statistics
• Verify database backups (can be done on another server) • Integrity checks (can be restored to another server and
checked)
• Index maintenance – defrag/rebuild – maybe not at all
– SQL backup/maintenance option with SAN/Flash Array
SQL Server – Documentation
• No Documentation!
– Make sure you document your configuration
• Are you really going to remember every single configuration setting when you set up another server 6 months from now?
• In case you get hit by a bus and your co-workers needs to know!
• Security audits may require it
– Kendal Van Dyke has a great PowerShell tool to document SQL Server configurations – Documentation is necessary at all levels of
SQL Server – Post-Setup Apathy
• Assume that all is well once your server is set up
– Monitor your server to ensure it is still healthy
• Performance Monitor
– Directly/manually
– Custom scripts/applications
– Third party tools (like SolarWinds) – Monitor SQL Server
• DMVs
• Monitoring tools
– SolarWinds DPA – excellent wait stats monitoring
– RedGate’s SQL Monitor – excellent overall monitoring including wait stats – SQL Sentry – excellent overall monitoring