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

到此这篇关于sqlserver数据库规模膨胀太大怎么优化的文章就介绍到这了,更多相关sqlserver数据库太大内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家！

最新评论