[MSSQL] มาดูขนาดของ Database กัล

หลังจากช่วยได้ นอกจากเป็น Dev แล้ว อีกอย่างที่ Dev ควรรู้ด้วย คือ ตัว Database ครับ วันนี้ผมขอแปะ Query สำหรับดูขนาดของ Database ไฟล์ .mdf กับ .ldf โดยสามารถดูข้อมูลจากได้ Query ดังนี้ัครับ

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

ประโยชน์

  • เอาไว้สำหรับสร้างแผนการทำ Maintance ระบบ
  • เอาไว้สำหรับตัดสินใจการ Shink DB

[MSSQL] รู้หรือไม่ว่า WHERE IN (มันมีข้อจำกัดนะ)

พอดีลองพัฒนาโมดูล Compliance ขึ้นมาใหม่ และลองทำ Stress Test ดูว่า ระบบมันรองรับได้มากที่สุดแค่ไหน โดยลองส่ง Portfolio ทีมีหลายร้อย Port เข้ามาตรวจดู ปรากฏว่าเจอ Exception ดังรูป

ถ้ามาดู Exception Message แบบละเอียดหละ

ซึ่งสาเหตุของมันเกิดจาก SQL ในส่วนของ WHERE IN (มันมีข้อจำกัด สำหรับ Microsoft SQL Server สามารถใส่ข้อมูล เข้าไปได้มากที่สุด 2100 ตัวเท่านั้น) โดยตัวอย่าง SQL IN เป็นแบบนี้

แล้วจำนวนเท่าไหร่ ถึงเหมาะสม

  • อันนี้ผมก็ตอบไม่ได้ครับ แต่ถ้าผมเป็นคนพัฒนาระบบ ผมเลือก Database ที่ต้องใช้ เช่น Oracle. MSSQL และ DB2 หาข้อจำกัดของมัน แล้วเลือกค่าที่น้อยที่สุดครับ ถ้า Parameter ที่ส่งมามันเกิน เราก็แอบสลับแบาง แล้วค่อยๆ ดึงข้อมูลออกมานะ

แล้ว Database ของเจ้าอื่นๆ มีข้อจำกัดที่เท่าไหร่

  • Oracle ได้จำนวน 1,000 Parameter
  • DB2 ได้จำนวน 1,000 Parameter
  • MSSQL ได้จำนวน 2,100 Parameter

 

[MSSQL] ทำให้เครื่องอื่นสามารถ Connect เข้ามาหาได้

พอดีมีเครื่องน้องในทีมลง Windows ใหม่แล้วพอลงอะไรเสร็จหมดแล้ว ปรากฏว่าเครื่องอื่นๆ ในวงแลน ไม่สามารถเชื่อมต่อฐานข้อมูลที่อยู่บนเครื่องนี้ได้ครับ สำหรับวิธีการ + ตรวจเช็คให้เครื่องอื่นสามารถ Connect เข้ามาหาได้ มีขั้นตอน ดังนี้

Check List : CMD – Ping

  • ping ดูก่อนนะครับ ว่าพบ Server หรือป่าว เดี๋ยวจะไปเจอว่าท้ายที่สุดแล้ว ลืมเสียบสายแลน (เคยมีเคสจริงที่ Site ลูกค้าแห่งหนึ่ง เกิดขึ้นแบบนี้ จนสุดท้ายไปเจอว่าหนูเทะสายแลนขาด แต่เรียกให้ vender มาแก้ซะงั้น)

Check List : MSSQL Server Configuration

  • ตรวจสอบว่า Server Properties ของ SQL Server ได้เปิด “Allow remote connection to this server” หรือไม่ โดยมีขั้นตอน ดังนี้
    • เข้าตัว SQL Server Management Studio (SSMS) จากนั้น Login ด้วยสิทธิิ์ที่สามารถจัดการ Database ได้ อย่างเช่น ใช้ Windows Authentication หรือ ใช้ User SA เป็นต้น
    • คลิกขวาที่ Server เลือก Properties
    • มาในส่วน Connection ติ๊กเลือก “Allow remote connection to this server”
    • กด OK เพื่อบันทึกการตั้งค่า
  • ตรวจสอบว่า Enable Protocols TCP/IP หรือ ยัง
    • เข้าตัว SQL Server 2017 Configuration Manager
    • จากนั้นเปิด TCP/IP ตาม Instance ที่ต้องการ (ในที่นี้ Instance ชื่อ “MSSQL2017”) ดังรูป
    • หากใช้ Port อื่นๆ นอกเหนือจาก Port 1433 (MSSQL  Default Port) ก็ตามไปแก้ Port ที่ใช้ด้วย
  • Restart Service ของ MSSQL ครับ

Check List : Windows Firewall ดูว่าได้ Allow Port ของ MSSQL หรือยัง ?

  • สำหรับบน Windows มีขั้นตอน ดังนี้ครับ
    • เข้า Control Panel >> Windows Firewall
    • เลือก Advance Settings ดังรูป
    • เลือก Inbound Rules จากนั้นเลือก New Rule
    • เลือกรูปแบบของกฏ ในที่นี้เลือกให้กฏ ตรวจสอบจาก Port ครับ
    • กำหนด Protocal และ Port
    • ถ้ามีเงื่อนไขเข้าตามกฏ ให้ทำอย่างไร อันนี้ คือ Allow (อนุญาติแบบไม่มีเงื่อนไข)
    • กำหนดขอบเขตของกฏที่สร้าง ในที่นี้กำหนดใช้กฏนี้กับ Domain Network ขององค์กรครับ
    • ตั้งชื่อกฏ และคำอธิบาย จากนั้นกด Finish

หมายเหตุ

  • สำหรับวิธีการนี้ได้ทดสอบใน MSSQL เวอร์ชัน 2008R2 กับ 2017 แล้ว บน Windows 10

[MSSQL] ขยับมาใช้ MSSQL ON LINUX 2017 กันดีกว่า

หลังจาก Blog ในปีก่อนโน้นนนนนนน ผมได้ลองใช้ MSSQL on Linux 2016 ไปแล้ว มาปีนี้ ในงานเล็กของเด็กฝึกงาน มาในปีนี้ พอดีกำลังเริ่ม Project ใหม่ ที่มีระยะเวลาทำระมาณ 3 เดือนครับ แม้จะ รู้ว่าเสี่ยง แต่คงต้องขอลอง โดยเอาตัว MSSQL on Linux 2017 มาใช้เป็น Dev-Enviroment สำหรับทำให้ทีมลองบ้างครับ สำหรับใน Blog ตอนนี้เขียนตั้งแต่

  • ช่วงลง Database บน Ubuntu
  • ลอง Restore DB
  • ทดสอบทำงานง่ายๆ
  • เรื่องอื่นๆ

ช่วงลง Database บน Ubuntu

  • ขั้นตอนการติดตั้ง เขียนสั้นๆ และเน้นอะไรที่ผมไม่คุ้นใน MSSQL 2016 ที่เคยใช้งานนะครับ
    • ลง curl ด้วยคำสั่ง (ถ้ามีแล้วข้ามไปครับ)
    • Import key
    • Register repository ของ Microsoft SQL Server Ubuntu ครับ
    • ลง SQL Server ด้วยคำสั่ง
    • ลงตัว SQLCMD ด้วยคำสั่ง
    • Add “/opt/mssql-tools/bin/” เข้าไปใน PATH environment variable. เพื่อให้สามารถเรียกกใช้ sqlcmdได้เลย ด้วยคำสั่ง
  • Step Config ตัว MSSQL Server 2017
    • Run คำสั่ง
    • มี Set SA Password (ขั้นต่ำ 8 ตัว ต้องประกอบไปด้วยตัวอักษร ตัวเลข และอักขระพิเศษ) และ อื่นๆ
    • อันนี้ผมเพิ่งเห็น มันมี Edition มาให้เลือกแล้ว สำหรับผมเลือกตัว Developer Edition ไปก่อน
    • จากนั้นมี Count Down ด้วยครับ เดี๋ยวต้องรอดู ว่าถ้าครบอายุมันเหมือน DB2 ไหม Lock DB เลย
  • ดูสถานะของตัว SQL Server ด้วยคำสั่ง
  • Connect DฺB โดยใช้ User SA ครับ
  • Note: Step ทั้งหมด ผมสรุปมาจาก Reference ของทาง Microsoft นะครับ

ลอง Restore DB

  • ผมได้ทำไฟล์ .bak เอาไว้แล้วนะครับ โดยโยนเข้าไปที่ VM ของ MSSQL ใน Path var/opt/mssql/data/
    • Note: ตอนนั้น ผมพยายามลองหลายท่ามาก ทั้งเลือกไฟล์จาก SSMS บน Host โดยเข้าไปที่ Guest Ubunt แต่มันไม่ Work เลย
  • SSMS – ทำเหมือน Windows เลยครับ แต่ Path ของไฟล์ .bak ต้องอยู่ที่ var/opt/mssql/data/ ไม่งั้นมันจะเอ๋อ หาไฟล์ไม่เจอครับ ขั้นตอนดูตาม Gallery เลยครับ

    This slideshow requires JavaScript.

  • ฺBash Command Line อันนี้ดีหน่อย ต่างจาก MSSQL2016 ไฟล์ .bak เอาไปวางที่ไหนก็ได้ครับ เวลา Restore ใช้คำสั่งตาม Pattern ดังนี้
  • Note: สำหรับ Bash Command Line Path ที่ Restore (ที่เป็นพวก MDF, LDF) ยังคง Lock ไว้ที่ var/opt/mssql/data/ ครับ ไม่งั้นมันจะ Fail

ทดสอบทำงานง่ายๆ

  • Query ดูภาพรวมของ Server ว่า Version อะไร ด้วยคำสั่ง SELECT @@VERSION ผลลัพธ์ที่ได้
  • ลอง Restore DB User  ผมใช้ Script จาก Blog นี้ โดยใช้ User SA แทน เพราะ SQL Server on Linux ไม่มีตัว Windows Authentication ครับ
  • ลองทำ Custom Queryบ้าง ตามงานที่ต้องใช้ แต่ปรับให้สัั้นลง (ของจริง 1 หน้า A4 ครับ)

    • ผลลัพธ์ของ Custom Query ครับ

เรื่องอื่นๆ

  • หน้าจอ UI ในส่วนของการ Design Table ยังใช้งานไม่ได้ ถ้าอยากแก้ Schema ต้อง Command ล้วนๆครับ

หลังจากนี้ หาติดปัญหาอะไร เขียน Blog เรื่อยๆ และอาจจะมีการเขียน ฺBlog สรุปปิดท้ายครับ

[MSSQL] ตัวอย่าง Script ที่ช่วยในการสร้าง User หลัง Restore Database

Blog เขียนมา เพราะ หลักจากได้รับไม้ต่อ ให้มาดูเคสนึงครับ ซึ่งถ้าขึ้นไม่ได้เนี่ย ลูกค้าขอเลื่อนการจ่ายเงินออกไปอีกครับ โดยตอนแรกปัญหาที่ได้รับมาจากทีม CS คือ

  • โปรแกรม Connector และ Excel-AddIns ใช้ไม่ได้เลยยย

พอเข้าไปผมเข้าไปเปิด Log4Net เป็นดูรายละเอียด Error ก็เจอว่า

เนื่องจากทาง CS ไม่สามารถแจ้งได้ว่า ตอนลงระบบเนี่ย เค้ามีขั้นตอนอย่างไร ทำให้การหาสาเหตุของปัญหายากมากครับ ผมเลยเสียเวลาไปกับการหาปัญหาของตัว Connector และ Excel-AddIns นานเลยครับ จนสุดท้ายลองเอา Tools ที่เคยทำมาทดสอบปรากฏว่าเป็นปัญหาที่ Database ครับ ไม่รู้ว่าใคร Restore ผิด?

  • Set User DB ให้เป็น db_denydatawriter – User DB เขียนลง Table ไม่ได้
  • ลืม Set User DB ให้เป็น db_owner ไม่เห็น Table เลย และปัญหาจุกจิอื่นๆครับ

สุดท้ายผม ทำ Script ขึ้นมาใหม่ เพื่อจัดการกับสิทธิ์ครับ และก็แปะลง Blog เนี่ยแหละ เผื่อคนอื่นลองนำไปศึกษาครับ ผมใช้สิทธิ Windows Authentication ในการ Run ครับ

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

วันนี้หลังจาก Train C# เสร็จ ผมรีบออกจากบริษัท ให้มาถึง BTS เร็วที่สุด เพราะกลัวฝนจะตกอีกรอบ เพื่อมางาน SQL Server Community Thailand Meeting ครั้งที่ 3 ครับ โดยครั้งนี้จัดที่ Agoda ที่เดิมครับ ซึ่งในงาน Meetup ครั้งนี้มี 2 หัวข้อครับ ได้แก่

  • Microsoft SQL Server Index & Statistics – Performance Impact
  • Introduction Power BI with R

คำเดือน Blog นี้เขียนโดยมุมมองของ Developer อาจจะมีบางเรื่องที่เขียนผิดไปบ้างนะครับ ขอเข้าเรื่องเลยและกัน

Microsoft SQL Server Index (Row Store Index) & Statistics by Chulladej Aramsri

  • ปกติแล้วในตัว MS SQL มี Index 2 แบบ ได้แก่
    • Row Store Index – มีมานานมากแล้วว
    • Column Store Index – เพิ่มมามีใน MS SQL Server 2012
  • สำหรับใน Blog นี้เน้นไปในเรื่องของ Row Store Index ครับ โดยเท่าที่ผมฟังมา มัน Softๆ กว่าเรื่องใน Meetup ครั้งก่อนครับ (อันนั้นกลับบ้านไป พักสมองยาววเลย)
  • Row Store Index
    • เป็นการทำ Index โดยมองจากมุมของ Row Record เป็นหลัก
    • ถ้ามองจากพื้นฐานของ Database แล้ว ท้ายที่สุดมันถูกเก็บลงที่ File โดยเนื้อของมันถูกเก็บใน Data File ซึ่งถูกแบ่งย่อยๆ ออกมาเป็น Page (ขนาด Default = 8KB)
    • การค้นหาข้อมูล สำหรับดูใน Executetion Plan มันมี 2 แบบ
      • Table Scan – Brute Force ไล่หาทั้ง Table
      • Index – มีสารบัญ ไว้เป็น key ในการค้นหา เน้นสำหรับการอ่าน ดังนั้นการวาง Index ต้องใช้ให้คุ้ม
    • ตัว Index เอาไม่ได้ถูกบังคับว่าต้องมีในมาตรฐาน ANSI แต่มันถูกเรื่องอื่นๆ ช่วยให้มันต้องมีขึ้นมา เช่น
      • Contraint อย่าง Primary Key ต้องรับประกันว่าข้อมูลต้องไม่ซ้ำกัน มันเลยต้องมี Index มาข่วย
      • แต่ Foreign Key ไม่จำเป็นนะ
  • แนวคิดพื้นฐานของ MSSQL Index
    • ตัว MSSQL ทำ Index รูปแบบ Tree ชนิด Balanced tree โดยทำให้ Tree กว้างที่สุดเท่าที่จะทำได้ครับ เวลาตัดข้อมูลที่ไม่เกี่ยวข้องจะได้ทำได้เร็ว และ Tree ไม่ลึกจนเกินไป
  • เงื่อนไขที่เอามาใช้ในการช่วยคัดเลื่อก Column เพื่อนำมาทำเป็น Index
    • Selectivity –  ความสามารถในการดึงข้อมูล ถ้าต้องการผลลัพธ์น้อยๆ จากข้อมูลทั้งหมดที่เยอะ ควรทำ Index เพื่อเพิ่มความเร็ว ตัวอย่างที่ใช้กัน คือ Primary Key (Unique อยู่แล้ว)
    • Density – ความหนาแน่นข้อมข้อมูล ยิ่งต่ำยิ่งดี ตัวอย่างที่ดี คือ Primary Key ตัว Density ต่ำมาก ข้อมูลไม่ซ้ำเลย
      • แต่ถ้าเอา Column อย่าง FirstName ซ้ำกับเพียบเลย หรือถ้าเอา Gender มาใช้ ซ้ำกันครึ่งๆเลยๆ เอามาทำ Index ก็ไม่ได้ช่วยอะไร
    • Index Depth – ถ้าใน MSSQL ตัว Database Engine เป็นคนกำหนดให้
  • Data Types & Index อันไหนที่เหมาะกับ DB
    • Numeric – ต้องเป็นแบบ Exactly Numeric หรือ เลขที่หารลงตัว เวลาเอามาทำ Index มีประสิทธิภาพดีที่สุด เร็วสุด
    • Charactor – ทำได้ดี ถ้า key ไม่ยาวมากเกินไป
    • Date-Related Index – กลไลเบื้องหลังมันแปลงเป็น Numeric  ก่อนจัเก็บอีกที เหมาะสำหรับงานที่ต้องค้นข้อมูลตามเวลา
    • Guid – ดีกว่า Character แต่คนอ่านยาก
    • Bit Index – Bit 0,1 มันดี ถ้าใช้งานได้ถูกกับลักษณะ เช่น เอาไปเป็น Flag แสดง Prouduct ช้นไหนที่ยัง Support / ไม่ Support
    • Note: เสริม Key ที่ไม่ดี คือ Key ประเภทชุดอักษรยาวๆ เข้ารหัสสินค้า เช่น BKK-AYMC-2015-X ถึงว่าคนอ่านจจะอ่านแล้วเข้าใจเลย แต่ Database มันทำ Index ยาก
  • Single Column vs Composite Index
    • เป็นไปได้ควรจะทำ Index จาก Column เดี๋ยว ที่ Unique พอทำให้เจอข้อมูลได้
    • แต่ในโลกความจริง มันมีสถานการณ์ที่ต้องทำ Composite Index
      • ใช้เพื่อเพิ่มความสะดวกในการทำ Application
      • แต่มันไม่ได้เพิ่ม Speed
      • Composite Index ที่เรียงจาก Column A, B กับ B, A นั้น ไม่เหมือนกันนะ
  • Heap table vs Clustered table
    • Heap table (index_id = 0 ถ้าดูจาก sys.indexes)
      • ตัว Page เอง ไม่มี Order เลย ถ้ามี Row เพิ่มเข้ามาตัว DBMS หา Page อันไหนที่ว่าง แล้ว ยัดข้อมูลเลย ทำให้มีจุดเด่นที่ใช้งานพื้นที่คุ้ม
      • มาดูแต่ละ Operation ดีกว่า
        INSERTเจอ Page ไหนว่าง ก็ยัดเลย
        UPDATEปกติถ้า Update ลง Page เดิม แต่ข้อมูลมันใหญ่กว่า ย้ายไป Page ใหม่ที่มีพื้นที่พอ แล้วทำ Pointer ไว้ ซึ่งถ้ามีแบบนี้เยอะๆ จะเกิดปัญหา Forwarding Pointers ถ้ามีเยอะๆ กระทบ Performance
        DELETE Mark Flag ไว้ หากใครมาเขียนข้อมลทับสามารถทำได้เลย
        SELECTทำ Table Scan อย่างเดียวเลย
      • งานที่เหมาะกับ Heap table พวกที่เก็บ Temp ทั้งหมด มีการ SELECT * FROM แต่ไม่ต้องใช้ WHERE
    • ปัญหา Forwarding Pointers
      • MSSQL รุ่นเก่า: DROP แล้วสร้าง Table ใหม่ย้ายกันวุ่นวาย
      • MSSQL 2008 เป็นต้นไป สามารถใช้คำสั่ง: Rebuild ช่วยได้
    • Clustered table (index_id = 1 ถ้าดูจาก sys.indexes)
      • ตัว Page มีการกำหนด Logical Order ซึ่งข้อมูลในแต่ละ Row เก็บมีการเก็บข้อมูลตาม Logical Order เหมือนกัน โดยที่ Leaf Level ของ Index มัน คือ Row ถูกจัดตาม Logical Order เลย
      • ใน Clustered table มีได้แค่ 1 Clustered Index เท่านั้น เป็น Physical Index เลย อารมณ์เหมือนกับ Dictionary ถ้ามีการเปลี่ยน Column ที่เป็น Cluster Index ที ต้องรอมัน Rebuild ใหม่เลย
      • สำหรับพวก Data warehouse พวก Fact Table นิยมทำกับ Clustered Index บน Field ประเภท Date เพราะ สิ่งที่เราสนใจดูพวกข้อมูลย้อนหลัง ในช่วงเวลาต่างๆ ถ้าทำ Index ไว้มันช่วยให้ดึงเร็วขึ้น
      • มาดูแต่ละ Operation ดีกว่า
        INSERTเก็บข้อมูลตาม Page โดยต้องเรียงตาม Logical Order ถ้าข้อมูลที่ต้องการเก็บใหญ่เกินไปเกิดการ Splitting Page (ย้ายข้อมุลที่ยัดลงไม่ได้ลง page ถัดไป)
        UPDATEถ้ากรณีที่แก้ไขข้อมูลแล้วไปตรงกับ Column ที่ทำ Clustered Index ไว้ Row นั้นต้องถูกย้ายที่เก็บให้ตรงตาม Logical Order
        DELETEMark Flag ไว้
        SELECTถ้า Query มีเงื่อนไขที่ตรงกับ Clustered Index มันช่วยให้การ เข้าถึงได้เร็วขึ้น ลด Table Scan ทั้ง Seek(ค้นข้อมูลเดี๋ยวๆ) และ Scan(ค้นเป็นช่วง)
        Note: ถ้า Scan ตาม  Clustered Index ข้อมูล Sort อยู่แล้ว
      • งานที่เหมาะกับ Table ทีมีการ SELECT เยอะ ใช้เงื่อนไขที่ตรงกับ Clustered Index
    • ปัญหา Index Fragmentation
      • เกิดจากการ Splitting Page หรือย้าย Row ในช่วย INSERT หรือ UPDATE ซึ่งถ้ามี Fragmentation เยอะๆ ไม่ดี Performance ตก
      • Type of Fragmentation
        • Internal – เกิดใน Page เดียวกัน
        • External – ข้อมูลกระจายไปข้าม Page
      • การ Defect Fragmentation ดูได้จาด
        • SSMS – Index Properties
        • sys.dm_index_physical_stats
      • Page fullness – ถ้ามีค่าเยอะๆ ข้อดีตอนอ่านข้อมูลอยู่ใกล้กันแล้ว อ่าน Pageได้ข้อมูลเยอะแล้ว แต่ข้อเสีย Fragmentation น้อยโอกาสเกิดการ Splitting Page สูง ลักษณะนี้เหมาะกับ Table เน้นอ่าน หากตรงข้ามกันเหมาะกับ Table ที่เน้นเขียน
        • สรุป Fragmentation ควรมีอยู่บ้างสำหรับ Table ที่เน้นเขียน
      • การ Control Fragmentation ทำได้โดย
        • การกำหนด FillFactor บอก DB ไปเลย ในแต่ละ Page เรายอมให้ DB เติมข้อมูลได้กี่ % ถ้าเกินปุ๊บ Splitting Page เลย แค่ค่า FillFactor กำหนดให้พอดี เพราะ ถ้าทำมาก Disk เปลือง Read อาจจะช้า เพราะ ต้องอ่านข้าม Page
        • เน้นทำ Clustered Index มากกว่า
        • ดูค่า Default ของ FillFactor จาก sys.sp_configure
        • การปรับ FillFactor ใช้คำสั่ง
  • Index Statistics
    • Statistics ค่าพวกนี้ ถ้าคนอ่านมันตีความยาก และไม่รู้ว่ามันสื่อถึงอะไร แต่ตัว Database Engine เอาค่าพวกนี้ไปใช้ในการกำหนด Execution Plan ซึ่งถ้าค่าพวกนี้ เป็นค่าที่อัพเดทล่าสุด จะช่วยให้ Database Engine ตัดสินใจได้แม่นยำมากขึน ในการกำหนด Plan
    • Statistics ถ้าเข้าไปดูใน Table >> Statistics พบอยู่ 2 กลุ่ม
      • Statistics ชื่อเดียวกับ Index
      • Statistics ชื่อประหลาด แต่มี Pattern ประมาณ _WA_Sys_
    • Statistics เกิดขึ้นมาตอนไหนหละ ?
      • เกิดขึ้นตอนที่ Build Index ครั้งแรก
      • เกิดขึ้นตอนที่ Query แล้วไป โดน Column ที่ไม่มี Index แต่ต้องใช้ ตัว Database Engine มันสร้าง Statistics พวก _WA_Sys_ ขึ้นมา เอาไว้อ้างอิงในครั้งถัดไปที่ใช้งาน
    • ตัว SQL Server มันมี Auto Update Statistics นะ แต่มันมีเงื่อนไขในการ Update ค่า ดังนี้
      • เงื่อนไขที่ 1: เมื่อ Table ที่จากเดิมว่างๆ แล้วมี Row แรกเข้ามา
      • เงื่อนไขที่ 2: Table มี Row < 500 Records และ มีการ Insert เพิ่มเข้ามาอีก 500 Row ขึ้นไป
      • เงื่อนไขที่ 3: Table มี Row >= 500 Records และ มีการ Insert เพิ่มเข้ามาอีก 500 Row + 20% ของจำนวน Row ทั้งหมด
    • แล้วทำไมมี Auto Update Statistics แลัวมัน Database ยังช้า
      • ตัว Auto Update Statistics ไม่ทำงาน เนื่องจากไม่เข้าเงื่อนไขของมัน ปัญหานี้ มักจะเจอใน Table ที่มีขนาดใหญ่มาก
      • Operation ที่เข้ามายุ่งกับ Table มีพฤติกรรมที่เปลี่ยนไป เช่น
        • จากเดิม SELECT น้อยๆ (1/10) อยู่ๆมา SELECT เอาข้อมูลในปริมาณที่มากกว่าแต่ก่อน (5/10)
        • หรืออยู่มีข้อมูลก้อนใหญ่เข้ามา Dump หรือถูกเอาออกไป แต่ไม่เข้าช่ายเงื่อนไขของ Auto Update Statistics
      • Table ไหนที่มี Statistics กลุ่ม _WA_Sys_ เด็มไปหมดเลยแสดงว่า Query ที่เราใช้มันไม่ตรงกับ Index ที่เคยสร้างเอาไว้เลย ซึ่งตัว _WA_Sys_ ถ้าอันไหนถูกใช้บ่อยๆ ก็เปลี่ยนมันให้เป็น Index ซะ
      • ถ้ามี Operation ที่ Dynamic มากๆ จนไม่เข้าข่ายของ Auto Update Statistics ก็ต้องทำเอง โดยใช้คำสั่ง
      • ถ้าเป็นไปได้คำสั่งพวกนี้ควรอยู่ในรอบการ MA ของ Database ด้วย
  • Non-Cluster Index เหมือนดัชนีท้ายเล่ม ทำบน Heap หรือ Cluster Index เพิ่ม
  • Q&A

Power BI with R by Chawalit Pusitdhikul

  • Power BI
    • ภาพรวมใน Section นี้คล้ายกับ Blog ตอนที่แล้ว
    • เพิ่งรุ้ว่ามี Onpremise Power Bi แต่ยัง Support Data Source ไม่เยอะ ในอนาคตการ Integrate เข้ากับ MSSQL
  • R
    • Stat software
    • flexible กว่า บางเรื่อง power bi ทำได้ แต่อาจจะ lock วิธีไว้
  • Prerequisite
    • R
    • Power Bi desktop จากนั้น Set Path ของ R ให้ Power BI รู้จัก
  • Where can you use R in Power BI
    • As a Data Source – เขียน Script เข้าไปเองเลย
    • With in a query applied step – เขียน Script เอามาทำ Custom Process ที่ Power BI ยังไม่มี
    • Inside R Visual in a Power BI Report -ทำการ Visualize แต่ต้องเลือกบางค่า เพื่อบอก R ว่าเอาอะไร ไปเป็น Input
    • Custom Visual build with R – เอาตัว Visual จาก Office store มาใช้งาน ต้องการ runtime ของ R และสามารถใช้ร่วมกับ Visual เดิมของ Microsoft ได้ จากตัวอย่างเป็นการทำ Clustering ครับ (กราฟทางขวา)
  • R integration Limitation with power bi
    • รองรับ dataFrame อย่างเดียว
    • ข้อมูลที่เป้น N/A Transalate เป้น Null
    • ไม่เหมาะกับข้อมูลที่เยอะมาก (ณ เวลานี้ ในอนาคตไม่แน่)
    • Visual Calculation มีรอบ 5 นาที ถ้าเกินจากนั้นมันส่ง Error ออกมา (เหมือนพวก Azure แหะ)

ปิดท้ายของ Blog อาหารการกินครับ มี KFC กับโดนัท อิ่มทั้งท้อง อิ่มทั้งสมองเลยครับ

This slideshow requires JavaScript.

 

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

สำหรับมางาน SQL Server Community Thailand Meeting งานนี้จัดเป็นครั้งที่ 2 แล้ว ส่วนงานแรกผมพลาด เพราะติดสอบพอดี โดยในวันนี้มี 2 หัวข้อใหญ่ครับ ได้แก่

  • SQL Server Performance Tuning Essential โดยคุณ Chulladej Aramsri
  • Power BI Desktop โดยคุณ Chalaivate Pipatpannawong

SQL Server Performance Tuning Essential โดยคุณ Chulladej Aramsri

  • หมายเหตุ: เขียนในมุมของ Developer หากมีอะไรผิดพลาด ขออภัยมา ณ ที่นี้ครับ
  • Root-Cause ของ Perdormance หลักๆ ที่พบบ่อยๆ จากมากไปน้อย
    • T-SQL – เกิดจากการ Query เขียนไม่ดี
    • Poor Index Strategy – วาง Index ผิด หรือไม่เหมาะสม เช่น Table นี้ Write เยอะ แต่ทำ Index เยอะ แต่ความจริง Index ช่วยให้การ Read ให้เร็วขึ้น
      • Note: SQL Server มี index 2 แบบ Row Store กับ Column Store ต้องเลือกใช้ และ Control ให้ดี
      • ถ้าเลือก Index ไม่เป็น ก็อย่าใส่ Index เลย มันบาปป
    • I/O SubSystem
    • Application Code – Code ของ Dev แหละ เช่น ใน Loop 1 ครั้ง แวะไปเยี่ยม DB ก่อน ทั้งที่ความจริงมันกวาดมาบางส่วนก่อนได้ (อันนี้ส่วนตัวผมเจอเยอะนะ)
    • DB Schema Design
    • Out-of-date /missing statistic
    • SQL Server Configuration – ตัว MS SQL Server การ Tuning มีรายละเอียดปลีกย่อยในแต่ละ Version โดยการ Tuning เริ่มทำตั้งแต่ติดตั้ง แต่ส่วนใหญ่กด Next รัวๆ กัน
    • Virtulization / Hardware Problem และ CPU Power Saving มีน้อยมาก ถ้าดูจากรูป
  • แล้วปกติที่ไปจัดการกับ Data ที่ทำผ่าน SQL มันมีแบบไหนบ้าง
    • Adhoc – เขียน Query สดๆ หรือ เป็น String ที่ฝั่งไว้ในตัว Application
    • Stored Procedure – ตัว DBMS มีการเตรียมพร้อมไว้แล้ว มันเร็วกว่าแบบ Adhoc แต่ขึ้นกับวิธีการเขียน Logic ด้วยนะครับ เช่น ตัว Cursor ถ้าใช้ไม่เป็นให้มันอ่านที่ละ Row มันก็ช้าได้ ปกติตัว DBMS อยากได้อะไรเป็นชิ้นๆ ส่งไปปุบ DB ไป Process ได้ผลลัพธ์เป็นชิ้นกลับมาเลย
  • Commond Approach ในการ Optimize Performance โดยเริ่มจากสิ่งที่ควรทำมากที่สุดลงไป
    • Schema Design – พื้นฐานสำคัญที่สุด
    • Query Optimization – แยกเรื่อง ทำให้เหมาะสม
    • Indexing – เน้นอ่าน
    • Locking
      • Deadlock ( 1205) – Resource ถูกยึดไว้
      • Blocking – น่ากลัวกว่า เพราะ มันไม่มี Error Number เอาง่ายๆ ตัว DBMS ไม่สามารถ Detect ได้ แบบ Deaklock (ส่วนตัวเข้าใจว่า Blocking = Starvation หรือ livelock ของการทำ Multithread นะ)
    • Server Turing ควรทำตั้งแต่ต้น ควรทำตั้งแต่ Design
      • Memory ตัว SQL Server DMV มันกิน Mem เอาไว้ Cache
      • I/O – ssd เขียนพอๆกับ disk หมุน Data File อยู่คนละ Physical Drive กับ Log File เราจับกลุ่ม Table แล้วแยกไฟล์ Data ของ DB เป็นหลายไฟล์
      • CPU
  • Wait Statistic Performance Tuning Approach
    • ใช้เมื่อทำ Commond Approach แล้ว ยังช้า ยังมีปัญหาด้าน Performance อยู่
    • สนใจงานที่รอทำงานอยู่ (Wait) ทำไม ? มันยังไม่เสร็จ เอามาวิเคราะห์จริงๆ มันช้า ที่ DB หรือ App เขียนไม่ดีแล้ว DB ช้าตาม Query ของ App
  • Back to Basic ก่อนจะรู้เรื่อง Wait Statistic เราควรรู้อะไรบ้าง
    • Three Network Protocal ที่ MS SQL Support
      • Shared Memory – ถ้าช้า ลองรันที่เครื่อง DB เรา ผ่าน SSMS เลย จะได้รู้ที่มันดึงข้อมูลช้า เกิดจากตัว Network หรือป่าว ?
      • Name Pipes
      • TCP/IP
    • สำหรับในส่วนของ Application Layer ใช้ตัว abular Data Stream Protocol(TDS) ซึ่งใช้ได้กับ MSSQL ทุก Version ที่นิยมใช้ในปัจจุบัน รวมถึงบน Azure ด้วย
    • Query LifeCycle จาก Query ที่เราส่งมาจาก App ของระบบงานที่พัฒนา หรือจาก SSMS มันต้องผ่านอะไรบ้าง
      • อย่าง Network
      • ถัดมาเข้า DB Engine
    • DB Engine มันส่วนประกอบอะไรบ้าง
      • Query Execution Layer – มาตรวจก่อน ว่า Query Syntax ถูกไหม มันคิดยาก หรือป่าว ถ้าง่าย SELECT * FROM ไป Query ตรงๆเลย แต่ถ้ายากใน Layer นี้ไปวางแผน หากลยุทธ์ เพื่อให้มันทำงานอย่างมีประสิทธิภาพที่สุดครับ โดยในแบบนี้ตัว DB สร้างตัว Execution Plan ขึ้นมาครับ
      • Storage Engine Layer – เอา Plan จาก Query Execution Layer มาใช้ใน Layer จัดพื้นที่ โดยตัว SQL มันมองเป็น Page เหมือนหนังสือ ว่าจะเอาข้อมูลตรงไหนมา
      • SQLOS – MSSQL มี OS ของตัวเอง โดยทำงานเป็น User Mode OS (ถ้าสาย ComSci เรียนวิชา OS มันมี 2 Mode คือ Kernel Mode กับ User Mode
        • ทำ Wait Statistic ทำนี้
        • low – level ที่จัดการ Resource, ทำ Scheduling, จัดการ I/O, Locking, จัดการ Transation, Deadlock, Exception และอื่นๆ
        • คุมงานที่ส่ง ป้อนให้ CPU ไปทำงาน
        • ถ้าลง DB แล้ว พยายามอย่าให้ Server ทำงานอื่นเสริม รวมทั้ง Service ของ MS เช่น ลง Service อย่าง Reporting / Analysis
        • ในตัว DMV ของกลุ่ม sys.dm_os_xxx เป็นตัวที่มา Track ดูการทำงานของ SQLOS
      • NOTE: ผมเพิ่งรู้ว่าตัว MSSQL Server มัมี OS ชั้นนี้ ปกติสนใจพวก Query Execution Plan มากกว่า
    • NUMA System
      • NUMA node แบ่งกลุ่มให้ CPU เข้าไปให้ Memory แต่ละผืนที่จองไว้ จากเป็น CPU 8 Core
      • 1 CPU ต่อ 1 Scheduler โดยเราสามารถปรับได้ที่ตัว Server Properties และกำหนด CPU ที่ใช้ตัว MSSQL มันใช้งานได้
      • ตัว CPU เองมันทำงานจริงได้ 1 งาน ตัว SQLOS ส่งไปเข้าที่ละ Task ให้มันเผา
      • ถ้ามีหลาย NUMA node พยายามเลือกใช้ CPU ใน Node เดียวกันให้ MSSQL เพราะ ถ้าทะลึ่งเลือกกันกระจายไปหลายๆ Node พอเวลามันใช้ข้อมูลจาก อีก Node มันต้องผ่านเส้น InterConnect ทำให้มันช้า
    • เวลามี Query เข้ามาจาก Client / App Server หรือ Web Server มันจอง 1 Session เอาไว้ โดยที่
      • 1 Session มีหลาย Request
      • 1 Request มี หลาย Task โดย Task เป็นตัวที่ CPU ต้องรีบเอางานไปเผาให้เร็วที่สุด
      • โดยใช้ตัว sys.dm_exec_requests เรารู้ว่ามีงานทำงาน Backgroud อยู่เท่าไหร่ ใช้ CPU อย่างไรบ้าง มี Status อะไรบ้าง เวลาดูให้ดูเบอร์ที่มากกว่า 50 น้อยกว่านั้นเป็นของ System
  • Preemptive vs Non-Preemptive
    • Preemptive – จัดความสำคัญ  Windows พอใช้ CPU หนักๆ เช่น เล่มเกม / Process EOD ตัว OS จัดงานนี้มีความสำคัญเยอะสุด
    • Non Preemptive – ทุกงานมีความสำคัญเท่ากัน ไม่ว่าจะงานใหญ่  หรืองานเล็ก ไม่สนว่าแต่ละ Task ทำทันไหม ตัวอย่างการทำงาน MSSQL
    • Note: ถ้า MSSQL ต่ำกว่า 2000 ใช้แบบ Preemptive  แต่ Version หลังๆ ใช้แบบ Non Preemeptive
      • MSSQL 2000 ยังไม่มี DMV ครับ
  • MSSQL-Non Preemptive Scheduler
    • ในแต่ละ Task มีเวลาทำงานเท่าๆกัน คือ 1 Quantum (4 ms) ถ้า CPU ทำทันจบ แต่ถ้าทำไม่ทัน มันวนเก็บไว้ใใน List เข้า Queue ไว้
    • ตัว List ที่สำคัญของ MSSQL
      • Worker List – งานส่งไปเผาไปให้ CPU เผา
      • Runable List – งานที่รอเผา โดยส่งไปเป็น Worker ทำงานเป็น FIFO
      • Waiter List – งานที่เผาไปแล้ว แต่ไม่เสร็จ เกินเวลา 1 Quantum (4 ms) จะถูกวนมาเก็บใน List นี้ โดยตัว MSSQL เก็บข้อมูลตรงนี้ เอาไปทำ Wait Statistic
      • I/O List
      • Time List
  • กลับมาที่ Wait Statistic

    • พอเข้าใจแนวคิดแล้ว ว่าตัว MSSQL มันแตกงานย่อยที่สุดเป็น Task โดยแต่ละ Task ต้องส่งไปเผา(Worker List) ถ้างานมันใหญ่เผาไม่หมด ถูกย้าย (Waiter List) พักไว้ ซึ่งถ้าแต่ละงานมันสัมพันธ์กัน แสดงว่างานที่ถูก Waiter ไปมันถูก Suspend รอให้ต้องรองานนั้นทำให้เสร็จก่อน จริงๆ เราเอาตรงนี้มาดูพวก Blocking ได้นะ
    • Wait & Queue
      • Wait ถ้าเป็นที่ Resource รองาน รอ I/O – ระบบแสดง Suspend
      • Wait ถ้าเป็นที่ Signal จาก CPU- ระบบแสดง Runable
      • Queue – งานใน Worker List ที่ทำไม่เสร็จ
    • DMV ที่เกี่ยวข้อง
  • Wait Statistic – Wait Type ที่เราพบเอกกันบ่อยๆ
    • LCK_M_* – Lock ที่ resource เจอเยอะๆไม่ดี
    • PAGELATCH_* – บอกว่ามีปัญหาระหว่าง Memory กับ Disk มี page ที่ถูกแย่งกันใช้งานเยอะ วิธีแก้ปัญหา เพิ่ม DataFile(.mdf) หรือทำ Partition
    • PAGEIOLATCH – บอกว่ามีปัญหาระหว่าง Disk กับ Memory วิธีแก้ปัญหา มีเงินปรับ I/O หรือไม่ทำ Index ให้ DB มัน Update ค่า Stat ใหม่ หรือง่ายที่สุด ปรับที่ Query เราเองที่ทำ Table Scan หนักๆ
    • CXPACKET – ดี เพราะ ตัว MSSQL Server ใช้ Parallel Plan (ต้องดูจาก Execution Plan)  มีการใช้ Scheduler มากว่า 1 ตัว ช่วยกันทำงานมากกว่า 1 CPU โดยปกติใช้ CPU ตัวเดียว ทำให้ Query มีการทำงานอย่างมีประสิทธิภาพ ถ้าจะปรับต้องไปดู MAXDOP
    • WRITELOG – ถ้ามันมีเยอะไป แสดงว่า I/O ของ Log มีปัญหา
  • Tools เอาไปไว้ใช้ดู Performance
  • Useful Resource

Power BI Desktop โดยคุณ Chalaivate Pipatpannawong

  • ตัว Power BI เป็น Tools ที่เกิดมา เพื่อเล่นกับ Data โดยเฉพาะ และสามารถรองรับข้อมูลจำนวนมากได้ ถ้าใน Excel แบบเดิม มันจะถึงจุดนึงที่ Excel มันเดี้ยง และเอาข้อมูลนั้นมาแสดงผลให้น่าดึงดูด และมีลูกเล่น (Visualization)
  • ตัว Power BI App มี 3 แบบ
    • Desktop – จัดการข้อมูล ทำ Model
    • Service อยู่บน Cloud – เป็น Software As A Service เท่าที่ฟังมา ผมเข้าใจว่าตัวนี้เป็ฯแกนหลักนะ
    • Mobile – เน้นการ View ดู เพื่อเอาข้อมูลสร้าง Value
  • ขั้นตอนการทำ มี 5 Step
    • Get Data
      • สามารถเอาข้อมูลจากหลายแหล่ง Multi Source เอาอะไรมายัดก็ได้ครับ อาทิ
        • Microsft Family พวก SQL Server / Azure หรือ พวก MS Office Excel เป็นต้น
        • Text File – CSV
        • ของค่ายอื่นอย่าง Oracle (พวก DB เป็นต้น)
        • หรือจาก Service อย่าง Google Analytic
        • และอื่นๆ Step การทำงานหลัก มี 5 ส่วน
      • Note: การเอาข้อมูลเข้ามาใน Power BI มี 2 Mode
        • Import – ดึงมาหมด ช้าในตอนแรก
        • Direct Query – ดึงทีสนใจ
    • Design
      • Clean Data – ทำพวก ETL จัดการข้อมูลเรียบร้อยให้พร้อมใช้งาน
      • Data Model – สามารถนำข้อมูลมาเทรวมกัน และสร้างความสัมพันธ์กันได้
      • Visualization – ทำให้มันดูง่ายขึ้น โดยสร้าง Report หรือ Dashboard ซึ่งของตัว Poewer BI มันเป็น Interactive ทำให้ข้อมูลมันมีลูกเล่น เปลี่ยนตามเงื่อนไขของผู้ใช้
        • Design Once Access All Device
        • พวกสูตรเหมือน Excel ใช้ DAX Syntax ช่วยได้
        • แต่ถ้ามันซับซ้อนสามารถใช้ภาษาที่ฮิตๆ ในการทำ Data Science อย่างภาษา R เข้ามาจัดการได้
        • เพิ่ม Q&A เวลา Public ขึ้นเว็บมีตัวช่วยให้ User สามารถค้น และการแสดงผลของข้อมูลตาม User ได้
    • Publish – เอาข้อมูลขึ้นเว็บ เพื่อให้คนอื่น เอา Model(Dashboard, Report) ที่ได้จาก Power BI ไปใช้งาน
    • Access – ดูได้จาก App ทั้ง 3 หรือ Get Code ไป Embled บน เว็บได้ ตัวอย่าง 9experttraining
    • Sync – ถ้าข้อมูลมีข้อมูลมาใหม่ สามารถเติมเข้าไปได้ แล้วตัว Model ที่สร้างจาก Power BI รับรู้ และดึงมาแสดงผลได้
  • Power BI – Save ได้ไฟล์ .pbix
  • Q&A #01 – สามารถกำหนดสิทธิของกับข้อมูลได้ไหม ?
    • Ans ได้ กำหนดสิทธิการเห็นข้อมูลได้ แต่ผู้บริหารเห็นทั้งหมด และหัวหน้าส่วนภาคต่างๆ ดูได้เฉพาะของพื้นที่ตัวเองเป็นต้น
  • Q&A #02 – สาย DEV ของ Office มีตัว VSTO ให้มาทำส่วนเสริม (AddIns) ของ Office ได้ ใน Power BI มีอะไรคล้ายกันไหม ?
    • Ans มีตัว Power BI เปิดใช้ ทำตัว Custom Visuals

สุดท้ายของกินภายในงานครับ มีโดนัทด้วย แต่ลืมถ่ายไว้

This slideshow requires JavaScript.

 

[MSSQL] มาปรับปรุง Query การตรวจสอบเรื่อง Lock ให้ดีขึ้น

จาก Blog ตอนที่แล้ว ผมได้เขียนไปเกี่ยวกับ [MSSQL] ตรวจสอบ Table ว่าถูก Lock หรือใช้งานโดย Application หรือไม่ ? จาก Query เดิม เรารู้แค่ว่า Table ไหน Lock เท่านั้น คราวนี้มาลองเพิ่มข้อมูลที่จำเป็น เพื่อให้ Dev หรือ DBA มาวิเคราะห์กันได้ง่ายขึ้นครีบ โดยข้อมูลที่มเพิ่มใน Query ได้แก่

  • ชื่อ Database
  • SQL ที่มีปัญหา
  • รูปแบบการ Lock
  • เครื่องไหนที่ทำ DB Lock และใช้ Application อะไร
  • ใช้ User อะไรเข้ามาทำรายการ และเข้า DB มาตอนเวลาไหน

Let’ s Go – ลองมาปรับ Query กัน

จากตัว Query เดิม เราพบว่าข้อมูลหลายๆส่วน ในตัว DMV ของ MS SQL Server ไม่มีให้นะครับ ต้องเอา DMV ตัวอื่นๆ เข้ามาช่วยครับ ดังนี้

หลังจากเอา DMV ต่างๆ เข้ามาปรับปรุง โดย Query Version ใหม่ ดังนี้ครับ

มาดูผลลัพธ์กันเลย มีหลาย Column เลย เนื่องจาก SELECT มาตาม Requirement ที่กำหนดไว้นะครับ สำหรับใครอยากรู้ว่า Column แค่ละตัว มันตรงกับ Requirement ไหน ลองดูจาก Comment ประกอบได้ครับ

This slideshow requires JavaScript.

NOTE: Tools ของ SQL Server ที่ช่วยในการดู Lock นะ เข่น SQL Server Profiler หรือ Activity Monitor เป็นต้น แต่อันนี้เป็นอีกแนวทางหนึ่งครับ เผื่อกรณีที่ว่าไม่สามารถใช้ Tools ได้ครับ

[MSSQL] ตรวจสอบ Table ว่าถูก Lock หรือใช้งานโดย Application หรือไม่ ?

ช่วงนี้น่าจะเป็การเขียน Blog ที่ดองๆไว้ให้เสร็จนะครับ (อันนี้ดองมาตั้งแต่ปี 2014 มาใส่ภาพประกอบตอน 2017 555) วันนี้สุดท้ายของเดือนแล้ว มาดูกันสำหรับตัว MS SQL Server เราตรวจสอบได้อย่างไรว่า Table ถูก Lock ไว้นะครับ โดยเจ้าตัว MS SQL Server สามารถตรวจสอบได้ว่า Tabel ไหนถูก Lock โดยดูจาก Query ชุดนี้ครับ

ตัวอย่าง

ผมทดสอบ โดยทำให้ Table Company ถูก Lock ด้วยคำสั่ง SQL ดังนี้

ทดสอบ Run และดูผลลัพธ์

ดูผลลัพธ์การพบว่า Query ที่เขียนขึ้นสามารถดีกได้ว่า Table ที่ถูก Lock คือ Table Company (แต่ผลลัพธ์ อาจจะเยอะนิดนึงนะครับ เดี๋ยว Blog ตอนหน้ามาจัดผลลัพธ์ให้อ่านง่ายขึ้นครับ)

NOTE: ทดสอบเสร็จอย่าลืมคำสั่ง ROLL BACK นะครับ

ถ้าเกิดลอง Run แล้วเกิด Error

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action. สามารถแก้ได้โดย

  • เปลี่ยนไปใช้ User ที่มีสิทธิเข้าดูใน System database “Master” อาทิ เช่น Windows Authentication User หรือ SA เป็นต้น
  • เพิ่มสิทธิให้ User เดิมนี่แหละ สามารถดูข้อมูลใน System database “Master” ได้ ด้วยคำสั่ง

สำหรับใครสนใจของตัว DB2 สามารถอ่านได้จาก Blog นี้ครับ [DB2] มาดักดูว่าใครทำ DB ค้าง

Reference

 

[MSSQL] แก้ปัญหา Database Log File โตไม่หยุด

หลายๆพอใช้ Database ไป ผ่านไปนานเข้า ระบบฟ้องว่า Disk เต็ม พอเข้าไปดูอ้าวไฟล์ Log (.ldf) มันโตกว่าไฟล์ Database (.mdf) เสียอีก คราวนี้มาลองดูสาเหตุกัน โดยสาเหตุที่เป็นไปได้

  • เลือก Recovery Model ที่ไม่เหมาะสม
  • ไม่มีการจัดเก็บ Backup Log ออกมา พอไปตั้งให้ Log มัน Auto Grow แล้วมันเลยบวมครับ

พอรู้สาเหตุแล้ว มาดูวิธีแก้บ้าง

  • ปรับเปลี่ยนเลือก Recovery Mode ที่ใช้ครับ โดยลองพิจารณ์ Recovery Model จาก Blog ผม หรือจะถาม Google ก็ได้ครับ เมื่อได้ Recovery Model ที่ใช่แล้ว มาดูวิธีขั้นตอนการปรับแก้ครับ
    • เข้าไปที่ SQL Server Management Studio (SSMS) อย่างน้อยเป็น Windows Authentication หรือใช้ User ที่มีสิทธินการจัดการ DB ครับ
    • ในหน้าจอ Object Explorer browse เลือก Database
    • คลิกขวาที่ Database ที่เลือก >> Properties >> Options
    • หาในส่วนของ Recovery Model เลือกให้เหมาะสมกับการใช้งาน
    • กด OK
  • ลดขนาดของ Database Log File (.ldf)
    • Shrink Database ตามวิธีการ
      • เข้าไปที่ SQL Server Management Studio (SSMS)
      • ในหน้าจอ Object Explorer browse เลือก Database
      • คลิกขวาที่ Database ที่เลือก Tasks >> Shrink >> Files
      • ดู File Type = Log
      • Shrink action ดูว่่าให้ DB มัน คืนพื้นที่ที่ไม่ได้ใช้ (Release unused space)
      • กด OK
    • Detach/Attach (ควรทำ Full Bacckup ก่อนนะ)
      • เข้าไปที่ SQL Server Management Studio (SSMS)
      • ในหน้าจอ Object Explorer browse เลือก Database
      • คลิกขวาที่ Database ที่เลือก Tasks >> Detach
      • ถ้ายังมี Application หรือ User ใช้งาน Database ก้อนนี้ออยู่ มันมีหน้าจอให้เตะ (Drop Connection) ครับ
      • ลบ หรือ เปลี่ยนชื่อ ไฟล์ .ldf เป็นชื่ออื่น
      • Attach Database (.mdf) กลับเข้าไปใหม่ โดยคลิกขวาที่ Database ที่เลือก Tasks >> Attach
    • Manual Log Backup
      • เข้าไปที่ SQL Server Management Studio (SSMS)
      • ในหน้าจอ Object Explorer browse เลือก Database
      • คลิกขวาที่ Database ที่เลือก Tasks >> Backup หน้าจอ Backup แสดงขึ้นมา
      • ตรวจสอบชื่อ Database (จะเปลี่ยนเป็นก้อนอื่นก็ได้นะ)
      • ตรวจสอบ Recovery model
      • Backup type เลือก Transaction Log ส่วนของ Option มีหลายแบบครับ เช่น Full(ทั้งหมด), Difference(เอาเฉพาะส่วนที่เปลี่ยนแปลง) เป็นต้น
      • จากนั้นเป็นการใส่ข้อมูลทั้งไป เช่น พวกชื่อ Backup / คำอธิบาย และมีการ Config ด้าน Security
      • เลือก Destination
      • รอ Vertify
      • จากนั้นกำหนด Config ของ Transaction Log ว่าให้ตัด Truncate Log หลัง Backup ไหม
      • มีหน้าจอถามไปอีก จากนั้นรอครับ

ในทีสุดเขียน Blog ที่ดองมา 3 ปีเสร็จ ช่วงนี้ยังมีเวลาว่างๆ ผมพยายามเก็บ Blog เก่าๆที่เขียนค้างไว้ หรือเขียน Blog ใหม่ไปเรื่อยๆ ครับ