处理SQLServer数据库规模膨胀的建议

1. 简介

随着SQLServer数据库中数据量的增长和应用系统使用时间的延长，其数据库规模也会不断膨胀。这种情况下，应该如何处理呢？下面就为大家介绍一些常见的处理方式。

2. 增加内存

2.1 增加物理内存

增加SQLServer数据库服务器的物理内存可以有效地提高数据库服务器的性能。因为，将数据库所需的数据和索引都缓存在内存中，可以减少磁盘I/O操作的次数，从而提高速度，缓解数据库规模膨胀的问题。

具体实现方法如下：

ALTER SERVER CONFIGURATION SET MEMORY=8192MB;

说明：

“ALTER SERVER CONFIGURATION SET MEMORY=8192MB”表示增加物理内存到8GB，具体根据服务器实际情况而定。

2.2 增加虚拟内存

增加SQLServer数据库服务器的虚拟内存也可以提高性能。因为，当内存不足时，SQLServer将使用硬盘上的虚拟内存来临时存储数据和索引，这虽然会导致访问速度变慢，但也可以减少磁盘I/O操作的次数，从而提高速度，缓解数据库规模膨胀的问题。

具体实现方法如下：

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

说明：

“ALTER SERVER CONFIGURATION SET C:\\PAGEFILE.SYS”表示增加虚拟内存到C盘根目录下的PAGEFILE.SYS文件中，具体根据服务器实际情况而定。

3. 数据压缩

3.1 压缩表格

在SQLServer数据库参数设置中，可以开启数据压缩功能以减少磁盘使用量。对于一些已经存在多年的历史数据，可以考虑对其进行数据压缩，以缓解数据库规模膨胀的问题。

具体实现方法如下：

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数据库自带的压缩方式将数据库文件中的部分数据压缩起来。这样可以节省磁盘空间，并可以缓解数据库规模膨胀的问题。

具体实现方法如下：

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中的所有索引执行重建操作，并采用页压缩方式，可以将关键数据压缩，从而减少磁盘空间占用。

4. 数据库分区

4.1 横向分区

SQLServer支持分割已有表格的数据为多个物理表格的技术，一般称为数据横向拆分。当数据库规模膨胀到一定程度后，可以采用横向分区的方式来进行数据管理，以保证数据库的高效稳定运行。

具体实现方法如下：

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中创建一个名为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]”表示将分区Schema的下一个使用分区修改为名为Archive2013的新分区。

4.2 纵向分区

SQLServer还支持将其列属性分裂为多个子集的功能，一般称为垂直分区。当数据库规模膨胀到一定程度后，可以采用垂直分区的方式来进行数据管理，以保证数据库的高效稳定运行。

具体实现方法如下：

-- 对表格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));”表示创建了一个名为Person.Name的表，并为其每一行添加了BusinessEntityID、FirstName、LastName和MiddleName四个字段，其中主键为BusinessEntityID；

“CREATE TABLE Person.Title (BusinessEntityID INT PRIMARY KEY, Title VARCHAR(50), Suffix VARCHAR(10));”表示创建了一个名为Person.Title的表，并为其每一行添加了BusinessEntityID、Title和Suffix三个字段，其中主键为BusinessEntityID。

5. 总结

数据库规模膨胀是SQLServer数据库运维中常见的问题之一。本文介绍了一些缓解膨胀问题的方法，包括增加内存、数据压缩、数据库分区等。在实践过程中，应根据具体情况，采取适当的方法，以达到最优的缓解膨胀问题的效果。