รู้จัก TempDB และการตั้งค่าบน SQL Server 2016

รู้จักฐานข้อมูล 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 ขึ้นมา 

รูปแสดงการกำหนด Trace Flag ผ่าน Startup Parameter ของ SQL Service

จากคำแนะนำข้างต้น จึงเป็นที่มาว่า  
ทำไม Microsoft ถึงให้ความสำคัญในเรื่องจำนวนของไฟล์ข้อมูลของฐานข้อมูล TempDB
และ สามารถกำหนดได้ตั้งแต่เริ่มการต้นติดตั้ง
 
ทำไมฐานข้อมูล 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';

No Table

--IF Object_ID('dbo.#Products') IS NULL
--    PRINT 'No Table'
--ELSE PRINT 'Found Table';
--GO

SELECT @@SPID as SessionID;
GO

USE tempdb;
GO

IF Object_ID('dbo.#Products') IS NULL
    PRINT 'No Table'
ELSE PRINT 'Found Table';
GO

--IF Object_ID('dbo.#Products') IS NULL
--    PRINT 'No Table'
--ELSE PRINT 'Found Table';
--GO

SELECT @@SPID as SessionID;
GO

USE tempdb;
GO

IF Object_ID('dbo.#Products') IS NULL
    PRINT 'No Table'
ELSE PRINT 'Found Table';
GO

SessionID
-----------
51

(1 row(s) affected)

Found Table

รูปแสดงการทดสอบหา Temp Object ใน Session ID=59

ผลลัพธ์คือไม่พบ Temp Object ใดๆ ที่สร้างจาก Session ID=51 ใน Session ID อื่นๆ
จึงสรุปได้ว่า Temp Object นั้นไม่ว่าคำสั่งสร้างจะมาจากฐานข้อมูลใดๆ ก็ตามจะถูกบันทึกลงบนฐานข้อมูล TempDB เสมอ
และจะมีอยู่และใช้งานได้เฉพาะใน Session ที่สั่งสร้างเท่านั้นเมื่อปิด Session Object เหล่านั้นก็จะถูกลบทิ้งไป

บางคนเลยสรุปเอาเองว่า เดี๋ยวมันก็มีอยู่ เดี๋ยวมันก็หายไป แบบนี้ต้องอยู่ใน Memory แน่ๆ ซึ่งไม่เป็นความจริง
สุดท้ายผู้เขียนจะแสดงให้เห็นว่า Temp Object ชื่อเดียวกัน สามารถถูกสร้างขึ้นได้หากมาจากต่าง Session กัน ดังรูป

การสร้าง Temp  Object ชื่อเดียวกันจากต่าง Session กัน จริงๆ แล้ว บันทึกลงฐานข้อมูล TempDB
แยกเป็นคนละตารางโดยนำชื่อของ Temp Object ไปต่อท้ายด้วย Session ID ตามรูป

แล้วทำไมต้องมา Tune Up ฐานข้อมูล TempDB กันด้วย เหตุผลง่ายๆ แต่สำคัญ ก็คือ
ฐานข้อมูล TempDB แบกรับการสร้าง Temp Objects ของทั้ง Instant เอาไว้ ไม่ว่า Instant นี้
จะมีซักกี่ฐานข้อมูลก็ตาม ยิ่งมีฐานข้อมูลมาก และผู้ใช้ในแต่ละฐานข้อมูลมากพอสมควร

ผู้ใช้หลายคนอาจเรียกใช้ Stored Procedure ตัวเดียวกัน โดย Stored Procedure ดังกล่าว
มีการสร้าง Temp Object ขึ้นมาทดข้อมูล แค่นี้ก็รุมสร้าง Temp Object กันมากมายแล้ว
ไม่เพียงแค่นั้นการทำงานหลังบ้าน ของ Microsoft SQL Server เอง ก็มีการสร้าง Temp Object ขึ้นมามากมาย เช่นกัน
อาทิ Isolation level บางประเภท ก็ใช้งาน TempDB ,การประกาศ Data Type ชนิด Table เบื้องหลังก็เก็บลง TempDB
เมื่อจำเป็นต้องใช้พื้นที่ใน Memory อาจต้อง swap ข้อมูลใน Memory ออกไปทดไว้ใน TempDB ก่อน
และ ยังมีกลไกอีกมากมายที่ใช้งาน TempDB

ดังนั้น จึงเกิดความคับคั่งขึ้นมา และจำเป็นต้องวางแผนเพื่อลดความคับคั่ง ดังเอกสารที่ได้กล่าวมาแล้ว
 
การตั้งค่าจำนวนไฟล์ข้อมูลตั้งแต่ติดตั้งทำได้อย่างไร

จากรูปให้กำหนดจำนวนของไฟล์ข้อมูล (Data Files) ตาม Best Practice ที่กล่าวมาแล้ว
ในที่นี้ติดตั้งลงบนเครื่องที่มีจำนวน core ของ CPU เท่ากับ 4 จากนั้นให้เพิ่มตำแหน่งที่อยู่ของไฟล์ข้อมูล (Drive และ Folder) เท่ากับจำนวนไฟล์ที่ระบุ
และตำแหน่งที่อยู่ของไฟล์ข้อมูล (Drive และ Folder) จะต้องแยกกันอยู่คนละ Physical Disk หรือ ชุดดิสก์

ห้ามเป็นการแบ่ง Partition บน Physical Disk เดียวกัน หรือแบ่ง Folder บน Physical Disk เดียวกันเด็ดขาด เพราะ การทำเช่นนั้น ไม่เกิดประโยชน์แต่อย่างใด
(รายละเอียดติดตามในหลักสูตร Microsoft SQL Server Database Administration)

จากนั้นระบุตำแหน่งที่อยู่ของไฟล์ Log ผู้เขียนไม่ค่อยให้ความสำคัญกับตำแหน่งที่อยู่ของไฟล์ Log ของฐานข้อมูล TempDB เท่าใดนัก
เพราะส่วนใหญ่แล้วฐานข้อมูล TempDB มักกำหนด Recovery Model เป็น Simple Recovery Model
(แต่สำหรับฐานข้อมูลอื่น ๆ ที่กำหนด Recovery Model เป็น Full Recovery Model หรือ Bulk-Log Recovery Model ผู้เขียนให้ความสำคัญเป็นพระเอก ที่ต้องดูแลอย่างดี รายละเอียดติดตามในหลักสูตร Microsoft SQL Server Database Administration)

เมื่อกำหนดค่าต่าง ๆ เสร็จเรียบร้อย ให้กด Next และติดตั้งไปตามปกติ
เมื่อติดตั้งเสร็จ เราสามารถตรวจสอบว่าไฟล์ข้อมูลของฐานข้อมูล TempDB ได้ถูกสร้างขึ้นตามตำแหน่งที่อยู่หรือไม่ ดังรูป

หรือตรวจสอบผ่าน SSMS (SQL Server Management Studio) โดยเข้าไปดูในส่วน Properties ของฐานข้อมูล TempDB ดังรูป

จะพบว่า Microsoft SQL Server จะสร้างไฟล์ข้อมูลตามจำนวนที่ระบุ และแยกลงตามตำแหน่งของไฟล์ข้อมูลที่ระบุเอาไว้โดยอัตโนมัติ
ซึ่งสะดวกมากกว่าสมัยก่อนที่จะต้องติดตั้งจนเสร็จเสียก่อน แล้วจึงมาย้ายไฟล์ข้อมูลไปยังตำแหน่งที่อยู่ที่ต้องการ
จากนั้นต้องมานั่งเพิ่มไฟล์เพิ่มดิสก์เพื่อให้ได้ตาม Best Practice ต่ออีก แบบนี้ลดขั้นตอนลงไปได้เยอะ
 
นอกเหนือจากความพยายามผลักดัน Best Practice ออกมาในหลายช่องทาง อาทิ เป็นเอกสาร White Paper, เป็น Help บรรจุอยู่ใน Book Online, เป็น Video ใน Channel 9 MSDN
และ ผ่านเอกสารฝึกอบรมจากทาง Microsoft แต่เชื่อเหลือเกินว่ามีกลุ่มคนอยู่พอสมควรที่เพิ่งทราบว่าจำเป็นต้องทำสิ่งนี้

แต่หากไม่ได้ตั้งค่าตาม Best Practice และใช้งานไปแล้ว Microsoft ยังออกเครื่องมือให้สามารถทำ SQL Server Assessment
หรือ ทำการสแกนหาจุดบกพร่องของการตั้งค่าผ่านเครื่องมือที่เรียกว่า System Center Advisor เป็นส่วนหนึ่งของ System Center Operations Manager

ซึ่งในปัจจุบันเปลี่ยนชื่อใหม่เป็น Operational Insight (ปัจจุบันอยู่ในส่วนของ Log Analytics) เป็นส่วนหนึ่งของ Microsoft Operations Management Suite
ซึ่งสามารถดูแลได้ทั้งที่อยู่บน Public Cloud และ Data Center ของผู้อ่านเอง
หน้าตาของ SQL Assessment เป็นดังรูป 

จะเห็นว่าการที่ไม่ลดความคับคั่งในการเข้าถึงตำบลของ page ข้อมูลบนฐานข้อมูล TempDB ถือเป็นผลกระทบรุนแรง
ซึ่งมีการให้น้ำหนักไว้ถึง 8.1 และหากอยากทราบข้อแนะนำก็สามารถ Drill Down เข้าไปดูในส่วนของ SQL Focus Area ดังรูป

นับเป็นเครื่องไม้เครื่องมือที่แจ้งเตือนและให้ความรู้กับผู้ดูแลระบบได้อย่างยอดเยี่ยม แต่จะดีกว่าหรือไม่
หากผู้ดูแลระบบฐานข้อมูลได้วางแผนและจัดการสิ่งเหล่านี้ไว้ตั้งแต่ต้น คือตั้งแต่เริ่มติดตั้งกันเลยทีเดียว