Erstellen und Testen eines Klassifizierers User-Defined Funktion

In diesem Thema wird das Erstellen und Testen einer benutzerdefinierten Klassifiziererfunktion (User-Defined Function, UDF) erläutert. Die Schritte umfassen das Ausführen Transact-SQL Anweisungen im SQL Server Management Studio-Abfrage-Editor.

Das im folgenden Verfahren gezeigte Beispiel veranschaulicht die Möglichkeiten zum Erstellen einer ziemlich komplexen benutzerdefinierten Klassifiziererfunktion.

In unserem Beispiel gilt Folgendes:

Ein Ressourcenpool (pProductionProcessing) und eine Workloadgruppe (gProductionProcessing) werden während eines bestimmten Zeitraums für die Produktionsverarbeitung erstellt.
Für die Verarbeitung von Verbindungen, die nicht den Anforderungen für die Produktionsverarbeitung entsprechen, wird ein Ressourcenpool (pOffHoursProcessing) und eine Workloadgruppe (gOffHoursProcessing) erstellt.
Eine Tabelle (TblClassificationTimeTable) wird im Master erstellt, um Start- und Endzeiten zu enthalten, die mit einer Anmeldezeit verglichen werden können. Dies muss im Master erstellt werden, da der Ressourcen-Manager Schemabindung bei Klassifiziererfunktionen verwendet.

Hinweis

Als bewährte Methode sollten Sie keine großen, häufig aktualisierten Tabellen im Master speichern.

Die Klassifiziererfunktion erweitert die Anmeldezeit. Eine übermäßig komplexe Funktion kann dazu führen, dass Anmeldungen eine Zeitüberschreitung verursachen oder schnelle Verbindungen verlangsamen.

So erstellen Sie die benutzerdefinierte Klassifiziererfunktion

Erstellen und Konfigurieren der neuen Ressourcenpools und Workloadgruppen. Weisen Sie jede Workloadgruppe dem entsprechenden Ressourcenpool zu.

--- Create a resource pool for production processing  
--- and set limits.  
USE master  
GO  
CREATE RESOURCE POOL pProductionProcessing  
WITH  
(  
     MAX_CPU_PERCENT = 100,  
     MIN_CPU_PERCENT = 50  
)  
GO  
--- Create a workload group for production processing  
--- and configure the relative importance.  
CREATE WORKLOAD GROUP gProductionProcessing  
WITH  
(  
     IMPORTANCE = MEDIUM  
)  
--- Assign the workload group to the production processing  
--- resource pool.  
USING pProductionProcessing  
GO  
--- Create a resource pool for off-hours processing  
--- and set limits.  

CREATE RESOURCE POOL pOffHoursProcessing  
WITH  
(  
     MAX_CPU_PERCENT = 50,  
     MIN_CPU_PERCENT = 0  
)  
GO  
--- Create a workload group for off-hours processing  
--- and configure the relative importance.  
CREATE WORKLOAD GROUP gOffHoursProcessing  
WITH  
(  
     IMPORTANCE = LOW  
)  
--- Assign the workload group to the off-hours processing  
--- resource pool.  
USING pOffHoursProcessing  
GO

Aktualisieren Sie die In-Memory-Konfiguration.

ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

Erstellen Sie eine Tabelle, und definieren Sie die Anfangs- und Endzeiten für den Produktionsverarbeitungszeitbereich.

USE master  
GO  
CREATE TABLE tblClassificationTimeTable  
(  
     strGroupName     sysname          not null,  
     tStartTime       time              not null,  
     tEndTime         time              not null  
)  
GO  
--- Add time values that the classifier will use to  
--- determine the workload group for a session.  
INSERT into tblClassificationTimeTable VALUES('gProductionProcessing', '6:35 AM', '6:15 PM')  
go

Erstellen Sie die Klassifiziererfunktion, die Zeitfunktionen und Werte verwendet, die anhand der Zeiten in der Nachschlagetabelle ausgewertet werden können. Informationen zur Verwendung von Nachschlagetabellen in einer Klassifiziererfunktion finden Sie unter "Bewährte Methoden für die Verwendung von Nachschlagetabellen in einer Klassifiziererfunktion".

Hinweis

SQL Server 2008 hat einen erweiterten Satz von Datentypen und Funktionen für Datum und Uhrzeit eingeführt. Weitere Informationen finden Sie unter Datums- und Uhrzeitdatentypen und -funktionen (Transact-SQL).

CREATE FUNCTION fnTimeClassifier()  
RETURNS sysname  
WITH SCHEMABINDING  
AS  
BEGIN  
     DECLARE @strGroup sysname  
     DECLARE @loginTime time  
     SET @loginTime = CONVERT(time,GETDATE())  
     SELECT TOP 1 @strGroup = strGroupName  
          FROM dbo.tblClassificationTimeTable  
          WHERE tStartTime <= @loginTime and tEndTime >= @loginTime  
     IF(@strGroup is not null)  
     BEGIN  
          RETURN @strGroup  
     END  
--- Use the default workload group if there is no match  
--- on the lookup.  
     RETURN N'gOffHoursProcessing'  
END  
GO

Registrieren Sie die Klassifiziererfunktion, und aktualisieren Sie die In-Memory-Konfiguration.

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier)  
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

Zur Überprüfung der Ressourcenpools, der Workload-Gruppen und der benutzerdefinierten Klassifizierungsfunktion

Rufen Sie die Ressourcenpool- und Workloadgruppenkonfiguration mithilfe der folgenden Abfrage ab.

USE master  
SELECT * FROM sys.resource_governor_resource_pools  
SELECT * FROM sys.resource_governor_workload_groups  
GO

Stellen Sie sicher, dass die Klassifiziererfunktion vorhanden ist und mithilfe der folgenden Abfragen aktiviert ist.

--- Get the classifier function Id and state (enabled).  
SELECT * FROM sys.resource_governor_configuration  
GO  
--- Get the classifer function name and the name of the schema  
--- that it is bound to.  
SELECT   
      object_schema_name(classifier_function_id) AS [schema_name],  
      object_name(classifier_function_id) AS [function_name]  
FROM sys.dm_resource_governor_configuration

Rufen Sie die aktuellen Laufzeitdaten für die Ressourcenpools und Workloadgruppen mithilfe der folgenden Abfrage ab.

SELECT * FROM sys.dm_resource_governor_resource_pools  
SELECT * FROM sys.dm_resource_governor_workload_groups  
GO

Ermitteln Sie, welche Sitzungen sich in jeder Gruppe befinden, indem Sie die folgende Abfrage verwenden.

SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))  
          FROM sys.dm_exec_sessions s  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
          ON g.group_id = s.group_id  
ORDER BY g.name  
GO

Ermitteln Sie anhand der folgenden Abfrage, welche Anforderungen sich in jeder Gruppe befinden.

SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text   
           FROM sys.dm_exec_requests r  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
            ON g.group_id = r.group_id  
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t  
ORDER BY g.name  
GO

Ermitteln Sie anhand der folgenden Abfrage, welche Anforderungen im Klassifizierer ausgeführt werden.

SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name   
           FROM sys.dm_exec_sessions s  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
           ON g.group_id = s.group_id  
                 AND 'preconnect' = s.status  
ORDER BY g.name  
GO  

SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text   
           FROM sys.dm_exec_requests r  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
           ON g.group_id = r.group_id  
                 AND 'preconnect' = r.status  
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t  
ORDER BY g.name  
GO

Bewährte Methoden für die Verwendung von Nachschlagetabellen in einer Klassifiziererfunktion

Verwenden Sie keine Nachschlagetabelle, es sei denn, sie ist unbedingt erforderlich. Wenn Sie eine Nachschlagetabelle verwenden müssen, kann sie hart in die Funktion selbst codiert werden. Dies muss jedoch mit der Komplexität und dynamischen Änderungen der Klassifiziererfunktion ausgeglichen werden.
Beschränken Sie die für Nachschlagetabellen ausgeführte E/A.
1. Verwenden Sie top 1, um nur eine Zeile zurückzugeben.
2. Minimieren Sie die Anzahl der Zeilen in der Tabelle.
3. Stellen Sie fest, dass alle Zeilen der Tabelle auf einer einzelnen Seite oder auf einer kleinen Anzahl von Seiten vorhanden sind.
4. Vergewissern Sie sich, dass Zeilen, die mithilfe der Indexsuche gefunden wurden, so viele Suchspalten wie möglich verwenden.
5. Denormalisieren Sie zu einer einzigen Tabelle, wenn Sie die Verwendung mehrerer Tabellen mit Joins erwägen.
Verhindern, dass die Nachschlagetabelle blockiert wird.
1. Verwenden Sie den NOLOCK Hinweis, um das Blockieren oder Verwenden SET LOCK_TIMEOUT in der Funktion mit einem Maximalwert von 1000 Millisekunden zu verhindern.
2. Tabellen müssen in der Masterdatenbank vorhanden sein. (Die Masterdatenbank ist die einzige Datenbank, die garantiert wiederhergestellt werden kann, wenn die Clientcomputer versuchen, eine Verbindung herzustellen).
3. Qualifizieren Sie den Tabellennamen immer vollständig mit dem Schema. Der Datenbankname ist nicht erforderlich, da er die Masterdatenbank sein muss.
4. Keine Auslöser auf der Tabelle.
5. Wenn Sie den Tabelleninhalt aktualisieren, stellen Sie sicher, dass Sie eine Transaktion auf Snapshotisolationsebene verwenden, um das Blockieren von Writer-Lesern zu verhindern. Beachten Sie, dass die Verwendung des NOLOCK Hinweises auch dies vermeiden sollte.
6. Deaktivieren Sie nach Möglichkeit die Klassifizierungsfunktion, wenn Sie den Tabelleninhalt ändern.
  
  Warnung
  
  Es wird dringend empfohlen, diese bewährten Methoden zu folgen. Wenn Es Probleme gibt, die Sie daran hindern, die bewährten Methoden zu folgen, empfehlen wir, dass Sie sich an den Microsoft-Support wenden, damit Sie zukünftige Probleme proaktiv verhindern können.

Siehe auch

Ressourcenkontrolle
Aktivieren der Ressourcenkontrolle
Ressourcenpool für die Ressourcenkontrolle
Ressourcenverwaltungsauslastungsgruppe
Konfigurieren der Ressourcenkontrolle mithilfe einer Vorlage
Anzeigen der Eigenschaften der Ressourcenkontrolle
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
ERSTELLEN EINER WORKLOADGRUPPE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

Last updated on 2017-06-13

Freigeben über

Erstellen und Testen eines Klassifizierers User-Defined Funktion

So erstellen Sie die benutzerdefinierte Klassifiziererfunktion

Zur Überprüfung der Ressourcenpools, der Workload-Gruppen und der benutzerdefinierten Klassifizierungsfunktion

Bewährte Methoden für die Verwendung von Nachschlagetabellen in einer Klassifiziererfunktion

Siehe auch

Zusätzliche Ressourcen