Handleiding voor validatie en optimalisatie na migratie
van toepassing op:
SQL Server-
De stap na de migratie van SQL Server is van cruciaal belang voor het afstemmen van de nauwkeurigheid en volledigheid van gegevens en het ontdekken van prestatieproblemen met de workload.
Veelvoorkomende prestatiescenario's
Hieronder volgen enkele veelvoorkomende prestatiescenario's die worden aangetroffen na de migratie naar SQL Server Platform en hoe u deze kunt oplossen. Dit zijn scenario's die specifiek zijn voor MIGRATIE van SQL Server naar SQL Server (oudere versies naar nieuwere versies) en extern platform (zoals Oracle, DB2, MySQL en Sybase) naar SQL Server-migratie.
Queryregressies door een verandering in de versie van de Kardinaliteitsschatter (CE)
van toepassing op: SQL Server naar SQL Server-migratie.
Wanneer u migreert van een oudere versie van SQL Server naar SQL Server 2014 (12.x) of latere versies en het upgraden van het databasecompatibiliteitsniveau naar de nieuwste beschikbare versie, kan een workload worden blootgesteld aan het risico van prestatieregressie.
Dit komt doordat vanaf SQL Server 2014 (12.x) alle wijzigingen in Query Optimizer zijn gekoppeld aan het meest recente databasecompatibiliteitsniveau, zodat plannen niet direct op het punt van upgrade worden gewijzigd, maar in plaats daarvan wanneer een gebruiker de COMPATIBILITY_LEVEL databaseoptie wijzigt in de meest recente databaseoptie. Deze mogelijkheid, in combinatie met Query Store, biedt u een groot niveau van controle over de queryprestaties in het upgradeproces.
Voor meer informatie over de wijzigingen in de Query Optimizer die in SQL Server 2014 (12.x) zijn geïntroduceerd, zie Uw queryplannen optimaliseren met de Cardinality Estimator van SQL Server 2014.
Zie Kardinaliteitsraming (SQL Server)voor meer informatie over de CE.
Stappen om op te lossen
Wijzig het databasecompatibiliteitsniveau in de bronversie en volg de aanbevolen upgradewerkstroom, zoals wordt weergegeven in de volgende afbeelding:
Zie Prestatiestabiliteit behouden tijdens de upgrade naar nieuwere SQL Server-voor meer informatie over dit artikel.
Gevoeligheid voor parameter-sniffing
van toepassing op: extern platform (zoals Oracle, DB2, MySQL en Sybase) naar SQL Server-migratie.
Notitie
Als dit probleem voor SQL Server-naar-SQL Server-migraties bestond in de bron-SQL Server, wordt dit scenario niet opgelost wanneer u migreert naar een nieuwere versie van SQL Server as-is.
SQL Server maakt queryplannen voor opgeslagen procedures door het uitlezen van de invoerparameters bij de eerste compilatie, waarbij een geparameteriseerd en herbruikbaar plan wordt gegenereerd dat geoptimaliseerd is voor die invoergegevensdistributie. Zelfs als er geen opgeslagen procedures zijn, worden de meeste instructies die triviale plannen genereren geparameteriseerd. Nadat een plan voor het eerst in de cache is opgeslagen, wordt elke toekomstige uitvoering toegewezen aan een eerder in de cache opgeslagen plan.
Er treedt een mogelijk probleem op wanneer die eerste compilatie niet gebruikmaakt van de meest voorkomende sets parameters voor de gebruikelijke workload. Voor verschillende parameters wordt hetzelfde uitvoeringsplan inefficiënt. Zie Parametergevoeligheidvoor meer informatie over dit artikel.
Stappen om op te lossen
- Gebruik de
RECOMPILEhint. Er wordt telkens een plan berekend aangepast aan elke parameterwaarde. - Herschrijf de opgeslagen procedure om de optie
(OPTIMIZE FOR(<input parameter> = <value>))te gebruiken. Bepaal welke waarde moet worden gebruikt voor de meeste relevante workload, waarbij u één plan maakt en onderhoudt dat efficiënt wordt voor de geparameteriseerde waarde. - Herschrijf de opgeslagen procedure met behulp van een lokale variabele in de procedure. Nu gebruikt de optimizer de dichtheidsvector voor schattingen, wat resulteert in hetzelfde plan, ongeacht de parameterwaarde.
- Herschrijf de opgeslagen procedure om de optie
(OPTIMIZE FOR UNKNOWN)te gebruiken. Hetzelfde effect als het gebruik van de lokale variabeletechniek. - Herschrijf de query om de hint
DISABLE_PARAMETER_SNIFFINGte gebruiken. Hetzelfde effect als het gebruik van de lokale variabeletechniek door parameter-sniffing volledig uit te schakelen, tenzijOPTION(RECOMPILE),WITH RECOMPILEofOPTIMIZE FOR <value>wordt gebruikt.
Tip
Gebruik de functie Analyse van Management Studio Plan om snel te bepalen of dit een probleem is. Raadpleeg Nieuw in SSMS: Probleemoplossing voor queryprestaties is eenvoudiger! voor meer informatie..
Ontbrekende indexen
van toepassing op: extern platform (zoals Oracle, DB2, MySQL en Sybase) en SQL Server naar SQL Server-migratie.
Onjuiste of ontbrekende indexen veroorzaken extra I/O, waardoor extra geheugen en CPU worden verspild. Dit kan komen doordat het workloadprofiel is gewijzigd, zoals het gebruik van verschillende predicaten, het ongeldig maken van het bestaande indexontwerp. Voorbeelden van een slechte indexeringsstrategie of wijzigingen in het workloadprofiel zijn:
- Zoek naar dubbele, redundante, zelden gebruikte en volledig ongebruikte indexen.
- Speciale zorg voor ongebruikte indexen met updates.
Stappen om op te lossen
- Gebruik het grafische uitvoeringsplan voor ontbrekende indexverwijzingen.
- Indexeringsuggesties die zijn gegenereerd door Database Engine Tuning Advisor.
- Gebruik de sys.dm_db_missing_index_details.
- Gebruik bestaande scripts die bestaande DMV's kunnen gebruiken om inzicht te krijgen in ontbrekende, dubbele, redundante, zelden gebruikte en volledig ongebruikte indexen, maar ook als een indexverwijzing wordt gehint/vastgelegd in bestaande procedures en functies in uw database.
Tip
Voorbeelden van dergelijke bestaande scripts zijn index maken en indexinformatie.
Onvermogen om predicaten te gebruiken om gegevens te filteren
van toepassing op: extern platform (zoals Oracle, DB2, MySQL en Sybase) en SQL Server naar SQL Server-migratie.
Notitie
Als dit probleem voor SQL Server-naar-SQL Server-migraties bestond in de bron-SQL Server, wordt dit scenario niet opgelost wanneer u migreert naar een nieuwere versie van SQL Server as-is.
SQL Server Query Optimizer kan alleen rekening houden met informatie die bekend is tijdens het compileren. Als een workload afhankelijk is van predicaten die alleen bekend zijn tijdens de uitvoering, neemt het potentieel voor een slechte plankeuze toe. Voor een beter kwaliteitsplan moeten predicaten SARGableof Search Argin staat zijn.
Enkele voorbeelden van niet-SARGable predicaten:
- Impliciete gegevensconversies, zoals varchar naar nvarchar, of int naar varchar. Zoek naar runtime-
CONVERT_IMPLICITwaarschuwingen in de werkelijke uitvoeringsplannen. Het converteren van het ene type naar het andere kan ook leiden tot een verlies van precisie. - Complexe niet-vastgestelde expressies, zoals
WHERE UnitPrice + 1 < 3.975, maar nietWHERE UnitPrice < 320 * 200 * 32. - Expressies die functies gebruiken, zoals
WHERE ABS(ProductID) = 771ofWHERE UPPER(LastName) = 'Smith' - Tekenreeksen met een voorloopjokerteken, zoals
WHERE LastName LIKE '%Smith', maar nietWHERE LastName LIKE 'Smith%'.
Stappen om op te lossen
Declareer altijd variabelen/parameters als het beoogde doel Gegevenstypen.
Dit kan betrekking hebben op het vergelijken van alle door de gebruiker gedefinieerde codeconstructies die zijn opgeslagen in de database (zoals opgeslagen procedures, door de gebruiker gedefinieerde functies of weergaven) met systeemtabellen die informatie bevatten over gegevenstypen die worden gebruikt in onderliggende tabellen (zoals sys.columns (Transact-SQL)).
Als u niet alle code naar het vorige punt kunt doorkruisen, wijzigt u voor hetzelfde doel het gegevenstype in de tabel zodat deze overeenkomt met een variabele/parameterdeclaratie.
Reden voor het nut van de volgende constructies:
- Functies die worden gebruikt als predicaten;
- Wildcard-zoekopdrachten
- Complexe expressies op basis van kolomgegevens: evalueer in plaats daarvan de noodzaak om persistente berekende kolommen te maken, die kunnen worden geïndexeerd;
Notitie
Al deze stappen kunnen programmatisch worden uitgevoerd.
Gebruik van tabelwaardefuncties (multiregel versus inline)
van toepassing op: extern platform (zoals Oracle, DB2, MySQL en Sybase) en SQL Server naar SQL Server-migratie.
Notitie
Als dit probleem voor SQL Server-naar-SQL Server-migraties bestond in de bron-SQL Server, wordt dit scenario niet opgelost wanneer u migreert naar een nieuwere versie van SQL Server as-is.
Tabelwaardefuncties retourneren een tabelgegevenstype dat een alternatief kan zijn voor weergaven. Hoewel weergaven beperkt zijn tot één SELECT-instructie, kunnen door de gebruiker gedefinieerde functies aanvullende instructies bevatten die meer logica toestaan dan mogelijk is in weergaven.
Belangrijk
Omdat de uitvoertabel van een tabelwaardefunctie met meerdere instructies (MSTVF) niet tijdens het compileren wordt gemaakt, is de SQL Server Query Optimizer afhankelijk van heuristiek en niet op de werkelijke statistieken om rijschattingen te bepalen. Zelfs als er indexen worden toegevoegd aan de basistabel(s), gaat dit niet helpen. Voor MSTVF's gebruikt SQL Server een vaste schatting van 1 voor het aantal rijen dat wordt verwacht te worden geretourneerd door een MSTVF (te beginnen met SQL Server 2014 (12.x) die een vaste schatting is van 100 rijen.
Stappen om op te lossen
Als de MSTVF slechts één enkele instructie bevat, converteert u naar een inline-tabelwaardefunctie.
CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int) RETURNS @tblAddress TABLE ([Address] VARCHAR(60) NOT NULL) AS BEGIN INSERT INTO @tblAddress ([Address]) SELECT TOP 1 [AddressLine1] FROM [Person].[Address] WHERE AddressID = @ID ORDER BY [ModifiedDate] DESC RETURN ENDHet inline-formaat voorbeeld wordt nu weergegeven.
CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int) RETURNS TABLE AS RETURN ( SELECT TOP 1 [AddressLine1] AS [Address] FROM [Person].[Address] WHERE AddressID = @ID ORDER BY [ModifiedDate] DESC )Als het complexer is, kunt u tussenliggende resultaten gebruiken die zijn opgeslagen in Memory-Optimized tabellen of tijdelijke tabellen.