Delen via

Problemen met queryprestaties tussen databasetoepassing en SSMS oplossen

  • Artikel

Wanneer u een query uitvoert in een databasetoepassing, wordt deze langzamer uitgevoerd dan dezelfde query in een toepassing zoals SQL Server Management Studio (SSMS), Azure Data Studio of SQLCMD.

Dit probleem kan een of meer van de volgende oorzaken hebben:

  • Query's gebruiken verschillende parameters of variabelen.

  • Query's worden verzonden naar de server via verschillende netwerken of er is een verschil hoe de toepassingen gegevens verwerken.

  • SET-opties in de databasetoepassing en SSMS verschillen.

Volg deze stappen om het probleem op te lossen:

Stap 1: controleer of de query's zijn verzonden met dezelfde parameters of variabelen

Voer de volgende stappen uit om deze query's te vergelijken en ervoor te zorgen dat ze in alle opzichten identiek zijn:

  1. Open uw SSMS en verbind deze met de database-engine die u gebruikt.

  2. Voer de volgende opdrachten uit om een sessie met uitgebreide gebeurtenissen te maken:

    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

    Opmerking

    Vervang de tijdelijke aanduidingen <EventSessionName> en <FilePath> door de aanduidingen die u wilt maken.

  3. Voer de volgende opdrachten uit om de sessie EventSessionName te starten:

    ALTER EVENT SESSION <EventSessionName> ON SERVER
STATE = START

  4. Voer uw query's uit om het probleem te reproduceren.

  5. Gebruik een van de volgende methoden om de verzamelde gegevens te analyseren:

    • Open Windows Verkenner, zoek het doel-.xel-bestand en dubbelklik erop. Het bestand wordt geopend in een ander SSMS-venster dat u kunt gebruiken om te bekijken en te analyseren.

    • Vouw in ObjectverkennerUitgebreide gebeurtenissen>voor beheersessies>>EventSessionName uit, klik met de rechtermuisknop op package0.event_file en selecteer vervolgens Doelgegevens weergeven....

    • Zoek de locatie van de .xel-bestanden en lees dit bestand met behulp van de functie sys.fn_xe_file_target_read_file.

  6. Vergelijk de veldinstructie door de volgende gebeurtenissen te controleren:

    • sp_statement_completed
    • sql_batch_completed
    • sql_statement_completed
    • rpc_completed

Zie de volgende voorbeelden voor meer informatie over de identieke query's:

  • Als de opgeslagen procedures of functies verschillende parameterwaarden hebben, kunnen de querytijden verschillen:

    • SpUserProc @p1 = 100

    • SpUserProc @p1 = 270

  • De volgende query's verschillen. De eerste query maakt gebruik van gemiddelde dichtheid uit het histogram voor een schatting van de kardinaliteit, terwijl de tweede query de histogramstap gebruikt voor de schatting van de kardinaliteit:

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

    • select * from table where c1 = 123

Om dezelfde reden als hierboven kan het vergelijken van de uitvoering van een opgeslagen procedure met de uitvoering van de equivalente ad-hocquery (met behulp van lokale variabelen) afwijken. Identieke instructies moeten worden vergeleken.

Stap 2: De uitvoeringstijd op de server meten

Voor een nauwkeurige vergelijking van queryduur kunt u de netwerklatentietijd of toepassingsspecifieke verwerkingstijd uitsluiten. Gebruik een van de volgende methoden om alleen de uitvoeringstijd op de SQL Server te meten:

  • Voer uw query uit met behulp van SET STATISTICS TIME:

    SET STATISTICS TIME ON
<YourQuery>
SET STATISTICS TIME OFF

  • Gebruik XEvent uit stap 1 om de duur of verstreken tijd van een query te onderzoeken (gebeurtenisklasse SQL:StmtCompleted, SQL:BatchCompletedof RPC:Completed).

In sommige gevallen kan het tijdsverschil tussen de query's worden veroorzaakt door één toepassing die wordt uitgevoerd in een ander netwerk of de toepassing zelf. Wanneer u de uitvoering op de server vergelijkt, vergelijkt u hoe lang het duurde om de query's op de server uit te voeren.

Stap 3: Set-opties voor elke verbinding controleren

Er zijn SET-opties die van invloed zijn op het queryplan, wat betekent dat ze de keuze van het queryplan kunnen wijzigen. Als een databasetoepassing gebruikmaakt van verschillende setopties van SSMS, kan voor elke ingestelde optie een ander queryplan worden opgehaald. Bijvoorbeeld ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN en ANSI_NULLS. Het meest voorkomende verschil tussen SSMS- en .NET-toepassingen is de optie SET ARITHABORT . De optie is standaard ingesteld op AAN in SSMS, maar in de meeste databasetoepassingen op UIT. Stel ARITHABORT op basis van de behoeften van uw toepassing in op dezelfde instelling in zowel SSMS als in de toepassing voor een geldige vergelijking tussen de twee.

Waarschuwing

De standaardinstelling ARITHABORT voor SQL Server Management Studio is AAN. Clienttoepassingen die ARITHABORT instellen op UIT kunnen verschillende queryplannen ontvangen, waardoor het moeilijk is om slecht uitgevoerde query's op te lossen. Dat wil dus dat dezelfde query snel kan worden uitgevoerd in Management Studio, maar traag in de toepassing. Bij het oplossen van problemen met query's met Management Studio, moet u altijd overeenkomen met de instelling ARITHABORT van de client.

Zie Opties instellen voor een lijst met alle opties die van invloed zijn op het plan.

Voer de volgende stappen uit om ervoor te zorgen dat de SET-opties in zowel SSMS als de toepassing hetzelfde zijn om een geldige vergelijking uit te voeren:

  1. Gebruik de verzamelde gegevens in stap 1.

  2. Vergelijk de ingestelde opties door de gebeurtenissen login en existing_connection, met name de options_text kolommen en opties en te controleren.