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

Secara bawaan, mencadangkan menggunakan kompresi secara signifikan meningkatkan penggunaan CPU, dan penggunaan CPU tambahan yang diakibatkan oleh proses kompresi dapat berdampak negatif pada operasi yang berjalan bersamaan. Oleh karena itu, Anda mungkin ingin membuat cadangan terkompresi berprioritas rendah dalam sesi yang penggunaan CPU-nya dibatasi oleh Resource Governor saat terjadi persaingan CPU. 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:

Menyiapkan Login dan Pengguna untuk Operasi Berprioritas Rendah
Mengonfigurasi Resource Governor untuk Membatasi Penggunaan CPU
Memverifikasi Klasifikasi Sesi Saat Ini (Transact-SQL)
Memadatkan Cadangan Menggunakan Sesi dengan CPU Terbatas

Menyiapkan Login dan Pengguna untuk Operasi Berprioritas Rendah

Skenario dalam topik ini memerlukan login dan pengguna SQL Server berprioritas rendah. 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)."

Buat login SQL Server untuk membuat cadangan terkompresi berprioritas rendah.

Untuk membuat login
- Membuat Login
- BUAT AKUN MASUK (Transact-SQL)
Secara opsional, berikan VIEW SERVER STATE untuk login ini.
- GRANT Hak Objek Sistem (Transact-SQL)
Untuk informasi selengkapnya, lihat GRANT Database Principal Permissions (Transact-SQL).
Buat pengguna SQL Server untuk masuk ini.

Untuk membuat pengguna
- Membuat Pengguna Database
- CREATE USER (Transact-SQL)
Untuk mengaktifkan sesi login dan pengguna ini mencadangkan database tertentu, tambahkan pengguna ke peran db_backupoperator pada database tersebut. Lakukan ini untuk setiap database yang pengguna ini akan cadangkan. Secara opsional, tambahkan pengguna ke peran database tetap lainnya.

Untuk menambahkan pengguna ke peran database tetap
- sp_addrolemember (T-SQL)
Untuk informasi selengkapnya, lihat GRANT Database Principal Permissions (Transact-SQL).

Contoh di bawah ini hanya relevan jika Anda memilih untuk membuat login dan pengguna SQL Server baru 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 dan pengguna SQL Server 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 dari sesi-sesi login tersebut. Contoh ini kemudian membuat pengguna untuk domain_name\MAX_CPU dan menambahkannya ke peran tetap database 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

[Ke Atas]

Mengonfigurasi Resource Governor untuk Membatasi Penggunaan CPU

Nota

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

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

Buat dan konfigurasikan kumpulan sumber daya Resource Governor yang membatasi bandwidth rata-rata maksimum CPU yang akan diberikan kepada permintaan di kumpulan sumber daya ketika terjadi kontensi CPU.
Buat dan konfigurasikan grup beban kerja Resource Governor yang menggunakan kumpulan ini.
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.
Daftarkan fungsi pengklasifikasi dengan Resource Governor.
Terapkan perubahan pada konfigurasi dalam memori Resource Governor.

Nota

Untuk informasi tentang kumpulan sumber daya Resource Governor, 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)

Mengonfigurasi Resource Governor untuk membatasi penggunaan CPU (Transact-SQL)

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

BUAT KUMPULAN SUMBER DAYA pool_name DENGAN ( 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.)
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;
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

Amerika Serikat

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

AKHIR

Untuk informasi tentang komponen pernyataan CREATE FUNCTION ini, lihat:
- DECLARE @local_variable (Transact-SQL)
- SUSER_SNAME (T-SQL)
  
  Penting
  
  SUSER_NAME hanyalah salah satu dari beberapa fungsi sistem yang dapat digunakan dalam fungsi pengklasifikasi. Untuk informasi selengkapnya, lihat Membuat dan Menguji Fungsi yang Ditentukan Pengguna Pengklasifikasi.
- SET @local_variable (Transact-SQL).
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);
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:

Membuat kumpulan pMAX_CPU_PERCENT_20 sumber daya.
gMAX_CPU_PERCENT_20 Membuat grup beban kerja.
Membuat fungsi pengklasifikasi rgclassifier_MAX_CPU() , yang menggunakan nama pengguna yang dibuat dalam contoh sebelumnya.
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 pengguna SQL Server 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

[Ke 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.

Nota

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

[Ke Atas]

Memadatkan Cadangan 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 pencadangan 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 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

[Ke Atas]

Lihat Juga

Membuat dan Menguji Fungsi yang Ditentukan Pengguna Pengklasifikasi
Resource Governor

Last updated on 2017-06-13

Bagikan melalui

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

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

Menyiapkan Login dan Pengguna untuk Operasi Berprioritas Rendah

Untuk menyiapkan pengguna login dan database untuk mengklasifikasikan sesi

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

Mengonfigurasi Resource Governor untuk Membatasi Penggunaan CPU

Mengonfigurasi Resource Governor untuk membatasi penggunaan CPU (Transact-SQL)

Contoh B: Mengonfigurasi Resource Governor (Transact-SQL)

Memverifikasi Klasifikasi Sesi Saat Ini (Transact-SQL)

Memadatkan Cadangan Menggunakan Sesi dengan CPU Terbatas

Contoh C: Membuat Cadangan Terkompresi (Transact-SQL)

Lihat Juga

Sumber Daya Tambahan: