Justera program och databaser för prestanda i Azure SQL Database

Gäller för:Azure SQL Database

När du har identifierat ett prestandaproblem med Azure SQL Database är den här artikeln utformad för att hjälpa dig:

• Finjustera ditt program och tillämpa några metodtips som kan förbättra prestandan.
• Justera databasen genom att ändra index och frågor så att den fungerar mer effektivt med data.

Den här artikeln förutsätter att du redan har gått igenom rekommendationerna för Azure SQL Database-databasrådgivaren och automatiska justeringsrekommendationer, om tillämpligt. Det förutsätter också att du har granskat översikten över övervakning och justering, Övervaka prestanda med hjälp av Query Store och relaterade artiklar som rör felsökning av prestandaproblem. Dessutom förutsätter den här artikeln att du inte har något prestandaproblem som rör processorresursanvändning som kan lösas genom att öka beräkningsstorleken eller tjänstnivån för att tillhandahålla fler resurser till databasen.

Justera programmet

I traditionell lokal SQL Server separeras ofta processen för inledande kapacitetsplanering från processen för att köra ett program i produktion. Maskinvaru- och produktlicenser köps först och prestandajustering görs efteråt. När du använder Azure SQL är det en bra idé att sammanlänka processen med att köra ett program och justera det. Med modellen för att betala för kapacitet på begäran kan du justera programmet så att det använder de minsta resurser som behövs nu, i stället för överetablering på maskinvara baserat på gissningar om framtida tillväxtplaner för ett program, vilket ofta är felaktigt.

Vissa kunder kan välja att inte finjustera ett program och i stället välja att överetablera maskinvaruresurser. Den här metoden kan vara en bra idé om du inte vill ändra ett nyckelprogram under en upptagen period. Men om du justerar ett program kan du minimera resurskraven och sänka månatliga fakturor.

Metodtips och antimönster i programdesign för Azure SQL Database

Även om Tjänstnivåerna i Azure SQL Database är utformade för att förbättra prestandastabiliteten och förutsägbarheten för ett program, kan vissa metodtips hjälpa dig att finjustera ditt program för att bättre dra nytta av resurserna med en beräkningsstorlek. Även om många program har betydande prestandavinster genom att växla till en högre beräkningsstorlek eller tjänstnivå, behöver vissa program ytterligare justering för att dra nytta av en högre tjänstnivå. För ökad prestanda bör du överväga ytterligare programjustering för program som har följande egenskaper:

• Program som har långsamma prestanda på grund av "chattigt" beteende

  Chattiga program gör överdrivna dataåtkomståtgärder som är känsliga för nätverksfördröjning. Du kan behöva ändra den här typen av program för att minska antalet dataåtkomståtgärder till databasen. Du kan till exempel förbättra programmets prestanda med hjälp av tekniker som att batcha ad hoc-frågor eller flytta frågorna till lagrade procedurer. Mer information finns i Batch-frågor.

• Databaser med en intensiv arbetsbelastning som inte kan stödjas av en hel enskild dator

  Databaser som överskrider resurserna med den högsta Premium-beräkningsstorleken kan ha nytta av att skala ut arbetsbelastningen. Mer information finns i Partitionering mellan databaser och funktionell partitionering.

• Program som har suboptimala frågor

  Program som har dåligt anpassade frågor kanske inte har en högre beräkningsstorlek. Detta inkluderar frågor som saknar en WHERE-sats, saknar index eller har inaktuell statistik. Dessa program drar nytta av standardtekniker för frågeprestandajustering. Mer information finns i Saknade index och Frågejustering och tips.

• Program som har en underoptimal dataåtkomstdesign

  Program som har problem med samtidighet i dataåtkomst, till exempel dödläge, kanske inte drar nytta av en högre beräkningsstorlek. Överväg att minska antalet turer mot databasen genom att cachelagra data på klientsidan med Azure Cachelagring-tjänsten eller någon annan cachelagringsteknik. Se Cachelagring på programnivå.

  Information om hur du förhindrar att dödlägen upprepas i Azure SQL Database finns i Analysera och förhindra dödlägen i Azure SQL Database.

Justera databasen

I det här avsnittet tittar vi på några tekniker som du kan använda för att finjustera databasen för att få bästa prestanda för ditt program och köra den med lägsta möjliga beräkningsstorlek. Vissa av dessa tekniker matchar traditionella metodtips för SQL Server-justering, men andra är specifika för Azure SQL Database. I vissa fall kan du undersöka de förbrukade resurserna för en databas för att hitta områden för att finjustera och utöka traditionella SQL Server-tekniker så att de fungerar i Azure SQL Database.

Identifiera och lägga till saknade index

Ett vanligt problem i OLTP-databasprestanda är relaterat till den fysiska databasdesignen. Ofta utformas och levereras databasscheman utan testning i stor skala (antingen i belastning eller i datavolym). Tyvärr kan prestandan för en frågeplan vara acceptabel i liten skala men försämras avsevärt under datavolymer på produktionsnivå. Den vanligaste källan till det här problemet är bristen på lämpliga index för att uppfylla filter eller andra begränsningar i en fråga. Ofta visas saknade index som en tabellgenomsökning när en indexsökning kan räcka.

I det här exemplet använder den valda frågeplanen en genomsökning när en sökning räcker:

DROP TABLE dbo.missingindex;
CREATE TABLE dbo.missingindex (col1 INT IDENTITY PRIMARY KEY, col2 INT);
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
    WHILE @a < 20000
    BEGIN
        INSERT INTO dbo.missingindex(col2) VALUES (@a);
        SET @a += 1;
    END
    COMMIT TRANSACTION;
    GO
SELECT m1.col1
    FROM dbo.missingindex m1 INNER JOIN dbo.missingindex m2 ON(m1.col1=m2.col1)
    WHERE m1.col2 = 4;

Azure SQL Database kan hjälpa dig att hitta och åtgärda vanliga indexvillkor som saknas. DMV:er som är inbyggda i Azure SQL Database tittar på frågekompileringar där ett index avsevärt skulle minska den uppskattade kostnaden för att köra en fråga. Under frågekörningen spårar databasmotorn hur ofta varje frågeplan körs och spårar det uppskattade avståndet mellan den körande frågeplanen och den inbillade där indexet fanns. Du kan använda dessa DMV:er för att snabbt gissa vilka ändringar i din fysiska databasdesign som kan förbättra den totala arbetsbelastningskostnaden för en databas och dess verkliga arbetsbelastning.

Du kan använda den här frågan för att utvärdera potentiella index som saknas:

SELECT
   CONVERT (varchar, getdate(), 126) AS runtime
   , mig.index_group_handle
   , mid.index_handle
   , CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS improvement_measure
   , 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + '
        (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL
        AND mid.inequality_columns IS NOT NULL
        THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
   , migs.*
   , mid.database_id
   , mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
   INNER JOIN sys.dm_db_missing_index_group_stats AS migs
      ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS mid
      ON mig.index_handle = mid.index_handle
 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

I det här exemplet resulterade frågan i det här förslaget:

CREATE INDEX missing_index_5006_5005 ON [dbo].[missingindex] ([col2])  

När den har skapats väljer samma SELECT-instruktion en annan plan, som använder en sökning i stället för en genomsökning och sedan kör planen mer effektivt:

Den viktigaste insikten är att I/O-kapaciteten för ett delat råvarusystem är mer begränsad än för en dedikerad serverdator. Det finns en premie för att minimera onödig I/O för att dra största möjliga nytta av systemet i resurserna för varje beräkningsstorlek på tjänstnivåerna. Lämpliga designval för fysiska databaser kan avsevärt förbättra svarstiden för enskilda frågor, förbättra dataflödet för samtidiga begäranden som hanteras per skalningsenhet och minimera de kostnader som krävs för att uppfylla frågan.

Mer information om hur du justerar index med hjälp av saknade indexbegäranden finns i Finjustera icke-illustrerade index med indexförslag som saknas.

Frågejustering och tips

Frågeoptimeraren i Azure SQL Database liknar den traditionella SQL Server-frågeoptimeraren. De flesta metodtipsen för att justera frågor och förstå begränsningarna i resonemangsmodellen för frågeoptimeraren gäller även för Azure SQL Database. Om du finjusterar frågor i Azure SQL Database kan du få ytterligare fördelar med att minska de aggregerade resursbehoven. Ditt program kanske kan köras till en lägre kostnad än en otunnad motsvarighet eftersom det kan köras med en lägre beräkningsstorlek.

Ett exempel som är vanligt i SQL Server och som även gäller för Azure SQL Database är hur frågeoptimeraren "sniffar" parametrar. Under kompileringen utvärderar frågeoptimeraren det aktuella värdet för en parameter för att avgöra om den kan generera en mer optimal frågeplan. Även om den här strategin ofta kan leda till en frågeplan som är betydligt snabbare än en plan som kompilerats utan kända parametervärden, fungerar den för närvarande ofullständigt både i Azure SQL Database. (En ny intelligent frågeprestandafunktion som introducerades med SQL Server 2022 med namnet Optimering av parameterkänslighetsplan adresserar scenariot där en enda cachelagrad plan för en parametriserad fråga inte är optimal för alla möjliga inkommande parametervärden. För närvarande är optimering av parameterkänslighetsplan inte tillgängligt i Azure SQL Database.)

Databasmotorn stöder frågetips (direktiv) så att du kan ange avsikt mer avsiktligt och åsidosätta standardbeteendet för parametersniffning. Du kan välja att använda tips när standardbeteendet är ofullkomligt för en viss arbetsbelastning.

Nästa exempel visar hur frågeprocessorn kan generera en plan som är suboptimal för både prestanda- och resurskrav. Det här exemplet visar också att om du använder ett frågetips kan du minska frågekörningstiden och resurskraven för databasen:

DROP TABLE psptest1;
CREATE TABLE psptest1(col1 int primary key identity, col2 int, col3 binary(200));
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
   WHILE @a < 20000
   BEGIN
     INSERT INTO psptest1(col2) values (1);
     INSERT INTO psptest1(col2) values (@a);
     SET @a += 1;
   END
   COMMIT TRANSACTION
   CREATE INDEX i1 on psptest1(col2);
GO

CREATE PROCEDURE psp1 (@param1 int)
   AS
   BEGIN
      INSERT INTO t1 SELECT * FROM psptest1
      WHERE col2 = @param1
      ORDER BY col2;
    END
    GO

CREATE PROCEDURE psp2 (@param2 int)
   AS
   BEGIN
      INSERT INTO t1 SELECT * FROM psptest1 WHERE col2 = @param2
      ORDER BY col2
      OPTION (OPTIMIZE FOR (@param2 UNKNOWN))
   END
   GO

CREATE TABLE t1 (col1 int primary key, col2 int, col3 binary(200));
GO

Installationskoden skapar skeva (eller oregelbundet distribuerade) data i t1 tabellen. Den optimala frågeplanen skiljer sig åt beroende på vilken parameter som väljs. Tyvärr kompilera inte planens cachelagringsbeteende alltid om frågan baserat på det vanligaste parametervärdet. Det är därför möjligt att cachelagras och användas för många värden, även om en annan plan kan vara ett bättre planval i genomsnitt. Sedan skapar frågeplanen två lagrade procedurer som är identiska, förutom att den ena har ett särskilt frågetips.

-- Prime Procedure Cache with scan plan
EXEC psp1 @param1=1;
TRUNCATE TABLE t1;

-- Iterate multiple times to show the performance difference
DECLARE @i int = 0;
WHILE @i < 1000
   BEGIN
      EXEC psp1 @param1=2;
      TRUNCATE TABLE t1;
      SET @i += 1;
    END

Vi rekommenderar att du väntar minst 10 minuter innan du börjar del 2 i exemplet, så att resultaten skiljer sig åt i resulterande telemetridata.

EXEC psp2 @param2=1;
TRUNCATE TABLE t1;

DECLARE @i int = 0;
    WHILE @i < 1000
    BEGIN
        EXEC psp2 @param2=2;
        TRUNCATE TABLE t1;
        SET @i += 1;
    END

Varje del av det här exemplet försöker köra en parameteriserad insert-instruktion 1 000 gånger (för att generera en tillräcklig belastning för att kunna använda som en testdatauppsättning). När den kör lagrade procedurer undersöker frågeprocessorn parametervärdet som skickas till proceduren under den första kompileringen (parametern "sniffing"). Processorn cachelagrar den resulterande planen och använder den för senare anrop, även om parametervärdet är annorlunda. Den optimala planen kanske inte används i alla fall. Ibland måste du vägleda optimeraren att välja en plan som är bättre för det genomsnittliga fallet i stället för det specifika fallet från när frågan först kompilerades. I det här exemplet genererar den första planen en "genomsökningsplan" som läser alla rader för att hitta varje värde som matchar parametern:

Eftersom vi utförde proceduren med hjälp av värdet 1var den resulterande planen optimal för värdet 1 men var suboptimal för alla andra värden i tabellen. Resultatet är förmodligen inte vad du skulle vilja om du skulle välja varje plan slumpmässigt, eftersom planen går långsammare och använder fler resurser.

Om du kör testet med SET STATISTICS IO inställt på ONutförs det logiska genomsökningsarbetet i det här exemplet i bakgrunden. Du kan se att det finns 1 148 läsningar gjorda av planen (vilket är ineffektivt, om det genomsnittliga fallet bara är att returnera en rad):

Den andra delen av exemplet använder ett frågetips för att instruera optimeraren att använda ett specifikt värde under kompileringsprocessen. I det här fallet tvingar det frågeprocessorn att ignorera värdet som skickas som parameter och i stället anta UNKNOWN. Detta refererar till ett värde som har den genomsnittliga frekvensen i tabellen (ignorerar skevhet). Den resulterande planen är en sökbaserad plan som är snabbare och använder färre resurser i genomsnitt än planen i del 1 av det här exemplet:

Du kan se effekten i sys.resource_stats systemvyn, som är specifik för Azure SQL Database. Det finns en fördröjning från den tid då du kör testet och när data fyller i tabellen. I det här exemplet körs del 1 under tidsperioden 22:25:00 och del 2 körs kl. 22:35:00. I det tidigare tidsfönstret användes fler resurser i det tidsfönstret än det senare (på grund av förbättringar av planeffektiviteten).

SELECT TOP 1000 *
FROM sys.resource_stats
WHERE database_name = 'resource1'
ORDER BY start_time DESC

Du kan undersöka sys.resource_stats om resursen för ett test använder fler eller färre resurser än ett annat test. När du jämför data separerar du tidpunkten för testerna så att de inte befinner sig i samma 5-minutersfönster i sys.resource_stats vyn. Målet med övningen är att minimera den totala mängden resurser som används och inte minimera de högsta resurserna. I allmänhet minskar även resursförbrukningen genom att optimera en kod för svarstid. Kontrollera att de ändringar du gör i ett program är nödvändiga och att ändringarna inte påverkar kundupplevelsen negativt för någon som kanske använder frågetips i programmet.

Om en arbetsbelastning har en uppsättning upprepade frågor är det ofta klokt att samla in och verifiera optimaliteten för dina planval eftersom den styr den minsta resursstorleksenhet som krävs för att vara värd för databasen. När du har verifierat det går det ibland att ompröva planerna för att se till att de inte har försämrats. Du kan lära dig mer om frågetips (Transact-SQL).

Metodtips för mycket stora databasarkitekturer i Azure SQL Database

Innan hyperskala-tjänstnivån för enskilda databaser i Azure SQL Database lanseras kan kunderna stöta på kapacitetsgränser för enskilda databaser. Även om elastiska hyperskalapooler (förhandsversion) erbjuder betydligt högre lagringsgränser, kan elastiska pooler och pooldatabaser på andra tjänstnivåer fortfarande begränsas av dessa lagringskapacitetsgränser på tjänstnivåerna för icke-Hyperskala.

I följande två avsnitt beskrivs två alternativ för att lösa problem med mycket stora databaser i Azure SQL Database när du inte kan använda tjänstnivån Hyperskala.

Horisontell partitionering mellan databaser

Eftersom Azure SQL Database körs på vanlig maskinvara är kapacitetsgränserna för en enskild databas lägre än för en traditionell lokal SQL Server-installation. Vissa kunder använder horisontell partitioneringstekniker för att sprida databasåtgärder över flera databaser när åtgärderna inte passar inom gränserna för en enskild databas i Azure SQL Database. De flesta kunder som använder horisontell partitioneringstekniker i Azure SQL Database delar upp sina data på en enda dimension i flera databaser. För den här metoden måste du förstå att OLTP-program ofta utför transaktioner som endast gäller för en rad eller för en liten grupp rader i schemat.

Om en databas till exempel har kundnamn, order- och orderinformation (till exempel i AdventureWorks databasen) kan du dela upp dessa data i flera databaser genom att gruppera en kund med den relaterade beställnings- och orderinformationen. Du kan garantera att kundens data finns kvar i en enskild databas. Programmet skulle dela upp olika kunder mellan databaser och effektivt sprida belastningen över flera databaser. Med horisontell partitionering kan kunderna inte bara undvika den maximala databasstorleksgränsen, utan Azure SQL Database kan också bearbeta arbetsbelastningar som är betydligt större än gränserna för de olika beräkningsstorlekarna, så länge varje enskild databas passar in i tjänstnivågränserna.

Även om databassharding inte minskar den aggregerade resurskapaciteten för en lösning är det mycket effektivt att stödja mycket stora lösningar som är spridda över flera databaser. Varje databas kan köras med olika beräkningsstorlek för att stödja mycket stora, "effektiva" databaser med höga resurskrav.

Funktionell partitionering

Användare kombinerar ofta många funktioner i en enskild databas. Om ett program till exempel har logik för att hantera lager för ett lager kan databasen ha logik associerad med inventering, spårning av inköpsorder, lagrade procedurer och indexerade eller materialiserade vyer som hanterar rapportering i slutet av månaden. Den här tekniken gör det enklare att administrera databasen för åtgärder som säkerhetskopiering, men du måste också ändra storleken på maskinvaran för att hantera den högsta belastningen för alla funktioner i ett program.

Om du använder en skalbar arkitektur i Azure SQL Database är det en bra idé att dela upp olika funktioner i ett program i olika databaser. Om du använder den här tekniken skalas varje program separat. När ett program blir mer omfattande (och belastningen på databasen ökar) kan administratören välja oberoende beräkningsstorlekar för varje funktion i programmet. Med den här arkitekturen kan ett program vara större än vad en enskild råvarudator kan hantera eftersom belastningen är spridd över flera datorer.

Batchfrågor

För program som har åtkomst till data med hög volym, frekventa, ad hoc-frågor, spenderas en betydande mängd svarstid på nätverkskommunikation mellan programnivån och databasnivån. Även om både programmet och databasen finns i samma datacenter kan nätverksfördröjningen mellan de två förstoras av ett stort antal dataåtkomståtgärder. Om du vill minska nätverksresorna för dataåtkomståtgärder kan du överväga att använda alternativet för att antingen batcha ad hoc-frågorna eller kompilera dem som lagrade procedurer. Om du batchar ad hoc-frågorna kan du skicka flera frågor som en stor batch i en enda resa till databasen. Om du kompilerar ad hoc-frågor i en lagrad procedur kan du uppnå samma resultat som om du batchar dem. Genom att använda en lagrad procedur kan du också öka risken för cachelagring av frågeplaner i databasen så att du kan använda den lagrade proceduren igen.

Vissa program är skrivintensiva. Ibland kan du minska den totala I/O-belastningen på en databas genom att överväga hur du batchar skrivningar tillsammans. Detta är ofta så enkelt som att använda explicita transaktioner i stället för att automatiskt återuppta transaktioner i lagrade procedurer och ad hoc-batchar. En utvärdering av olika tekniker som du kan använda finns i Batchbearbetningstekniker för databasprogram i Azure. Experimentera med din egen arbetsbelastning för att hitta rätt modell för batchbearbetning. Se till att förstå att en modell kan ha lite olika garantier för transaktionskonsekvens. Att hitta rätt arbetsbelastning som minimerar resursanvändningen kräver att du hittar rätt kombination av konsekvens- och prestandavägningar.

Cachelagring på programnivå

Vissa databasprogram har läsintensiva arbetsbelastningar. Cachelagring lager kan minska belastningen på databasen och eventuellt minska den beräkningsstorlek som krävs för att stödja en databas med hjälp av Azure SQL Database. Om du har en läsintensiv arbetsbelastning i Azure Cache for Redis kan du läsa data en gång (eller kanske en gång per dator på programnivå, beroende på hur de har konfigurerats) och sedan lagra dessa data utanför databasen. Det här är ett sätt att minska databasbelastningen (CPU och läs-I/O), men det påverkar transaktionskonsekvensen eftersom data som läses från cacheminnet kan vara osynkroniserade med data i databasen. Även om viss inkonsekvens är acceptabel i många program är det inte sant för alla arbetsbelastningar. Du bör förstå alla programkrav innan du implementerar en cachelagringsstrategi på programnivå.

Få konfigurations- och designtips

Om du använder Azure SQL Database kan du köra ett T-SQL-skript med öppen källkod för att förbättra databaskonfigurationen och designen i Azure SQL Database. Skriptet analyserar databasen på begäran och ger tips för att förbättra databasens prestanda och hälsa. Vissa tips föreslår konfiguration och driftsändringar baserat på bästa praxis, medan andra tips rekommenderar designändringar som passar din arbetsbelastning, till exempel att aktivera avancerade databasmotorfunktioner.

Mer information om skriptet och kom igång finns på wikisidan för Azure SQL Tips.

Relaterat innehåll

• Lär dig mer om den DTU-baserade inköpsmodellen
• Läs mer om köpmodellen baserad på virtuell kärna
• Läs Vad är en elastisk Azure-pool?
• Upptäck När du ska överväga en elastisk pool
• Läs mer om Övervakning av prestanda med dynamiska hanteringsvyer
• Lär dig att diagnostisera och felsöka hög CPU i Azure SQL Database
• Ställ in icke-klustrade index med förslag på index som saknas
• Video: Metodtips för datainläsning i Azure SQL Database
• Övervaka Azure SQL Database med Azure Monitor