Przeczytaj w języku angielskim

Udostępnij za pośrednictwem

Użyj Resource Governor, aby ograniczyć zużycie CPU przez kompresję kopii zapasowej (Transact-SQL)

  • Artykuł

Dotyczy:programu SQL Server

Domyślnie tworzenie kopii zapasowej przy użyciu kompresji znacznie zwiększa użycie procesora CPU, a dodatkowe użycie procesora CPU przez proces kompresji może mieć negatywny wpływ na operacje współbieżne. W związku z tym możesz utworzyć skompresowaną kopię zapasową o niskim priorytecie w sesji, której użycie procesora jest ograniczone przez Zarządcę zasobów, gdy wystąpi konflikt w użyciu CPU. W tym temacie przedstawiono scenariusz, który klasyfikuje sesje określonego użytkownika programu SQL Server, mapując je na grupę obciążeń Zarządca zasobów, która ogranicza użycie procesora CPU w takich przypadkach.

Ważne

W danym scenariuszu zarządcy zasobów klasyfikacja sesji może być oparta na nazwie użytkownika, nazwie aplikacji lub innych elementach, które mogą rozróżniać połączenie. Aby uzyskać więcej informacji, zobacz Funkcja klasyfikatora zarządcy zasobów i Grupa obciążenia zarządcy zasobów.

Ten temat zawiera następujący zestaw scenariuszy, które są prezentowane w sekwencji:

  1. konfigurowanie logowania i użytkownika na potrzeby operacji Low-Priority

  2. Konfigurowanie zarządcy zasobów w celu ograniczenia użycia procesora CPU

  3. Weryfikacja klasyfikacji bieżącej sesji (Transact-SQL)

  4. Kompresowanie kopii zapasowych przy użyciu sesji z ograniczonym użyciem CPU

Konfigurowanie logowania i użytkownika na potrzeby operacji Low-Priority

Scenariusz w tym temacie wymaga logowania i użytkownika SQL Server o niskim priorytecie. Nazwa użytkownika będzie używana do klasyfikowania sesji uruchomionych podczas logowania i kierowania ich do grupy obciążenia Zarządca zasobów, która ogranicza użycie procesora CPU.

W poniższej procedurze opisano kroki konfigurowania logowania i użytkownika w tym celu, a następnie przykład Transact-SQL "Przykład A: Konfigurowanie logowania i użytkownika (Transact-SQL)".

Aby skonfigurować użytkownika logowania i bazy danych na potrzeby klasyfikowania sesji

  1. Utwórz identyfikator logowania programu SQL Server do tworzenia skompresowanych kopii zapasowych o niskim priorytecie.

    Aby utworzyć login

  2. Opcjonalnie przyznaj uprawnienia VIEW SERVER STATE (WYŚWIETL STAN SERWERA) do tego konta logowania.

    Aby uzyskać więcej informacji, zobacz GRANT Database Principal Permissions (Transact-SQL).

  3. Utwórz użytkownika programu SQL Server dla tego loginu.

    Aby utworzyć użytkownika

  4. Aby umożliwić sesje logowania i użytkownika do tworzenia kopii zapasowej danej bazy danych, dodaj użytkownika do roli db_backupoperator tej bazy danych. Wykonaj tę czynność dla każdej bazy danych, którą ten użytkownik będzie tworzył kopię zapasową. Opcjonalnie dodaj użytkownika do innych stałych ról bazy danych.

    Aby dodać użytkownika do stałej roli bazy danych

    Aby uzyskać więcej informacji, zobacz GRANT Database Principal Permissions (Transact-SQL).

Przykład A: Konfigurowanie logowania i użytkownika (Transact-SQL)

Poniższy przykład jest istotny tylko wtedy, gdy zdecydujesz się utworzyć nowe dane logowania programu SQL Server i użytkownika do tworzenia kopii zapasowych o niskim priorytcie. Alternatywnie możesz użyć istniejącego identyfikatora logowania i użytkownika, jeśli istnieje odpowiedni.

Ważne

W poniższym przykładzie użyto przykładowej nazwy logowania i nazwy użytkownika domain_name\MAX_CPU. Zastąp je nazwą logowania do programu SQL Server oraz użytkownika, którego planujesz użyć podczas tworzenia skompresowanych kopii zapasowych o niskim priorytecie.

W tym przykładzie utworzono identyfikator logowania dla konta domain_name\MAX_CPU Windows, a następnie nadano logowaniu uprawnienia VIEW SERVER STATE. To uprawnienie umożliwia zweryfikowanie klasyfikacji sesji logowania przez Zarządcę Zasobów. Następnie przykład tworzy użytkownika dla domain_name\MAX_CPU i dodaje go do stałej roli bazy danych db_backupoperator dla przykładowej bazy danych AdventureWorks2022. Ta nazwa użytkownika będzie używana przez funkcję klasyfikatora zarządcy zasobów.

SQL 
-- Create a SQL Server login for low-priority operations  
USE master;  
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;  
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];  
GO  
-- Create a SQL Server user in AdventureWorks2022 for this login  
USE AdventureWorks2022;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO

[Top]

Konfigurowanie zarządcy zasobów w celu ograniczenia użycia CPU

Uwaga

Upewnij się, że zarządca zasobów jest włączony. Aby uzyskać więcej informacji, zobacz „Enable Resource Governor”.

W tym scenariuszu zarządcy zasobów konfiguracja obejmuje następujące podstawowe kroki:

  1. Utwórz i skonfiguruj pulę zasobów Zarządzanie zasobami, która ogranicza maksymalną średnią przepustowość CPU, która zostanie przekazana żądaniom w puli zasobów, gdy wystąpi obciążenie procesora.

  2. Utwórz i skonfiguruj grupę obciążeń Zarządcy zasobów, która używa tej puli.

  3. Utwórz funkcję klasyfikatora , która jest funkcją zdefiniowaną przez użytkownika (UDF), której wartości zwracane są używane przez zarządcę zasobów do klasyfikowania sesji, aby były kierowane do odpowiedniej grupy obciążeń.

  4. Zarejestruj funkcję klasyfikatora za pomocą zarządcy zasobów.

  5. Zastosuj zmiany w konfiguracji zarządcy zasobów w pamięci.

Uwaga

Aby uzyskać informacje o pulach zasobów, grupach obciążeń i klasyfikacji zarządcy zasobów, zobacz Zarządca zasobów.

Instrukcje Transact-SQL dla tych kroków opisano w procedurze "Konfigurowanie Resource Governor w celu ograniczenia użycia CPU", po której przedstawiono Transact-SQL przykład procedury.

Aby skonfigurować zarządcę zasobów (SQL Server Management Studio)

Aby skonfigurować zarządcę zasobów w celu ograniczania użycia procesora (Transact-SQL)

  1. Wydaj instrukcję CREATE RESOURCE POOL, aby utworzyć pulę zasobów. W przykładzie tej procedury użyto następującej składni:

    SQL 
    CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );

    wartość jest liczbą całkowitą z zakresu od 1 do 100, która wskazuje wartość procentową maksymalnej średniej przepustowości procesora CPU. Odpowiednia wartość zależy od środowiska. Dla zaprezentowania przykładu ten temat używa 20% procent (MAX_CPU_PERCENT = 20).

  2. Wydaj instrukcję CREATE WORKLOAD GROUP, aby utworzyć grupę obciążeń dla operacji o niskim priorytecie, w których użycie procesora CPU ma być kontrolowane. W przykładzie tej procedury użyto następującej składni:

    SQL 
    CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];

  3. Wydaj instrukcję CREATE FUNCTION, aby utworzyć funkcję klasyfikatora, która mapuje grupę obciążeń utworzoną w poprzednim kroku na użytkownika logowania o niskim priorytcie. W przykładzie tej procedury użyto następującej składni:

    SQL 
    CREATE FUNCTION [<schema_name>].[<function_name>]() RETURNS sysname  
WITH SCHEMABINDING  
AS  
BEGIN  
    DECLARE @workload_group_name AS [<sysname>]  
    IF (SUSER_NAME() = '<user_of_low_priority_login>')  
    SET @workload_group_name = '<workload_group_name>'  
    RETURN @workload_group_name  
END;

    Aby uzyskać informacje o składnikach tej instrukcji CREATE FUNCTION, zobacz:

  4. Wydaj instrukcję ALTER RESOURCE GOVERNOR, aby zarejestrować funkcję klasyfikatora za pomocą zarządcy zasobów. W przykładzie tej procedury użyto następującej składni:

    SQL 
    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);

  5. Wydaj drugą instrukcję ALTER RESOURCE GOVERNOR, aby zastosować zmiany w pamięciowej konfiguracji Resource Governor w następujący sposób:

    SQL 
    ALTER RESOURCE GOVERNOR RECONFIGURE;

Przykład B: Konfigurowanie zarządcy zasobów (Transact-SQL)

Poniższy przykład wykonuje następujące kroki w ramach jednej transakcji:

  1. Tworzy pulę zasobów pMAX_CPU_PERCENT_20.

  2. Tworzy grupę obciążeń gMAX_CPU_PERCENT_20.

  3. Tworzy funkcję klasyfikatora rgclassifier_MAX_CPU(), która używa nazwy użytkownika utworzonej w poprzednim przykładzie.

  4. Rejestruje funkcję klasyfikatora za pomocą zarządcy zasobów.

Po zatwierdzeniu transakcji przykład wdraża zmiany w konfiguracji, które są wymagane w instrukcjach ALTER WORKLOAD GROUP lub ALTER RESOURCE POOL.

Ważne

W poniższym przykładzie użyto nazwy użytkownika przykładowego programu SQL Server utworzonego w sekcji "Przykład A: Konfigurowanie identyfikatora logowania i użytkownika (Transact-SQL)" domain_name\MAX_CPU. Zastąp to nazwą użytkownika logowania, którego planujesz użyć do tworzenia skompresowanych kopii zapasowych o niskim priorytetu.

SQL 
-- Configure Resource Governor.  
USE master;  
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%.   
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20  
   WITH  
      (MAX_CPU_PERCENT = 20);  
GO  

-- Create a workload group to use this pool.   
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20  
USING pMAX_CPU_PERCENT_20;  
GO  

-- Create a classification function.  
-- Note that any request that does not get classified goes into   
-- the 'Default' group.  
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname   
WITH SCHEMABINDING  
AS  
BEGIN  
    DECLARE @workload_group_name AS sysname  
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')  
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'  
    RETURN @workload_group_name  
END;  
GO  
  
-- Register the classifier function with Resource Governor.  
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);  
COMMIT TRAN;  
GO  

-- Start Resource Governor  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

[Top]

Weryfikowanie klasyfikacji bieżącej sesji (Transact-SQL)

Opcjonalnie zaloguj się jako użytkownik określony w funkcji klasyfikatora i sprawdź klasyfikację sesji, wydając następującą instrukcję SELECT w Eksploratorze obiektów:

SQL 
USE master;  
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name   
FROM sys.dm_exec_sessions AS sess   
JOIN sys.dm_resource_governor_workload_groups AS grps   
    ON sess.group_id = grps.group_id  
WHERE session_id > 50;  
GO

W okienku wyników kolumna nazwa powinna zawierać co najmniej jedną sesję dla nazwy grupy roboczej, którą określiłeś w funkcji klasyfikującej.

Uwaga

Aby uzyskać informacje na temat dynamicznych widoków zarządzania wywoływanych przez tę instrukcję SELECT, zobacz sys.dm_exec_sessions (Transact-SQL) i sys.dm_resource_governor_workload_groups (Transact-SQL).

[Top]

Kompresowanie kopii zapasowych przy użyciu sesji z ograniczonym procesorem CPU

Aby utworzyć skompresowaną kopię zapasową w sesji z ograniczonym maksymalnym procesorem CPU, zaloguj się jako użytkownik określony w funkcji klasyfikatora. W poleceniu kopii zapasowej określ opcję WITH COMPRESSION (Transact-SQL) lub wybierz opcję Kompresuj kopię zapasową (SQL Server Management Studio). Aby utworzyć skompresowaną kopię zapasową bazy danych, zobacz Tworzenie pełnej kopii zapasowej bazy danych (SQL Server).

Przykład C: Tworzenie skompresowanej kopii zapasowej (Transact-SQL)

Poniższy przykład BACKUP tworzy skompresowaną pełną kopię zapasową bazy danych AdventureWorks2022 w nowo sformatowanym pliku kopii zapasowej, Z:\SQLServerBackups\AdvWorksData.bak.

SQL 
--Run backup statement in the gBackup session.  
BACKUP DATABASE AdventureWorks2022 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'   
WITH   
   FORMAT,   
   MEDIADESCRIPTION='AdventureWorks2022 Compressed Data Backups',
   DESCRIPTION='First database backup on AdventureWorks2022 Compressed Data Backups media set',
   COMPRESSION;  
GO

[Top]

Zobacz też

Tworzenie i testowanie klasyfikatora User-Defined funkcja
Zarządca zasobów