sqlserver數據庫規(guī)模膨脹太大怎么優(yōu)化

更新時間：2024年02月17日 16:16:05 投稿：yin

數據庫規(guī)模膨脹是SQLServer數據庫運維中常見的問題之一,本文介紹了一些緩解膨脹問題的方法,包括增加內存、數據壓縮、數據庫分區(qū)等,在實踐過程中,應根據具體情況,采取適當的方法,以達到最優(yōu)的緩解膨脹問題的效果

1. 簡介

隨著SQLServer數據庫中數據量的增長和應用系統(tǒng)使用時間的延長，其數據庫規(guī)模也會不斷膨脹。這種情況下，應該如何處理呢？下面就為大家介紹一些常見的處理方式。

2. 增加內存

2.1 增加物理內存

增加SQLServer數據庫服務器的物理內存可以有效地提高數據庫服務器的性能。因為，將數據庫所需的數據和索引都緩存在內存中，可以減少磁盤I/O操作的次數，從而提高速度，緩解數據庫規(guī)模膨脹的問題。

具體實現方法如下：

ALTER SERVER CONFIGURATION SET MEMORY=8192MB;

說明：

“ALTER SERVER CONFIGURATION SET MEMORY=8192MB”表示增加物理內存到8GB，具體根據服務器實際情況而定。

2.2 增加虛擬內存

增加SQLServer數據庫服務器的虛擬內存也可以提高性能。因為，當內存不足時，SQLServer將使用硬盤上的虛擬內存來臨時存儲數據和索引，這雖然會導致訪問速度變慢，但也可以減少磁盤I/O操作的次數，從而提高速度，緩解數據庫規(guī)模膨脹的問題。

具體實現方法如下：

ALTER SERVER CONFIGURATION SET C:\\PAGEFILE.SYS;

說明：

“ALTER SERVER CONFIGURATION SET C:\\PAGEFILE.SYS”表示增加虛擬內存到C盤根目錄下的PAGEFILE.SYS文件中，具體根據服務器實際情況而定。

3. 數據壓縮

3.1 壓縮表格

在SQLServer數據庫參數設置中，可以開啟數據壓縮功能以減少磁盤使用量。對于一些已經存在多年的歷史數據，可以考慮對其進行數據壓縮，以緩解數據庫規(guī)模膨脹的問題。

具體實現方法如下：

SELECT name, type_desc, total_pages, used_pages FROM sys.allocation_units WHERE type IN (1,3) ORDER BY total_pages DESC

說明：

“SELECT name, type_desc, total_pages, used_pages FROM sys.allocation_units WHERE type IN (1,3) ORDER BY total_pages DESC”表示按照總頁數從大到小排序輸出已經壓縮的表格信息。

3.2 壓縮數據庫

對于整個數據庫的數據壓縮，可以使用SQLServer數據庫自帶的壓縮方式將數據庫文件中的部分數據壓縮起來。這樣可以節(jié)省磁盤空間，并可以緩解數據庫規(guī)模膨脹的問題。

具體實現方法如下：

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 110;
GO
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM;
GO
USE [AdventureWorks]
GO
ALTER INDEX ALL ON [Person].[Address] REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

說明：

“ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 110;”表示將AdventureWorks數據庫的兼容性級別設置為SQL Server 2012，具體根據數據庫實際情況而定；

“ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM;”表示啟用SQL Server提供的頁面校驗檢查；

“ALTER INDEX ALL ON [Person].[Address] REBUILD WITH (DATA_COMPRESSION = PAGE);”表示對表格Person.Address中的所有索引執(zhí)行重建操作，并采用頁壓縮方式，可以將關鍵數據壓縮，從而減少磁盤空間占用。

4. 數據庫分區(qū)

4.1 橫向分區(qū)

SQLServer支持分割已有表格的數據為多個物理表格的技術，一般稱為數據橫向拆分。當數據庫規(guī)模膨脹到一定程度后，可以采用橫向分區(qū)的方式來進行數據管理，以保證數據庫的高效穩(wěn)定運行。

具體實現方法如下：

CREATE TABLE Archive_2012 (LIKE Sales.SalesOrderDetail);
GO
ALTER TABLE Archive_2012 ADD CONSTRAINT PK_Archive_2012 PRIMARY KEY (SalesOrderID, SalesOrderDetailID)
GO
CREATE CLUSTERED INDEX CIX_SalesOrderID ON Archive_2012 (SalesOrderID)
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderDetailID ON Archive_2012 (SalesOrderDetailID)
GO
ALTER PARTITION SCHEME [psArchive] NEXT USED [Archive2013];
GO

說明：

“CREATE TABLE Archive_2012 (LIKE Sales.SalesOrderDetail);”表示在AdventureWorks中創(chuàng)建一個名為Archive_2012的表，該表的結構與Sales.SalesOrderDetail相同；

“ALTER TABLE Archive_2012 ADD CONSTRAINT PK_Archive_2012 PRIMARY KEY(SalesOrderID, SalesOrderDetailID)”表示向一個已經存在的表中添加一個主鍵索引；

“CREATE CLUSTERED INDEX CIX_SalesOrderID ON Archive_2012 (SalesOrderID)”表示為序列SalesOrderID添加聚簇索引；

“CREATE NONCLUSTERED INDEX IX_SalesOrderDetailID ON Archive_2012 (SalesOrderDetailID)”表示為列SalesOrderDetailID添加非聚簇索引；

“ALTER PARTITION SCHEME [psArchive] NEXT USED [Archive2013]”表示將分區(qū)Schema的下一個使用分區(qū)修改為名為Archive2013的新分區(qū)。

4.2 縱向分區(qū)

SQLServer還支持將其列屬性分裂為多個子集的功能，一般稱為垂直分區(qū)。當數據庫規(guī)模膨脹到一定程度后，可以采用垂直分區(qū)的方式來進行數據管理，以保證數據庫的高效穩(wěn)定運行。

具體實現方法如下：

-- 對表格Person.Person進行垂直拆分
ALTER TABLE Person.Person 
DROP COLUMN Suffix, Title;
GO
CREATE TABLE Person.Name 
( 
    BusinessEntityID INT PRIMARY KEY, 
    FirstName VARCHAR(50), 
    LastName VARCHAR(50), 
    MiddleName VARCHAR(50) 
) 
GO
CREATE TABLE Person.Title 
( 
    BusinessEntityID INT PRIMARY KEY, 
    Title VARCHAR(50), 
    Suffix VARCHAR(10) 
);

說明：

“ALTER TABLE Person.Person DROP COLUMN Suffix, Title;”表示將表格Person.Person中的列Suffix和Title刪除；

“CREATE TABLE Person.Name (BusinessEntityID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), MiddleName VARCHAR(50));”表示創(chuàng)建了一個名為Person.Name的表，并為其每一行添加了BusinessEntityID、FirstName、LastName和MiddleName四個字段，其中主鍵為BusinessEntityID；

“CREATE TABLE Person.Title (BusinessEntityID INT PRIMARY KEY, Title VARCHAR(50), Suffix VARCHAR(10));”表示創(chuàng)建了一個名為Person.Title的表，并為其每一行添加了BusinessEntityID、Title和Suffix三個字段，其中主鍵為BusinessEntityID。