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

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

Database File Size

SELECT LOGICALNAME = DBF.NAME
     , FILETYPE = DBF.TYPE_DESC
     , FILEGROUPNAME = FG.NAME
     , PHYSICALFILELOCATION = DBF.PHYSICAL_NAME
     , FILESIZEMB = CONVERT(DECIMAL(10,2),DBF.SIZE/128.0)
     , USEDSPACEMB = CONVERT(DECIMAL(10,2),DBF.SIZE/128.0 - ((DBF.SIZE/128.0) - CAST(FILEPROPERTY(DBF.NAME, 'SPACEUSED') AS INT)/128.0))
     , FREESPACEMB = CONVERT(DECIMAL(10,2),DBF.SIZE/128.0 - CAST(FILEPROPERTY(DBF.NAME, 'SPACEUSED') AS INT)/128.0)
FROM SYS.DATABASE_FILES DBF 
	LEFT JOIN SYS.FILEGROUPS FG 
		ON DBF.DATA_SPACE_ID = FG.DATA_SPACE_ID 
ORDER BY DBF.TYPE DESC, DBF.NAME;

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

Table Size

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceMB DESC,
    t.Name;

ประโยชน์

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

แถม

ถ้าเราต้องการจะลด Database Size หลังจากรู้ Size Database แล้ว ทั้งในาส่วน mdf / ldf ถ้าต้องการลด Database Size ตัวเนื่อ DB นะ ลองทำตาม Step ประมาณนี้ได้

• คุยกันก่อนว่าจะลบ
• ลบข้อมูลจาก table ที่มันบวมๆ เวลาลบแนะนำให้ใช้คำสั่ง TRUNCATE มันเร็ว และไม่เพิ่ม Transaction Log ด้วย

TRUNCATE TABLE <YOUR_TABLE_NAME>

การลบข้อมูล ข้อมูลหลายจริง แต่พื้นที่มันยังจะไม่คืนนะ ต้อ

• ดูเลข id ของ db file

SELECT FILE_ID, NAME FROM SYS.DATABASE_FILES

• ลดขนาดเนื่อไฟล์จริงๆด้วยคำสั่ง
  - โดยที่ 1 คือ FILE_ID จากคำสั่งก่อหน้า
  - TRUNCATEONLY - บอก shrink เฉพาะส่วนที่ว่างท้ายไฟล์ (ไม่จัดเรียงข้อมูลใหม่)

DBCC SHRINKFILE (1, TRUNCATEONLY);

การ shrink บ่อยๆ อาจกระทบประสิทธิภาพและ fragmentation
และก็คำสั่ง DBCC SHRINKFILE มีคู่มือเต็มๆลองไปอ่านกันได้ https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql

ปล. จริงๆแล้ว SHRINKFILE กดจาก GUI ใน SSMS ก็ได้นะ

แต่ส่วนใหญ่ที่เจอ จะพบ log บวมมากกว่า สามารถไปอ่านได้จากอีก Blog ได้เลยครับ ส่วน DB2 ตามมา Blog แปะไว้ด้านล่างเรียบร้อย