Felsöka skillnader i frågeprestanda mellan databasprogram och SSMS

När du kör en fråga i ett databasprogram körs den långsammare än samma fråga i ett program som SQL Server Management Studio (SSMS), Azure Data Studio eller SQLCMD.

Det här problemet kan uppstå av följande skäl:

• Frågor använder olika parametrar eller variabler.

• Frågor skickas till servern via olika nätverk eller så är det skillnad på hur programmen bearbetar data.

• SET-alternativen i databasprogrammet och SSMS skiljer sig.

Felsök problemet med hjälp av följande steg:

Steg 1: Kontrollera att frågorna skickas med samma parametrar eller variabler

Följ dessa steg för att jämföra dessa frågor och se till att de är identiska på alla sätt:

1. Öppna din SSMS och anslut den till den databasmotor som du använder.

2. Kör följande kommandon för att skapa en extended events-session:

   CREATE EVENT SESSION <EventSessionName> ON SERVER
   ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
   ADD EVENT sqlserver.login(SET collect_options_text=(1)
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.rpc_completed,
   ADD EVENT sqlserver.sp_statement_completed(
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.sql_batch_completed(
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.sql_statement_completed(
       ACTION(sqlserver.client_app_name))
   ADD TARGET package0.event_file(SET filename=N'<FilePath>')
   WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 
   SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
   GO
   

   Kommentar

   Byt ut platsmarkörerna <EventSessionName> och <FilePath> med de som du vill skapa.

3. Kör följande kommandon för att starta sessionen EventSessionName:

   ALTER EVENT SESSION <EventSessionName> ON SERVER
   STATE = START
   

4. Kör dina frågor för att återskapa problemet.

5. Använd någon av följande metoder för att analysera insamlade data:

   • Öppna Utforskaren, leta upp .xel-målfilen och dubbelklicka på den. Filen öppnas i ett annat SSMS-fönster som du kan använda för att visa och analysera.

   • I Object Explorer expanderar du Management>Extended Events>Sessions>EventSessionName, högerklickar på package0.event_file och väljer sedan Visa måldata....

   • Hitta platsen för .xel-filerna och läs den här filen med hjälp av funktionen sys.fn_xe_file_target_read_file.

6. Jämför field-instruktionen genom att kontrollera följande händelser:

   • sp_statement_completed
   • sql_batch_completed
   • sql_statement_completed
   • rpc_completed

Mer information om identiska frågor finns i följande exempel:

• Om de lagrade procedurerna eller funktionerna har olika parametervärden kan frågetiderna vara olika:

  • SpUserProc @p1 = 100

  • SpUserProc @p1 = 270

• Följande frågor skiljer sig. Den första frågan använder genomsnittlig densitet från histogrammet för kardinalitetsuppskattning, medan den andra frågan använder histogramsteget för kardinalitetsuppskattning:

  • declare @variable1 = 123
    select * from table where c1 = @variable1
    

  • select * from table where c1 = 123
    

Av samma anledning som ovan kan det vara annorlunda att jämföra körningen av en lagrad procedur med körningen av motsvarande ad hoc-fråga (med hjälp av lokala variabler). Identiska instruktioner måste jämföras.

Steg 2: Mät körningstiden på servern

För en korrekt jämförelse av frågevaraktigheterna kan du exkludera nätverkets svarstid eller programspecifik databehandlingstid. Använd någon av följande metoder för att endast mäta körningstiden på SQL Server:

• Kör frågan med HJÄLP av SET STATISTICS TIME:

  SET STATISTICS TIME ON
  <YourQuery>
  SET STATISTICS TIME OFF
  

• Använd XEvent från steg 1 för att undersöka varaktigheten eller den förflutna tiden för en fråga (händelseklassen SQL:StmtCompleted, SQL:BatchCompletedeller RPC:Completed).

I vissa fall kan tidsskillnaden mellan frågorna orsakas av ett program som körs i ett annat nätverk eller själva programmet. När du jämför körningen på servern jämför du hur lång tid det tog att köra frågorna på servern.

Steg 3: Kontrollera SET-alternativen för varje anslutning

Det finns SET-alternativ som påverkar frågeplanen, vilket innebär att de kan ändra valet av frågeplan. Om ett databasprogram använder olika uppsättningsalternativ från SSMS kan därför varje uppsättningsalternativ få en annan frågeplan. Till exempel ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN och ANSI_NULLS. Den vanligaste skillnaden mellan SSMS- och .NET-program är alternativet SET ARITHABORT . Som standard är alternativet inställt på PÅ i SSMS men inställt på AV i de flesta databasprogram. Baserat på dina programbehov anger du ARITHABORT till samma inställning i både SSMS och programmet för en giltig jämförelse mellan de två.

En lista över alla alternativ som påverkar planen finns i Ange alternativ.

Följ dessa steg för att säkerställa att SET-alternativen i både SSMS och programmet är desamma för att kunna utföra en giltig jämförelse:

1. Använd insamlade data i steg 1.

2. Jämför uppsättningsalternativen genom att kontrollera händelserna login och existing_connection, särskilt kolumnerna options_text och alternativen.