ตั้งค่าตาม Best Practice ทันทีตั้งแต่ตอนติดตั้ง Microsoft SQL Server 2019
ตั้งค่าตาม Best Practice ทันทีตั้งแต่ตอนติดตั้ง Microsoft SQL Server 2019
ผู้เขียนติดตาม Microsoft SQL Server 2019 จาก Community Technology Previews (CTPs) จนตอนนี้ออก Release Candidate (RC) แรกออกมาแล้ว
สิ่งหนึ่งที่ผู้เขียนพอใจมากคือมีการตั้งค่าระดับ Instance ตาม Best Practice ที่ควรจะเป็นให้ตั้งแต่ตอนติดตั้งเลย ดังนี้
- ค่า Max Degree of Parallelism
- ค่า Min Server Memory (MB) และ Max Server Memory (MB)
ค่า Max Degree of Parallelism (MaxDOP)
ในเวอร์ชั่นก่อนหน้า (ตั้งแต่ Microsoft SQL Server 2017 ลงไป) ค่า MaxDOP ในระดับ Instance ถูกตั้งไว้มีค่าเท่ากับศูนย์ (0) ตามภาพ
ซึ่งการตั้งค่า MaxDOP ให้เป็นศูนย์นั้นจะหมายความว่าในหนึ่งงานที่เข้าประมวลผลสามารถประมวลผลบน CPU เพียงCore เดียวหรือแตกออกเป็นงานย่อย ๆ ตั้งแต่สองงานย่อยไปจนถึงเท่ากับจำนวน Core CPU มากสุด ที่ Microsoft SQL Server สามารถใช้ได้
แต่สำหรับ Microsoft SQL Server 2019 นั้นจะตรวจนับจำนวน Core CPU ที่มี
และทำตาม Best Practice ในการตั้งค่า MaxDOP ตามลิงก์นี้
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sqlallproducts-allversions#Guidelines
จากเอกสาร Best Practice ของ Microsoft สรุปได้ดังนี้
| จำนวน NUMA Node | จำนวน Core CPU/Node | ค่า MaxDOP |
|---|---|---|
| มีเพียง Node เดียว | น้อยกว่าหรือเท่ากับ 8 | เท่ากับจำนวน Core CPU |
| มากกว่า 8 | 8 | |
| มีหลาย Node | น้อยกว่าหรือเท่ากับ 16 | เท่ากับจำนวน Core CPU |
| มากกว่า 16 | 16 |
มีหลายคนมักตั้งคำถามว่าเป็นศูนย์ก็ดีอยู่แล้วเพราะ Microsoft SQL Server ก็จะสามารถแตกงานออกเป็นงานย่อย ๆ ได้เท่ากับจำนวน Core CPU เลยไม่ใช่เหรอ
ไปกดมันไว้จะใช้งาน CPU ได้คุ้มค่าหรือไม่
ผู้เขียนขอทำความเข้าใจก่อน
โดยสมมติว่าเครื่อง Server ของเรามี CPU ให้ใช้ได้ 96 Cores แต่กำหนด MaxDOP เอาไว้เท่ากับ 8
จะหมายความว่า แต่ละงาน ที่เข้าประมวลผลสามารถประมวลผลได้บน Core เดียวไปจนถึงมากสุด 8 Cores ครับ
แต่งานอื่น ๆ ก็สามารถใช้ Core CPU ที่เหลือประมวลแต่ละงานบน Core เดียวไปจนถึงมากสุด 8 Cores เช่นกัน
การที่ MaxDOP มีค่าเป็นศูนย์ต่างหากที่ใช้ CPU ไม่คุ้มค่า
สมมติว่าหนึ่งงานถูกแตกออกเป็น 48 งานย่อยบน 48 Cores แต่มี 36 งานย่อยทำเสร็จแล้ว อีก 12 งานย่อยยังไม่เสร็จ
ทั้ง 36 งานย่อยจะรออีก 12 งานย่อยเสร็จจึงจะปล่อย Core CPU ไปให้งานอื่นใช้ได้ต่อไป
แบบนี้กลายเป็นใช้งาน CPU ไม่คุ้มค่ามากกว่า งานย่อยที่เสร็จแล้ว แต่ต้องรองานย่อยอื่น ๆ ให้เสร็จตามมาจะส่งสัญญาณ Wait ชนิด CXPACKET ออกมา
การกำหนด MaxDOP ตาม Best Practice แล้วได้ผลหรือไม่ เราสามารถติดตามได้จากสัญญาณ Wait ชนิด CXPACKET นี้
จริง ๆ แล้วยังมีค่าในระดับ Instance ชื่อ Cost Threshold for Parallelism อีกค่าที่สัมพันธ์กับค่า MaxDOP
ผู้เขียนเคยเขียนเกี่ยวกับทั้ง 2 ค่านี้มาแล้วในบทความก่อนหน้า ผู้อ่านสามารถกลับไปลองอ่านดูได้
หากตอนติดตั้ง Microsoft SQL Server 2019 นั้นผู้อ่านไม่ได้เข้ามาในหน้า MaxDOP เพื่อเปลี่ยนเป็นค่าอื่น
ขั้นตอนการติดตั้งจะใช้ค่าตาม Best Practice ให้ทันที
ค่า Min Server Memory (MB) และ Max Server Memory (MB)
สำหรับการกำหนดค่า Min Server Memory (MB) และ Max Server Memory (MB) ในระดับ Instance นั้น
ค่าตั้งต้นยังเป็นค่าเดิม คือ ค่า Min คือ 0 MB และค่า Max คือ 2,147,483,647 MB (2 PB) ก็แสดงว่าใช้ RAM ทั้งหมดเท่าที่มีเพราะคงไม่มี Server งานปกติทั่วไปใช้ RAM เกิน 2 PB เป็นแน่
อันที่จริงค่าตั้งต้นก็เป็นค่าที่ดีแล้ว ผู้เขียนมักบอกผู้ฟังบรรยายเสมอว่าให้ Microsoft SQL Server เป็น Dedicated Server อย่าติดตั้งบริการอื่น ๆ ลงไปให้บริการรวมกับ Microsoft SQL Server อีก ด้วยเหตุผลหลายประการ
ดังนั้น Physical Memory (RAM) นอกเหนือจาก OS ใช้งานแล้วก็ควรเป็นของ Microsoft SQL Server
สำหรับ Microsoft SQL Server 2019 สามารถเปลี่ยนมาใช้ค่า Recommended ตามรูปได้
= Stack Size * Max worker Threads |
โดย ค่า Stack Size ได้มาจากตารางต่อไปนี้
| OS Architecture | SQL Server Architecture | Stack Size |
|---|---|---|
| x64 (64-bit) | x86 (32-bit) | 768 KB |
| x64 (64-bit) | x64 (64-bit) | 2048 KB |
| IA64 (Itanium) | IA64 (Itanium) | 4096 KB |
และ Max Worker Threads หรือจำนวนงานย่อยมากสุดที่รองรับได้มาจากสูตรต่อไปนี้
| จำนวน Core CPUs | จำนวน Max Worker Threads |
|---|---|
| น้อยกว่าหรือเท่ากับ 4 | 512 |
| 4 – 64 | 512 + ( (Core CPUs - 4) * 16 ) |
| มากกว่า 64 | 512 + ( (Core CPUs - 4) * 32 ) |
สมมติว่าเราติดตั้ง Microsoft SQL Server 2019 (64 bit) บนเครื่องที่มี 96 Core CPUs
- Stack Size = 2,048 KBytes
- Max Worker Threads = 512 + ( ( 96 - 4 ) * 32) = 3,456
- ดังนั้น Max Server Memory จะเท่ากับ 2,048 KBytes * 3,456 = 7,077,888 KBytes หรือ 6.75 GBytes
ทั้งสองค่าเป็นตัวอย่างการปรับตัวที่ดีของ Microsoft SQL Server ที่ไม่กำจัดปัญหาจากการตั้งค่าไม่เหมาะสมออกไปตั้งแต่ตอนติดตั้ง
ทำให้การติดตั้งโดยประมาทจากผู้ที่ไม่มีความรู้เช่นการคลิก Next ไปเรื่อย ๆ อาจไม่ส่งผลเสียมากมายนัก
ซึ่ง Microsoft เองก็พยายามปรับตัวในเรื่องนี้มาตั้งแต่เวอร์ชั่น 2016 เห็นได้จากการสร้าง Data Files ของฐานข้อมูลตามการตรวจนับจำนวน Core CPUs เช่นกัน เพราะเพิ่มจำนวน Page Free Space Page มากขึ้นตามจำนวน Data Files เพื่อลดผลกระทบเรื่อง Allocation Contention https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d ดังภาพ
แต่ทั้งนี้การวางแผนทรัพยากรก่อนติดตั้งก็ยังคงมีความจำเป็น และเสริมด้วยการตั้งค่าตาม Best Practice ให้อัตโนมัติ
แบบนี้ ผู้เขียนว่ามาถูกทางมากกว่าครั้งไหน ๆ แล้วครับ