SQL Server 2008 R2占用cpu、内存越来越大的两种解决方法

下面是详细讲解 SQL Server 2008 R2 占用 CPU、内存越来越大的两种解决方法的完整攻略。

问题现象及原因

当 SQL Server 2008 R2 数据库运行一段时间后，服务器的 CPU 使用率和内存占用率会越来越高，最终导致服务器崩溃或性能下降，导致无法正常使用。这是由于 SQL Server 2008 R2 常驻内存的特性引起的，它会一直占用系统资源直到达到上限。此外，当 SQL Server 2008 R2 执行大量查询或读写操作时，也会占用较多的 CPU 资源，导致服务器出现性能问题。

解决方法一：修改 SQL Server 配置

步骤 1：修改最大内存限制

1. 打开 SQL Server Management Studio。
2. 右键点击服务器名称，选择“属性”。
3. 选择“内存”选项卡。
4. 在“最大服务器内存（MB）”一项中，修改最大内存限制，建议设置为服务器可用内存的70%~80%。
5. 点击“应用”保存设置，重启 SQL Server 服务使其生效。

步骤 2：禁用资源调度器

1. 在 SQL Server Management Studio 中执行以下 SQL 语句：

USE master;
GO
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'max worker threads', 0;
GO
RECONFIGURE;
GO

1. 在 SQL Server 配置管理器中，选择“SQL Server services”并找到相应的实例。
2. 右键点击该实例并选择“属性”。
3. 在“进程器”选项卡中，取消勾选“启用自适应服务器内存”和“启用资源调度器”选项。
4. 点击“应用”保存设置，重启 SQL Server 服务使其生效。

解决方法二：优化 SQL Server 查询

步骤 1：使用索引

在数据库中添加索引可以加速查询，减少资源占用。例如，在查询语句中加入 WHERE 子句或者 ORDER BY 子句时，SQL Server 可以更快地查找相应的记录，从而减少 CPU 和内存的使用。

步骤 2：使用分页查询

对于大量数据查询，可以使用分页查询来减少对 CPU 和内存的占用。例如：

SELECT * 
FROM TableName 
ORDER BY ColumnName 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

这将返回第一页的数据，偏移量为 0，返回 10 行记录。

示例说明

使用索引

例如，有以下表结构：

CREATE TABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [age] [int] NULL,
    [dept] [nvarchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

对于查询“年龄大于 30 的员工”，如果不加索引，查询速度将会很慢：

SELECT * FROM Employee WHERE age > 30;

使用索引可以较大地提升查询速度：

CREATE NONCLUSTERED INDEX idx_age ON Employee(age);

SELECT * FROM Employee WHERE age > 30;

使用分页查询

以下是一张名为“Orders”的表，它包含了成千上万的订单记录：

CREATE TABLE Orders (
    order_id        INT IDENTITY(1,1) PRIMARY KEY,
    customer_id     INT,
    order_date      DATE,
    order_total     MONEY
    -- ...
)

如果要返回所有的订单记录，这可能会导致 CPU 和内存的资源消耗过大：

SELECT * FROM Orders;

使用分页查询可以大大减少对 CPU 和内存的占用：

SELECT * FROM Orders ORDER BY order_date OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

这将只返回前 10 条记录，而不是全部记录。

总结

以上是针对 SQL Server 2008 R2 占用 CPU、内存越来越大的两种解决方法的详细攻略，可以通过修改 SQL Server 配置或优化 SQL Server 查询来减少对系统资源的占用，提高系统性能和稳定性。