1 / 32

Putting a Better SQL Server in Production

Putting a Better SQL Server in Production. Who am I?. @SQLSoldier. 11+. www.sqlsoldier.com. Putting a Better SQL Server in Production. “We will sell no wine, before its time.” ~ Orson Welles (Paul Masson wine ad). Planning a Deployment.

gefen
Download Presentation

Putting a Better SQL Server in Production

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Putting a Better SQL Server in Production

  2. Who am I? @SQLSoldier 11+ www.sqlsoldier.com

  3. Putting a Better SQL Server in Production “We will sell no wine, before its time.” ~ Orson Welles (Paul Masson wine ad)

  4. Planning a Deployment • Application requirements and Service Level Agreements (SLAs) should drive planning • Performance requirements • Availability requirements • Recoverability requirements

  5. 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

  6. Planning a Deployment Storage layout Default Storage Configuration:

  7. Planning a Deployment Storage layout Default Cluster Storage Configuration:

  8. Planning a Deployment • Additional storage considerations • Distribution database • Replication data share • Full-text catalogs (pre-SQL 2008)

  9. 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

  10. 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

  11. 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

  12. 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:BufferManager\Stolen pages • SQLServer:BufferManager\Reserved pages • (Stolen pages + Reserved pages)/100

  13. 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-reservation-aka-memtoleave-in-sql-server.aspx

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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)

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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?

  24. 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

  25. 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?

  26. 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

  27. 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

  28. Monitoring tempDB Contention http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontention

  29. 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

  30. Configuring tempDB files • Recommended (optional): • Set log file to double the size of a single data file • Disable auto-growth on the data files

  31. 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_kehayias/archive/2010/05/13/does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx

  32. Thanks! • Thanks for joining! • Thanks to Iderafor sponsoring! • Session files will be available at http://www.sqlsoldier.com/BetterSQL • My blog: www.sqlsoldier.com • Twitter: twitter.com/SQLSoldier

More Related