Problemen oplossen met een query die een aanzienlijk prestatieverschil tussen twee servers laat zien

Artikel
06/20/2023

Van toepassing op: SQL Server

Dit artikel bevat stappen voor probleemoplossing voor een prestatieprobleem waarbij een query langzamer wordt uitgevoerd op de ene server dan op een andere server.

Symptomen

Stel dat er twee servers zijn waarop SQL Server zijn geïnstalleerd. Een van de SQL Server exemplaren bevat een kopie van een database in het andere SQL Server exemplaar. Wanneer u een query uitvoert op de databases op beide servers, wordt de query op de ene server langzamer uitgevoerd dan de andere.

De volgende stappen kunnen u helpen dit probleem op te lossen.

Stap 1: bepalen of het een veelvoorkomend probleem is met meerdere query's

Gebruik een van de volgende twee methoden om de prestaties voor twee of meer query's op de twee servers te vergelijken:

Test de query's handmatig op beide servers:
1. Kies verschillende query's voor testen met prioriteit voor query's die:
  - Aanzienlijk sneller op de ene server dan op de andere.
  - Belangrijk voor de gebruiker/toepassing.
  - Vaak uitgevoerd of ontworpen om het probleem op aanvraag te reproduceren.
  - Voldoende lang om er gegevens op vast te leggen (kies bijvoorbeeld in plaats van een query van 5 milliseconden een query van 10 seconden).
2. Voer de query's uit op de twee servers.
3. Vergelijk de verstreken tijd (duur) op twee servers voor elke query.
Prestatiegegevens analyseren met SQL Nexus.
1. Verzamel PSSDiag/SQLdiag - of SQL LogScout-gegevens voor de query's op de twee servers.
2. Importeer de verzamelde gegevensbestanden met SQL Nexus en vergelijk de query's van de twee servers. Zie Prestatievergelijking tussen twee logboekverzamelingen (bijvoorbeeld Traag en Snel) voor meer informatie.

Scenario 1: slechts één query wordt anders uitgevoerd op de twee servers

Als slechts één query anders wordt uitgevoerd, is het probleem waarschijnlijk eerder specifiek voor de afzonderlijke query dan voor de omgeving. Ga in dit geval naar Stap 2: Gegevens verzamelen en bepaal het type prestatieprobleem.

Scenario 2: Meerdere query's worden anders uitgevoerd op de twee servers

Als meerdere query's langzamer worden uitgevoerd op de ene server dan de andere, is de meest waarschijnlijke oorzaak de verschillen in server- of gegevensomgeving. Ga naar Omgevingsverschillen vaststellen en kijk of de vergelijking tussen de twee servers geldig is.

Stap 2: gegevens verzamelen en het type prestatieprobleem bepalen

Verstreken tijd, CPU-tijd en logische leesbewerkingen verzamelen

Als u de verstreken tijd en CPU-tijd van de query op beide servers wilt verzamelen, gebruikt u een van de volgende methoden die het beste bij uw situatie passen:

Controleer total_elapsed_time en cpu_time kolommen in sys.dm_exec_requests voor het uitvoeren van instructies. Voer de volgende query uit om de gegevens op te halen:

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

Voor eerdere uitvoeringen van de query controleert u last_elapsed_time en last_worker_time kolommen in sys.dm_exec_query_stats. Voer de volgende query uit om de gegevens op te halen:

SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
     qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
     qs.total_logical_writes / qs.execution_count AS avg_writes,
     (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

Opmerking

Als avg_wait_time er een negatieve waarde wordt weergegeven, is het een parallelle query.

Als u de query op aanvraag kunt uitvoeren in SQL Server Management Studio (SSMS) of Azure Data Studio, voert u deze uit met SET STATISTICS TIMEON en SET STATISTICS IOON.

SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Vervolgens ziet u in Berichten de CPU-tijd, verstreken tijd en logische leesbewerkingen als volgt:

  Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

Als u een queryplan kunt verzamelen, controleert u de gegevens uit de eigenschappen van het uitvoeringsplan.
1. Voer de query uit met Werkelijke uitvoeringsplan opnemen op.
2. Selecteer de meest linkse operator in Uitvoeringsplan.
3. Vouw in Eigenschappende eigenschap QueryTimeStats uit.
4. Schakel ElapsedTime en CpuTime in.

Vergelijk de verstreken tijd en CPU-tijd van de query om het probleemtype voor beide servers te bepalen.

Type 1: CPU-gebonden (runner)

Als de CPU-tijd bijna, gelijk is aan of hoger is dan de verstreken tijd, kunt u deze behandelen als een CPU-gebonden query. Als de verstreken tijd bijvoorbeeld 3000 milliseconden (ms) is en de CPU-tijd 2900 ms is, betekent dit dat de meeste verstreken tijd wordt besteed aan de CPU. Dan kunnen we zeggen dat het een CPU-gebonden query is.

Voorbeelden van uitgevoerde (CPU-gebonden) query's:

Verstreken tijd (ms)	CPU-tijd (ms)	Leesbewerkingen (logisch)
3200	3000	300000
1080	1000	20

Logische leesbewerkingen - het lezen van gegevens/indexpagina's in de cache - zijn meestal de drivers van het CPU-gebruik in SQL Server. Er kunnen scenario's zijn waarin CPU-gebruik afkomstig is van andere bronnen: een while-lus (in T-SQL of andere code zoals XProcs of SQL CRL-objecten). Het tweede voorbeeld in de tabel illustreert een dergelijk scenario, waarbij het merendeel van de CPU niet afkomstig is van leesbewerkingen.

Opmerking

Als de CPU-tijd langer is dan de duur, geeft dit aan dat er een parallelle query wordt uitgevoerd; meerdere threads gebruiken tegelijkertijd de CPU. Zie Parallelle query's - runner of ober voor meer informatie.

Type 2: Wachten op een knelpunt (ober)

Een query wacht op een knelpunt als de verstreken tijd aanzienlijk groter is dan de CPU-tijd. De verstreken tijd omvat de tijd die de query uitvoert op de CPU (CPU-tijd) en de tijd die moet worden gewacht tot een resource wordt vrijgegeven (wachttijd). Als de verstreken tijd bijvoorbeeld 2000 ms is en de CPU-tijd 300 ms is, is de wachttijd 1700 ms (2000 - 300 = 1700). Zie Typen wachttijden voor meer informatie.

Voorbeelden van wachtende query's:

Verstreken tijd (ms)	CPU-tijd (ms)	Leesbewerkingen (logisch)
2000	300	28000
10080	700	80000

Parallelle query's - runner of ober

Parallelle query's gebruiken mogelijk meer CPU-tijd dan de totale duur. Het doel van parallellisme is om meerdere threads toe te staan om delen van een query tegelijkertijd uit te voeren. In één seconde van de kloktijd kan een query acht seconden CPU-tijd gebruiken door acht parallelle threads uit te voeren. Daarom wordt het lastig om een CPU-gebonden of een wachtquery te bepalen op basis van het verstreken tijds- en CPU-tijdsverschil. In het algemeen geldt echter dat u de principes volgt die in de bovenstaande twee secties worden vermeld. De samenvatting is:

Als de verstreken tijd veel groter is dan de CPU-tijd, beschouwt u het als een ober.
Als de CPU-tijd veel groter is dan de verstreken tijd, beschouwt u het als een hardloper.

Voorbeelden van parallelle query's:

Verstreken tijd (ms)	CPU-tijd (ms)	Leesbewerkingen (logisch)
1200	8100	850000
3080	12300	1500000

Stap 3: gegevens van beide servers vergelijken, het scenario uitzoeken en het probleem oplossen

We gaan ervan uit dat er twee computers zijn met de naam Server1 en Server2. En de query wordt langzamer uitgevoerd op Server1 dan op Server2. Vergelijk de tijden van beide servers en volg vervolgens de acties van het scenario dat het beste overeenkomt met dat van u uit de volgende secties.

Scenario 1: De query op Server1 gebruikt meer CPU-tijd en de logische leesbewerkingen zijn hoger op Server1 dan op Server2

Als de CPU-tijd op Server1 veel groter is dan op Server2 en de verstreken tijd overeenkomt met de CPU-tijd op beide servers, zijn er geen grote wachttijden of knelpunten. De toename van de CPU-tijd op Server1 wordt waarschijnlijk veroorzaakt door een toename van logische leesbewerkingen. Een belangrijke wijziging in logische leesbewerkingen duidt meestal op een verschil in queryplannen. Bijvoorbeeld:

Server	Verstreken tijd (ms)	CPU-tijd (ms)	Leesbewerkingen (logisch)
Server1	3100	3000	300000
Server2	1100	1000	90200

Actie: Uitvoeringsplannen en -omgevingen controleren

De uitvoeringsplannen van de query op beide servers vergelijken. Gebruik hiervoor een van de twee methoden:
- Uitvoeringsplannen visueel vergelijken. Zie Een werkelijke uitvoeringsplan weergeven voor meer informatie.
- Sla de uitvoeringsplannen op en vergelijk ze met behulp van SQL Server Management Studio functie Planvergelijking.
Omgevingen vergelijken. Verschillende omgevingen kunnen leiden tot verschillen in queryplannen of directe verschillen in CPU-gebruik. Omgevingen omvatten serverversies, database- of serverconfiguratie-instellingen, traceringsvlagken, CPU-aantal of kloksnelheid en virtuele machine versus fysieke machine. Zie Verschillen in queryplan vaststellen voor meer informatie.

Scenario 2: De query is een ober op Server1, maar niet op Server2

Als de CPU-tijden voor de query op beide servers vergelijkbaar zijn, maar de verstreken tijd op Server1 veel groter is dan op Server2, besteedt de query op Server1 veel langer aan het wachten op een knelpunt. Bijvoorbeeld:

Server	Verstreken tijd (ms)	CPU-tijd (ms)	Leesbewerkingen (logisch)
Server1	4500	1000	90200
Server2	1100	1000	90200

Wachttijd op Server1: 4500 - 1000 = 3500 ms
Wachttijd op Server2: 1100 - 1000 = 100 ms

Actie: Wachttypen controleren op Server1

Identificeer en elimineer het knelpunt op Server1. Voorbeelden van wachttijden zijn blokkeren (vergrendelingswachttijden), vergrendelingswachttijden, I/O-wachttijden voor schijven, netwerkwachttijden en geheugenwachttijden. Als u veelvoorkomende knelpunten wilt oplossen, gaat u naar Wacht- of knelpunten vaststellen.

Scenario 3: De query's op beide servers zijn obers, maar de wachttypen of -tijden verschillen

Bijvoorbeeld:

Server	Verstreken tijd (ms)	CPU-tijd (ms)	Leesbewerkingen (logisch)
Server1	8000	1000	90200
Server2	3000	1000	90200

Wachttijd op Server1: 8000 - 1000 = 7000 ms
Wachttijd op Server2: 3000 - 1000 = 2000 ms

In dit geval zijn de CPU-tijden op beide servers vergelijkbaar, wat aangeeft dat queryplannen waarschijnlijk hetzelfde zijn. De query's worden op beide servers gelijk uitgevoerd als ze niet wachten op de knelpunten. De verschillen in duur zijn dus afhankelijk van de verschillende wachttijden. De query wacht bijvoorbeeld op vergrendelingen op Server1 gedurende 7000 ms, terwijl deze op I/O op Server2 2000 ms wacht.

Actie: Wachttypen op beide servers controleren

Los elk knelpunt afzonderlijk op elke server op en versnel de uitvoeringen op beide servers. Het oplossen van dit probleem is arbeidsintensief omdat u knelpunten op beide servers moet elimineren en de prestaties vergelijkbaar moet maken. Als u veelvoorkomende knelpunten wilt oplossen, gaat u naar Wacht- of knelpunten vaststellen.

Scenario 4: De query op Server1 gebruikt meer CPU-tijd dan op Server2, maar de logische leesbewerkingen zijn dichtbij

Bijvoorbeeld:

Server	Verstreken tijd (ms)	CPU-tijd (ms)	Leesbewerkingen (logisch)
Server1	3000	3000	90200
Server2	1000	1000	90200

Als de gegevens voldoen aan de volgende voorwaarden:

De CPU-tijd op Server1 is veel groter dan op Server2.
De verstreken tijd komt nauw overeen met de CPU-tijd op elke server, wat aangeeft dat er geen wachttijden zijn.
De logische leesbewerkingen, meestal het hoogste stuurprogramma van CPU-tijd, zijn vergelijkbaar op beide servers.

Vervolgens is de extra CPU-tijd afkomstig van enkele andere CPU-gebonden activiteiten. Dit scenario is het zeldzaamste van alle scenario's.

Oorzaken: Tracering, UDF's en CLR-integratie

Dit probleem kan worden veroorzaakt door:

XEvents/SQL Server tracering, met name met het filteren op tekstkolommen (databasenaam, aanmeldingsnaam, querytekst, enzovoort). Als tracering is ingeschakeld op de ene server, maar niet op de andere, kan dit de reden voor het verschil zijn.
Door de gebruiker gedefinieerde functies (UDF's) of andere T-SQL-code waarmee CPU-gebonden bewerkingen worden uitgevoerd. Dit is meestal de oorzaak wanneer andere omstandigheden verschillen op Server1 en Server2, zoals gegevensgrootte, CPU-kloksnelheid of Energiebeheer.
SQL Server CLR-integratie of Extended Stored-procedures (XPs) die de CPU kunnen aandrijven, maar geen logische leesbewerkingen uitvoeren. Verschillen in de DLL's kunnen leiden tot verschillende CPU-tijden.
Verschil in SQL Server functionaliteit die CPU-gebonden is (bijvoorbeeld tekenreeksmanipulatiecode).

Actie: Traceringen en query's controleren

Controleer traceringen op beide servers op het volgende:
1. Als er een tracering is ingeschakeld op Server1, maar niet op Server2.
2. Als een tracering is ingeschakeld, schakelt u de tracering uit en voert u de query opnieuw uit op Server1.
3. Als de query deze keer sneller wordt uitgevoerd, schakelt u de tracering terug in, maar verwijdert u er tekstfilters uit, indien aanwezig.
Controleer of de query gebruikmaakt van UDF's die tekenreeksbewerkingen uitvoeren of uitgebreide verwerking uitvoeren op gegevenskolommen in de SELECT lijst.
Controleer of de query lussen, functie-recursies of geneste functies bevat.

Omgevingsverschillen vaststellen

Controleer de volgende vragen en bepaal of de vergelijking tussen de twee servers geldig is.

Zijn de twee SQL Server exemplaren dezelfde versie of build?

Als dat niet het probleem is, kunnen er enkele oplossingen zijn die de verschillen hebben veroorzaakt. Voer de volgende query uit om versiegegevens op beide servers op te halen:
```
SELECT @@VERSION
```
Is de hoeveelheid fysiek geheugen op beide servers vergelijkbaar?

Als de ene server 64 GB geheugen heeft en de andere 256 GB geheugen heeft, zou dat een aanzienlijk verschil zijn. Omdat er meer geheugen beschikbaar is voor de cache van gegevens/indexpagina's en queryplannen, kan de query anders worden geoptimaliseerd op basis van de beschikbaarheid van hardwareresources.
Zijn CPU-gerelateerde hardwareconfiguraties op beide servers vergelijkbaar? Bijvoorbeeld:
- Het aantal CPU's verschilt per computer (24 CPU's op de ene computer versus 96 CPU's op de andere).
- Energiebeheerschema's: gebalanceerd versus hoge prestaties.
- Virtuele machine (VM) versus fysieke (bare-metal) machine.
- Hyper-V versus VMware: verschil in configuratie.
- Verschil in kloksnelheid (lagere kloksnelheid versus hogere kloksnelheid). 2 GHz versus 3,5 GHz kan bijvoorbeeld een verschil maken. Voer de volgende PowerShell-opdracht uit om de kloksnelheid op een server op te halen:
```
Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
```
Gebruik een van de volgende twee manieren om de CPU-snelheid van de servers te testen. Als ze geen vergelijkbare resultaten opleveren, ligt het probleem buiten SQL Server. Dit kan een verschil in energiebeheerschema, minder CPU's, VM-softwareprobleem of een verschil in kloksnelheid zijn.
- Voer het volgende PowerShell-script uit op beide servers en vergelijk de uitvoer.
```
$bf = [System.DateTime]::Now
for ($i = 0; $i -le 20000000; $i++) {}
$af = [System.DateTime]::Now
Write-Host ($af - $bf).Milliseconds " milliseconds"
Write-Host ($af - $bf).Seconds " Seconds"
```
- Voer de volgende Transact-SQL-code uit op beide servers en vergelijk de uitvoer.
```
SET NOCOUNT ON 
DECLARE @spins INT = 0
DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT

WHILE (@spins < 20000000)
BEGIN
   SET @spins = @spins +1
END

SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate())

SELECT @spins Spins, @time_millisecond Time_ms,  @spins / @time_millisecond Spins_Per_ms
```

Wachttijden of knelpunten vaststellen

Als u een query wilt optimaliseren die wacht op knelpunten, identificeert u hoe lang de wachttijd is en waar het knelpunt zich bevindt (het wachttype). Zodra het wachttype is bevestigd, vermindert u de wachttijd of elimineert u de wachttijd volledig.

Als u de geschatte wachttijd wilt berekenen, trekt u de CPU-tijd (werktijd) af van de verstreken tijd van een query. Normaal gesproken is de CPU-tijd de werkelijke uitvoeringstijd en wacht het resterende deel van de levensduur van de query.

Voorbeelden van het berekenen van de geschatte wachttijd:

Verstreken tijd (ms)	CPU-tijd (ms)	Wachttijd (ms)
3200	3000	200
7080	1000	6080

Het knelpunt identificeren of wachten

Voer de volgende query uit om historische langverwachte query's te identificeren (bijvoorbeeld >20% van de totale verstreken tijd is wachttijd). Deze query maakt gebruik van prestatiestatistieken voor queryplannen in de cache sinds het begin van SQL Server.

SELECT t.text,
         qs.total_elapsed_time / qs.execution_count
         AS avg_elapsed_time,
         qs.total_worker_time / qs.execution_count
         AS avg_cpu_time,
         (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
         AS avg_wait_time,
         qs.total_logical_reads / qs.execution_count
         AS avg_logical_reads,
         qs.total_logical_writes / qs.execution_count
         AS avg_writes,
         qs.total_elapsed_time
         AS cumulative_elapsed_time
FROM sys.dm_exec_query_stats qs
         CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
         > 0.2
ORDER BY qs.total_elapsed_time / qs.execution_count DESC

Voer de volgende query uit om te bepalen welke query's momenteel worden uitgevoerd met wachttijden die langer zijn dan 500 ms:

SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
FROM sys.dm_exec_requests r 
   JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
WHERE wait_time > 500
AND is_user_process = 1

Als u een queryplan kunt verzamelen, controleert u de WaitStats van de eigenschappen van het uitvoeringsplan in SSMS:
1. Voer de query uit met Werkelijke uitvoeringsplan opnemen op.
2. Klik met de rechtermuisknop op de operator die het meest links is op het tabblad Uitvoeringsplan
3. Selecteer Eigenschappen en vervolgens de eigenschap WaitStats .
4. Controleer de WaitTimeMs en WaitType.
Als u bekend bent met PSSDiag/SQLdiag- of SQL LogScout LightPerf/GeneralPerf-scenario's, kunt u een van deze scenario's gebruiken om prestatiestatistieken te verzamelen en wachtende query's op uw SQL Server exemplaar te identificeren. U kunt de verzamelde gegevensbestanden importeren en de prestatiegegevens analyseren met SQL Nexus.

Verwijzingen om wachttijden te voorkomen of te verminderen

De oorzaken en oplossingen voor elk wachttype variëren. Er is geen enkele algemene methode om alle wachttypen op te lossen. Hier volgen artikelen om veelvoorkomende problemen met wachttypen op te lossen:

Zie de tabel in Typen wachttijden voor beschrijvingen van veel wachttypen en wat ze aangeven.

Verschillen in queryplan vaststellen

Hier volgen enkele veelvoorkomende oorzaken voor verschillen in queryplannen:

Verschillen in gegevensgrootte of gegevenswaarden

Wordt dezelfde database gebruikt op beide servers, met dezelfde databaseback-up? Zijn de gegevens op de ene server gewijzigd in vergelijking met de andere? Gegevensverschillen kunnen leiden tot verschillende queryplannen. Het samenvoegen van tabel T1 (1000 rijen) met tabel T2 (2.000.000 rijen) verschilt bijvoorbeeld van het samenvoegen van tabel T1 (100 rijen) met tabel T2 (2.000.000 rijen). Het type en de snelheid van de JOIN bewerking kunnen aanzienlijk verschillen.
Statistiekenverschillen

Zijn statistieken bijgewerkt op de ene database en niet op de andere? Zijn statistieken bijgewerkt met een andere steekproeffrequentie (bijvoorbeeld 30% versus 100% volledige scan)? Zorg ervoor dat u statistieken aan beide zijden bijwerkt met dezelfde steekproeffrequentie.
Verschillen in databasecompatibiliteitsniveau

Controleer of de compatibiliteitsniveaus van de databases verschillen tussen de twee servers. Voer de volgende query uit om het compatibiliteitsniveau van de database op te halen:
```
SELECT name, compatibility_level
FROM sys.databases
WHERE name = '<YourDatabase>'
```
Verschillen in serverversie/build

Verschillen de versies of builds van SQL Server tussen de twee servers? Is de ene server bijvoorbeeld SQL Server versie 2014 en de andere SQL Server versie 2016? Er kunnen productwijzigingen zijn die kunnen leiden tot wijzigingen in de wijze waarop een queryplan wordt geselecteerd. Zorg ervoor dat u dezelfde versie en build van SQL Server vergelijkt.
```
SELECT ServerProperty('ProductVersion')
```
Verschillen in versie van kardinaliteitsschatter (CE)

Controleer of de verouderde kardinaliteitsschatter is geactiveerd op databaseniveau. Zie Kardinaliteitschatting (SQL Server) voor meer informatie over CE.
```
SELECT name, value, is_value_default
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'
```
Optimizer-hotfixes ingeschakeld/uitgeschakeld

Als de hotfixes voor queryoptimalisatie zijn ingeschakeld op de ene server, maar op de andere server zijn uitgeschakeld, kunnen verschillende queryplannen worden gegenereerd. Zie voor meer informatie SQL Server query optimizer hotfix trace flag 4199 servicing model.

Voer de volgende query uit om de status van hotfixes voor queryoptimalisatie op te halen:
```
-- Check at server level for TF 4199
DBCC TRACESTATUS (-1)
-- Check at database level
USE <YourDatabase>
SELECT name, value, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'QUERY_OPTIMIZER_HOTFIXES'
```
Verschillen in traceringsvlagmen

Sommige traceringsvlagken zijn van invloed op de selectie van het queryplan. Controleer of er traceringsvlagmen zijn ingeschakeld op de ene server die niet zijn ingeschakeld op de andere. Voer de volgende query uit op beide servers en vergelijk de resultaten:
```
-- Check at server level for trace flags
DBCC TRACESTATUS (-1)
```
Hardwareverschillen (CPU-aantal, geheugengrootte)

Voer de volgende query uit om de hardwaregegevens op te halen:
```
SELECT cpu_count, physical_memory_kb/1024/1024 PhysicalMemory_GB 
FROM sys.dm_os_sys_info
```

Hardwareverschillen volgens de queryoptimalisatie

Controleer de OptimizerHardwareDependentProperties van een queryplan en kijk of hardwareverschillen als belangrijk worden beschouwd voor verschillende abonnementen.

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
  txt.text,
  t.OptHardw.value('@EstimatedAvailableMemoryGrant', 'INT') AS EstimatedAvailableMemoryGrant , 
  t.OptHardw.value('@EstimatedPagesCached', 'INT') AS EstimatedPagesCached, 
  t.OptHardw.value('@EstimatedAvailableDegreeOfParallelism', 'INT') AS EstimatedAvailDegreeOfParallelism,
  t.OptHardw.value('@MaxCompileMemory', 'INT') AS MaxCompileMemory
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//OptimizerHardwareDependentProperties') AS t(OptHardw)
CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) txt
WHERE text Like '%<Part of Your Query>%'

Time-out voor optimizer

Is er een time-outprobleem met de optimalisatie ? Het queryoptimalisatieprogramma kan stoppen met het evalueren van planopties als de query die wordt uitgevoerd te complex is. Wanneer het stopt, wordt het plan gekozen met de laagste kosten die op dat moment beschikbaar zijn. Dit kan leiden tot een willekeurige plankeuze op de ene server versus de andere.
SET-opties

Sommige SET-opties zijn van invloed op het plan, zoals SET ARITHABORT. Zie OPTIES INSTELLEN voor meer informatie.
Verschillen in queryhint

Gebruikt de ene query hints voor query's en de andere niet? Controleer de querytekst handmatig om de aanwezigheid van queryhints vast te stellen.
Parametergevoelige plannen (probleem met parametersniffing)

Test u de query met exact dezelfde parameterwaarden? Zo niet, dan kunt u daar beginnen. Is het plan eerder gecompileerd op één server op basis van een andere parameterwaarde? Test de twee query's met behulp van de queryhint RECOMPILE om ervoor te zorgen dat er geen plan opnieuw wordt gebruikt. Zie Parametergevoelige problemen onderzoeken en oplossen voor meer informatie.

Verschillende databaseopties/configuratie-instellingen met bereik

Worden op beide servers dezelfde databaseopties of configuratie-instellingen voor het bereik gebruikt? Sommige databaseopties kunnen van invloed zijn op plankeuzes. Bijvoorbeeld databasecompatibiliteit, verouderde CE versus standaard-CE en parameter-sniffing. Voer de volgende query uit vanaf één server om de databaseopties te vergelijken die op de twee servers worden gebruikt:

-- On Server1 add a linked server to Server2 
EXEC master.dbo.sp_addlinkedserver @server = N'Server2', @srvproduct=N'SQL Server'

-- Run a join between the two servers to compare settings side by side
SELECT 
   s1.name AS srv1_config_name, 
   s2.name AS srv2_config_name,
   s1.value_in_use AS srv1_value_in_use, 
   s2.value_in_use AS srv2_value_in_use, 
   Variance = CASE WHEN ISNULL(s1.value_in_use, '##') != ISNULL(s2.value_in_use,'##') THEN 'Different' ELSE '' END
FROM sys.configurations s1 
FULL OUTER JOIN [server2].master.sys.configurations s2 ON s1.name = s2.name


SELECT 
   s1.name AS srv1_config_name,
   s2.name AS srv2_config_name,
   s1.value srv1_value_in_use,
   s2.value srv2_value_in_use,
   s1.is_value_default,
   s2.is_value_default,
   Variance = CASE WHEN ISNULL(s1.value, '##') != ISNULL(s2.value, '##') THEN 'Different' ELSE '' END
FROM sys.database_scoped_configurations s1
FULL OUTER JOIN [server2].master.sys.database_scoped_configurations s2 ON s1.name = s2.name

Planhandleidingen

Worden er planhandleidingen gebruikt voor uw query's op de ene server, maar niet op de andere? Voer de volgende query uit om verschillen tot stand te brengen:
```
SELECT * FROM sys.plan_guides
```

Share via