Erstellen und Testen einer benutzerdefinierten Klassifizierungsfunktion
In diesem Thema wird das Erstellen und Testen einer benutzerdefinierten Klassifizierungsfunktion (User-Defined Function, UDF) erläutert. Die Schritte umfassen das Ausführen von Transact-SQL-Anweisungen im SQL Server Management Studio-Abfrage-Editor.
Das in der folgenden Prozedur dargestellte Beispiel veranschaulicht die Möglichkeiten zum Erstellen einer recht komplexen benutzerdefinierten Klassifizierungsfunktion.
In unserem Beispiel gilt Folgendes:
Ein Ressourcenpool (pProductionProcessing) und eine Arbeitsauslastungsgruppe (gProductionProcessing) werden zur Produktionsverarbeitung während eines angegebenen Zeitbereichs erstellt.
Ein Ressourcenpool (pOffHoursProcessing) und eine Arbeitsauslastungsgruppe (gOffHoursProcessing) werden für das Verwalten von Verbindungen erstellt, die die Anforderungen für die Produktionsverarbeitung nicht erfüllen.
In master wird eine Tabelle (TblClassificationTimeTable) erstellt, in der die Start- und Endzeiten gespeichert werden, die für eine Anmeldungszeit ausgewertet werden können. Diese Tabelle muss in der Masterdatenbank erstellt werden, da die Ressourcenkontrolle die Schemabindung für Klassifizierungsfunktionen verwendet.
Hinweis Als Vorgehensweise wird empfohlen, in master keine großen, häufig aktualisierten Tabellen zu speichern.
Die Klassifizierungsfunktion verlängert die Anmeldezeit. Eine übermäßig komplexe Funktion kann zu einem Timeout bei Anmeldungen oder zur Verlangsamung schneller Verbindungen führen.
So erstellen Sie die benutzerdefinierte Klassifizierungsfunktion
Erstellen und konfigurieren Sie die neuen Ressourcenpools und Arbeitsauslastungsgruppen. Weisen Sie jeder Arbeitsauslastungsgruppe den 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 Konfiguration im Arbeitsspeicher.
ALTER RESOURCE GOVERNOR RECONFIGURE GO
Erstellen Sie eine Tabelle, und definieren Sie die Start- und Endzeiten für den Zeitbereich der Produktionsverarbeitung.
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 Klassifizierungsfunktion, die Zeitfunktionen und -werte verwendet, die bezüglich der Zeiten in der Suchtabelle ausgewertet werden können. Informationen zum Verwenden von Nachschlagetabellen in einer Klassifizierungsfunktion finden Sie unter "Best Practices für die Verwendung von Nachschlagetabellen in Klassifizierungsfunktionen" in diesem Thema.
Hinweis In SQL Server 2008 wurde ein erweiterter Satz von Datums- und Uhrzeitdatentypen und zugehörigen Funktionen eingeführt. Weitere Informationen finden Sie unter Datums- und Uhrzeitdatentypen und zugehörige 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 Klassifizierungsfunktion, und aktualisieren Sie die Konfiguration im Arbeitsspeicher.
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier) ALTER RESOURCE GOVERNOR RECONFIGURE GO
So überprüfen Sie die Ressourcenpools, die Arbeitsauslastungsgruppen und die benutzerdefinierte Klassifizierungsfunktion
Ermitteln Sie die Konfiguration von Ressourcenpools und Arbeitsauslastungsgruppen mit der folgenden Abfrage.
USE master SELECT * FROM sys.resource_governor_resource_pools SELECT * FROM sys.resource_governor_workload_groups GO
Stellen Sie unter Verwendung der folgenden Abfragen sicher, dass die Klassifizierungsfunktion vorhanden und 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 mit der folgenden Abfrage die aktuellen Laufzeitdaten für die Ressourcenpools und Arbeitsauslastungsgruppen ab.
SELECT * FROM sys.dm_resource_governor_resource_pools SELECT * FROM sys.dm_resource_governor_workload_groups GO
Ermitteln Sie mit der folgenden Abfrage, welche Sitzungen in jeder Gruppe vorhanden sind.
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 mit der folgenden Abfrage, welche Anforderungen in jeder Gruppe vorhanden sind.
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 mit der folgenden Abfrage, welche Anforderungen in der Klassifizierung 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
Best Practices für die Verwendung von Nachschlagetabellen in Klassifizierungsfunktionen
Verwenden Sie Nachschlagetabellen nur, wenn es unbedingt notwendig ist. Wenn Sie eine Nachschlagetabelle verwenden müssen, kann diese in die Funktion selbst hartcodiert werden. Dies muss jedoch unter Berücksichtigung der Komplexität und der dynamischen Änderungen der Klassifizierungsfunktion geschehen.
Begrenzen Sie die E/A, die für Nachschlagetabellen ausgeführt wird.
Verwenden Sie TOP 1, um nur eine Zeile zurückzugeben.
Minimieren Sie die Anzahl der Zeilen in der Tabelle.
Stellen Sie sicher, dass alle Zeilen der Tabelle auf einer einzigen oder einer kleinen Anzahl von Seiten vorliegen.
Stellen Sie sicher, dass Zeilen, die mithilfe von Index Seek-Vorgängen gefunden werden, so viele Suchspalten wie möglich verwenden.
Denormalisieren Sie auf eine einzige Tabelle, wenn Sie die Verwendung mehrerer Tabellen mit Joins erwägen.
Verhindern Sie ein Blockieren der Nachschlagetabelle.
Verwenden Sie den Hinweis NOLOCK, um Blockierungen zu verhindern, oder verwenden Sie in der Funktion die Option SET LOCK_TIMEOUT mit einem Maximalwert von 1000 Millisekunden.
Die Tabelle(n) muss bzw. müssen in der Masterdatenbank vorhanden sein. (Die Masterdatenbank ist die einzige Datenbank, die mit Sicherheit wiederhergestellt wird, wenn die Clientcomputer versuchen, eine Verbindung herzustellen.)
Der Tabellenname muss mit dem Schema stets vollqualifiziert werden. Da es sich um die Masterdatenbank handeln muss, ist kein Datenbankname erforderlich.
Keine Trigger für die Tabelle.
Wenn Sie die Tabelleninhalte aktualisieren, stellen Sie sicher, dass eine Momentaufnahme-Isolationsstufentransaktion verwendet wird, um das Blockieren von Lesern (Readers) durch einen Schreiber (Writer) zu verhindern. Beachten Sie, dass die Verwendung des Hinweises NOLOCK dies ebenfalls verhindern kann.
Deaktivieren Sie beim Ändern der Tabelleninhalte die Klassifizierungsfunktion, wenn möglich.
Vorsicht Es wird dringend empfohlen, diese Best Practices zu befolgen. Wenn Sie die Best Practices aufgrund von Problemen nicht befolgen können, wenden Sie sich an den Microsoft Support, um zukünftigen Problemen vorzubeugen.
Siehe auch
Verweis
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)
Konzepte
Aktivieren der Ressourcenkontrolle
Ressourcenpool für die Ressourcenkontrolle
Arbeitsauslastungsgruppe der Ressourcenkontrolle