Resource Governor gebruiken om het CPU-gebruik te beperken met behulp van back-upcompressie (Transact-SQL)

van toepassing op:SQL Server-

Standaard verhoogt het maken van een back-up met behulp van compressie het CPU-gebruik aanzienlijk en kan het extra CPU-verbruik dat door het compressieproces wordt verbruikt, een negatieve invloed hebben op gelijktijdige bewerkingen. Daarom wilt u mogelijk een gecomprimeerde back-up met lage prioriteit maken in een sessie waarvan het CPU-gebruik wordt beperkt door Resource Governor wanneer CPU-conflicten optreden. In dit onderwerp wordt een scenario beschreven waarin de sessies van een bepaalde SQL Server-gebruiker worden ingedeeld door deze toe te passen aan een Resource Governor-workloadgroep die het CPU-gebruik in dergelijke gevallen beperkt.

Belangrijk

In een bepaald Resource Governor-scenario kan sessieclassificatie zijn gebaseerd op een gebruikersnaam, een toepassingsnaam of iets anders dat een verbinding kan onderscheiden. Zie Resource Governor Classifier Function en Resource Governor Workload Groupvoor meer informatie.

Dit onderwerp bevat de volgende reeks scenario's, die op volgorde worden gepresenteerd:

1. Een Aanmelding en Gebruiker instellen voor Low-Priority Operaties

2. Resource Governor configureren om het CPU-gebruik te beperken

3. de classificatie van de huidige sessie (Transact-SQL) controleren

4. Back-ups comprimeren met een sessie met beperkte CPU

Een aanmelding en gebruiker instellen voor Low-Priority bewerkingen

Voor het scenario in dit onderwerp is een SQL Server-aanmelding met lage prioriteit en een gebruiker vereist. De gebruikersnaam wordt gebruikt om sessies te classificeren die worden uitgevoerd tijdens het inloggen en om door te sturen naar een Resource Governor workloadgroep die het CPU-gebruik beperkt.

In de volgende procedure worden de stappen beschreven voor het instellen van een aanmelding en gebruiker voor dit doel, gevolgd door een Transact-SQL voorbeeld: 'Voorbeeld A: Een aanmelding en gebruiker instellen (Transact-SQL).'

Een aanmeldings- en databasegebruiker instellen voor het classificeren van sessies

1. Maak een SQL Server-aanmelding voor het maken van gecomprimeerde back-ups met lage prioriteit.

   Een account aanmaken

   • Een login aanmaken

   • AANMAKEN LOGIN (Transact-SQL)

2. Optioneel, verleen VIEW SERVER STATE aan deze login.

   • machtigingen voor systeemobjecten verlenen (Transact-SQL)

   Voor meer informatie, zie Machtigingen voor database-principals (Transact-SQL) verlenen.

3. Maak een SQL Server-gebruiker voor deze aanmelding.

   Een gebruiker maken

   • een databasegebruiker maken

   • MAAK GEBRUIKER AAN (Transact-SQL)

4. Om sessies van deze login en gebruiker in staat te stellen om een back-up van een bepaalde database te maken, voegt u de gebruiker toe aan de db_backupoperator database rol van die database. Doe dit voor elke database waar deze gebruiker een back-up van maakt. U kunt de gebruiker desgewenst toevoegen aan andere vaste databaserollen.

   Een gebruiker toevoegen aan een vaste databaserol

   • sp_addrolemember (Transact-SQL)

   Voor meer informatie, zie Machtigingen voor database-principals (Transact-SQL) verlenen.

Voorbeeld A: Een aanmelding en gebruiker instellen (Transact-SQL)

Het volgende voorbeeld is alleen relevant als u ervoor kiest om een nieuwe SQL Server-aanmelding en -gebruiker te maken voor back-ups met lage prioriteit. U kunt ook een bestaande aanmelding en gebruiker gebruiken, als er een geschikte bestaat.

Belangrijk

In het volgende voorbeeld wordt een voorbeeldaanmelding en gebruikersnaam gebruikt, domain_name\MAX_CPU. Vervang deze door de namen van de SQL Server-aanmelding en -gebruiker die u wilt gebruiken bij het maken van gecomprimeerde back-ups met lage prioriteit.

In dit voorbeeld wordt een aanmelding gemaakt voor het domain_name\MAX_CPU Windows-account en wordt vervolgens VIEW SERVER STATE-machtiging verleend voor de aanmelding. Met deze machtiging kunt u de Resource Governor-classificatie van sessies van de aanmelding controleren. In het voorbeeld wordt vervolgens een gebruiker aangemaakt voor domain_name\MAX_CPU en toegevoegd aan de vaste databaserol db_backupoperator voor de voorbeelddatabase AdventureWorks2022. Deze gebruikersnaam wordt gebruikt door de classificatiefunctie Resource Governor.

-- 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  

[Boven]

Resource Governor configureren om het CPU-gebruik te beperken

Notitie

Zorg ervoor dat Resource Governor is ingeschakeld. Zie Resource Governor inschakelenvoor meer informatie.

In dit Resource Governor-scenario bestaat de configuratie uit de volgende basisstappen:

1. Maak en configureer een Resource Governor-resourcegroep die de maximale gemiddelde CPU-bandbreedte beperkt die wordt gegeven aan aanvragen in de resourcegroep wanneer CPU-conflicten optreden.

2. Maak en configureer een Resource Governor-workloadgroep die gebruikmaakt van deze pool.

3. Maak een classificatiefunctie. Dit is een door de gebruiker gedefinieerde functie (UDF) waarvan de retourwaarden door Resource Governor worden gebruikt voor het classificeren van sessies, zodat ze worden doorgestuurd naar de juiste workloadgroep.

4. Registreer de classificatiefunctie bij Resource Governor.

5. Pas de wijzigingen toe op de configuratie van Resource Governor in het geheugen.

Notitie

Zie Resource Governorvoor informatie over Resource Governor-resourcepools, workloadgroepen en classificatie.

De Transact-SQL instructies voor deze stappen worden beschreven in de procedure'Resource Governor configureren voor het beperken van het CPU-gebruik', gevolgd door een Transact-SQL voorbeeld van de procedure.

Resource Governor (SQL Server Management Studio) configureren

• Resource Governor configureren met behulp van een sjabloon

• een resourcepool maken

• Een Workloadgroep Maken

Resource Governor configureren voor het beperken van het CPU-gebruik (Transact-SQL)

1. Geef een CREATE RESOURCE POOL instructie om een resourcepool te maken. In het voorbeeld voor deze procedure wordt de volgende syntaxis gebruikt:

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

   waarde is een geheel getal van 1 tot 100 dat het percentage van de maximale gemiddelde CPU-bandbreedte aangeeft. De juiste waarde is afhankelijk van uw omgeving. Ter illustratie gebruikt het voorbeeld in dit onderwerp 20% procent (MAX_CPU_PERCENT = 20.)

2. Geef een CREATE WORKLOAD GROUP-instructie uit om een workloadgroep te maken voor werkzaamheden met lage prioriteit waarvan u het CPU-gebruik wilt beheren. In het voorbeeld voor deze procedure wordt de volgende syntaxis gebruikt:

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

3. Geef een CREATE FUNCTION instructie uit om een classificatiefunctie te maken waarmee de workloadgroep die in de vorige stap is gemaakt, wordt toegewezen aan de gebruiker van de aanmelding met lage prioriteit. In het voorbeeld voor deze procedure wordt de volgende syntaxis gebruikt:

   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;
   

   Zie voor meer informatie over de onderdelen van deze CREATE FUNCTION-instructie:

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Belangrijk

     SUSER_NAME is slechts een van de verschillende systeemfuncties die kunnen worden gebruikt in een classificatiefunctie. Zie Een classificatie maken en testen User-Defined Functievoor meer informatie.

   • SET @local_variable (Transact-SQL).

4. Geef een ALTER RESOURCE GOVERNOR instructie uit om de classificatiefunctie te registreren bij Resource Governor. In het voorbeeld voor deze procedure wordt de volgende syntaxis gebruikt:

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

5. Geef als volgt een tweede ALTER RESOURCE GOVERNOR-instructie uit om de wijzigingen toe te passen op de configuratie van Resource Governor in het geheugen:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Voorbeeld B: Resource Governor configureren (Transact-SQL)

In het volgende voorbeeld worden de volgende stappen binnen één transactie uitgevoerd:

1. Maakt de pMAX_CPU_PERCENT_20 resourcepool.

2. Hiermee maakt u een gMAX_CPU_PERCENT_20-werklastgroep aan.

3. Hiermee maakt u de rgclassifier_MAX_CPU() classificatiefunctie, die gebruikmaakt van de gebruikersnaam die in het vorige voorbeeld is gemaakt.

4. Registreert de classificatiefunctie bij Resource Governor.

Nadat de transactie is doorgevoerd, worden in het voorbeeld de configuratiewijzigingen toegepast die zijn aangevraagd in de instructies ALTER WORKLOAD GROUP of ALTER RESOURCE POOL.

Belangrijk

In het volgende voorbeeld wordt de gebruikersnaam gebruikt van de voorbeeld-SQL Server-gebruiker die is gemaakt in 'Voorbeeld A: Een aanmelding en gebruiker instellen (Transact-SQL),' domain_name\MAX_CPU. Vervang dit door de naam van de gebruiker van de aanmelding die u wilt gebruiken voor het maken van gecomprimeerde back-ups met lage prioriteit.

-- 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    

[Boven]

De classificatie van de huidige sessie controleren (Transact-SQL)

Meld u desgewenst aan als de gebruiker die u hebt opgegeven in de classificatiefunctie en controleer de sessieclassificatie door de volgende SELECT-instructie uit te geven in Objectverkenner:

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  

In het resultatenvenster moet de naam kolom één of meer sessies weergeven voor de naam van de workloadgroep die u hebt opgegeven in de classificeerfunctie.

Notitie

Zie sys.dm_exec_sessions (Transact-SQL) en sys.dm_resource_governor_workload_groups (Transact-SQL)voor informatie over de dynamische beheerweergaven die worden aangeroepen met deze SELECT-instructie.

[Boven]

Back-ups comprimeren met behulp van een sessie met beperkte CPU

Als u een gecomprimeerde back-up wilt maken in een sessie met een beperkt maximum CPU, meldt u zich aan als de gebruiker die is opgegeven in de classificatiefunctie. Geef in uw back-upopdracht WITH COMPRESSION (Transact-SQL) op of selecteer Back-up comprimeren (SQL Server Management Studio). Zie Een volledige databaseback-up (SQL Server) makenals u een gecomprimeerde databaseback-up wilt maken.

Voorbeeld C: een gecomprimeerde back-up maken (Transact-SQL)

In het volgende BACKUP voorbeeld wordt een gecomprimeerde volledige back-up van de AdventureWorks2022-database gemaakt in een nieuw opgemaakt back-upbestand, Z:\SQLServerBackups\AdvWorksData.bak.

--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  

[Boven]

Zie ook

Een classificatie User-Defined functie maken en testen
Resource Governor