SQL Server資料庫空間管理 (1)

標籤：

   資料庫經常遇到的問題：

   1)、資料庫檔案空間用盡  2)、記錄檔不停增長 3)、資料庫檔案無法收縮  4)、自動成長和自動收縮

    本系列就以上面的4個問題入手分析並總結資料庫空間的管理方法。

  1、檔案的分配方式以及檔案空間檢查方法

      首先，你在你的資料庫中運行sp_spaceused;之後會看到當前資料庫的使用空間資訊；這個命令也就會將大概的資料庫空間資訊給你展現出來，並不能查看每個資料檔案和記錄檔的使用方式；其中對於資料庫tempdb來說裡面儲存的臨時資料對象，這個命令是統計不到的。我們知道資料庫分為主檔案(.mdf)和輔助檔案(.ndf);資料的存放按照頁和區來存放和管理的，這裡有一條命令可以查看具體的頁內容:DBCC PAGE (<db id >,<file id >,<page id>,<format id>)我們來運行這個命令前開啟追蹤旗標（trace flag）:

 

      使用參數為3的命令，下面我們設定參數為2：看看輸出的資訊，裡面的資訊結合Sql Server專題一：索引（上）的內容便可理解，下面的內容其實就將222的資料頁內的內容給展現出來了；補充(與下文無關)：SQL Server會將總長超過8K的的資料頁分開存放，單獨放在行溢出(Row-Overflow)頁中.

PAGE: (1:222)

BUFFER:BUF @0x00000001F07A2F40bpage = 0x00000001E1068000          bhash = 0x0000000000000000          bpageno = (1:222)bdbid = 5                           breferences = 0                     bcputicks = 0bsampleCount = 0                    bUse1 = 7102                        bstat = 0x9blog = 0x2121215a                   bnext = 0x0000000000000000          PAGE HEADER:Page @0x00000001E1068000m_pageId = (1:222)                  m_headerVersion = 1                 m_type = 2m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200m_objId (AllocUnitId.idObj) = 34    m_indexId (AllocUnitId.idInd) = 2   Metadata: AllocUnitId = 562949955649536Metadata: PartitionId = 562949955649536                                  Metadata: IndexId = 2Metadata: ObjectId = 34             m_prevPage = (1:221)                m_nextPage = (1:223)pminlen = 10                        m_slotCnt = 137                     m_freeCnt = 38m_freeData = 7880                   m_reservedCnt = 0                   m_lsn = (33:400:6)m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0m_tornBits = -639233702             DB Frag ID = 1                      Allocation StatusGAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULLDIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           DATA:Memory Dump @0x000000000F89A000000000000F89A000:   01020000 00020200 dd000000 01000a00 df000000  ....................000000000F89A014:   01008900 22000000 2600c81e de000000 01000000 省略  ............000000000F89BFF4:   90015c01 1001be00 86006000                    ..\.......`.OFFSET TABLE:Row - Offset                        136 (0x88) - 7826 (0x1e92)          135 (0x87) - 7792 (0x1e70)          134 (0x86) - 7720 (0x1e28)          133 (0x85) - 7636 (0x1dd4)               125 (0x7d) - 7174 (0x1c06)                118 (0x76) - 6802 (0x1a92)          省略  ............           5 (0x5) - 400 (0x190)               4 (0x4) - 348 (0x15c)               3 (0x3) - 272 (0x110)               2 (0x2) - 190 (0xbe)                1 (0x1) - 134 (0x86)                0 (0x0) - 96 (0x60)                 DBCC 執行完畢。

       另一種查看資料庫的方法是：在Mangement Studio中選擇資料，右鍵-->報表-->標準報表中查看：這兩種統計的方法是不同的；這裡是按照區來統計的；上面的sp_spaceused使用的是頁統計方法；報表這種方法內部是調用了DBCC SHOWFILESTATS命令，這個命令直接從GAM和SGAM這樣的系統分配頁面上讀取區的分配資訊，直接算出資料庫檔案裡面有多少區被分配了；因為SQL Server絕大多數是按照區為單位來分配新的空間的，而系統分配頁上的資訊是即時更新的，所以這個統計方法比較可靠；在伺服器負載很高的情況下也可以執行；所以不推薦sp_spaceused的方法，這種方法還不如查看系統試圖(sys.dm_db_partition_stats);

         其實還有一種查看資料空間的方法：DBCC SHOWCONTIG（或者 sys.dm_db_index_physical_stats）這個命令可以統計出你的某張表或者索引用了多少頁面、多少區、甚至頁面的平均資料量。從這些值你可以算出一張表格佔用了多少空間。這個命令的代價恨到，SQL Server會掃描資料庫，而掃描的過程是要加鎖的;例如sys.dm_db_index_physical_stats就有三種掃描方式。越精確掃描越久。1：LIMITED   2 : SAMPLED  3 : DETAILED這3中方式掃描時間依次增加； 下面為DBCC SHOWCONTIG的統計資訊。這種資訊對於統計區的使用方式、片段程度展現比較好。

DBCC SHOWCONTIG 正在掃描 ‘DimAccount‘ 表...表: ‘DimAccount‘ (5575058)；索引 ID: 1，資料庫 ID: 8已執行 TABLE 層級的掃描。- 掃描頁數................................: 2- 掃描區數..............................: 1- 區切換次數..............................: 0- 每個區的平均頁數........................: 2.0- 掃描密度 [最佳計數:實際計數].......: 100.00% [1:1]- 邏輯掃描片段 ..................: 50.00%- 區掃描片段 ..................: 0.00%- 每頁的平均可用位元組數.....................: 2695.0- 平均頁密度(滿).....................: 66.70%DBCC SHOWCONTIG 正在掃描 ‘DimCurrency‘ 表...表: ‘DimCurrency‘ (21575115)；索引 ID: 1，資料庫 ID: 8已執行 TABLE 層級的掃描。- 掃描頁數................................: 1- 掃描區數..............................: 1- 區切換次數..............................: 0- 每個區的平均頁數........................: 1.0- 掃描密度 [最佳計數:實際計數].......: 100.00% [1:1]- 邏輯掃描片段 ..................: 0.00%- 區掃描片段 ..................: 0.00%- 每頁的平均可用位元組數.....................: 3147.0- 平均頁密度(滿).....................: 61.12%DBCC SHOWCONTIG 正在掃描 ‘DimCustomer‘ 表...表: ‘DimCustomer‘ (37575172)；索引 ID: 1，資料庫 ID: 8已執行 TABLE 層級的掃描。- 掃描頁數................................: 1569- 掃描區數..............................: 197- 區切換次數..............................: 1238- 每個區的平均頁數........................: 8.0- 掃描密度 [最佳計數:實際計數].......: 15.90% [197:1239]- 邏輯掃描片段 ..................: 75.27%- 區掃描片段 ..................: 3.05%- 每頁的平均可用位元組數.....................: 3037.7- 平均頁密度(滿).....................: 62.47%  省略……DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

    附加：記錄檔 

        我們知道記錄檔不是按照頁、區來分配和管理的。SQL Server 資料庫引擎在內部將每一物理記錄檔分成多個虛擬日誌單元。虛擬日誌單元沒有固定大小，並且一個物理記錄檔所包含的虛擬日誌單元數十部固定的。管理員不能配置或設定虛擬日誌單元的大小或數量。但 SQL Server 會嘗試控制虛擬日誌單元的數目，把它限制在一個合理的範圍裡。例外情況是，記錄檔每欄位增長一次，會至少增加一個虛擬日誌單元。所以，如果一個記錄檔經曆了多次自動成長，裡面的虛擬日誌單元數目會比正常的記錄檔多很多，這種情況會影響記錄檔管理的效率，以至於影響SQL Server 的啟動。交易記錄是一種迴繞的檔案。另外，這裡有一篇專門介紹SQL Server的日誌的部落格。為交易記錄的工作方式；

   

2、空間監測執行個體

待續……通過指令碼監視tempdb空間

 

SQL Server資料庫空間管理 (1)