Sql Server

วิธีตรวจสอบการใช้พื้นที่ดิสก์สำหรับตารางใน SQL Server โดยใช้ T-SQL

เมื่อจัดการฐานข้อมูล การทำความเข้าใจว่าตารางแต่ละตารางใช้พื้นที่ดิสก์มากน้อยเพียงใดมีความสำคัญต่อการเพิ่มประสิทธิภาพและการวางแผนความจุ ในขณะที่ผู้ใช้หลายคนอาจคุ้นเคยกับการตรวจสอบคุณสมบัติของตารางผ่านส่วนติดต่อกราฟิกของ SQL Server Management Studio (SSMS) การทราบวิธีการดำเนินการเดียวกันนี้โดยใช้ T-SQL สามารถเป็นประโยชน์อย่างมาก โดยเฉพาะสำหรับการทำให้เป็นอัตโนมัติและงานการจัดการฐานข้อมูลในระดับที่สูงขึ้น

ในบล็อกโพสต์นี้ เราจะเดินผ่านวิธีการที่ตรงไปตรงมาสำหรับการกำหนดพื้นที่ดิสก์ที่ถูกใช้โดยตารางใน SQL Server โดยใช้ T-SQL วิธีการนี้ไม่เพียงแต่เพิ่มประสิทธิภาพของคุณ แต่ยังให้โอกาสในการเจาะลึกเข้าไปในสคริปต์ SQL

ภาพรวมของวิธีการ

เราจะใช้ตารางชั่วคราวที่มีการใช้คู่กับโปรซีเจอร์เก็บข้อมูลระบบ sp_spaceused เพื่อรวบรวมข้อมูลเกี่ยวกับตารางต่างๆ ในฐานข้อมูลของคุณ นี่คือการแบ่งกลุ่มสิ่งที่เราจะทำ:

  1. สร้างตารางชั่วคราว เพื่อเก็บข้อมูลขนาด
  2. ดำเนินการคำสั่ง sp_spaceused เพื่อรวบรวมข้อมูลขนาดจากทุกตาราง
  3. วิเคราะห์ข้อมูลที่รวบรวม ให้เป็นมาตรฐานที่อ่านได้
  4. แสดงผลลัพธ์ ที่เรียงตามขนาดที่ถูกจองไว้

มาเริ่มกันเลย!

ขั้นตอนที่ 1: สร้างตารางชั่วคราว

ขั้นตอนแรกคือการกำหนดตารางชั่วคราวสองตารางที่จะช่วยเราเก็บข้อมูลขนาดที่เราจะดึงออกมา ใน SQL Server ตารางชั่วคราวจะมีสัญลักษณ์ # นำหน้า

CREATE TABLE #tmpSizeChar (
     table_name sysname ,
     row_count int,
     reserved_size varchar(50),
     data_size varchar(50),
     index_size varchar(50),
     unused_size varchar(50))                              

CREATE TABLE #tmpSizeInt (
     table_name sysname ,
     row_count int,
     reserved_size_KB int,
     data_size_KB int,
     index_size_KB int,
     unused_size_KB int)

คำอธิบาย:

  • #tmpSizeChar: เก็บผลลัพธ์ในรูปแบบตัวอักษร ซึ่งรักษาขนาดพร้อมหน่วย (เช่น MB, KB)
  • #tmpSizeInt: เก็บผลลัพธ์ในรูปแบบจำนวนเต็มเพื่อให้ง่ายต่อการคำนวณ

ขั้นตอนที่ 2: รวบรวมข้อมูลขนาดจากตาราง

เราจะใช้โปรซีเจอร์เก็บข้อมูล sp_msforeachtable เพื่อเรียกใช้คำสั่ง sp_spaceused กับทุกตารางในฐานข้อมูล คำสั่งนี้ให้ข้อมูลโดยละเอียดเกี่ยวกับการใช้พื้นที่ของแต่ละตาราง

SET NOCOUNT ON
INSERT #tmpSizeChar
EXEC sp_msforeachtable 'sp_spaceused ''?'''

คำอธิบาย:

  • NOCOUNT ON: ป้องกันไม่ให้ข้อความที่ระบุจำนวนแถวที่ถูกเปลี่ยนแปลงโดยคำสั่ง T-SQL ถูกส่งคืน ซึ่งช่วยปรับปรุงประสิทธิภาพสำหรับสคริปต์ที่มีหลายคำสั่ง

ขั้นตอนที่ 3: วิเคราะห์ข้อมูลที่รวบรวม

เมื่อเราเก็บรวบรวมข้อมูลแล้ว ก็จำเป็นต้องเปลี่ยนข้อมูลตัวอักษรให้เป็นค่าจำนวนเต็มที่เหมาะสำหรับการวิเคราะห์

INSERT INTO #tmpSizeInt (
        table_name,
        row_count,
        reserved_size_KB,
        data_size_KB,
        index_size_KB,
        unused_size_KB
        )
SELECT  [table_name],
        row_count,
        CAST(SUBSTRING(reserved_size, 0, PATINDEX('% %', reserved_size)) AS int) reserved_size,
        CAST(SUBSTRING(data_size, 0, PATINDEX('% %', data_size)) AS int) data_size,
        CAST(SUBSTRING(index_size, 0, PATINDEX('% %', index_size)) AS int) index_size,
        CAST(SUBSTRING(unused_size, 0, PATINDEX('% %', unused_size)) AS int) unused_size
FROM #tmpSizeChar

คำอธิบาย:

  • ขั้นตอนนี้ทำการแยกส่วนของตัวเลขออกจากขนาดที่ถูกจองและเปลี่ยนเป็นจำนวนเต็มเพื่อง่ายต่อการคำนวณ โดยจะแก้ไขลบตัวอักษร (เช่น KB, MB) ออกจากสายขนาด

ขั้นตอนที่ 4: แสดงผลลัพธ์

สุดท้าย เราจะดึงและแสดงผลลัพธ์ที่สรุปจากตารางขนาดจำนวนเต็มชั่วคราวของเรา

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

คำอธิบาย:

  • คำสั่ง SQL นี้ดึงข้อมูลทั้งหมดจาก #tmpSizeInt และเรียงตามคอลัมน์ reserved_size_KB ช่วยให้คุณเห็นว่าตารางใดใช้พื้นที่ดิสก์มากที่สุด

สรุป

โดยการทำตามขั้นตอนที่กำหนดไว้ข้างต้น คุณมีวิธีการที่จัดระเบียบเพื่อกำหนดว่าตารางใดใช้พื้นที่ดิสก์ใน SQL Server ด้วย T-SQL วิธีการนี้สามารถเป็นประโยชน์โดยเฉพาะสำหรับผู้ดูแลฐานข้อมูลและนักพัฒนาที่ต้องการรักษาประสิทธิภาพที่ดีที่สุดและติดตามการใช้ทรัพยากรในสภาพแวดล้อม SQL Server ของตน

ตอนนี้คุณสามารถแทนที่การคลิกใน GUI ด้วยวิธีการบรรทัดคำสั่งที่ทรงพลัง ซึ่งจะช่วยยกระดับทักษะในการจัดการฐานข้อมูลของคุณ!