65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 SQL Server 治理和控制您的服务器资源

Christian Luketa Kanyinda
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.80/5 (2投票s)

2017年5月18日

CPOL

15分钟阅读

viewsIcon

11179

downloadIcon

2

本文将允许您治理资源并对其进行控制以优化您的服务器。

引言

早上 10 点，一个充满压力的早晨，部署管理应用程序 5 周后。从该应用程序访问数据库变得越来越慢且麻烦。曾经耗时很短的简单查询，现在却需要花费很长时间才能运行。用户四处呼叫，我们担心系统会崩溃。所有 IT 人员都在密切关注，四处奔走，在 Google 上冲浪，翻阅书籍，审查算法以缓解这种情况。

开发人员、IT 网络管理员和我们数据库管理员互相指责。我们不明白发生了什么。DBA 认为应用程序开始表现不佳，算法可能实现不当，或者存储在数据库中的数据产生了影响。反过来，开发人员将错误推给了网络管理员，他们认为网络不够优化，而后者则对自己所做的工作充满信心。

每个人都陷入了完全的恐慌，奇怪的是，我们 DBA 认为数据库应该依赖于应用程序和连接的质量，这让我们与众不同。但现实是，我们 DBA 在不知不觉中是这一切发生的根源。但是怎么做到的呢？您将找出答案。

数据库的优化取决于我们 DBA，因为所有连接到数据库的应用程序（主管理应用程序、报告、维护请求和计划任务）都执行了 DBA 预先创建的存储过程或存储在数据库中的系统存储过程。因此，98%，我们是所有这一切发生的罪魁祸首。

关系数据库管理系统 (RDBMS) 就像一个记忆库：它包含对公司来说非常重要，甚至至关重要的数据。公司利益相关者能够以一致的响应时间高效且快速地访问数据的能力至关重要，并且这是生产数据库管理员的首要职责。对此没有任何借口！

Rudi BRUCHEZ，《优化 SQL SERVER》，DUNOD 出版社，巴黎 2008 年，第 10 页

然而，数据库的优化仍然是数据库管理员的全部职责，处于开发阶段的数据库与处于生产阶段的数据库完全不同。

数据库管理员需要做出必要的安排，以确保服务器的优化，只要他们

  1. 从应用程序开发阶段开始，通过更优化的查询设计和数据控制方法支持开发人员。（因为有时开发人员常常将数据库视为一个可以随时随地以任何方式插入任何东西的袋子。他唯一关心的是应用程序是否正常运行，他甚至愿意创建一个“sex”表来克服任何阻碍他快速完成任务的困难。哈哈！！）。这就是为什么 DBA 有责任参与应用程序的开发以纠正问题。
  2. 在应用程序部署和生产过程中，实施措施以优化数据库，以确保尽可能高的优化。

当然，信息系统的缓慢并不一定总是与数据库服务器的配置有关，还有许多其他因素需要考虑，包括：

  • 连接的质量；
  • 源代码的质量；
  • 计算机的质量和性能等。

我曾对以前发表的关于此主题的文章进行了广泛研究，并决定在我的水平上发表这篇文章，以分享我所经历的这种经验，优化只涉及服务器的配置。

我认为这将对数据库管理有用，但这并不总是优化我们服务器的唯一真正解决方案。但这种经验可以帮助您提升您的应用程序。我也承认这篇文章并不完美，因此我保持谦虚并接受您所有的评论，考虑到它将给我的重要性，我将获得更多的动力。

资源管理器

良好的记忆是我们每个人的目标。我们总是喜欢轻易地记住过去的事件、以前的信息、操作的细节、一个人的名字，并且能在创纪录的时间内完成。我们每个人都不喜欢忘记某事，这会让人不适。

想象一下，您正在听一首歌曲，但记不起歌名，您被迫思考找到它的名字，耗时越长，您就越烦恼。想象一下，您应该告诉您的经理一个您不再记得的服务器密码，您当然会感到更加紧张，因为您的经理已经站了 2 分钟，而您仍然在想该告诉他什么。这总是令人困扰！

正如关系数据库管理系统 (RDBMS) 就像一个记忆库：它包含对公司来说非常重要，甚至至关重要的数据。公司利益相关者能够以一致的响应时间高效且快速地访问数据的能力至关重要，并且这是数据库管理员的首要职责！

定义

资源管理器是 SQL Server 2008 引入的一项技术，用于通过指定传入请求对资源消耗的限制来管理工作负载和 SQL Server 资源。从资源管理器的角度来看，工作负载是一组大小相似的查询，它们可以或应该被视为一个整体。虽然不是必需的，但工作负载资源的使用模式越一致，使用资源管理器就越有益。资源限制可以实时重新配置，对正在运行的工作负载影响微乎其微。

https://technet.microsoft.com/fr-fr/library/bb895232(v=sql.105).aspx

资源管理器仅在 SQL Server 的 Enterprise、Developer 和 Evaluation 版本中可用。

数据库管理员必须根据企业需求设置资源管理器，这将允许限制分配给在生产数据库上执行的某些类型请求的资源。

我们可以有来自四面八方的多个查询连接到数据库。这些查询可以分为 3 类。

  • 主应用程序

    每个数据库都有一个为其创建的应用程序。该应用程序在一天中的不同时间运行多个请求，具体取决于用户是否登录。因此，这些请求必须是众多请求中的优先事项，因为此类系统最关心的是应用程序执行所有请求的速度和灵活性（显示列表、删除或修改信息等）。

  • 服务器维护

    偶尔，会计划服务器维护计划，通过实施计划任务来执行包含繁重维护、服务器优化等内容的脚本。这些查询通常会独自占用大量资源来运行。这使得其他查询（主要是来自应用程序的查询）执行缓慢。

    另一方面，有一些服务器监控应用程序（例如 KANKURU），它们必须每分钟向您报告服务器的总体状态。这是为了帮助您检测和预防故障或问题。确实，这些监控应用程序每分钟都会运行数百个请求，以期每分钟向您提供服务器的相关信息。

    这些查询不构成一定的优先级视图，因为它们不是从应用程序执行的。因此，资源分配必须合理，以免阻止或加重来自应用程序的请求的执行。

    因此，必须将最多的资源分配给这些请求，以确保应用程序的正确使用和优化。所以，在应用程序繁琐方面没有任何借口。

  • 报告

    这些查询已知会消耗大量资源。例如，各种用户可以随时访问报告以概览事务操作。

    因此，在我们的案例中，考虑到优先优化主应用程序，可以容忍报告缓慢而损害应用程序。报告的特点是其繁重性，通常会占用所有资源来在创纪录的时间内执行，并最大程度地减轻其繁重性。

话虽如此，数据库管理员必须能够设置资源管理器来解决一些困难，例如：

  • 服务器上的查询资源消耗过大：消耗大量资源的查询可能会占用服务器上的大部分资源。
  • 不可预测的工作负载执行：在同一服务器上运行的并发应用程序具有不同大小和类型的不同工作负载。例如，可能是两个数据仓库应用程序，或 OLTP 应用程序和数据仓库的组合。这些应用程序彼此之间不是隔离的，由此产生的资源竞争会导致工作负载执行不可预测。
  • 定义工作负载的优先级级别：允许一个工作负载比另一个工作负载处理得更快，或者在资源冲突的情况下保证其执行。资源管理器允许您为每个工作负载分配一个相对重要性参数。

对我们来说，这就是我们无法实现的全部。我之前向您承诺过，我将告诉您应用程序为何变得繁琐。

应用程序是根据此架构部署的。

文件服务器

应用程序存储大量文件。为了释放和减轻数据库的负担，我们必须创建一个文件服务器，应用程序将所有文件存储在其中，并通过数据库中的相应链接引用这些文件。这样，数据库的增长就可以得到管理。

应用程序服务器

这是应用程序部署的服务器。

数据库服务器

这是数据库部署的服务器。

报告服务器

这是报告部署的服务器。

因此，我们将 3 类用户分组。

  • 使用应用程序的用户，他们根据各自的分配执行多个事务。有大量用户部署在许多站点以运行应用程序。
  • 那些查看报告的人。有大量用户随时登录查看所有操作的报告。由于报告的重要性和范围，这些用户可以随时多次执行此操作。此外，还有几种类型的报告。您可以想象数百个繁重的报告查询会执行并连接到数据库。
  • 此外，数据库管理员会随时发起维护请求，并实现后台运行的计划任务。他们使用大量部署的监控应用程序，这些应用程序运行在多台机器上。您明白，您永远无法由一名 DBA 来管理这样一个数据库。因此，每小时都有少数人随时待命，以保证系统的高可用性。

这一切都证明了数据库服务器每两分钟所承受的压力有多大。结果，应用程序变得缓慢而卡顿。起初，一切都正常，但随着我们不断发展，数据填充了数据库，系统也越来越糟。

许多人可能会告诉我，托管 DBMS 的服务器不够强大，无论是内存还是处理器，但正如您所知，即使一年后，您也会看到更糟糕的情况。

您明白了我们对这种缓慢负有多大的责任。缓慢可能并不总是与上述原因有关。但是本文中提到的建议可以帮助您优化系统，因为在我们的案例中，我们应用了这些优化方法，突然之间，就像变魔术一样，应用程序比以往任何时候都更加优化。关键是要优化应用程序，因为它是数据库存在的唯一重要元素。因此，优先事项是优化主应用程序。

实现

使用 SQL SERVER 实现资源管理器需要 4 个步骤。

  1. 创建 2 个连接帐户，对应连接到数据服务器的 3 种请求源。
  2. 在控制数据上创建用户，并将它们映射到先前创建的连接帐户并分配权限。
  3. 为先前创建的连接帐户汇集资源。
  4. 创建工作负载组并映射到相对资源池。
  5. 创建分类函数和测试函数。
  6. 指示资源管理器使用分类函数。
  7. 激活资源管理器。

我们将使用一个测试数据库：db_Gouvernor。

步骤 1：创建 2 个连接帐户（登录名）。

如上所述，每类请求都有其登录帐户。由于应用程序拥有更高的特权，我们将仅为其他类型的查询（维护和报告）创建这两个连接。

USE [master]; 

--  Login for Maintainance requests--

CREATE LOGIN Login_Maintenance
WITH
PASSWORD=N'%123456%',
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
 
-- Login for Reporting  requests--

CREATE LOGIN Login_Reporting
WITH
PASSWORD=N'%123456%',
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO

您可能会问，为什么不为应用程序用户创建一个帐户或登录名？我们选择使用默认帐户。

步骤 2：在要控制的数据库中创建 2 个用户。

这会自动将这些用户映射到各自的登录名。我们假设我们的控制数据库已存在，名为：db_Gouvernor。

USE [master]; 
GO

-- User for Maintainance requests--
 
CREATE USER User_Maintenance FROM LOGIN Login_Maintenance;
GO
GRANT EXECUTE ON DATABASE::db_Gouvernor TO User_Maintenance;
GO
sp_defaultdb 'User_Maintenance','db_Gouvernor'
GO

-- User for Reporting requests

CREATE USER User_Reporting FROM LOGIN Login_Reporting;
GO
GRANT EXECUTE ON DATABASE::db_Gouvernor TO User_Reporting;
GO
sp_defaultdb 'User_Reporting','db_Gouvernor'
GO

步骤 3：创建 2 个资源池。

资源池允许您设置限制或强加对服务器资源使用的约束。涉及的资源是处理器和内存。目前无法执行相同的磁盘子系统输入/输出控制。资源池和工作负载组的大小是设置资源管理器过程的重要组成部分。这里的挑战在于根据业务需求确定要分配的资源。当然，可以事后修改资源池或工作负载组的任何参数。如我们所见，资源池有两个不同的部分，一部分与其他池共享，另一部分是为其保留的固定部分。MIN 和 MAX 值在这里很重要，因为它们一方面可以计算池的共享资源，另一方面可以计算同一个池可以使用的有效资源的最大值。

David BARBARIN，《SQL SERVER 2008 资源管理器简介》，Developpez.com，2009 年，第 5 页。

USE [master]; 
GO

-- Resssource Pool for Maintainance Requests 
 
CREATE RESOURCE POOL Pool_Maintenance
WITH
(
    min_cpu_percent=0, 
    max_cpu_percent=20, 
    min_memory_percent=0, 
    max_memory_percent=40 

)
GO

-- Resssource Pool for Reporting Requests 

CREATE RESOURCE POOL  Pool_Reporting
WITH
(
   min_cpu_percent=0,
   max_cpu_percent=20,
   min_memory_percent=0,
   max_memory_percent=40
) 
GO

-- Update Default Pool for Application Requests

ALTER RESOURCE POOL [default]
WITH
(
    min_cpu_percent=50,
    max_cpu_percent=100,
    min_memory_percent=50,
    max_memory_percent=100
)

GO

MIN_CPU_PERCENT：此选项允许在 CPU 争用时，为池中活动的查询保证最少百分比的资源。理解这一点很重要。资源管理器会在所有活动线程之间平衡池中的可用资源。MIN_CPU_PERCENT 参数的值可以在 0 到 100 之间，但所有资源管理器池的值的总和不能超过 100。

MAX_CPU_PERCENT：当存在 CPU 争用时，此参数可确保池中的所有活动线程不会消耗超过某个阈值的资源。此参数不能设置为 0。MAX_CPU_PERCENT 参数的值必须大于或等于 MIN_CPU_PERCENT 参数的值。

MIN_MEMORY_PERCENT：此参数在所有情况下都确保为池保证一定百分比的可用内存资源。换句话说，即使没有内存争用，也会为该池保留一定量的内存，即使该池中没有活动。设置不当会导致不必要地保留内存资源，而其他池可能需要它。与 MIN_CPU_PERCENT 参数一样，值可以在 0 到 100 之间。所有池的此参数值总和不能超过 100%。

MAX_MEMORY_PERCENT：此参数确保池中的所有活动线程消耗的内存资源不会超过配置的阈值。值可以在 0 到 100 之间。此参数的值必须大于或等于 MIN_MEMORY_PERCENT 参数的值。在此处指定仅涉及执行缓存的内存很重要。

步骤 4：创建 2 个工作负载组。

工作负载组是一个逻辑容器，用于收集具有相同特征的传入请求。工作负载组的好处是双重的：它们允许为同一组的所有请求建立相同的资源分配策略，并分析它们的消耗。

默认存在两个工作组：默认组和内部组。默认组汇集了所有没有排名标准或缺少请求路由组的传入请求。通常，未能对请求进行分类会导致请求自动转移到默认组。每个工作负载组都与一个重要性概念相关联，该概念可以设置为 LOW、MEDIUM 或 HIGH。重要性定义了相对于同一资源池中的其他组，工作负载组可用的 CPU 资源比例。这种重要性的概念不同于我们所知的经典优先级概念。事实上，调度程序将根据此比例分发工作负载组的不同执行任务。每个重要性对应一个数值（LOW 为 1，MEDIUM 为 3，HIGH 为 9）。

David BARBARIN，《SQL SERVER 2008 资源管理器简介》，Developpez.com，2009 年，第 10 页。

USE [master]; 
GO

-- Maintainance Requests WorkLoad Group 
 
CREATE WORKLOAD GROUP Group_Maintenance
USING Pool_Maintenance;
GO

ALTER WORKLOAD GROUP Group_Maintenance
WITH
(
    importance =LOW 

)
GO

-- Maintainance Requests WorkLoad Group   

CREATE WORKLOAD GROUP Group_Maintenance
USING Pool_Maintenance;
GO

ALTER WORKLOAD GROUP Group_Maintenance 
WITH 
( 
   importance =LOW 
) 
 
GO

-- Application Requests WorkLoad Group  

ALTER WORKLOAD GROUP [default]
WITH
(
    
    importance =LOW

)

GO

步骤 5：创建分类函数。

它是资源管理器使用的特定用户函数，可以创建规则来对传入的请求进行分类，然后将它们路由到相应的工作负载组。还有一些内部规则由 SQL 引擎使用，它们对内部工作负载组执行相同的操作。

USE [master]; 
GO

CREATE FUNCTION dbo.Function_Classification()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
RETURN
(
    SELECT CASE SUSER_NAME()
    WHEN 'User_Maintenance' THEN 'Group_Maintenance'
    WHEN 'User_Reporting' THEN 'Group_Reporting'
    ELSE 'Default'
    END
)
END

步骤 6：指示资源管理器使用分类函数。

USE [master]; 
GO
   
ALTER RESOURCE GOVERNOR
WITH
(
  CLASSIFIER_FUNCTION = dbo.Function_Classification
);

步骤 7：启用资源管理器。

USE [master]; 
GO
   
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

一些脚本

1. 检查此负载组中排队的请求数量。

SELECT name, total_request_count, total_queued_request_count 
FROM sys.dm_resource_governor_workload_groups

2. 按会话验证负载组。

EXECUTE AS LOGIN ='sa'
SELECT SUSER_NAME() AS session_name,dbo.Function_Classification()AS groupe_name;
       
EXECUTE AS LOGIN ='Login_Maintenance'
SELECT SUSER_NAME() AS session_name,dbo.Function_Classification()AS groupe_name;
       
EXECUTE AS LOGIN ='Login_Reporting'
SELECT SUSER_NAME() AS session_name,dbo.Function_Classification()AS groupe_name;

3. 将请求分发到不同的池和工作负载组。

 SELECT
        p.name AS pool_name,
        g.name AS group_name,
        r.session_id,
        t.text AS sql_txt
        FROM sys.resource_governor_resource_pools AS p
        INNER JOIN sys.resource_governor_workload_groups AS g
        ON p.pool_id = g.pool_id
        INNER JOIN sys.dm_exec_requests AS r
        ON r.group_id = g.group_id
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
        WHERE p.name <> 'internal'
        ORDER BY p.name, g.name;

4. 查看资源管理器配置。

SELECT
        p.name AS pool_name,
        p.min_cpu_percent AS pool_min_cpu_percent,
        p.max_cpu_percent AS pool_max_cpu_percent,
        g.name AS group_name,
        g.importance AS group_importance
        FROM sys.resource_governor_resource_pools AS p
        INNER JOIN sys.resource_governor_workload_groups AS g
        ON p.pool_id = g.pool_id;
        GO


SELECT * FROM sys.resource_governor_configuration;

5. 排队的请求数量。

SELECT
        name,
        total_request_count,
        total_queued_request_count
        FROM sys.dm_resource_governor_workload_groups
        GO

6. 禁用资源管理器。

ALTER RESOURCE GOVERNOR DISABLE;
GO
© . All rights reserved.