sql server tweaks & installation · hardware for sql ram - as much as fits •10 000x faster...
Post on 03-Aug-2020
8 Views
Preview:
TRANSCRIPT
1.10.2017.
1
SQL Servertweaks & installation
for Windows Admins and Accidental DBAs
Vedran Kesegić
• Sponzori
• Pokrovitelji
SPONZORI I POKROVITELJI
1.10.2017.
2
About me
• M.Sc., FER, Croatia
• Mensa
• Microsoft Certified Master, MCT
• SQL Consultancy (performance)
• Author: XDetails (Addin for SSMS)
vedran.kesegic@gmail.com
SQL Server 2017 - launch 2.10.2017• Adaptive Query Processing
- Adaptive Join (new operator)• Solves variable workloads
• Uses Hash Join or Nested Loops, without recompile (uses same plan)
• Decision per exec, based on row count treshold
• Adaptive Query Processing- Interleaved execution• Exact table-valued function cardinality
(before: 100 rows SQL2014-2016, 1 row 2012-)
• No recompile, just deffers compile of that part until execution
• Adaptive Query Processing- Adaptive Memory Grant• Solves tempdb spills and wasted memory problem
• Cached plan has RAM grant size for sorts/hashes
• Size dynamically corrected up/down based on recent needs
• Auto Tuning - Auto Plan Correction• Solves Parameter Sniffing problem
• Leverages Query Store to auto-revert to previous good plan
• Auto Tuning - Auto Index Management (Azure)• Automatically creates missing and drops unused
indexes
Resumable index create/rebuild (continues after failover)Linux
1.10.2017.
3
Agenda
• Hardware for SQL (for on premise)
• Editions & Limits - (ab)use them!
• SQL in Virtual Machines - specific setup!
• BIOS Tweaks
• OS (Windows) Tweaks
• SQL Instance Tweaks
• Tempdb Tweaks
• SQL Database Tweaks
Hardware for SQL
CPU - maximize GHz
• Becase CPU is 50x cheaper than SQL per-core licence• Need more power? 4 sockets
(or multiple 1-socket machines)
CPUProcessing Power (GHz)= Cores * Frequency
Licence cost for SQL Enterprise (EUR) CPU cost (EUR)
Cost of p. power (EUR per 1 GHz)
Xeon D-1577 16 cores x 2.1GHz = 33.6 16 * 8 000 = 128 000 1 500 3 850 EUR
Xeon Gold 6144 8 cores x 4.2 GHz = 33.6 8 * 8 000 = 64 000 3 000 2 000 EUR
Xeon W-2155 10 cores x 4.5GHz = 45.0 10 * 8 000 = 80 000 1 500 1 800 EUR
Xeon W-2195 18 cores x 4.3 GHz = 77.4 18 * 8000 = 144 000 2 600 1 900 EUR
Xeon Platinum 8180 28 cores x 3,8 GHz = 106.4 28 * 8000 = 224 000 10 000 2 200 EUR
Cost
CPU SQL License
1.10.2017.
4
Storage• RAID
• RAID STRIPE size 64KB (manufacturer?)
• Format 64KB alloc unit (match stripe size)
• TEMPDB• Hast the MOST IO, like ALL other db together!
• Should be similar to RAM (small latency)
• 2xSSD in RAID1 Local! (not SAN) = 50 000 IOPS, <1ms Latency, 500 EUR
• User DATA files (.mdf)• Needs fast random READ (users do not wait for write)
• N x SSD (or HDD) in RAID 5 - fast read, space efficient
• User LOG files (.ldf)• Fast WRITE (users do not wait for read)
• 2xSSD RAID1 (can be 2xHDD RAID1 if only ONE log file)
• Always have 50GB+ free space on log drive for log growth explosion!
Storage• HP 3PAR StoreServ 8000
• SAN for medium-large business (can serve as NAS too)• Very fast, completely redundant controllers• All flash, 20 GByte/sec, 3 000 000 IOPS• 20 000 EUR
• Synology RS3617xs+• for small business (SAN + NAS)• 3,5” HDD or 2,5” SSD - any from compatibility list• 3 000 EUR + disks. No redundant controllers, not fast as 3PAR
• Synology DS3018xs and even cheaper models• For backup (NAS) or SAN for lab environments - reliable• 1 500 EUR + disks
1.10.2017.
5
Storage - Measure!
Preferably BEFORE SQL is installed!
Easier to fix early detected slowness
Do you know your numbers?
Do you know what you actually have?
Max IOPS, Avg Latency?
Perfmon Counters:
• Avg Disk Sec / Read
• Avg Disk Sec / Write
• Avg Reads / Sec
• Avg Writes / Sec
Latency (<20ms)
IOPS (>10 000)
Measure!
Average storage:
• Latency < 20ms• Sustained >50ms = alarm, action needed!
• IOPS• My 5 year old laptop: 50 000 IOPS (Samsung SSD)• SANs reach 3 000 000 IOPS+• What is your server’s sustained IOPS?
Crystal Disk MarkDISKSPD
You paid 20 000 IOPSbut got 20 IOPS
1.10.2017.
6
Hardware for SQL
RAM - as much as fits
• 10 000x faster than fastest SSD (Latency 0.000002 ms)
• Free - no SQL licence cost, hw is cheap
• RAM is almost entirely used for .mdf cache
• More RAM = Less reads from disk = (much) faster system
• SQL Standard Edition - limited to• 128 GB buffer pool + 32GB columnstore +OS (10GB)
• 32GB for Memory Optimized - per DB
• = approx. 200 GB RAM per machine
• max 24 CPU cores
The Limits• SQL Standard Edition - 2000 EUR/core
• max 24 CPU cores• approx. 190 GB RAM for SQL (+ add for OS)• Index rebuild blocks users (weekend?)• Availability Group secondary is not readable• With Software Assurance (around 25% core lic./year)
• Free secondary PASSIVE node (High Availability at low price)• Free upgrades to newest version
• SQL Enterprise Edition - 8000 EUR/core• Unlimited everything• ONLINE index maintenance (no blocking of users during index rebuild or creation)• For 24x7x365 High Availability & scaling multiple nodes
• SQL Developer Edition - Free• Unlimited everything (=Enterprise)• for test and dev (NOT prod) – only legal difference from Ent.E.
• SQL Express - Free, production, <10GB per DB (.mdf)
1.10.2017.
7
Virtualized EnvironmentsSTATIC memory for every SQL Server VM ???
SQL Server takes all RAM you give!• Uses it to cache .MDF files („buffer pool” cache)• More RAM = less disk reads (RAM > DB = zero reads = fast)• RAM shrinking =performance drops, CPU temporary rises (compiles)
Which Dev/Test environment will run faster?• Hypervisor 20GB RAM, 16GB available for 8 VM• Admin A: gives every machine STATIC RAM 16GB/8 VM = 2GB per VM• Admin B: gives every machine DYNAMIC 1-6GB RAM
(overprovisioned by a factor of 3x, because 6*8=48GB/16GB=3x)
Explanation• Most of the time dev VM are idle. Developers are on coffee • Chance that 2 machines will need their max memory at the same time is 10%• Chance that all 8 machines will need their max 6GB all at the same time is 0.1^8 =
0.000001% (never!)• A (reserved RAM) will be VERY SLOW - every VM reserved tiny RAM
VM Memory: Static
1.10.2017.
8
VM Memory: Static
Assigned RAM („Static” case)
Total Commited RAM
Assigned RAM(„Dynamic” case)
Actual work time
Avg RAM usage = 1.5GBShould we reserve 2GB?
1.10.2017.
9
Dynamic - use only needed
Dynamic - leverage unused space (increase MAX)
1.10.2017.
10
But not too much!
Virtualized Environments
Measures (per VM)
• Guest RAM = Guest thinks he is machine with that much RAM• Can by dynamic (can go only up) - if Dynamic is ON
• Assigned memory = Actual (RAM given to VM)• Assigned = Guest RAM - Baloon driver RAM• Can be lower than guest RAM (because of baloon driver)• Can be very dynamic (goes up and down) - if Dynamic is ON
• Committed memory = Demand (RAM+page file used by all processes within VM)
1.10.2017.
11
HyperV - VM MemoryTest or Dev• Use Dynamic! (Do NOT reserve)
• Specify min-max, low buffer (5%), and let the hypervisor manage memory
Production (non-critical)• Reserve 50%+
• Increase priority
• Buffer default (20%)
Production• Static
Not all SQL Servers are production!
Not all production is critical!
Virtualized EnvironmentsMeasuring VM RAM
• If Demand > Actual+10%AND PageFile activity => performance is hurt!• VM complains, hungry for more RAM
• Demand is bigger than max? Increase max
• Else, increase priority (if machine is important)
• If Actual > Demand+10% = idle RAM spent for nothing!• VM ate RAM it doesn’t need, others starve
• Using static? Go Dynamic! (can shrink)
• Min or Max bigger than demand? Set it lower!
• Released idle RAM boosts other VMs
• Actual = Demand ± 10% = Be Fit!
1.10.2017.
12
OS & BIOS settings
• BIOS• „Power Management” – set to „Disabled” or „OS control”
• Disable c-state, disable power saving / energy management
• Windows• High performance power plan (not „Balanced”)
• gpedit.msc – set time limit for Idle/Disconnected RDP sessions, eg 1h
• secpol.msc – give this permissions to „NT Service\MSSQLSERVER”• Perform volume maintenance task – faster restores & growth operations
• Lock pages in memory – only if more than 8GB of RAM
Antivirus
If you can’t avoid having it:
• Exclude folders: all data and log locations
• Exclude folders: all SQL „program files” locations, instance location
• Exclude process: „sqlsrvr.exe”
• Exclude extensions: mdf, ldf, ndf, bak, trn, dif
1.10.2017.
13
SQL Instance settings
SQL Instance settings• Max Server Memory
• Default is TERRIBLE – a max int number
• Good starting point is 3GB lower than total RAM
• Decrease until Available RAM>500MB and Free Ram>100MB for entire day
• CTOP (Cost Treshold for Parallelism)• Default is 5, way too low
• 500 is a good starting point
• MAXDOP• Default is 0 (all cores)
• Set to no more than CPUs in single NUMA node
• 4-8 is a good starting point
• Do not set MAXDOP 1. Instead, increase CTOP to 5000+ and MAXDOP low (eg. 4)
1.10.2017.
14
SQL Instance settings• Fill Factor
• Default is 0 (0=100=100%)
• Set to 90
• Less page splits = faster insert
• Enable Backup Compression• Faster backups
• Refresh Index and statistics• Use job scripts from Ola Hallengren
• Daily or Weekly
• REORGANIZE = always online! - daily
• REBUILD = offline on Standard edition - weekend
Tempdb
• Should mimic RAM performance (low latency)• Separate drive, preferred local (not SAN)
• SSD strongly suggested, almost mandatory
• Extreme: Standard Edition with > 200GB RAM –> RamDRIVE !• But only if you PROVE tempdb latency is a problem!
• Min 4 data files, equal size, euqal growth (MB, not pct!)
• Initial size of data and log• Set big enough so NO GROWTH occurs in normal situations
• Track the size few days, set initial size to current size• Max size unlimited
1.10.2017.
15
Database settingsFILEGROUPS
• DATA - if DB will grow > 50GB• Create new, with 4 equal files• Set as DEFAULT filegroup (new tables are created there)
• MYDB_DATA1.ndf• MYDB_DATA2.ndf• MYDB_DATA3.ndf• MYDB_DATA4.ndf
• PRIMARY• Minimal size (no user tables there!)
• MYDB.mdf
• Why?• Moving file_id 1 and 2 is NOT POSSIBLE online• File_id 1 is data file of PRIMARY filegroup - hardcoded, unchangeable• Keeping PRIMARY small = no need to MOVE
Tip of the iceberg...
• Many more tricks exist
1.10.2017.
16
Summary
• Changing several options - dramatic results
• 1.5TB database, 1500 request/sec
• India
Questions & Contact
vedran.kesegic@gmail.com
blog.sqlxdetails.com
THANK YOU!
top related