ดึงค่าประสิทธิภาพของ SQL Server ด้วย Power BI

บทความนี้น่าจะออกไล่เลี่ยกันกับบทความ “รู้ได้อย่างไรว่า Microsoft SQL Server มีประสิทธิภาพแย่ลง” เพราะเขียนในช่วงเวลาเดียวกัน
ผู้เขียนได้พูดถึงแหล่งข้อมูลของค่าประสิทธิภาพว่ามีอะไรกันบ้าง อยากให้ผู้อ่านลองอ่านบทความนั้นก่อนเพื่อเป็นปูพื้นความรู้

สำหรับบทความนี้ผู้เขียนขอดึงข้อมูลจาก Dynamic Management Views เพียงบางตัวมาพอให้เห็นภาพรวม
ข้อควรคำนึงอีกเรื่องคือ ปกติหากเราคิวรีค่าประสิทธิภาพออกมาตรง ๆ มักนำมาใช้ประโยชน์ลำบาก
เพราะค่าประสิทธิภาพนั้นมักเก็บข้อมูลในลักษณะ Cumulative หรือเป็นค่าบวกสะสม

ผู้เขียนทดสอบให้เห็นโดยสืบค้นจาก DMV ชื่อ sys.dm_os_wait_stats ด้วยคิวรี่ต่อไปนี้

SELECT TOP 20 * FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN

(
'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TASK_STOP'

,'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE'

,'CHKPT', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'CLR_SEMAPHORE', 'CXCONSUMER'

,'DBMIRROR_DBM_EVENT', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRROR_WORKER_QUEUE'

,'DBMIRRORING_CMD', 'DIRTY_PAGE_POLL', 'DISPATCHER_QUEUE_SEMAPHORE'

,'EXECSYNC', 'FSAGENT', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX'

,'HADR_CLUSAPI_CALL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETIO', 'HADR_LOGCAPTURE_WAIT'

,'HADR_NOTIFICATION_DEQUEUE', 'HADR_TIMER_TASK', 'HADR_WORK_QUEUE'

,'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE'

,'MEMORY_ALLOCATION_EXT', 'ONDEMAND_TASK_QUEUE'

,'PARALLEL_REDO_DRAIN_WORKER', 'PARALLEL_REDO_LOG_CACHE'

,'PARALLEL_REDO_TRAN_LIST', 'PARALLEL_REDO_WORKER_SYNC'

,'PARALLEL_REDO_WORKER_WAIT_WORK', 'PREEMPTIVE_HADR_LEASE_MECHANISM'

,'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'PREEMPTIVE_OS_LIBRARYOPS'

,'PREEMPTIVE_OS_COMOPS', 'PREEMPTIVE_OS_CRYPTOPS'

,'PREEMPTIVE_OS_PIPEOPS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS'

,'PREEMPTIVE_OS_GENERICOPS', 'PREEMPTIVE_OS_VERIFYTRUST'

,'PREEMPTIVE_OS_FILEOPS', 'PREEMPTIVE_OS_DEVICEOPS'

,'PREEMPTIVE_OS_QUERYREGISTRY', 'PREEMPTIVE_OS_WRITEFILE'

,'PREEMPTIVE_OS_WRITEFILEGATHER', 'PREEMPTIVE_XE_CALLBACKEXECUTE'

,'PREEMPTIVE_XE_DISPATCHER', 'PREEMPTIVE_XE_GETTARGETSTATE'

,'PREEMPTIVE_XE_SESSIONCOMMIT', 'PREEMPTIVE_XE_TARGETINIT'

,'PREEMPTIVE_XE_TARGETFINALIZE', 'PWAIT_ALL_COMPONENTS_INITIALIZED'

,'PWAIT_DIRECTLOGCONSUMER_GETNEXT', 'PWAIT_EXTENSIBILITY_CLEANUP_TASK'

,'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'QDS_ASYNC_QUEUE'

,'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'

,'REQUEST_FOR_DEADLOCK_SEARCH', 'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK'

,'SLEEP_BPOOL_FLUSH', 'SLEEP_DBSTARTUP', 'SLEEP_DCOMSTARTUP'

,'SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED'

,'SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK', 'SLEEP_TASK'

,'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'SOS_WORK_DISPATCHER'

,'SP_SERVER_DIAGNOSTICS_SLEEP', 'SQLTRACE_BUFFER_FLUSH'

,'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'SQLTRACE_WAIT_ENTRIES'

,'STARTUP_DEPENDENCY_MANAGER', 'WAIT_FOR_RESULTS', 'WAITFOR'

,'WAITFOR_TASKSHUTDOW', 'WAIT_XTP_HOST_WAIT'

,'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'WAIT_XTP_CKPT_CLOSE'

,'WAIT_XTP_RECOVERY', 'XE_BUFFERMGR_ALLPROCESSED_EVENT', 'XE_DISPATCHER_JOI'

,'XE_DISPATCHER_WAIT', 'XE_LIVE_TARGET_TVF', 'XE_TIMER_EVENT'

)

AND waiting_tasks_count > 0

ORDER BY waiting_tasks_count DESC

ผู้เขียนได้กรอง Wait Type ที่ไม่เราไม่ใช้ในการพิจารณาประสิทธิภาพ (อ้างอิงจากสคริปต์ของ Glenn Berry https://glennsqlperformance.com/resources )
และ Waiting ที่ไม่เคยเกิดขึ้นออกไป จากนั้นดึงเฉพาะ Waiting Type ที่เกิดบ่อยที่สุด 20 อันดับแรกมา ผลลัพธ์ที่ได้คือ

โดยคอลัมน์ต่าง ๆ มีความหมายดังนี้

waiting_tasks_count นับจำนวน Task ที่ทำให้เกิด Waiting Type
wait_time_ms เวลาสะสมของการรอทั้งหมด (Resource Wait + Runnable) ก่อนเปลี่ยนเป็นสถานะ Running
max_wait_time_ms เวลารอที่นานที่สุดใน Waiting Type นี้ (ค่านี้ไม่ใช่ Cumulative แต่เป็นของ Task ใดที่รอนานสุด)
signal_wait_time_ms เวลาสะสมของการรอในสถานะ Runnable ก่อนเปลี่ยนเป็นสถานะ Running

ดังนั้น หากเราต้องการทราบ Resource Wait time ทำได้โดยนำ wait_time_ms ไปลบออกด้วย signal_wait_time_ms นั่นเอง
เพื่อความง่ายในการแสดงตัวอย่างผู้เขียนขอใช้เพียงคอลัมน์ wait_time_ms เท่านั้น

โดยผู้เขียนทำการสร้างฐานข้อมูลชื่อ sqlperf และสร้างตารางชื่อ landing.WaitStats ด้วยสคริปต์ต่อไปนี้

CREATE DATABASE sqlperf;

USE sqlperf;

CREATE SCHEMA landing;

CREATE TABLE landing.WaitStats

(

ExtractTime datetime NOT NULL

, wait_type nvarchar(60) NOT NULL

, wait_time_ms bigint NOT NULL

)

จากนั้นผู้เขียนเลือกที่จะใช้ Job ใน SQL Server Agent ทำการคิวรี่ข้อมูลใส่ลงในตาราง ๆ ทุก 1 นาที ดังแสดง

ได้ข้อมูลมาจำนวนหนึ่งเพราะตั้งให้ทำงานอยู่เพียง 1 ชั่วโมง 40 นาที แต่สำหรับในงานอาจต้องเปิดตลอดไป และมีการลบข้อมูลทิ้งหลัง ETL แล้วครับ เพื่อบริหารความจุ

ตัวอย่างข้อมูล landing ของ Wait Type ชื่อ HADR_FILESTREAM_IOMGR_IOCOMPLETION

พบว่าเมื่อ Wait Type นี้เกิดครั้งแรก (สีเหลือง) เป็นเวลา 40,258,819 ms
ครั้งถัดมา (สีเขียว) เป็นเวลา 40,319,456 ms
และครั้งที่ถัดมา (สีฟ้า) เป็นเวลา 40,378,444 ms
จะเห็นว่าค่าเป็นบวกสะสมไปเรื่อย ๆ หากเราคิวรี่ตรง ๆ เราต้องตั้งกลไกให้ทำการดึงข้อมูลในมีความถี่คงที่

ตัวอย่างนี้คือ ทุก 1 นาที หรือ 60 วินาที ดังนั้นหากเอาไปวาดเป็นกราฟจะได้จุดสองจุดคือ

จุดแรก =(ค่าครั้งที่ 2 - ค่าครั้งที่ 1) /60 = (40319456 - 40258819)/60 = รอคอยรวมทั้งสิ้น 1,010.6166 มิลลิวินาที ใน 1 วินาที

และ

จุดสอง =(ค่าครั้งที่ 3 - ค่าครั้งที่ 2)/60 = (40378444 - 40319456)/60 = รอคอยรวมทั้งสิ้น 983.1333 มิลลิวินาที ใน 1 วินาที

ผู้เขียนลองนำไปคำนวนด้วยสคริปต์ต่อไปนี้

WITH Wait as

(

SELECT

ExtractTime

, wait_type

, ROW_NUMBER() OVER(PARTITION BY wait_Type ORDER BY ExtractTime)

as WaitType_SEQ

, wait_time_ms

FROM landing.WaitStats

)

SELECT

w2.ExtractTime

, w2.wait_type

, (w2.wait_time_ms-w1.wait_time_ms)/60. as waitTime

FROM Wait as w1 INNER JOIN wait as w2

ON w2.wait_type=w1.wait_type AND w2.WaitType_SEQ-1 = w1.WaitType_SEQ

WHERE w2.wait_type='HADR_FILESTREAM_IOMGR_IOCOMPLETION'

ผลลัพธ์ที่ได้คือ

ตรงตามความต้องการของเรา จากนั้นผู้เขียนลบประโยค WHERE ทิ้งไป แล้วนำไปใช้ในการ Extract ลง staging ด้วยสคริปต์ต่อไปนี้

CREATE SCHEMA stg;

CREATE TABLE stg.WaitStats

(

wait_type nvarchar(60) NOT NULL,

waitTime numeric(25, 6) NULL,

ExtractTime datetime NOT NULL

)

WITH Wait as

(

SELECT

ExtractTime

, wait_type

, ROW_NUMBER() OVER(PARTITION BY wait_Type ORDER BY ExtractTime)

as WaitType_SEQ

, wait_time_ms

FROM landing.WaitStats

)

INSERT INTO stg.WaitStats

SELECT

w2.wait_type

, (w2.wait_time_ms-w1.wait_time_ms)/60. as waitTime

, w2.ExtractTime

FROM Wait as w1 INNER JOIN wait as w2

ON w2.wait_type=w1.wait_type AND w2.WaitType_SEQ-1 = w1.WaitType_SEQ

จากนั้นผู้เขียนทำการ Load ไปลงตาราง Fact ชื่อ factWaitStats บน Datawarehouse ชื่อ DemoDW (เป็นการจำลองสภาพแวดล้อมให้ใกล้เคียงการทำงานจริง) ด้วยสคริปต์ต่อไปนี้

USE master;

CREATE DATABASE DemoDW;

USE DemoDW;

CREATE TABLE factWaitStats

(

DateKey bigint NULL

, wait_type nvarchar(60) NOT NULL

, waitTime numeric(25, 6) NULL

, ExtractTime datetime NOT NULL

)

INSERT INTO factWaitStats

SELECT

CONVERT(bigint,FORMAT(ExtractTime,'yyyyMMddHHmm')) as DateKey

, wait_type

, waitTime

, ExtractTime

FROM sqlperf.stg.WaitStats

จะได้ข้อมูลดังแสดง

จากนั้นผู้เขียนสร้างตาราง Dimension ชื่อ dimDate เพื่อทำหน้าที่เป็น Datetime Dimension

USE DemoDW

CREATE TABLE dbo.dimDate

(

DateKey bigint NOT NULL

, DateAltKey datetime NULL

, DateDisplay nvarchar(50) NULL

, EveryMinuteKey smallint NULL

, EveryMinuteDisplay nvarchar(20) NULL

, Every5MinuteKey smallint NULL

, Every5MinuteDisplay nvarchar(20) NULL

, Every15MinuteKey smallint NULL

, Every15MinuteDisplay nvarchar(20) NULL

, HourKey smallint NULL

, HourDisplay nvarchar(20) NULL

, MonthKey int NULL

, MonthDisplay nvarchar(10) NULL

, YearKey smallint NULL

, YearDisplay nvarchar(8) NULL

, CONSTRAINT PK_dimDate PRIMARY KEY CLUSTERED

(

DateKey ASC

)

แล้วโหลดข้อมูลเข้าด้วยสคริปต์ต่อไปนี้

DECLARE @startDate datetime

SELECT @startDate=MIN(ExtractTime) FROM sqlperf.stg.WaitStats

DECLARE @endDate datetime

SELECT @endDate=MAX(ExtractTime) FROM sqlperf.stg.WaitStats

DECLARE @CurrentDate datetime

SET @CurrentDate=@StartDate

WHILE @CurrentDate<DATEADD(DAY,1,@EndDate)

BEGIN

/* DateKey */

DECLARE

@DateKey bigint = FORMAT(@CurrentDate,'yyyyMMddHHmm')

, @DateAltKey datetime = @CurrentDate

/* ----------------- Day Section ----------------- */

DECLARE @DateDisplayTH nvarchar(15)

SET @DateDisplayTH = FORMAT(@CurrentDate, 'dd MMM yyyy', 'th-TH')

/* ----------------- Minute Section ----------------- */

DECLARE

@MinuteKey smallint = DATEPART(MINUTE,@CurrentDate)

, @MinuteDisplay nvarchar(20) = N'นาทีที่ ' + FORMAT(DATEPART(MINUTE,@CurrentDate),'D2')

, @Every5MinuteKey smallint

= CASE

WHEN DATEPART(MINUTE,@CurrentDate)<=5 THEN 5

WHEN DATEPART(MINUTE,@CurrentDate)<=10 THEN 10

WHEN DATEPART(MINUTE,@CurrentDate)<=15 THEN 15

WHEN DATEPART(MINUTE,@CurrentDate)<=20 THEN 20

WHEN DATEPART(MINUTE,@CurrentDate)<=25 THEN 25

WHEN DATEPART(MINUTE,@CurrentDate)<=30 THEN 30

WHEN DATEPART(MINUTE,@CurrentDate)<=35 THEN 35

WHEN DATEPART(MINUTE,@CurrentDate)<=40 THEN 40

WHEN DATEPART(MINUTE,@CurrentDate)<=45 THEN 45

WHEN DATEPART(MINUTE,@CurrentDate)<=50 THEN 50

WHEN DATEPART(MINUTE,@CurrentDate)<=55 THEN 55

WHEN DATEPART(MINUTE,@CurrentDate)<60 THEN 60

END

, @Every5MinuteDisplay nvarchar(20)

= CASE

WHEN DATEPART(MINUTE,@CurrentDate)<=5 THEN N'นาทีที่ 5'

WHEN DATEPART(MINUTE,@CurrentDate)<=10 THEN N'นาทีที่ 10'

WHEN DATEPART(MINUTE,@CurrentDate)<=15 THEN N'นาทีที่ 15'

WHEN DATEPART(MINUTE,@CurrentDate)<=20 THEN N'นาทีที่ 20'

WHEN DATEPART(MINUTE,@CurrentDate)<=25 THEN N'นาทีที่ 25'

WHEN DATEPART(MINUTE,@CurrentDate)<=30 THEN N'นาทีที่ 30'

WHEN DATEPART(MINUTE,@CurrentDate)<=35 THEN N'นาทีที่ 35'

WHEN DATEPART(MINUTE,@CurrentDate)<=40 THEN N'นาทีที่ 40'

WHEN DATEPART(MINUTE,@CurrentDate)<=45 THEN N'นาทีที่ 45'

WHEN DATEPART(MINUTE,@CurrentDate)<=50 THEN N'นาทีที่ 50'

WHEN DATEPART(MINUTE,@CurrentDate)<=55 THEN N'นาทีที่ 55'

WHEN DATEPART(MINUTE,@CurrentDate)<60 THEN N'นาทีที่ 60'

END

, @Every15MinuteKey smallint

= CASE

WHEN DATEPART(MINUTE,@CurrentDate)<=15 THEN 15

WHEN DATEPART(MINUTE,@CurrentDate)<=30 THEN 30

WHEN DATEPART(MINUTE,@CurrentDate)<=45 THEN 45

WHEN DATEPART(MINUTE,@CurrentDate)<60 THEN 60

END

, @Every15MinuteDisplay nvarchar(20)

= CASE

WHEN DATEPART(MINUTE,@CurrentDate)<=15 THEN N'นาทีที่ 15'

WHEN DATEPART(MINUTE,@CurrentDate)<=30 THEN N'นาทีที่ 30'

WHEN DATEPART(MINUTE,@CurrentDate)<=45 THEN N'นาทีที่ 45'

WHEN DATEPART(MINUTE,@CurrentDate)<60 THEN N'นาทีที่ 60'

END

/* ----------------- Hour Section ----------------- */

DECLARE

@HourKey int

, @HourDisplayTH nvarchar(20)

SET @HourKey = DATEPART(HOUR,@CurrentDate)

SET @HourDisplayTH = N'ชั่วโมงที่ ' + FORMAT(DATEPART(HOUR,@CurrentDate),'D2')

/* ----------------- Month Section ----------------- */

DECLARE

@MonthKey int

, @MonthDisplayTH nvarchar(10)

SET @MonthKey = MONTH(@CurrentDate)

SET @MonthDisplayTH = FORMAT(@CurrentDate, 'MMMM', 'th-TH')

/* ----------------- Year Section ----------------- */

DECLARE

@YearKey smallint

, @YearDisplayTH nvarchar(8)

SET @YearKey = YEAR(@CurrentDate)

SET @YearDisplayTH = FORMAT(@CurrentDate,N'ปี yyyy', 'th-TH')

INSERT INTO dimDate

(

DateKey

, DateAltKey

, DateDisplay

, EveryMinuteKey

, EveryMinuteDisplay

, Every5MinuteKey

, Every5MinuteDisplay

, Every15MinuteKey

, Every15MinuteDisplay

, HourKey

, HourDisplay

, MonthKey

, MonthDisplay

, YearKey

, YearDisplay)

VALUES

(

@DateKey

, @DateAltKey

, @DateDisplayTH

, @MinuteKey

, @MinuteDisplay

, @Every5MinuteKey

, @Every5MinuteDisplay

, @Every15MinuteKey

, @Every15MinuteDisplay

, @HourKey

, @HourDisplayTH

, @MonthKey

, @MonthDisplayTH

, @YearKey

, @YearDisplayTH

)

SET @CurrentDate=DATEADD(minute,1,@CurrentDate)

END

จากนั้นผูกความสัมพันธ์ระหว่างตาราง Fact และ Dimension ให้เรียบร้อยด้วยสคริปต์ต่อไปนี้

USE DemoDW

ALTER TABLE factWaitStats WITH CHECK

ADD CONSTRAINT FK_factWaitStats_dimDate FOREIGN KEY(DateKey)

REFERENCES dimDate (DateKey)

ALTER TABLE factWaitStats CHECK CONSTRAINT FK_factWaitStats_dimDate

เราก็จะได้ Datawarehouse ของเราแล้ว จากนี้เราต้องสร้าง Data Model เพื่อหาผลรวมต่อ
ทางเลือกคือใช้ Microsoft SQL Server Analysis Service (SSAS) สร้าง Data Model แล้วให้ Power BI แสดงผล Visualization เท่านั้น
หรือสร้าง Data Model ด้วย Power BI เองเลย

ผู้เขียนขอเลือกสร้างด้วย Power BI แต่ในการใช้งานจริงเมื่อปริมาณข้อมูลมากขึ้นควรพิจารณาสร้าง Data Model ด้วย SSAS

เมื่อ Import ข้อมูลเข้า Power BI แล้ว ให้ทำการสร้าง Hierarchy บน dimDate ดังแสดง

เมื่อนำไปแสดงบน Visualization จะเห็นว่าสามารถ Drill-Up และ Drill-Down เพื่อดูผลรวมตามลำดับชั้นของเวลาได้
แต่น่าเสียดายที่ตัวอย่างที่แสดงให้ดูนี้มี Wait Type ชื่อ HADR_FILESTREAM_IOMGR_IOCOMPLETION ใช้เวลานานมากกว่าตัวอื่น ๆ ทำให้แทบมองไม่เห็น Wait Type ตัวอื่นเลย

Visualization จะเห็นว่าสามารถ Drill-Down

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

สังเกตว่า Wait Type ชื่อ HADR_FILESTREAM_IOMGR_IOCOMPLETION
ในช่วงนาทีที่ 60 ของชั่วโมงที่ 11 มีค่าลดลงจากในช่วงนาทีที่ 45 ของชั่วโมงที่ 11

นี่เป็นเพียงตัวอย่างที่ไม่สมบูรณ์นัก เพื่อให้พอเห็นการนำค่าประสิทธิภาพไปใช้งาน
ส่วนการดำเนินการกับทั้ง landing, staging และ Datawarehouse ก็ทำขึ้นคร่าว ๆ เช่นกัน
หากมีโอกาสผู้เขียนจะทดลองทำเวอร์ชั่นใช้งานจริงออกมา แล้วแจกจ่าย Source Code อีกครั้ง
หวังว่าตัวอย่างเหล่านี้จะทำให้เห็นการนำค่าประสิทธิภาพไปใช้งานกันครับ

บทความโดย

อาจารย์ภัคพงศ์ กฤตวัฒน์