ป้ายกำกับ: Database

Defragment & Optimize Database (MS SQL 2008 R2)

แผนภาพรวมการดำเนินการ Defragment & Optimize Database   สเต็ปที่ 1: การสำรองข้อมูลระบบ (Full Backup) เพื่อความปลอดภัยสูงสุดก่อนเริ่มงานสเต็ปที่ 3: รันสคริปต์วิเคราะห์เปอร์เซ็นต์ Fragmentation แยกรายตารางแบบละเอียดสเต็ปที่ 4: ดำเนินการจัดเรียงโครงสร้างข้อมูล (Reorganize / Rebuild Index)สเต็ปที่ 5: อัปเดตสถิติการเข้าถึงข้อมูล (Update Statistics) เพื่อให้คิวรีทำงานได้เร็วที่สุดสเต็ปที่ 6: ตรวจสอบประสิทธิภาพหลังการปรับปรุงและคืนพื้นที่เก็บข้อมูล สเต็ปที่ 1: การสำรองข้อมูลระบบ (Full Backup) เพื่อความปลอดภัยสูงสุดก่อนเริ่มงาน ก่อนที่จะมีการขยับหน้าข้อมูล (Data Pages) หรือสร้างอินเดกซ์ใหม่ สิ่งแรกที่จำเป็นต้องทำคือ การทำ Full Backup เพื่อเป็นหลักประกันว่าหากเกิดเหตุสุดวิสัยระหว่างทาง (เช่น ไฟดับ, ปลั๊กหลุด หรือดิสก์เต็ม) เราจะมีจุดที่ย้อนกลับมาได้ 100% ครับ USE master; GO -- สั่ง Backup ฐานข้อมูล dbwins_VCARGO1911 BACKUP DATABASE [dbwins_VCARGO1911] TO DISK = 'D:\Backup\dbwins_VCARGO1911_BeforeDefrag.bak' WITH FORMAT, MEDIANAME = 'DB_Defrag_Backups', NAME = 'Full Backup before Defrag', COMPRESSION, -- สั่งบีบอัดไฟล์ (ถ้าตัว Edition รองรับ จะช่วยประหยัดพื้นที่ดิสก์ได้มาก) STATS = 10; GO   สเต็ปที่ 2 และ 3: ตรวจสอบพื้นที่ว่าง และ สแกนหาจุดที่กระจัดกระจาย (Analysis, Reorganize/Rebuild Index) เนื่องจากคุณไม่ได้ระบุว่าใช้ SQL Server 2008 R2 รุ่น Standard หรือ Enterprise (ซึ่งมีผลต่อการสั่ง Rebuild แบบ Online) เพื่อความปลอดภัยที่สุด เราจะทำการ "สแกนดูอาการ" ก่อน โดยยังไม่สั่งแก้ไขครับ รบกวนเปิด SSMS แล้วนำสคริปต์ 2 ชุดนี้ไปรันได้เลยครับ (สคริปต์นี้ใช้แค่อ่านข้อมูล ปลอดภัย ไม่มีการล็อกตารางครับ แต่อาจจะใช้เวลาประมวลผลสัก 1-2 นาทีเนื่องจากฐานข้อมูลมีขนาด 13 GB):   สเต็ปที่ 3: เช็คขนาดพื้นที่ข้อมูลปัจจุบัน USE dbwins_VCARGO1911; GO EXEC sp_spaceused; GO   สเต็ปที่ 4: สแกนหาตารางที่มีปัญหา Fragmentation สูงสุด (ระดับวิกฤต) USE dbwins_VCARGO1911; GO DECLARE @db_id INT; SET @db_id = DB_ID(); SELECT OBJECT_NAME(IND.OBJECT_ID) AS TableName, IND.name AS IndexName, CAST(STATS.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS Fragmentation_Percent, STATS.page_count AS PageCount, STATS.index_type_desc AS IndexType FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') AS STATS JOIN sys.indexes AS IND ON STATS.OBJECT_ID = IND.OBJECT_ID AND STATS.index_id = IND.index_id WHERE IND.name IS NOT NULL AND STATS.index_level = 0 -- ดึงมาเฉพาะ Leaf Level (ระดับล่างสุดที่เก็บเนื้อข้อมูลจริงๆ) AND STATS.page_count > 10 -- กรองเฉพาะตารางที่เล็กมากๆ (ไม่ถึง 10 หน้า) ทิ้งไป เพื่อไม่ให้ตารางขยะมาบังตา ORDER BY STATS.avg_fragmentation_in_percent DESC; GO   สเต็ปที่ 5: สคริปต์สลายการกระจัดกระจาย (Defragmentation Script) สแกนทุกตารางอัตโนมัติเพื่อเลือกว่าจะ "จัดเรียงใหม่" (Reorganize) หรือ "รื้อสร้างใหม่" (Rebuild) ตามระดับความรุนแรงของปัญหา ช่วยลดภาระการทำงานของเซิร์ฟเวอร์ คืนพื้นที่ว่าง และทำให้ระบบสามารถดึงข้อมูลได้รวดเร็วสูงสุดอีกครั้ง USE [dbwins_VCARGO1911]; GO -- 1. ประกาศตัวแปรรับค่า ID แยกออกมาข้างนอก DECLARE @CurrentDBID INT; SET @CurrentDBID = DB_ID('dbwins_VCARGO1911'); -- 2. สร้างตารางชั่วคราวเก็บข้อมูล IF OBJECT_ID('tempdb..#IndexList') IS NOT NULL DROP TABLE #IndexList; CREATE TABLE #IndexList ( TableName NVARCHAR(255), IndexName NVARCHAR(255), FragPercent FLOAT ); -- 3. ใช้ตัวแปร @CurrentDBID ในการ Query INSERT INTO #IndexList SELECT OBJECT_NAME(STATS.OBJECT_ID), IND.name, STATS.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(@CurrentDBID, NULL, NULL, NULL, 'LIMITED') AS STATS JOIN sys.indexes AS IND ON STATS.OBJECT_ID = IND.OBJECT_ID AND STATS.index_id = IND.index_id WHERE IND.name IS NOT NULL AND STATS.page_count > 10 AND STATS.avg_fragmentation_in_percent > 5; -- 4. ใช้ Cursor ปกติ DECLARE @TableName NVARCHAR(255); DECLARE @IndexName NVARCHAR(255); DECLARE @FragPercent FLOAT; DECLARE @SQL NVARCHAR(MAX); DECLARE IndexCursor CURSOR FOR SELECT TableName, IndexName, FragPercent FROM #IndexList; OPEN IndexCursor; FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent; WHILE @@FETCH_STATUS = 0 BEGIN IF @FragPercent >= 30 BEGIN SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD WITH (FILLFACTOR = 90);'; END ELSE BEGIN SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE;'; END EXEC(@SQL); FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent; END; CLOSE IndexCursor; DEALLOCATE IndexCursor; DROP TABLE #IndexList; GO   สเต็ปที่ 6: ตรวจสอบประสิทธิภาพ/อัปเดตสถิติ (Update Statistics) หลังการปรับปรุงและคืนพื้นที่เก็บข้อมูล USE [dbwins_VCARGO1911]; GO -- สั่งอัปเดตสถิติของทุกตารางด้วยการสแกนแบบเต็ม EXEC sp_updatestats; GO     เพื่อเป็นการปิดงานอย่างสวยงาม ผมแนะนำให้คุณทำตามนี้ 3 ข้อครับ: 1. ทดสอบการใช้งานจริง: ลองเข้าโปรแกรมแล้วเปิดรายงานที่เคยทำงานช้า (โดยเฉพาะรายงานบัญชีหรือรายงานสินค้าที่ดึงข้อมูลจาก GLDT หรือ ICStockDetail) หากการตอบสนองเร็วกว่าเดิมชัดเจน ถือว่าเราบรรลุเป้าหมายแล้วครับ 2. ตรวจสอบ Error Log: ใน SSMS ให้ลองไปที่ SQL Server Error Log (ในส่วนของ Management) เพื่อดูว่ามี Error อะไรโผล่ขึ้นมาหลังรันสคริปต์ไหม (ถ้าไม่เจออะไร ถือว่าปกติครับ) 3. วางแผนการ Maintenance ระยะยาว: การทำแบบนี้ครั้งเดียวไม่เพียงพอครับ สำหรับฐานข้อมูลที่มีการเขียนข้อมูลตลอดเวลา (Active Database) แนะนำให้ทำ "Rebuild/Reorganize + Update Statistics" เป็นประจำ เช่น เดือนละครั้ง หรือ 2 เดือนครั้ง เพื่อไม่ให้มันสะสมจนกระจัดกระจายหนักเหมือนเดิม USE [dbwins_VCARGO1911]; GO -- 1. เปลี่ยน Recovery Model เป็น SIMPLE ALTER DATABASE [dbwins_VCARGO1911] SET RECOVERY SIMPLE; GO -- 2. รันคำสั่ง CHECKPOINT เพื่อสั่งให้ SQL Server เขียนข้อมูลค้างทิ้งลงไฟล์ .MDF ให้หมดก่อน CHECKPOINT; GO -- 3. สั่ง Shrink ไฟล์อีกครั้ง (ระบุขนาดให้เล็กลง เช่น 100MB ก่อนก็ได้ครับ) DBCC SHRINKFILE (dbERP_New_Log, 100); GO -- 4. เปลี่ยน Recovery Model กลับเป็น FULL (ถ้าจำเป็นต้องใช้) ALTER DATABASE [dbwins_VCARGO1911] SET RECOVERY FULL; GO

อ่านเพิ่มเติม