Självstudie: Exempel för att konfigurera styrning av tempdb-utrymmesresurser

Gäller för: Förhandsversion av SQL Server 2025 (17.x) och senare versioner

Exempel i den här artikeln visar hur du anger gränser tempdb för utrymmesförbrukning och visar utrymmesförbrukningen tempdb för varje arbetsbelastningsgrupp.

En introduktion till tempdb styrning av rymdresurser finns i Tempdb-resursstyrning för utrymme.

De här exemplen är avsedda att hjälpa dig att bekanta dig med tempdb styrning av rymdresurser i en testmiljö som inte är produktionsmiljö.

Exempel förutsätter att resursguvernören inte är aktiverad från början och att dess konfiguration inte ändras från standardinställningen. De förutsätter också att andra arbetsbelastningar på din SQL Server-instans inte väsentligen bidrar till tempdb utrymmesförbrukningen när du kör skripten.

Ange en fast gräns för default arbetsbelastningsgruppen

Det här exemplet begränsar den totala tempdb utrymmesförbrukningen för begäranden (frågor) i default arbetsbelastningsgruppen till en fast gräns.

1. default Ändra arbetsbelastningsgruppen för att konfigurera en fast gräns på 20 GB för tempdb utrymmesförbrukning.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
   

2. Aktivera resursguvernör för att göra den aktuella konfigurationen effektiv.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

3. Visa gränserna för tempdb utrymmesförbrukning.

   SELECT group_id,
          name,
          group_max_tempdb_data_mb,
          group_max_tempdb_data_percent
   FROM sys.resource_governor_workload_groups
   WHERE name = 'default';
   

4. Kontrollera den aktuella tempdb utrymmesförbrukningen för default arbetsbelastningsgruppen, lägg till data tempdb genom att skapa en tillfällig tabell och infoga en rad och kontrollera sedan utrymmesförbrukningen igen för att se ökningen.

   SELECT group_id,
          name,
          tempdb_data_space_kb
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   
   SELECT REPLICATE('A', 1000) AS c
   INTO #t;
   
   SELECT group_id,
          name,
          tempdb_data_space_kb
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   

5. Du kan också ta bort gränserna för gruppen och inaktivera resursguvernören default för att återgå till icke-kontrollerad utrymmesförbrukning i tempdb:

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);
   
   ALTER RESOURCE GOVERNOR DISABLE;
   

Ange en procentgräns för default arbetsbelastningsgruppen

Det här exemplet konfigurerar tempdb datafiler så att procentgränsen kan användas och begränsar sedan den totala tempdb utrymmesförbrukningen för begäranden (frågor) i default arbetsbelastningsgruppen till en procentgräns.

1. Ange FILEGROWTH och MAXSIZE för att alla tempdb datafiler ska uppfylla kraven, vilket begränsar den maximala storleken tempdb till 1 GB.

   Det här exemplet förutsätter att det tempdb finns fyra datafiler. Du kan behöva justera skriptet om konfigurationen tempdb använder ett annat antal filer eller om de logiska filnamnen är olika. Du kan också behöva starta om SQL Server-instansen eller minska användningen av tempdb om du får fel 5040, ÄNDRA FIL misslyckades för databasen "tempdb" ... Filstorlek ... är större än MAXSIZE ... när du kör detta skript.

   ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp2', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp3', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp4', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   

2. default Ändra arbetsbelastningsgruppen för att konfigurera en gräns på fem procent för utrymmesförbrukningentempdb. Med maximal tempdb storlek på 1 GB begränsar default detta gruppen till cirka 51 MB tempdb utrymme.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 5);
   

3. Om en fast gräns har angetts tar du bort den så att den inte åsidosätter procentgränsen.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);
   

4. Aktivera resursguvernör för att göra konfigurationen effektiv.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

5. Visa gränserna för tempdb utrymmesförbrukning.

   SELECT group_id,
          name,
          group_max_tempdb_data_mb,
          group_max_tempdb_data_percent
   FROM sys.resource_governor_workload_groups
   WHERE name = 'default';
   

6. Lägg till data i tempdb för att nå gränsen.

   SELECT *
   INTO #m
   FROM sys.messages;
   

   Uttalandet avbryts med fel 1138.

7. Kontrollera arbetsbelastningsgruppens statistik för tempdb.

   SELECT group_id,
          name,
          tempdb_data_space_kb,
          peak_tempdb_data_space_kb,
          total_tempdb_data_limit_violation_count
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   

   Värdet i total_tempdb_data_limit_violation_count kolumnen ökas med 1, vilket visar att en begäran i arbetsbelastningsgruppen avbröts eftersom dess default utrymmesförbrukning begränsades av resursguvernörentempdb.

8. Du kan också ta bort gränserna för gruppen och inaktivera resursguvernören default för att återgå till icke-kontrollerad utrymmesförbrukning i tempdb:

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);
   
   ALTER RESOURCE GOVERNOR DISABLE;
   

9. Du kan också återställa de tempdb ändringar i datafilens konfiguration som gjordes tidigare i det här exemplet.

Ange en fast gräns för en användardefinierad arbetsbelastningsgrupp

Det här exemplet skapar en ny arbetsbelastningsgrupp och skapar sedan en klassificerarfunktion för att tilldela sessioner med ett specifikt programnamn till den här arbetsbelastningsgruppen.

I det här exemplet anges den fasta gränsen tempdb för utrymmesförbrukning för arbetsbelastningsgruppen till ett litet värde på 1 MB. Exemplet visar sedan att ett försök att allokera utrymme i tempdb som överskrider gränsen avbryts.

1. Skapa en arbetsbelastningsgrupp och begränsa dess tempdb utrymmesförbrukning till 1 MB.

   CREATE WORKLOAD GROUP limited_tempdb_space_group
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 1);
   

2. Skapa klassificerarfunktionen i master databasen. Klassificeraren använder den inbyggda funktionen APP_NAME för att fastställa programnamnet som anges i klientanslutningssträngen. Om programnamnet är inställt på limited_tempdb_application returnerar funktionen limited_tempdb_space_group som namnet på den arbetsbelastningsgrupp som ska användas. Annars returnerar funktionen default som namnet på arbetsbelastningsgruppen.

   USE master;
   GO
   
   CREATE FUNCTION dbo.rg_classifier()
   RETURNS sysname
   WITH SCHEMABINDING
   AS
   BEGIN
   
   DECLARE @WorkloadGroupName sysname = N'default';
   
   IF APP_NAME() = N'limited_tempdb_application'
       SELECT @WorkloadGroupName = N'limited_tempdb_space_group';
   
   RETURN @WorkloadGroupName;
   
   END;
   GO
   

3. Ändra resursguvernören så att funktionen klassificerare används och konfigurera om resursguvernören så att den nya konfigurationen används.

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

4. Öppna en ny session som klassificeras i limited_tempdb_space_group arbetsbelastningsgruppen.

   1. I SQL Server Management Studio (SSMS) väljer du File på huvudmenyn, New, Database Engine Query.

   2. I dialogrutan Anslut till databasmotor anger du samma databasmotorinstans där du skapade arbetsbelastningsgruppen och klassificerarfunktionen i föregående steg.

      Välj fliken Ytterligare anslutningsparametrar och ange App=limited_tempdb_application. Detta gör att SSMS använder limited_tempdb_application som programnamn när du ansluter till instansen. Funktionen APP_NAME() i klassificeraren returnerar även det här värdet.

   3. Välj Anslut för att öppna en ny session.

5. Kör följande instruktion i frågefönstret som öppnades i föregående steg. Utdata bör visa att sessionen klassificeras i limited_tempdb_space_group arbetsbelastningsgruppen.

   SELECT wg.name AS workload_group_name
   FROM sys.dm_exec_sessions AS s
   INNER JOIN sys.dm_resource_governor_workload_groups AS wg
   ON s.group_id = wg.group_id
   WHERE s.session_id = @@SPID;
   

6. Kör följande instruktion i samma frågefönster.

   SELECT REPLICATE('S', 100) AS c
   INTO #t1;
   

   Uttalandet slutförs framgångsrikt. Kör följande instruktion i samma frågefönster:

   SELECT REPLICATE(CAST ('F' AS NVARCHAR (MAX)), 1000000) AS c
   INTO #t2;
   

   Instruktionen avbryts med fel 1138 eftersom den försöker överskrida förbrukningsgränsen på 1 MB tempdb för arbetsbelastningsgruppen.

7. Se aktuell och högsta tempdb utrymmesförbrukning för limited_tempdb_space_group arbetsbelastningsgruppen.

   SELECT group_id,
          name,
          tempdb_data_space_kb,
          peak_tempdb_data_space_kb,
          total_tempdb_data_limit_violation_count
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'limited_tempdb_space_group';
   

   Värdet i total_tempdb_data_limit_violation_count kolumnen är 1, vilket visar att en begäran i den här arbetsbelastningsgruppen avbröts eftersom dess tempdb utrymmesförbrukning begränsades av resursguvernören.

8. Om du vill återgå till den inledande konfigurationen av det här exemplet kan du koppla från alla sessioner med hjälp av limited_tempdb_space_group arbetsbelastningsgruppen och köra följande T-SQL-skript:

   /* Disable resource governor so that the classifier function can be dropped. */
   ALTER RESOURCE GOVERNOR DISABLE;
   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
   DROP FUNCTION IF EXISTS dbo.rg_classifier;
   
   /* Drop the workload group. This requires that no sessions are using this workload group. */
   DROP WORKLOAD GROUP limited_tempdb_space_group;
   
   /* Reconfigure resource governor to reload the effective configuration without the classifier function and the workload group. This enables resource governor. */
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   
   /* Disable resource governor to revert to the initial configuration. */
   ALTER RESOURCE GOVERNOR DISABLE;
   

   Eftersom SSMS behåller anslutningsparametrarna på fliken Ytterligare anslutningsparametrar måste du ta bort parametern App nästa gång du ansluter till samma databasmotorinstans. Detta förhindrar att dina anslutningar klassificeras i limited_tempdb_space_group arbetsbelastningsgruppen om den finns.

Relaterat innehåll

• Styrning av tempdb-utrymmesresurser
• Resursguvernör
• Självstudie: Konfigurationsexempel för resursguvernör och metodtips
• ÄNDRA RESURSGUVERNÖR (Transact-SQL)
• SKAPA ARBETSBELASTNINGSGRUPP (Transact-SQL)
• ÄNDRA ARBETSBELASTNINGSGRUPP (Transact-SQL)
• SLÄPP ARBETSBELASTNINGSGRUPP (Transact-SQL)
• sys.resource_governor_workload_groups
• sys.dm_resource_governor_workload_groups