[MSSQL] สรุปงาน SQL Server Community Thailand Meeting #10

หลังจากห่างหายไปนานกับงานมาฟังความรู้ Idea ดีๆแบบนี้นะครับ กลับมาคราวนี้อ้าวครั้งที่ 10 แล้วเหรอ ผ่านไปไวเหมือนโกหกครับ มาดู Blog เก่าๆได้ที่นี่ครับ

• [MSSQL] สรุปงาน SQL Server Community Thailand Meeting #03
• [MSSQL] สรุปงาน SQL Server Community Thailand Meeting #02

สำหรับ Session ในวันนี้เป็นการแชร์ความรู้ใน 2 เรื่องครับ

• Data Classification and Vulnerability Assessment โดยคุณ Khemika Pattaranipit
• Basic​ and​ How to​ read​ Graphical Execution​ Plan โดยคุณ Phakkhaphong Krittawat

มาดูรายละเอียดของแต่ละหัวข้อกันเลยครับ ลุยยยยยยยยยยยยยยยยยย

Data Classification and Vulnerability Assessment

- Azure SQL DB : Data Classification and Vulnerability Assessment

• Data Classification: ดูจากชื่อ Column ได้ว่าควร ป้องกันข้อมูล เช่น Data of Birth และ เลข Credit Card
• เมนู Audit Log เอาไว้ในช่วยทำรายงานความปลอดภัยได้ง่ายขึ้น จากเดิมอาจจะทำอยู่ใน App
• สำหรับข้อมูล Data Classification เก็บใน Table Metadata เก็บใน System Table

- Transparent Data File Encryption (TDE) (SQL Server 2008 ++)

• ป้องกัน Data File เช่น backup แล้ว ใครเอาไปใช้ได้บ้าง
• ยกเว้นใน Memory อันนี้เอามาอ่านแล้วต้องมีการ Decrypted
• ไม่กระทบ App
• ถ้าทำ Always On ทำ Auto Sync ต้องเอา Key ชุดเดียวกัน ไม่งั้นมัน Decrypt แล้วเอาลงไม่ได้
• ถ้ามี DB ก้อนนึงเปิด TDE ตัว tempdb ถึง Encrypted ด้วย รวม In memory
• ข้อเสีย
  • มี overhead ทั้งใน CPU
  • Auto Grow หรือ Instant File initialization ไม่ได้ มีผลกับ TDE มันจะช้าลง รอถม 0

Dynamic Data Masking

• ป้องกันข้อมูลไม่ให้ Select เห็นหมด
• มี Template มาให้จะเอาตาม Data Type หรือทำ Custom
• column ที่ Always Encrypt ใช้ไม่ได้ หรือไว้ compute column
• mask และ unmask
• static data marking protect ข้อมูลที่เป็น sensitive data ได้ สำหรับทาง Dev 3rd party ที่มา Dev แต่ไม่ต้องเห็น Data ทั้งหมด

Row Level Seucrity

• เดิมมี Column อันนี้เรากำหนดได้เองแล้ว ส่วนระดับ Row เราไปกำหนดใน Application เอา
• แต่ Row Level Security เอา Business Logic มาลง DB เพื่อ Screen Data ทำเป็น Function มาเป็น Security Policy มาผูก จากนั้น Select ตรงๆได้

Alway Encrypted

• ทำ Column Encrypted ใสตัว DB เอง
• จากเมื่อก่อนต้องทำจาก App จะ Select ที InSert ที ก็ Decrypted Encrypted

Basic​ and​ How to​ read​ Graphical Execution​ Plan

มาดูกันก่อนว่า Database มีพื้นฐานการทำงานอย่างไรครับ

• Query Processing
  • Parsing (Ctrl+F5) ตรวจสอบ Syntax แต่ถ้ากด F5 run
  • ตรวจสอบ Syntax ถ้าเพิ่ม Column แล้วลืม Group By มันไม่รู้นะ
  • ผลลัพธ์เป็น Logical Query Tree
• Binding
  • เอา parsing มาผูกกับ object พวก table เวลาตรวจ select group by ก็ตรวจในนี้นะ
  • ผลลัพธ์เป็น Algebrizer Tree แต่ละ Node ใน Tree มีการคิด Cost ด้วย
• Query Optimization
  • เอา Algebrizer tree มาหา Execution Plan ที่ Cost น้อยที่สุด
  • ได้ Execution Plan (เอาดีที่สุดตามเวลาที่สนใจ) โดย Plan ที่ถูกเลือกจะเรียกว่า Compile Plan
  • ตัว Query Optimization เวลาเลือก Plan ที่ดีที่สุด มาจากแบบแรก เอาจาก Cache หรือ หาใหม่โดยดูจาก Cost โดยมีตัวกลไกที่สำคัญ คือ Cardinality Estimation
• Query Execution
  • นำ Execution Plan ไปทำงานจริง เพื่อให้ได้ผลลัพธ์กลับมา Result Set

มาดูที่ Execution Plan Cache (เก็บใน Memory) โดยจะเปลี่ยนแปลงเมื่อ

• Statistics เปลี่ยน จะเปลี่ยนเมื่อมีการ Insert >= 20% ของ Table หรือมีการ Update Stat ใหม่ จากกดจาก GUI หรือใช้คำสั่ง SP_UpdateStat
• Service Start / Stop
• ปิดเครื่อง
• WITH RECOMPILE ใน Store หรือในคำสั้ง Exec

- ปัญหา Parameter Sniffing

Parameter Sniffing คือ การ query รอบแรกจำ buffer น้อยกว่าครั้งถัดไป ทำให้ตอน Query รอบถัดไปผลลัพธ์ที่ได้ไม่ได้อยู่ใน Buffer แต่ไปเก็บลงที่ Disk แทน spill to disk ซึ่งแก้ไขโดย

• Keyword recomplie อันนี้กระทบกับ Execution Plan Cache มันจะถูกลบออกทุกครั้ง
• Keyword optimize for หาค่าที่เหมาะสมสำหรับการจอง buffer
• ใช้ MSSQL 2017 ++ Adaptive Query Processing ปรับการจอง memory ให้พอดี
  • batch mode memory grant feedback
  • Batch Mode Adaptive Join
  • Interleaved Execution - พวก Table Value Function ทดลองรันไปทดสอบ แล้วจอง Buffer ให้พอดี

Cardinality Estimation แผนจะแม่นยำแค่ไหนขึ้นอยู่ที่เธอ โดยเจ้าตัว Cardinality Estimation

• ประเมินจำนวน Row ที่น่าจะเกิดในแต่ละ Operation ของ Estimate Excution Plan จากนั้นไปจองพื้นที่ Buffer ที่ใช้
• โดยข้อมูลพื้นฐานมาจากสถิติ และการกระจายตัวข้อมูลจาก Lookup Column (Index) แต่ถ้ามันหาไม่ได้จริงๆ ก็มาจากการสุ่ม

- ประเภทของ Execution Plan

• Estimate Excution Plan: คิดแผนไว้ แต่ยังไม่ได้ทำการรบกับข้อมูล ลองกับข้อมูลจริงๆ
• Actual Excution Plan: แผนที่ถูกเอามาใช้กับข้อมูลจริงๆ

เมื่อลองเข้าไปในดู Execution Plan มี Operation ต่างๆมากมาย

Operator

• Where อยู่ใน Access Method
• Having เกิด Operation Filter
• Table Cluster VS Heap
  • Clustered Table เป็น Cluster มันจะเรียง ทำ Tree ตาม Key ข้อมูลจะเรียง
  • Heap Table ตาม page ที่ว่าง ถ้าพอใช้ ข้อมูลไม่เรียง
• ใช้ Cluster Index Scan อ่านตาม Key ที่กำหนดใน Clustered Table ข้อมูลจะรจะเรียงมาอยู่แล้ว
• ใช้ Non Cluster อันนี้เป็น Index ที่สร้างขึ้นมาเองและ

โดยเน้น Opertion ที่สำคัญมาจุดนึงอย่าง Inner Join ครับ์

• Nested Loop Join - Table A เชื่อม Table B จะใช้งานได้ดี เมื่อจำนวน Row ของ A และ B ต่างกันมากๆ
• Merge Join - Table A และ Table B ข้อมูลพอๆกัน และมี Lookup เหมือนกันทั้ง 2 ฝั่ง Lookup ก็ คือ Index นั้นเองครับ
• Hash Map Join - Table A และ Table B ข้อมูลพอๆกัน แต่ไม่มี Key เลย ตัว MSSSQL Server จะเอา Column ที่ใช้ Join มาสร้าง Hash Table
• ** พยายามกำจัด Nested Loop กับ Hash Map

Parallelism แค่ไหนถึงเรียกว่าดี

• ทำให้เกิด Parallelism ได้ยาก เพราะ Parallelism แตกเยอะไปก็จะเกิดปัญหาการอกัน หรือกั๊ก Resource ปกติ กำหนด Cost Threshold for Parallelism ไว้เยอะๆ 35-50 ต่อ 1 Task
• Parallelism จะเกิดได้ ถ้า Cost มากกว่า Cost Thresold of parallelism (ค่ากลาง)
• Max degree of Parallelism ถ้ากำหนด 0 คือ MSSQL จะใช้ CPU เป็นไปได้ 1-N Core มากที่สุด 64 ซึ่งไม่คิดแน่ๆ
  • Best Practices ถ้า CPU > 8 ใช้กำหนดใช้งานได้แค่ 8 พอต่อ 1 Task ถ้าต่ำกว่า 8 กำหนดตามจำนวน CPU

พอดู Cost ระหว่าง Estimate Excution Plan และ Actual Excution Plan แล้ว ถ้ามัน Diff กันเยอะๆ ควรทำอย่างไร๊

• Update Stat โดยใช้คำสั่ง SP_UpdateStat เดี๋ยวมันหาให้เองว่า Table ไหน Index ไหนควรจะทำการ Update Stat
• เกิด Hash Map Join เยอะๆ แก้โดยการเพิ่ม Index เพื่อทำให้เกิด Merge Join
• การทำ Index ตัว ใช้ include column ช่วยลด Cost ของ index ได้ include เช่น ไม่ได้ Where Column นั้น แต่ปกติถูก Selected มาร่วมกัน
• ลด Implict convert เช่น column date แต่เอา string มา where หรือ Int แต่อีก Column เป็น String มา Where

ปิด Blog นี้ด้วยของกินในงานครับ ขอบคุณที่จัดงานดีๆครับ