Transcript Putting a Better SQL Server in Production

Putting a Better SQL Server in
Production
Who am I?
Putting a Better SQL Server in
Production
“We will sell no wine,
before its time.”
~ Orson Welles
(Paul Masson wine ad)
Planning a Deployment
• Application requirements and Service
Level Agreements (SLAs) should drive
planning
– Performance requirements
– Availability requirements
– Recoverability requirements
Planning a Deployment
• Settings at install time
– Service accounts
• Non-user domain accounts
– Components to install
• Only required components
– Storage layout
– Power management plan
• High performance
Planning a Deployment
• Storage layout
Default Storage Configuration:
Volume Usage
Directory
RAID
C
Operating System
C:\Windows
O or 1
D
SQL Server executables &
system data/log files
D:\Program Files\Microsoft SQL Server 0 ir 1
E-F
DB & tran log backups
E:\Backups
6
G-N
User DB data files
G:\MSSQL\DATA
10
O-P
User DB log files
O:\MSSQL\LOG
10
T
Tempdb data and log files
T:\MSSQL\DATA
10
Planning a Deployment
• Storage layout
Default Cluster Storage Configuration:
Volume Usage
Directory
RAID
C
Operating System
C:\Windows
0 or 1
D
SQL Server executables
D:\Program Files\Microsoft SQL Server 0 or 1
E
DB & tran log backups
E:\Backups
H
User/System DB data files H:\MSSQL\DATA
10
O
User/System DB log files
O:\MSSQL\LOG
10
Q
Quorum drive (if used)
Q:\
Any
R
DTC (if used)
R:
Any
T
Tempdb files
T:\MSSQL\DATA
10
6
Planning a Deployment
• Additional storage considerations
– Distribution database
– Replication data share
– Full-text catalogs (pre-SQL 2008)
Planning a Deployment
• Power Management Plan
– Select High Performance power plan
• Via Control Panel
– Verify current power usage with CPU-Z utility
• Download from www.cpuid.com
Post-installment Settings
• Settings post-install time
– Min and Max Server Memory
– Lock Pages in Memory
– AWE, 3GB, PAE
– Instant File Initialization
– Max Degree of Parallelism
– tempDB configuration
Post-installment Settings
• Min and Max Server Memory
– Min Memory = lowest operating value for the
instance
• Less important if LPiM is set
• Still important for memory ramp-up
– Loads extents into memory instead of pages
• RoT: default to ½ of Max Server Memory
Post-installment Settings
• Min and Max Server Memory
– Max Memory = total memory minus the
amount required to be left free.
• Best option is to baseline an existing system
– SQLServer:Buffer Manager\Stolen pages
– SQLServer:Buffer Manager\Reserved pages
– (Stolen pages + Reserved pages)/100
Post-installment Settings
• Min and Max Server Memory
– 2nd best option is to estimate startup VAS
Reservation
• Formula for calculating is detailed on Jonathan
Kehayias’ blog:
• http://sqlblog.com/blogs/jonathan_kehayias/archive
/2009/07/07/understanding-the-vas-reservationaka-memtoleave-in-sql-server.aspx
Post-installment Settings
• Min and Max Server Memory
– RoT For unknown workloads:
•
•
•
•
4 GB server - a minimum of 1 GB of RAM
8 GB or more - a minimum of 2 GB of RAM
32 GB or more a minimum of 4 to 6 GB of RAM
Additional memory required for non-buffer pooled
SQL processes
–
–
–
–
DTS packages
SSIS packages
Linked servers
CLR
Post-installment Settings
• Lock Pages in Memory
– Do not set until Max Memory is set
– Required to use AWE in 32 bit
– Highly debated whether needed for 64 bit
– Set with Local Security Policy Editor
• gpedit.msc
Post-installment Settings
• AWE - Address Windowing Extensions
– Allows SQL Server to address more than 4
GB of RAM
– 32 bit only
• 64 bit SQL Server ignores this setting
– Requires Lock Pages in Memory
Post-installment Settings
• 3GB
–
–
–
–
Allows SQL Server to address 3 GB of RAM
32 bit only
Often used in conjunction with the /PAE switch
In Windows 2003 and earlier, /3GB switch added
to the boot.ini file of the OS
– In Windows 2008+, replaced by IncreaseUserVA
• Boot.ini replaced by Boot Configuration Data
• Edit by using BCDEdit from a command line:
BCDEdit /set IncreaseUserVA 3072
Post-installment Settings
• PAE - Physical Address Extensions
– Allows Windows to address up to 128 GB of RAM
– 32 bit only
– Enterprise and Datacenter Editions of Windows only
• Standard Edition only supports a maximum of 4 GB of RAM
– Often used in conjunction with the /3GB switch
– Required by AWE
– May already be enabled to support other features in
Windows 2003 SP1+
• Hot-add memory
• Data-Execution Prevention (DEP)
Post-installment Settings
• PAE - Physical Address Extensions
– In Windows 2003 and earlier, /PAE switch
added to the boot.ini file of the OS
– In Windows 2008+, /3GB replaced by PAE
• Boot.ini replaced by Boot Configuration Data
• Edit by using BCDEdit from a command line:
BCDEdit /set PAE ForceEnable
Post-installment Settings
• Instant File Initialization
– Recommended for all SQL 2005+ servers
– Set using Local Security Policy Editor
• Gpedit.msc
• Perform Volume Maintenance Tasks
– May be prohibited by certain compliancy laws
Post-installment Settings
• Max Degree of Parallelism
– Generic starting point
– > 8 logical CPUs: Max DOP = 8
– <= 8 logical CPUs: Max DOP = 0
– NUMA (HT): Max DOP = # of CPUs per
NUMA node
– OLAP or large databases with large data
operations may need a lower DOP
Post-installment Settings
• tempDB configuration
– On dedicated drive
– Multiple data files
– 1 log file
– Data files pre-sized and auto-growth disabled
– Log file pre-sized to 2X size of data file and
auto-growth enabled with a set growth size
(not %)
– Pre-sized files consume at least 90% of drive
tempDB: How many data files? 1:1?
• Official Microsoft recommendation still 1:1
– Tested by PSS/SQL team on SQL 2008 R2 on
SQL Server with > 64 logical CPUs
– Actual need: 1 per concurrent process using
tempDB
• Is this a realistic recommendation?
How many data files, really?
• Systems that need 1:1 data files are rare
• Evidence indicates that performance does
degrade somewhat with more files
– Average I/O block size decreased
– Data access patterns appear random
• Most SQL Servers may not need more
than 1:4 or 1:2 data files per logical CPUs
– This is only a starting point
Start with 1:4 or 1:2 data files?
• It depends!
• What is your comfort level with dealing
with tempDB contention?
• Can you recognize it?
• Do you know how to fix it?
• Are you actively monitoring for it?
• If it occurs, are you okay with the time it
would take to respond to and fix it?
What is tempDB contention?
• Latch contention on allocation pages
– PFS: Page Free Space
• Page 1 and every 8088 pages
– GAM: Global Allocation Map
• Page 2 and every 511,232 pages
– SGAM : Shared Global Allocation Map
• Page 3 and every 511,232 pages
– PAGEIOLATCH_xx waits
– PAGELATCH_xx waits
Monitoring tempDB Contention
• Use DMV sys.dm_os_waiting_tasks
• Parse resource_description column
– <database ID>:<file ID>:<page number>
– Database ID = 2 for tempDB
– File ID = ID of a data file
– Page number = do the math
• GAM: (Page ID – 2) % 511232
• SGAM: (Page ID – 3) % 511232
• PFS: (Page ID – 1) % 8088
Monitoring tempDB Contention
• http://www.sqlsoldier.com/wp/sqlserver/bre
akingdowntempdbcontention
Configuring tempDB files
• 1 log file only
• All files pre-sized to avoid data growth
• All data files the same size
– Required for round-robin usage
• Set auto-growth on log file to hard value,
not percentage
Configuring tempDB files
• Recommended (optional):
– Set log file to double the size of a single data
file
– Disable auto-growth on the data files
Large files & tempDB startup
• Make sure instant file initialization is
enabled
• TempDB reuses the existing files
– Does not zero initialize the log file if it already
exists
• http://sqlblog.com/blogs/jonathan_kehayia
s/archive/2010/05/13/does-the-tempdblog-file-get-zero-initialized-at-startup.aspx
Thanks!
• Thanks for joining!
• Thanks to Idera for sponsoring!
• Session files will be available at
http://www.sqlsoldier.com/BetterSQL
• My blog: www.sqlsoldier.com
• Twitter: twitter.com/SQLSoldier