New Function in SQL Server 2019
สวัสดีครับเพื่อนๆ วันนี้ผมจะมานำเสนอเรื่อง New Function ของ Microsoft SQL Server 2019 ว่ามีความสามารถใหม่อะไรกันบ้างนะครับ เพื่อนๆ รู้หรือไม่ว่า Microsoft SQL Server คืออะไรและตอนนี้ Microsoft SQL Server 2019 มี function อะไรใหม่ๆ ที่น่าสนใจเพิ่มขึ้นบ้าง เรามาดูกันเลยครับ
SQL Server คืออะไร??
เริ่มแรกต้องขออธิบายให้เพื่อนๆ รู้จักกับตัว SQL Server กันก่อนเลยนะครับ Microsoft SQL Server คือ เป็นระบบที่ใช้ในการบริหารจัดการฐานข้อมูลที่ดีที่สุดของ Microsoft โดยเป็นในรูปแบบของ RDBMS (Relational Database Management System) ซึ่งจะเป็นการบริหารข้อมูลให้กับผู้ใช้บริการต่าง ๆ รองรับการทำงานได้จำนวนมาก และมีความสามารถมากมาย เทียบเท่ากับระบบฐานข้อมูลอื่น ๆ เช่น Oracle, DB2, Informix เป็นต้น อีกทั้งตัวโปรแกรมยังมีความเด่นในเรื่องของ User Interface ที่สามารถใช้งานได้ง่ายนั้นเองคร้าบ 😊
New Functions ใน SQL Server 2019
เมื่อเรารู้จักกับ SQL Server คร่าวๆกันแล้ว ก็ได้เวลาที่เราจะมาทำความรู้จัก function ใหม่ๆ ที่ทาง Microsoft พัฒนาขึ้นมาใน Microsoft SQL Server 2019 จะน่าสนใจขนาดไหนกันน้า~ พร้อมแล้วเรามาเริ่มกันเลยครับ โดยผมจะนำเสนอ function ต่างๆที่จะช่วยเพิ่มประสิทธิภาพการทำงานของเพื่อนๆบน database ที่น่าสนใจ ดังนี้ครับ
1. Approximate Count Distinct
โดยปกติแล้วเมื่อเพื่อนๆ ต้องการดูจำนวนข้อมูลแบบ Unique , Not null ใน database จะต้องใช้ COUNT และ DISTINCT ใน query ใช่ไหมละครับ แต่ใน SQL Server 2019 ได้มี function ใหม่เพิ่มเติมขึ้นมาก็คือ APPROX_COUNT_DISTINCT นั้นเองครับ มันเป็น function ที่จับเอา COUNT และ DISTINCT มารวมกัน แต่เดี๋ยวก่อนนะครับ!!! ผมต้องบอกก่อนเลยว่า เพื่อนๆอย่าพึ่งคิดว่าผลลัพธ์ที่ได้จาก COUNT DISTINCT และ APPROX_COUNT_DISTINCT จะเหมือนกันนะครับ เพราะว่าผลลัพธ์ที่ได้จาก APPROX_COUNT_DISTINCT นั้นคือค่าประมาณของจำนวนข้อมูลแบบ Unique และ Not null ของ table นั้นเอง ผมได้ทดสอบการใช้ function โดยเปรียบเทียบ query ที่ใช้ COUNT DISTINCT กับ APPROX_COUNT_DISTINCT ใน query select ข้อมูลจำนวน ID (ข้อมูลใน column เป็น Unique และ Not null ทั้งหมด) จาก table ที่มีจำนวน 10,000,000 rows สามารถสร้าง table ได้จาก script ดังนี้ครับ
CREATE TABLE TEST_RESUMABLE
( ID INT IDENTITY PRIMARY KEY,
Name NVARCHAR(50),
Address NVARCHAR(50)
)
DECLARE @ID INT
SET @ID = 1
WHILE @ID <= 10000000 --เพื่อนๆสามารถเปลี่ยนจำนวน row ข้อมูลจากตรงนี้
BEGIN
INSERT INTO TEST_RESUMABLE VALUES
('TestData - ' + CAST(@ID AS NVARCHAR(10)),
'TestCountry - ' + CAST(@ID AS NVARCHAR(10)))
PRINT @ID
SET @ID = @ID + 1
END
พบว่า ผลลัพธ์ที่ได้จาก function COUNT DISTINCT คือ 10,000,000 นั้นถูกต้องครับ แต่ผลลัพธ์จาก function APPROX_COUNT_DISTINCT ที่ได้คือ 10,062,312 ซึ่งไม่ตรงกับความเป็นจริงครับ query ทั้งสอง query ให้ผลลัพธ์ที่ต่างกันออกไปทั้งในเรื่องของผลลัพธ์ที่ได้จาก query และเวลาที่ใช้ในการ execute เพื่อนๆสามารถดูผลการเปรียบเทียบ query ได้จากภาพด้านล่างนี้ครับ
เพื่อนๆสงสัยเหมือนผมไหมครับว่าทำไมทาง Microsoft ถึงต้องสร้าง function นี้ขึ้นมากันละ ทั้งที่ผลลัพธ์ที่ได้ก็ไม่ถูกต้อง นั้นเพราะว่า APPROX_COUNT_DISTINCT ถูกออกแบบมาให้รับมือกับข้อมูลจำนวนมากๆประมาณ 1,000,000 row หรือมากกว่าและรับมือกับ column ที่มี distinct value จำนวนมากได้โดยใช้เวลาที่น้อยกว่าการใช้ COUNT DISTINCT นั้นเอง ผมได้ทดสอบโดยการนำ execution plan ของ query ที่ใช้ COUNT DISTINCT กับ APPROX_COUNT_DISTINCT มาเปรียบเทียบพบว่า query ที่ใช้ function APPROX_COUNT_DISTINCT นั้นใช้ Compile CPU และ Compile Time น้อยกว่า query ที่ใช้ function COUNT DISTINCT นั้นเองครับ
สรุป
Function APPROX_COUNT_DISTINCT เป็น function ที่จะใช้ประมาณจำนวนของข้อมูลที่เป็น Unique และ Not null ซึ่งใช้เวลาและทรัพยากรในการ execute น้อยกว่า function COUNT DISTINCT นั้นเอง เพื่อนๆสามารถลองนำ function ไปปรับใช้กับ query ดูนะครับ แต่ต้องดูความเหมาะสมในการใช้งานด้วยนะครับ ว่างานของเพื่อนๆต้องการอะไรมากกว่ากันระหว่างความถูกต้องของข้อมูลหรือความเร็วและการใช้ทรัพยากรในการทำงานน้อย
2. Scalar UDF Inlining
ใน SQL Server 2019 มี function ใหม่ที่จะช่วยให้คุณสามารถทำงานกับ UDFs(User-Defined Functions) ได้อย่างมีประสิทธิภาพมากขึ้นนั้นก็คือ Scalar UDF Inlining แต่ก่อนอื่นเพื่อนๆรู้ไหมครับว่า UDFs (User-Defined Functions) คืออะไรและมีประโยชน์อย่างไร?? ผมจะมาอธิบายให้เข้าใจง่ายๆกันนะครับ โดยปกติแล้วใน database จะมีฟังก์ชั่นพื้นฐานที่ต่างๆที่คอยสนับสนุนการใช้งานในการคำนวณหรือจัดการข้อมูล อย่างเช่น Sum, Average, Trim เป็นต้น ซึ่งบางครั้งฟังก์ชั่นเหล่านี้ก็ไม่สามารถตอบโจทย์การใช้งานได้ทั้งหมด แต่เราก็ยังสามารถสร้างฟังก์ชั่นขึ้นมาใช้ได้เองหรือที่เรียกว่า UDFs (User-Defined Functions) นั้นเอง เพื่อนๆสามารถดูตัวอย่างการนำ UDFs เข้ามาใช้งานได้ตามตัวอย่างด้านล่างครับ ผมจะยกตัวอย่าง Query ธรรมดาๆ ที่มี expression อยู่ใน select statement โดยนำ Column A และ B มาคำนวณ ดังนี้
SELECT SUM (Column_A *(1 — Column_B))FROM Table;
ต่อมาเราจะทำการ Create Function ให้สามารถรับค่าของ Column A และ B จาก query แล้วนำมาคำนวณแทนการเขียน expression ธรรมดาเหมือน query ตัวอย่างกันนะครับ แต่ UDFs จะสามารถ return ค่าเป็น single value ที่สามารถส่งค่ากลับมาได้หลายประเภท ยกเว้น text, ntext, image, cursor และ timestamp ครับ
CREATE FUNCTION Example_Function(@a DECIMAL(12,2), @b DECIMAL(12,2))RETURNS DECIMAL (12,2) ASBEGINRETURN @a * (1 — @b);END
นำ Function ที่สร้างขึ้นมาไปใส่แทนที่ expression และกำหนดค่าตัวแปรที่ตัวการนำไปคำนวณ เท่านี้ก็เสร็จสิ้นแล้วครับ
SELECT SUM(Example_Function(Column_A, Column_B ))FROM Table;
เมื่อรู้จักกับ UDFs กันแล้ว ผมก็จะทำการเปรียบเทียบการใช้งานระหว่าง query ที่ไม่ใช้ UDFs กับ query ที่ใช้ UDFs without inline และ query ที่ใช้ UDFs with inline ดังนี้ครับ
query ที่ 1 ไม่ใช้ UDFs
เราเริ่มจากใช้ query ธรรมดาๆ ที่นำ SUBSTRING มาใช้ใน query มาเป็นตัวอย่าง ซึ่งผลลัพธ์ที่ได้จากการรันก็คือเราจะได้ข้อมูลจำนวน 10,000,000 rows โดยใช้เวลาในการ execute query คือ 51 วินาที ตามรูปด้านล่างครับ
query ที่ 2 ใช้ UDFs without inline
ต่อมาผมได้สร้าง UDFs ขึ้นมาเพื่อทำการ SUBSTRING แทนการเขียนเป็น expression และปิด option inline ไว้ จากนั้นทำการรัน query ที่ใช้ UDFs โดยไม่มี option inline ซึ่งผลที่ได้จากการรันก็คือเราจะได้ข้อมูลจำนวน 10,000,000 rows โดยใช้เวลาในการ execute query คือ 1 นาที 35 วินาที ตามรูปด้านล่างครับ
จากนั้นเรามาดู Execution plan ของ query ที่ใช้ UDFs แบบไม่มี inline กันครับ จะเห็นได้ว่า Execution plan จะแยกส่วนกันระหว่าง query กับ function UDFs ที่เราสร้างขึ้นมา ซึ่งค่า cost จะไม่ได้ถูกคำนวณรวมกัน อาจจะทำให้การ tuning ได้ยากขึ้น
query ที่ 3 ใช้ UDFs with inline
ใน query สุดท้ายผมทำการสร้าง UDFs ที่เหมือนกัน แต่คราวนี้เราจะใช้ option inline ด้วย เมื่อสร้างเสร็จก็ทำการรันกันเลยครับ ซึ่งผลที่ได้จากการรันก็คือเราจะได้ข้อมูลจำนวน 10,000,000 rows โดยใช้เวลาในการ execute query คือ 56 วินาที ตามรูปด้านล่างครับ
งั้นเรามาดู Execution plan ของ query ที่ใช้ UDFs แบบมี inline กันต่อเลย จะเห็นได้ว่า Execution plan ค่า cost ถูกคำนวณโดยมี UDFs อยู่ใน plan เดียวกัน
สรุป
สุดท้ายนี้ผลที่ได้จากการรัน query ทั้งสามแบบก็คือ query ที่ไม่ใช้ UDFs นั้นใช้เวลาในการรันน้อยที่สุดอยู่ที่ 51 วินาที รองลงมาเป็น query ที่ใช้ UDFs with inline ที่ใช้เวลาในการรัน 56 วินาที และสุดท้าย query ที่ใช้ UDFs without inline ที่ใช้เวลาในการรัน 1 นาที 35 วินาที ตามตารางเปรียบเทียบด้านล่างครับ
จะเห็นว่า function inline สามารถทำให้เวลาที่ใช้ในการรันของ query ที่ใช้ UDFs ลดลง นอกเหนือจากนั้นก็คือ การคำนวณ cost ของ Execution plan ที่รวม select statement กับ UDFs อยู่ใน plan เดียวกัน
ทำไมไม่ใช้ query ที่ใช้ expression ธรรมดา??
แต่ถ้าเพื่อนๆสงสัยว่าทำไมเราไม่ใช้งาน query ที่เขียน expression ธรรมดาละใช้เวลาน้อยกว่า UDFs with inline อีก ผมก็ต้องบอกว่าขึ้นอยู่กับการใช้งานนะครับ เจ้าตัว UDFs จะทำให้เราไม่ต้องเขียน expression เดิมทุกครั้งที่ใช้ แต่ถ้าเพื่อนๆไม่ได้ใช้ expression ใน query บ่อยนักละก็ใช้ query ที่เขียน expression ธรรมดาก็ได้ครับ
3. Table Variable Deferred Compilation
ใน SQL Server 2019 นั้นมี function ที่เกี่ยวข้องกับ Table variable เพิ่มขึ้นมาใหม่นั้นก็คือ Table Variable Deferred Compilation ซึ่งเป็น function เสริมขึ้นมาเพื่อทำให้เราสามารถทำงานกับ Table variable ได้มีประสิทธิภาพมากขึ้น แต่ก่อนอื่นเพื่อนๆรู้จัก Table variable กันไหมครับ?? Table Variable นั้นมีมาตั้งแต่ SQL Server 2000 แล้วนะครับ เป็นตัวแปรประเภทหนึ่งที่ใช้เก็บข้อมูลแบบตารางเพื่อใช้งานชั่วคราว เมื่อเราใช้งานเสร็จมันก็จะทำลายตัวเองทิ้งทันที แล้วมันจะแตกต่างกับ Temporary table ยังไงละ?? เจ้าตัว Table variable มันจะคล้ายกับ local temporary table (จะทำลายตัวเอง เมื่อเราใช้งานระบบเสร็จ) แต่ต่างกันที่วิธีการเก็บครับ Temporary table จะถูกเก็บไว้ใน tempdb แต่ Table variable จะถูกเก็บไว้ที่หน่วยความจำตอนที่เรานำมาใช้งานนั้นเองครับ เพื่อนๆสามารถดูตัวอย่างการสร้าง Table variable ขึ้นมาใช้งานได้ตาม script ด้านล่างครับ ซึ่งตาม script จะหมายถึงเราสร้าง Table variable ขึ้นมา 1 table แล้วเราก็ทำการ insert ข้อมูลจาก table อื่นที่มีอยู่ใน database ลงไป จากนั้นก็ทำการเรียกใช้งาน Table variable ใน query ครับ
--สร้าง Table variable
DECLARE @Test TABLE
([TestID] INT,
[TestName] VARCHAR(30),
[TestAddress] VARCHAR(30));--เพิ่มข้อมูลลงใน Table variable
INSERT INTO @Test
SELECT ID, Name, Address
FROM AdventureWorks2017_Test.dbo.Test_Resumable;--เรียกใช้ Table variable
SELECT *
FROM @Test;
โดยใน SQL Server 2019 นี้มีการปรับปรุงให้ query ที่มีการใช้งาน Table variable ดีขึ้นโดยเปิดการใช้งาน deferred compilation ครับ ผมจะทำการเปรียบเทียบกันระหว่าง query ที่ใช้ Table variable ที่เปิดและปิด deferred compilation ให้ดูกันนะครับ ซึ่งเราสามารถตั้งค่าการใช้งาน deferred compilation ได้จาก
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON(ใช้)|OFF(ไม่ใช่)Query ที่ใช้ Table variable และปิด deferred compilation
เมื่อรู้วิธีการเปิดและปิดกันแล้ว เรามาลอง query ที่ใช้ Table variable และปิด deferred compilation กันก่อนเลย ผลที่ได้จากการรันคือ ข้อมูลจำนวน 10,000,000 row และใช้เวลาในการรัน 3 นาที 39 วินาที ครับ
Query ที่ใช้ Table variable และเปิด deferred compilation
ต่อมาเรามาลอง query ที่ใช้ Table variable และเปิด deferred compilation กันครับ ผลที่ได้จากการรันคือ ข้อมูลจำนวน 10,000,000 row และใช้เวลาในการรัน 2 นาที 26 วินาที ครับ
สรุป
จากการรันทั้งสอง query จะเห็นได้ว่า ผลลัพธ์ที่ได้จาก query ทั้งสองนั้นเหมือนกัน แต่เมื่อเราทำการเปิด deferred compilation แล้ว query ของเราจะใช้เวลาในการรันน้อยลงเมื่อเทียบกับตอนปิด deferred compilation จึงสรุปได้ว่า function deferred compilation นั้นสามารถทำให้ query ที่เรียกใช้ Table variable นั้นสามารถลดเวลาในการ execute ได้นั้นเองครับ
4. Resumable Online Index Create
เพื่อนๆ ทราบหรือไม่ครับว่า function Resumable นั้นมีประโยชน์อย่างไร?? ผมต้องบอกก่อนนะครับว่า function Resumable Online นั้นเคยมีมาก่อนแล้วใน SQL Server 2017 ก็คือ Resumable ของการ rebuild index นั้นเอง ประโยชน์ของ function นี้ก็คือเมื่อต้อง rebuild index ที่ใช้เวลานาน แล้วดันมีเหตุจำเป็นที่ทำให้ต้อง cancel การ rebuild index เช่น การทำ Database failover, ปัญหาเรื่อง disk space, resource contention และ transaction log เป็นต้น เพื่อนๆก็คงไม่อยาก start rebuild ใหม่โดยที่ต้องใช้เวลานานเหมือนเดิมใช่ไหมละครับ Function นี้จะช่วยทำให้เราสามารถ stop การ rebuild index และสามารถ resume การ rebuild ต่อจากที่เรา stop ได้นั้นเอง
ใน SQL Server 2019 ได้มีการเพิ่ม function ในเรื่อง resumable ขึ้นมาเหมือนกัน function นั่นก็คือการ resumable ของการ create index นั่นเองครับ เพื่อนๆคงจะได้ใช้ประโยชน์จาก function นี้แน่ๆหากต้องการสร้าง index จาก table ที่มีขนาดใหญ่เป็นล้าน row หรือมากกว่าเพราะอาจจะต้องใช้เวลานานในการสร้าง เรามาลองทดสอบการใช้งาน function Resumable create index กันเลย โดยเริ่มจากสร้าง table ที่มีข้อมูล 10,000,000 row หรือมากกว่านั้นก็ได้นะครับ ผมจะใช้ script ในการสร้างตามรูปด้านล่าง
จากนั้นทำการสร้าง index จาก table ที่เราพึ่งสร้างขึ้นมา โดยผมทำการสร้าง index “TEST_INDEX” จาก column ที่ชื่อ Name และ Address จาก table “TEST_RESUMABLE” ซึ่งเราต้องเพิ่ม Option ในการสร้าง index ให้ Online และ Resumable เป็นค่า on ด้วยนะครับ เมื่อพร้อมแล้วกด execute ได้เลยครับ
CREATE NONCLUSTERED INDEX TEST_INDEX
ON dbo.TEST_RESUMABLE(Name, Address)
WITH (ONLINE=ON, RESUMABLE=ON);ในขณะที่กำลัง execute อยู่นั้น เราก็จะทำการ stop การสร้าง index โดยใช้คำสั่งตามภาพเลยครับ
ALTER INDEX TEST_INDEX ON fbo.TEST_RESUMABLE
PAUSE;
GOเรามาดูกันครับว่าการสร้าง index ของเรานั้นสำเร็จไปกี่เปอร์เซ็นต์แล้ว โดยดูได้จาก table ที่ชื่อว่า sys.index_resumable_operations จากรูปด้านล่างจะเห็นว่า index ชื่อ TEST_INDEX ถูกหยุดการสร้างโดยสำเร็จไป 23.33 เปอร์เซ็นต์แล้วนะครับ
ผมจะลองกด resume และตรวจสอบ status ของการสร้าง index พบว่า index ถูกสร้างต่อจากที่เราทำการ stop ไว้ล่าสุด
ALTER INDEX TEST_INDEX ON dbo.TEST_RESUMABLE
RESUME
GO;
ถ้าเกิดเพื่อนๆทำการ stop การสร้าง index ไว้ แล้วดันลืมว่ากำลังสร้าง index จาก column Name และ Address ของ TEST_RESUMABLE อยู่และจะทำการสร้างใหม่ index จาก column Name และ Address จะเกิดอะไร?? มันก็จะเป็นการ resume การสร้าง index ต่อจากที่เคย stop ไว้นั่นเอง
CREATE NONCLUSTERED INDEX TEST_INDEX
ON dbo.TEST_RESUMABLE(Name, Address)
WITH(ONLINE = ON, RESUMABLE = ON);
แต่ๆๆ ถ้าเป็นในกรณีที่เราต้องการสร้าง index จาก column หนึ่งจาก Name หรือ Address ของ TEST_RESUMABLE ละก็จะแจ้ง Error ว่า ไม่สามารถดำเนินการได้ เนื่องจาก Object อยู่ใน resumable index state ไปดูรายละเอียดได้ใน sys.index_resumable_operations นะครับ
จาก function ต่างๆ ที่ผมได้หยิบยกขึ้นมาเป็นเพียงส่วนหนึ่งของ SQL Server 2019 เท่านั้น ยังมี function ต่างๆอีกมากมายที่จะสนับสนุนการทำงานบน SQL Server ให้มีประสิทธิภาพมากยิ่งขึ้น ถ้าเพื่อนๆคนไหนอยากรู้ว่าใน SQL Server 2019 ยังมีอะไรที่น่าสนใจอีกละก็สามารถเข้าไปหาไปดูข้อมูลเพิ่มเติมได้ที่เว็บไซต์ของทาง Microsoft ได้เลยนะครับ ไม่เพียงแค่นั้นเพื่อนๆยังสามารถทดลองใช้ SQL Server 2019 ได้ฟรีอีกด้วยโดยเข้าไป download ในเว็บไซต์ครับ สุดท้ายนี้หวังว่า Blog ของผมจะเป็นประโยชน์ให้กับเพื่อนๆได้ไม่มากก็น้อยและคงต้องลาไปก่อน เจอกันใหม่ Blog หน้าครับ ขอบคุณครับ
