35、SQL Server自动化维护与监控指南

最新推荐文章于 2025-09-18 18:04:10 发布

职场萌新987

最新推荐文章于 2025-09-18 18:04:10 发布

阅读量35

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 2012管理精华文章标签： SQL Server 自动化维护监控指南

本文链接：https://blog.youkuaiyun.com/go5gopher/article/details/150568921

SQL Server 2012管理精华专栏收录该内容

46 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

SQL Server自动化维护与监控指南

1. 自动化日常维护

在SQL Server中，自动化日常维护是提高效率和确保数据库稳定性的关键。以下是一些常见的自动化维护方法。

1.1 代理账户

代理账户允许你为SQL Server作业步骤指定特定的安全上下文。创建新代理账户的步骤如下：
1. 打开“新建代理账户”对话框的“主体”页面。
2. 选择“添加”按钮，为代理账户分配SQL Server登录名或角色。注意，sysadmin角色的成员可以访问所有代理账户，因此无需在“主体”页面添加他们。
3. 点击“确定”创建新的代理账户。

创建后，代理账户将在“新建作业步骤”对话框的“常规”页面的“运行方式”下拉列表中可用。但只有当你选择“操作系统 (CmdExec)”或“PowerShell”作为步骤类型时，代理账户才会显示在“运行方式”列表中，因为这是我们允许代理账户使用的仅有的两个子系统。

1.2 维护计划

维护计划使你能够在SQL Server实例上设置一组预定义的日常维护任务。你可以使用“维护计划向导”或直接使用“维护计划设计”选项卡来创建维护计划。

维护计划向导 ：提供了一个简单的界面，用于定义和安排执行日常维护的任务。操作步骤如下：
1. 右键单击“管理”节点下的“维护计划”文件夹，从上下文菜单中选择“维护计划向导”。
2. 欢迎屏幕出现后，点击“下一步”显示“选择计划属性”页面。
3. 为维护计划命名并提供简要描述。注意此页面的调度选项：
  - 若选择“为每个任务单独安排计划”，将为每个任务创建一个单独的SQL Server代理作业，每个任务将位于其自己的子计划中。此页面的调度选项将被禁用，你需要在配置每个单独任务时提供单独的计划。
  - 若选择“整个计划使用单个计划”或“不安排计划”，每个任务将包含在使用单个SQL Server代理作业的单个子计划中，每个任务的单独调度选项将被禁用。
4. 点击“下一步”显示“选择维护任务”页面，你可以通过高亮显示每个任务来查看其简要描述，选择要包含在维护计划中的任务，并继续通过向导配置每个任务。
维护计划设计选项卡 ：可用于从头开始构建维护计划，或调整由“维护计划向导”创建的计划。使用向导创建计划后，建议打开“设计”选项卡并重命名子计划，使其更具描述性。操作步骤如下：
1. 若要打开使用向导创建的计划，只需在SQL Server Management Studio对象资源管理器的“管理”节点下的“维护计划”文件夹中双击该维护计划。
2. 若要使用“设计”选项卡创建新的维护计划，右键单击“管理”节点下的“维护计划”文件夹，选择“新建维护计划”。提示时输入新计划的名称，然后点击“确定”。

1.3 维护计划任务

所有这些维护任务都可以使用T - SQL重现。虽然维护计划很方便，但有些人更喜欢使用T - SQL脚本来创建自己的维护作业，以获得更多的控制。例如，在重建索引之前先检查碎片级别。互联网上有许多脚本可以帮助你绕过维护计划的使用，如Ola Hallengren维护的一组优秀的维护脚本（http://ola.hallengren.com/）。

2. 服务器监控

作为数据库管理员，你需要监控SQL Server的性能、登录到服务器的用户以及环境中对象的更改。有效的监控可以让你主动做出明智的决策。SQL Server 2012和Windows Server提供了一些有用的工具来帮助你进行监控，主要包括性能监视器、活动监视器、动态管理视图 (DMVs) 以及分析和跟踪功能。

2.1 服务器基准测试

为了主动监控服务器并识别变化，你需要对服务器进行基准测试。基准测试会随着时间捕获服务器的统计信息，并将这些测量值声明为基准性能测量值。后续的监控就是将新的测量值与该基准进行比较，以检测变化。

选择要收集的内容 ：收集基准指标时，需要考虑多个方面，包括CPU、内存、物理磁盘I/O以及SQL Server活动，如缓冲区管理器统计信息、常规统计信息、SQL统计信息、索引使用统计信息、缺失索引、等待类型统计信息、计划缓存统计信息、查询统计信息和计划等。
捕获和存储基准数据 ：基准测试可能听起来复杂，但你不必在第一天就对所有内容进行基准测试。可以先选择一个类别，捕获并存储该数据，找出最佳的捕获、存储和分析数据的方法，然后再逐步扩展到所有类别。常见的捕获和存储基准数据的方法包括：
- 创建一个数据库来存储所有收集的数据，数据库名称应具有描述性。
- 创建自定义脚本来捕获你想要收集的数据，确保脚本不会对系统性能产生负面影响。
- 创建将平面文件或CSV文件导入数据库的过程，可以使用SSIS、BCP或其他你熟悉的方法。
- 创建分析收集数据的脚本，以便在系统压力大时无需临时开发脚本。
- 在Excel或其他应用程序中创建图表，以图形方式显示收集数据之间的差异，有时图形化显示更容易识别性能指标随时间的细微变化。

2.2 熟悉性能监视器

性能监视器是Microsoft操作系统自带的图形化工具，用于监控系统上的不同活动。它分为三个基本部分：监控工具、数据收集器集和报告。

启动性能监视器 ：有多种方法可以启动性能监视器，你可以从控制面板或开始菜单导航到“管理工具”文件夹，然后选择“性能”；或者从开始菜单选择“运行”，然后输入“perfmon”。启动应用程序后，你将看到性能监视器的概述以及系统摘要，显示带有默认计数器的图表。
使用性能监视器 ：启动应用程序后，切换到“监控工具”下的“性能监视器”开始监控系统。在性能监视器中，有两个重要的选项需要熟悉：
- 添加：允许你增加要监控的计数器数量。点击“添加”按钮后，将弹出“添加计数器”对话框。在该对话框中，你可以确定要添加的计数器的来源、选择要添加计数器的对象、决定是选择对象的所有计数器还是特定计数器，以及在适用的情况下选择要捕获的计数器实例。不要忘记勾选“显示描述”复选框，它会提供所选计数器的简要描述。选择好计数器后，点击“添加”按钮，计数器将显示在图表、直方图或报告中。
- 突出显示 ：当你在图表中同时添加了大量计数器，难以识别每个计数器的值时，“突出显示”选项可以通过在显示中突出显示计数器来帮助你快速确定其值。

2.3 监控CPU

需要注意的是，这些阈值可能因环境而异，你可以将其作为监控环境的起点，而不是绝对的限制。此外，除了性能监视器和DMVs，你还可以使用任务管理器快速确定CPU使用率是否过高，并找出占用CPU的应用程序。启动性能监视器后，添加用于监控CPU的计数器，然后移除与基准或推荐阈值相符的计数器，这样可以减少屏幕上的干扰，只保留需要评估和分析的计数器。

SQL Server自动化维护与监控指南

3. 监控CPU的具体操作与分析

使用性能监视器监控CPU时，我们可以按照以下步骤进行操作，以更有效地识别CPU瓶颈：
1. 启动性能监视器 ：通过控制面板或开始菜单导航到“管理工具”文件夹，选择“性能”；或者从开始菜单选择“运行”，输入“perfmon”。
2. 添加CPU相关计数器 ：启动性能监视器后，切换到“监控工具”下的“性能监视器”，点击“添加”按钮，在“添加计数器”对话框中，选择与CPU相关的计数器，如“处理器: %处理器时间 (总计)”、“处理器: %特权时间”等。
3. 移除符合基准或阈值的计数器 ：添加完计数器后，检查每个计数器的值，移除那些与基准或推荐阈值相符的计数器，使屏幕上只保留需要重点关注的计数器。

以下是一个简单的mermaid流程图，展示了监控CPU的主要步骤：

graph LR
    A[启动性能监视器] --> B[切换到性能监视器]
    B --> C[点击添加按钮]
    C --> D[选择CPU相关计数器]
    D --> E[添加计数器到监控列表]
    E --> F[检查计数器值]
    F --> G{是否符合基准或阈值}
    G -- 是 --> H[移除计数器]
    G -- 否 --> I[保留计数器继续监控]

在监控过程中，我们需要密切关注各个计数器的值。例如，如果“处理器: %处理器时间 (总计)”超过80%持续15分钟以上，可能表示CPU存在瓶颈。此时，我们可以结合其他计数器进行综合分析，如“SQL统计信息: 批请求/秒”、“SQL统计信息: SQL编译/秒”等，以确定问题的根源。

4. 综合监控与决策

在实际的数据库管理中，我们需要综合使用各种监控工具和方法，以全面了解服务器的性能和状态。以下是一个综合监控的流程：
1. 使用性能监视器进行实时监控 ：通过性能监视器监控CPU、内存、磁盘I/O等关键指标，及时发现潜在的性能问题。
2. 利用动态管理视图 (DMVs) 进行深入分析 ：DMVs可以提供更详细的SQL Server内部信息，帮助我们深入了解数据库的运行状态。
3. 结合活动监视器和分析跟踪功能 ：活动监视器可以实时显示当前正在执行的查询和会话信息，分析跟踪功能可以记录和分析数据库的活动，帮助我们找出性能瓶颈和问题。
4. 定期进行服务器基准测试 ：定期对服务器进行基准测试，更新基准数据，以便更好地比较和分析服务器的性能变化。

以下是一个综合监控的mermaid流程图：

graph LR
    A[性能监视器实时监控] --> B[发现潜在问题]
    B --> C[利用DMVs深入分析]
    C --> D[结合活动监视器和跟踪功能]
    D --> E[找出性能瓶颈和问题]
    F[定期服务器基准测试] --> G[更新基准数据]
    G --> H[更好地比较和分析性能变化]
    E --> I[做出决策并采取措施]
    H --> I

在做出决策时，我们需要根据监控结果和实际情况进行综合考虑。例如，如果发现CPU存在瓶颈，可以考虑优化查询、增加硬件资源或调整数据库配置等措施。同时，我们还需要建立有效的预警机制，当关键指标超过阈值时及时发出警报，以便及时采取措施。

5. 总结

通过自动化日常维护和有效的服务器监控，我们可以提高SQL Server的性能和稳定性，减少故障发生的概率。在自动化维护方面，代理账户、维护计划和维护计划任务可以帮助我们简化日常维护工作，提高效率。在服务器监控方面，性能监视器、活动监视器、动态管理视图 (DMVs) 以及分析和跟踪功能等工具可以帮助我们实时了解服务器的性能和状态，及时发现并解决问题。

为了更好地进行数据库管理，我们建议：
1. 定期对服务器进行基准测试，建立准确的基准数据。
2. 综合使用各种监控工具和方法，全面了解服务器的性能和状态。
3. 建立有效的预警机制，及时发现并处理潜在的问题。
4. 根据监控结果和实际情况，灵活调整数据库配置和维护策略。

通过以上方法，我们可以更加主动地管理SQL Server，确保数据库的稳定运行，为业务提供可靠的支持。