Ressourcen Kontrolle

Die Ressourcen Kontrolle (engl. Resource Governor) ist ein Microsoft SQL Server Enterprise Feature, welches erlaubt, dass Anwendungen oder Benutzer im Verbrauch von Systemressourcen eingeschränkt werden. Die Einschränkung erfolgt jedoch nur, wenn nicht mehr ausreichend Ressourcen verfügbar sind. D.h. wenn eine Anwendung max. 50% der CPU Last verwenden darf, aber 100% verfügbar sind, erfolgt die Einschränkung nicht, erst wenn der Bedarf von höher konfigurierten und priorisierten Anwendungen vorhanden ist. Die Einschränungen können unter den folgenden Indikatoren vorgenommen werden:

  • Arbeitsspeicher
  • CPU
  • Timeouts
  • Anfragen

Die jeweiligen Einstellungen können mit min. und max. Werten konfiguriert werden.

In der Umsetzung werden sogenannte Pools erzeugt, die den Ressourcen Gruppen zur Verfügung stehen. Die Unterscheidung welcher Benutzer oder welche Anwendung wie eingeschränkt wird, bzw. hier überhaupt berücksichtigt wird, erfolgt über Klassifizierungsfunktionen.

Die Überwachung der Einstellungen kann über den Windows Ressourcen Monitor durchgeführt werden. Dafür wird eine Gruppe „Workload Group Stats“ zur Verfügung gestellt. Dazu aber später mehr …

In diesem Beispiel geht es nun um die Einschränkung von 2 Anwendern, die jeweils nur eine bestimmte CPU Zeit zugewiesen bekommen sollen. (kleiner Hinweis: Besser nur eine CPU im SQL Server verwenden, damit die CPU Auslastung besser sichtbar wird)

Um erstmal zu beginnen, werden 2 Logins erstellt:

CREATE LOGIN [BenutzerKundeA] WITH PASSWORD=N'asdf', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE LOGIN [BenutzerKundeB] WITH PASSWORD=N'asdf', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Nun wird die Ressourcen Kontrolle aktiviert (zu finden unter der Verwaltung im SMSS):

ALTER RESOURCE GOVERNOR RECONFIGURE;

Nun werden 2 Pools erstellt:

CREATE RESOURCE POOL [HoheLeistung]
GO
CREATE RESOURCE POOL [NiedrigeLeistung]
GO

Und nun 2 Gruppen die dem jeweiligen Pool zugewiesen werden (jeder Pool kann unterschiedliche Gruppen beinhalten die dazu noch priorisiert werden können):

CREATE WORKLOAD GROUP [KundeA] USING [HoheLeistung]
GO
CREATE WORKLOAD GROUP [KundeB] USING [NiedrigeLeistung]
GO

Nun wird die Ressourcen Kontrolle aktiviert und die Änderungen übernommen:

ALTER RESOURCE GOVERNOR RECONFIGURE;

Nun erfolgt die Einschränkung der Pools:

ALTER RESOURCE POOL [HoheLeistung] WITH (min_cpu_percent=0, max_cpu_percent=50);
ALTER RESOURCE POOL [NiedrigeLeistung] WITH (min_cpu_percent=0, max_cpu_percent=50);

Beide Pools verfügen über maximal 50% CPU Last

… und nun wieder die Änderungen übernehmen)

ALTER RESOURCE GOVERNOR RECONFIGURE;

Nun wird in der master Datenbank die Klassifizierungsfunktions erstellt, welche nach dem jeweiligen Benutzernamen die jeweilige, zu verwedende Gruppe zurückgibt:

CREATE FUNCTION dbo.RG_KlassifizierungsFunktion() RETURNS SYSNAME
WITH SCHEMABINDING
AS BEGIN
DECLARE @GroupName SYSNAME
IF(SUSER_NAME() = 'BenutzerKundeA')
BEGIN
SET @GroupName = 'KundeA'
END
IF(SUSER_NAME() = 'BenutzerKundeB')
BEGIN
SET @GroupName = 'KundeB'
END
RETURN @GroupName
END
GO

Nun wird die Funktion der Ressourcen Kontrolle übergeben und die Änderungen werden angewendet:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RG_KlassifizierungsFunktion)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Wenn man nun folgenden Code unter den beiden Benutzern ausführt und die Indikatoren in den PerfMon mit aufnimmt, tritt die Ressourcen Kontrolle in Kraft …

set nocount on
declare @i int
declare @s varchar(100)
set @i = 100000000
while @i > 0
begin
select @s = @@version;
set @i = @i - 1;
end

und bringt folgendes Ergebnis im PerfMon:

Damit das Beispiel auch Sinn macht, werden die Gruppen angepasst:

ALTER RESOURCE POOL HoheLeistung
WITH(min_cpu_percent=0, max_cpu_percent=75);
GO
ALTER RESOURCE POOL NiedrigeLeistung
WITH(min_cpu_percent=0, max_cpu_percent=25);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Nun erhält der eine Pool max. 75% CPU und der andere max. 25%. Im PerfMon sieht das dann folgendermaßen aus:

Partitionierung von SQL Server Tabellen

In diesem Blog Eintrag wird die Partitionierung von Tabellen mit dem Microsoft SQL Server 2008R2 beschrieben.
Diese Funktion wurde mit der Version 2005 eingeführt und ermöglicht die Verteilung von Daten einer Tabelle in verschiedene Dateigruppen um diese bspw. Archivieren zu können indem die Daten anhand eines Merkmals in verschiedene Dateigruppen unterteilt werden.

Folgendes Beispiel beschreibt die einer Buchungstabelle in der die Datensätze anhand des Buchungsdatums in einzelne Dateigruppen unterteilt werden können. So kann bei anwachsen der Datenbank eine einzelne Datei, wo die Datensätze nicht mehr benötigt werden, gesichert und geleert werden. Dadurch wird die Datenbank wieder kleiner und erhöht gleichzeitig die Performance der Datenbank.

Vorab möchte ich noch darauf hinweisen, dass die Partitionierungs-Funktion nur in der Enterprise/Datacenter Edition verügbar ist.

Ausgangssituation für folgendes Beispiel:

  • Eine Buchungstabelle soll erstellt werden in der Daten aus einer anderen Datenbank übernommen werden
  • Es werden Daten aus den Kalenderjahren 2010, 2009 und früher übernommen.
  • Die Daten die früher als 2009 eingegeben wurden sind nur wenige
  • Die Partitionierung soll erweitert werden können um zukünftige Geschäftsjahre aufnehmen zu können

Lösungsmöglichkeit: Zuerst werden 4 Dateigruppen in die Datenbank eingefügt für die Geschäftsjahre 2011, 2010, 2009 und eine Datei für alle vorherigen Jahre:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
Nun wird zu den 4 Dateigruppen jeweils eine Datendatei hinzugefügt:
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2009.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2010.ndf' ,SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Aktuell.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Archiv.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird eine Partitionierungs-Funktion erstellt, die auf ein DATETIME Feld prüft und die Daten entsprechend verteilt:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010 geht
);

Nun wird das Schema erstellt was die Funktion als Basis für die Datenverteilung nutzt und diese dann in die Dateigruppen aufteilt:
CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011');
Als nächstes wird die Buchungstabelle erstellt die dem Schema zugeordnet wird. Das Datumsfeld, welches die Daten an die Funktion zur Verteilung weitergibt, muss mit in den Primärschlüssel aufgenommen werden:
CREATE TABLE dbo.buchungen (
[uid] uniqueidentifier default NEWID() NOT NULL,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY) ON ArchivierungsSchema(datum);
GO
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]);

INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2008', '20080308', '100');
GO
-- Insert in Archiv2009
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('März 2009', '20090308', '100');
GO
-- Insert in Archiv2010
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Februar 2010', '20100209', '100');
GO
-- Insert in Archiv2011
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2011', '20110120', '100');
Um zu prüfen ob die eingetragenen Datensätze sich in den richtigen Partionen befinden, kann folgendes Statement verwendet werden:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Hier sehen wir dann die Datenverteilung wie die Datensätze sich durch die Funktion in die richtigen Dateien verteilt haben.

 

Problematisch wird es dann wenn ein neues Geschäftsjahr anbricht und Datensätze für 2012 eingetragen werden. Diese Datensätze befinden sich dann in der Partition für 2011: INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2012', '20120115', '300'); Dieses Problem kann dann durch Erstellen einer neuen Dateigruppe und dem Anpassen der Funktion gelöst werden:
-- Neue Dateigruppe für 2012
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
GO

-- Neue Datei für die Dateigruppe 2012
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]

Nun muss das Schema angepasst werden um die neue Dateigruppen zu verwenden, diese wird dann als Standard hinten dran gestellt.
Weiterhin wird durch die SPLIT() Funktion eine neue Begrenzung für 2012 eingefügt. Dadurch werden dann die bestehenden Daten geprüft und neu verteilt:
ALTER PARTITION SCHEME ArchivierungsSchema
NEXT USED [Archiv2012]
GO
ALTER PARTITION FUNCTION ArchivierungsFunktion ()
SPLIT RANGE ('20120101');

Hier ist dann zu sehen das die neue Dateigruppe 2012 verwendet wird, und der Datensatz aus der Partition 2011 in die Partition 2012 verschoben worden ist.

 

Download: