Bagikan melalui

Gunakan Resource Governor untuk Membatasi Penggunaan CPU dengan Kompresi Cadangan (Transact-SQL)

  • Artikel

Secara default, mencadangkan menggunakan kompresi secara signifikan meningkatkan penggunaan CPU, dan CPU tambahan yang dikonsumsi oleh proses kompresi dapat berdampak buruk pada operasi bersamaan. Oleh karena itu, Anda mungkin ingin membuat cadangan terkompresi berprioritas rendah dalam sesi yang penggunaan CPU-nya dibatasi oleh Resource Governor ketika ketidakcocokan CPU terjadi. Topik ini menyajikan skenario yang mengklasifikasikan sesi pengguna SQL Server tertentu dengan memetakannya ke grup beban kerja Resource Governor yang membatasi penggunaan CPU dalam kasus tersebut.

Penting

Dalam skenario Resource Governor tertentu, klasifikasi sesi mungkin didasarkan pada nama pengguna, nama aplikasi, atau apa pun yang dapat membedakan koneksi. Untuk informasi selengkapnya, lihat Fungsi Pengklasifikasi Resource Governor dan Grup Beban Kerja Resource Governor.

Topik ini berisi serangkaian skenario berikut, yang disajikan secara berurutan:

  1. Menyiapkan Login dan Pengguna untuk Operasi Low-Priority

  2. Mengonfigurasi Resource Governor untuk Membatasi Penggunaan CPU

  3. Memverifikasi Klasifikasi Sesi Saat Ini (Transact-SQL)

  4. Memadatkan Pencadangan Menggunakan Sesi dengan CPU Terbatas

Menyiapkan Login dan Pengguna untuk Operasi Low-Priority

Skenario dalam topik ini memerlukan prioritas rendah SQL Server masuk dan pengguna. Nama pengguna akan digunakan untuk mengklasifikasikan sesi yang berjalan di login dan merutekannya ke grup beban kerja Resource Governor yang membatasi penggunaan CPU.

Prosedur berikut menjelaskan langkah-langkah untuk menyiapkan login dan pengguna untuk tujuan ini, diikuti dengan contoh T-SQL, "Contoh A: Menyiapkan Login dan Pengguna (Transact-SQL)."

Untuk menyiapkan pengguna login dan database untuk mengklasifikasikan sesi

  1. Buat login SQL Server untuk membuat cadangan terkompresi berprioritas rendah.

    Untuk membuat login

  2. Secara opsional, berikan VIEW SERVER STATE ke login ini.

    Untuk informasi selengkapnya, lihat GRANT Database Principal Permissions (Transact-SQL).

  3. Buat pengguna SQL Server untuk login ini.

    Untuk membuat pengguna

  4. Untuk mengaktifkan sesi login dan pengguna ini untuk mencadangkan database tertentu, tambahkan pengguna ke peran database db_backupoperator database tersebut. Lakukan ini untuk setiap database yang akan dicadangkan pengguna ini. Secara opsional, tambahkan pengguna ke peran database tetap lainnya.

    Untuk menambahkan pengguna ke peran database tetap

    Untuk informasi selengkapnya, lihat GRANT Database Principal Permissions (Transact-SQL).

Contoh A: Menyiapkan Login dan Pengguna (Transact-SQL)

Contoh berikut hanya relevan jika Anda memilih untuk membuat login SQL Server baru dan pengguna untuk pencadangan berprioritas rendah. Atau, Anda dapat menggunakan login dan pengguna yang ada, jika ada yang sesuai.

Penting

Contoh berikut menggunakan contoh login dan nama pengguna, domain_name\MAX_CPU. Ganti ini dengan nama login SQL Server dan pengguna yang Anda rencanakan untuk digunakan saat membuat cadangan terkompresi berprioritas rendah.

Contoh ini membuat login untuk akun Windows domain_name\MAX_CPU lalu memberikan izin LIHAT STATUS SERVER ke login. Izin ini memungkinkan Anda memverifikasi klasifikasi Resource Governor sesi masuk. Contohnya kemudian membuat pengguna untuk domain_name\MAX_CPU dan menambahkannya ke peran database tetap db_backupoperator untuk database sampel AdventureWorks2012. Nama pengguna ini akan digunakan oleh fungsi pengklasifikasi 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 AdventureWorks2012 for this login  
USE AdventureWorks2012;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO

[Atas]

Mengonfigurasi Resource Governor untuk Membatasi Penggunaan CPU

Catatan

Pastikan Resource Governor diaktifkan. Untuk informasi selengkapnya, lihat Mengaktifkan Resource Governor.

Dalam skenario Resource Governor ini, konfigurasi terdiri dari langkah-langkah dasar berikut:

  1. Membuat dan mengonfigurasi kumpulan sumber daya Resource Governor yang membatasi bandwidth CPU rata-rata maksimum yang akan diberikan untuk permintaan di kumpulan sumber daya ketika ketidakcocokan CPU terjadi.

  2. Buat dan konfigurasikan grup beban kerja Resource Governor yang menggunakan kumpulan ini.

  3. Buat fungsi pengklasifikasi, yang merupakan fungsi yang ditentukan pengguna (UDF) yang nilai pengembaliannya digunakan oleh Resource Governor untuk mengklasifikasikan sesi sehingga dirutekan ke grup beban kerja yang sesuai.

  4. Daftarkan fungsi pengklasifikasi dengan Resource Governor.

  5. Terapkan perubahan ke konfigurasi Resource Governor dalam memori.

Catatan

Untuk informasi tentang Resource Governor kumpulan sumber daya, grup beban kerja, dan klasifikasi, lihat Resource Governor.

Pernyataan Transact-SQL untuk langkah-langkah ini dijelaskan dalam prosedur, "Untuk mengonfigurasi Resource Governor untuk membatasi penggunaan CPU," yang diikuti oleh contoh prosedur Transact-SQL.

Untuk mengonfigurasi Resource Governor (SQL Server Management Studio)

Untuk mengonfigurasi Resource Governor untuk membatasi penggunaan CPU (Transact-SQL)

  1. Terbitkan pernyataan CREATE RESOURCE POOL untuk membuat kumpulan sumber daya. Contoh untuk prosedur ini menggunakan sintaks berikut:

    MEMBUAT pool_name KUMPULAN SUMBER DAYA WITH ( MAX_CPU_PERCENT = value );

    Nilai adalah bilangan bulat dari 1 hingga 100 yang menunjukkan persentase bandwidth CPU rata-rata maksimum. Nilai yang sesuai tergantung pada lingkungan Anda. Untuk tujuan ilustrasi, contoh dalam topik ini menggunakan 20% persen (MAX_CPU_PERCENT = 20.)

  2. Terbitkan pernyataan CREATE WORKLOAD GROUP untuk membuat grup beban kerja untuk operasi berprioritas rendah yang penggunaan CPU-nya ingin Anda kelola. Contoh untuk prosedur ini menggunakan sintaks berikut:

    MEMBUAT group_name GRUP BEBAN KERJA MENGGUNAKAN pool_name;

  3. Terbitkan pernyataan CREATE FUNCTION untuk membuat fungsi pengklasifikasi yang memetakan grup beban kerja yang dibuat pada langkah sebelumnya kepada pengguna login berprioritas rendah. Contoh untuk prosedur ini menggunakan sintaks berikut:

    CREATE FUNCTION [schema_name.] function_name() RETURNS sysname

    DENGAN SCHEMABINDING

    AS

    MULAI

    DECLARE @workload_group_name AS sysname

    IF (SUSER_NAME() = 'user_of_low_priority_login')

    SET @workload_group_name = 'workload_group_name'

    KEMBALI @workload_group_name

    END

    Untuk informasi tentang komponen pernyataan CREATE FUNCTION ini, lihat:

  4. Terbitkan pernyataan ALTER RESOURCE GOVERNOR untuk mendaftarkan fungsi pengklasifikasi dengan Resource Governor. Contoh untuk prosedur ini menggunakan sintaks berikut:

    UBAH RESOURCE GOVERNOR DENGAN (CLASSIFIER_FUNCTION = schema_name.function_name);

  5. Terbitkan pernyataan ALTER RESOURCE GOVERNOR kedua untuk menerapkan perubahan pada konfigurasi dalam memori Resource Governor, sebagai berikut:

    ALTER RESOURCE GOVERNOR RECONFIGURE;

Contoh B: Mengonfigurasi Resource Governor (Transact-SQL)

Contoh berikut melakukan langkah-langkah berikut dalam satu transaksi:

  1. Membuat kumpulan pMAX_CPU_PERCENT_20 sumber daya.

  2. gMAX_CPU_PERCENT_20 Membuat grup beban kerja.

  3. Membuat fungsi pengklasifikasi rgclassifier_MAX_CPU() , yang menggunakan nama pengguna yang dibuat dalam contoh sebelumnya.

  4. Mendaftarkan fungsi pengklasifikasi dengan Resource Governor.

Setelah melakukan transaksi, contoh menerapkan perubahan konfigurasi yang diminta dalam pernyataan ALTER WORKLOAD GROUP atau ALTER RESOURCE POOL.

Penting

Contoh berikut menggunakan nama pengguna sampel SQL Server pengguna yang dibuat di "Contoh A: Menyiapkan Login dan Pengguna (Transact-SQL)," domain_name\MAX_CPU. Ganti ini dengan nama pengguna login yang Anda rencanakan untuk digunakan untuk membuat cadangan terkompresi berprioritas rendah.

-- Configure Resource Governor.  
BEGIN TRAN  
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

[Atas]

Memverifikasi Klasifikasi Sesi Saat Ini (Transact-SQL)

Secara opsional, masuk sebagai pengguna yang Anda tentukan dalam fungsi pengklasifikasi Anda, dan verifikasi klasifikasi sesi dengan mengeluarkan pernyataan SELECT berikut di Object Explorer:

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

Di panel hasil, kolom nama harus mencantumkan satu atau beberapa sesi untuk nama grup beban kerja yang Anda tentukan dalam fungsi pengklasifikasi Anda.

Catatan

Untuk informasi tentang tampilan manajemen dinamis yang dipanggil oleh pernyataan SELECT ini, lihat sys.dm_exec_sessions (Transact-SQL) dan sys.dm_resource_governor_workload_groups (Transact-SQL).

[Atas]

Memadatkan Pencadangan Menggunakan Sesi dengan CPU Terbatas

Untuk membuat cadangan terkompresi dalam sesi dengan CPU maksimum terbatas, masuk sebagai pengguna yang ditentukan dalam fungsi pengklasifikasi Anda. Dalam perintah cadangan Anda, tentukan WITH COMPRESSION (Transact-SQL) atau pilih Kompres cadangan (SQL Server Management Studio). Untuk membuat cadangan database terkompresi, lihat Membuat Pencadangan Database Lengkap (SQL Server).

Contoh C: Membuat Cadangan Terkompresi (Transact-SQL)

Contoh BACKUP berikut membuat cadangan penuh terkompresi dari database AdventureWorks2012 dalam file cadangan yang baru diformat, Z:\SQLServerBackups\AdvWorksData.bak.

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

[Atas]

Lihat juga

Membuat dan Menguji Fungsi User-Defined Pengklasifikasi
Resource Governor