รู้จักฐานข้อมูล TempDB และการตั้งค่าที่ง่ายขึ้นบน Microsoft SQL Server 2016
จากการที่ผู้เขียนได้ทดลอง ติดตั้ง Microsoft SQL Server เวอร์ชัน 2016 พบว่าในส่วนของ Database Engine configuration สามารถกำหนดจำนวนไฟล์ที่ใช้ในฐานข้อมูล TempDB ลงไปได้ ดังรูป
ซึ่งไม่มีใน SQL Server เวอร์ชั่นก่อนหน้า และผู้เขียนเห็นว่าเป็นประโยชน์อย่างยิ่ง จึงนำมาขยายความให้เข้าใจว่าเหตุใด Microsoft ถึงเพิ่มสิ่งนี้เอาไว้ในส่วนของการติดตั้งเลย
ในหลักสูตร Microsoft SQL Server Database Administration ที่ผู้เขียนบรรยายอยู่ในสถาบัน 9Expert นั้น ผู้เขียนจะเน้นย้ำกับผู้เข้าฝึกอบรมอยู่เสมอว่ามี Best Practice สำหรับฐานข้อมูล TempDB ซึ่งแนะนำมาจาก Microsoft โดยตรง นั่นคือ ต้องวางแผนไฟล์และดิสก์สำหรับฐานข้อมูล TempDB โดยการแยกไฟล์ข้อมูล (ส่วนของ Data Files) ของฐานข้อมูล TempDB ออกไปอยู่บนดิสก์หรือชุดดิสก์ต่างหาก ไม่ปะปนกับดิสก์หรือชุดดิสก์ที่ใช้เก็บ OS และ Database Engine อีกทั้งไม่ปะปนกับฐานข้อมูลอื่นๆ อีกด้วย
โดยดิสก์หรือชุดดิสก์นั้นควรต้องเป็น RAID 0 (Stripe Set) ที่เร็วที่สุดเท่าที่จะหาได้ นอกเหนือจากนั้น หากจำนวน core ของ CPU น้อยกว่าหรือเท่ากับ 8 เช่น มี 2 core ก็ให้สร้างไฟล์ข้อมูล 2 ไฟล์ แยกลง 2 ดิสก์หรือ 2 ชุดดิสก์ หากมี 4 core ก็ให้สร้างไฟล์ข้อมูล 4 ไฟล์ แยกลง 4 ดิสก์หรือ 4 ชุดดิสก์เป็นต้น แต่หากมีจำนวน core ของ CPU เกินกว่า 8 ก็ให้สร้างไฟล์ข้อมูลเพียงแค่ 8 ไฟล์ ลง 8 ดิสก์หรือ 8 ชุดดิสก์ สิ่งนี้ทำเพื่อลดความคับคั่งในการเข้าถึงตำบลของ page ข้อมูลบนฐานข้อมูล TempDB
โดยปกติปัญหาความคับคั่งจะฟ้องออกมาในรูปแบบของ Error Message และนั่นเป็นเครื่องบ่งชี้ว่าความคับคั่งยังสูงอยู่ การแก้ไข คือ การเพิ่มจำนวนไฟล์ข้อมูลแยกลงบนดิสก์หรือชุดดิสก์ขึ้นไปอีก ผู้อ่านสามารถทำตามส่วนของ Resolution ในเอกสาร Recommendations to reduce allocation contention in SQL Server TempDB database โดยเพิ่มจำนวนตามคำแนะนำไปจนความคับคั่งลดลง แต่หากปัญหาความคับคั่ง ไปเกิดในส่วนของเพจ SGAM แล้ว เราอาจจำเป็นต้องกำหนด Trace Flag หมายเลย 1118 ตอนสตาร์ท Service ของ Database Engine ขึ้นมา
ทำไมฐานข้อมูล TempDB ถึงสำคัญ
มีคนจำนวนไม่น้อยที่เข้าใจว่า Temp Objects อาทิ เช่น Temp Table สร้างขึ้น และ เรียกใช้งานจาก Memory แต่ไม่ใช่เลย การสร้าง Temp Objects ไม่ว่าชนิดใดก็ตาม จะถูกบันทึกลงบนดิสก์ ในส่วนของฐานข้อมูล TempDB ผู้เขียนจะทดสอบให้ดูจากคำสั่งต่อไปนี้
USE TSQL;
GO
CREATE TABLE dbo.#Products
(
productid int IDENTITY(1,1) NOT NULL,
productname nvarchar(40) NOT NULL,
supplierid int NOT NULL,
categoryid int NOT NULL,
unitprice money NOT NULL,
discontinued bit NOT NULL,
);
GO
IF Object_ID('dbo.#Products') IS NULL
PRINT 'No Table'
ELSE PRINT 'Found Table';จากนั้นผู้เขียนลองเปลี่ยนไปใช้ฐานข้อมูล TempDB แทน (ในขณะที่ยังอยู่ใน Session เดิม) และค้นหา Temp Object อีกครั้ง ผลลัพธ์ที่ได้คือ Found Table แสดงให้เห็นว่า Temp Object ถึงแม้คำสั่งในการสร้างจะออกจากฐานข้อมูลใดๆ ก็ตาม แต่ Temp Object จะถูกสร้างลงในฐานข้อมูล TempDB อยู่ดี
การสร้าง Temp Object ชื่อเดียวกันจากต่าง Session กัน จริงๆ แล้ว บันทึกลงฐานข้อมูล TempDB แยกเป็นคนละตารางโดยนำชื่อของ Temp Object ไปต่อท้ายด้วย Session ID
แล้วทำไมต้องมา Tune Up ฐานข้อมูล TempDB กันด้วย เหตุผลง่ายๆ แต่สำคัญ ก็คือ ฐานข้อมูล TempDB แบกรับการสร้าง Temp Objects ของทั้ง Instant เอาไว้ ไม่ว่า Instant นี้จะมีซักกี่ฐานข้อมูลก็ตาม ยิ่งมีฐานข้อมูลมาก และผู้ใช้ในแต่ละฐานข้อมูลมากพอสมควร ผู้ใช้หลายคนอาจเรียกใช้ Stored Procedure ตัวเดียวกัน โดย Stored Procedure ดังกล่าวมีการสร้าง Temp Object ขึ้นมาทดข้อมูล
จากรูปให้กำหนดจำนวนของไฟล์ข้อมูล (Data Files) ตาม Best Practice ที่กล่าวมาแล้ว ในที่นี้ติดตั้งลงบนเครื่องที่มีจำนวน core ของ CPU เท่ากับ 4 จากนั้นให้เพิ่มตำแหน่งที่อยู่ของไฟล์ข้อมูล (Drive และ Folder) เท่ากับจำนวนไฟล์ที่ระบุ และตำแหน่งที่อยู่ของไฟล์ข้อมูล (Drive และ Folder) จะต้องแยกกันอยู่คนละ Physical Disk หรือ ชุดดิสก์ ห้ามเป็นการแบ่ง Partition บน Physical Disk เดียวกัน หรือแบ่ง Folder บน Physical Disk เดียวกันเด็ดขาด
หรือตรวจสอบผ่าน SSMS (SQL Server Management Studio) โดยเข้าไปดูในส่วน Properties ของฐานข้อมูล TempDB
จะพบว่า Microsoft SQL Server จะสร้างไฟล์ข้อมูลตามจำนวนที่ระบุ และแยกลงตามตำแหน่งของไฟล์ข้อมูลที่ระบุเอาไว้โดยอัตโนมัติ ซึ่งสะดวกมากกว่าสมัยก่อนที่จะต้องติดตั้งจนเสร็จเสียก่อน แล้วจึงมาย้ายไฟล์ข้อมูลไปยังตำแหน่งที่อยู่ที่ต้องการ
จะเห็นว่าการที่ไม่ลดความคับคั่งในการเข้าถึงตำบลของ page ข้อมูลบนฐานข้อมูล TempDB ถือเป็นผลกระทบรุนแรง ซึ่งมีการให้น้ำหนักไว้ถึง 8.1 และหากอยากทราบข้อแนะนำก็สามารถ Drill Down เข้าไปดูในส่วนของ SQL Focus Area
นับเป็นเครื่องไม้เครื่องมือที่แจ้งเตือนและให้ความรู้กับผู้ดูแลระบบได้อย่างยอดเยี่ยม แต่จะดีกว่าหรือไม่ หากผู้ดูแลระบบฐานข้อมูลได้วางแผนและจัดการสิ่งเหล่านี้ไว้ตั้งแต่ต้น คือตั้งแต่เริ่มติดตั้งกันเลยทีเดียว