Bepalen of de QTA (Query Tuning Assistant) geschikt is voor u
U bent op de hoogte van mogelijke regressie van queryplannen en prestatieverlies na upgrades van de databaseversie. Als u de prestaties na databaseupgrades wilt behouden, moet u de beste methode vinden voor het identificeren en beperken van teruggedraaide query's. In deze les wordt beschreven hoe u de QTA (Query Store en Query Tuning Assistant) kunt gebruiken om ervoor te zorgen dat verminderde prestaties geen probleem zijn na de upgrades.
Aan de slag met de Query Store en Query Tuning Assistant
De QTA is afhankelijk van Query Store-gegevens om query's te vinden die na een upgrade terugkeren. Met Query Store kunt u metrische gegevens verzamelen over de oudere databaseversie voordat u een upgrade uitvoert.
De Query Store is geïntroduceerd in SQL Server 2016 en de QTA is geïntroduceerd in SQL Server 2017. Elke databaseversie die wordt uitgevoerd op een exemplaar van SQL Server 2022, kan beide functies gebruiken. Deze hulpprogramma's zijn geïntegreerd in SQL Server Management Studio (SSMS) en werken op databaseniveau.
Het compatibiliteitsniveau van de database bepaalt de versie, die op zijn beurt de versie van de kardinaliteitsschatter bepaalt die wordt gebruikt. De kardinaliteitsschatter voorspelt hoeveel rijen een query waarschijnlijk retourneert, zodat de queryoptimalisatie het laagste kostenplan kan selecteren. SQL Server 2014 heeft een bijgewerkt algoritme voor kardinaliteitsschatter geïntroduceerd dat de meeste query's ten goede komt, maar dat zelden een negatieve invloed kan hebben op de prestaties.
Om de invloed van de prestaties te meten, rapporteert de Query Store teruggedraaide query's en query's die de meeste systeemresources verbruiken. De QTA vergelijkt de queryprestaties van Query Store vóór en na de database-upgrade en experimenten op de query's om de prestaties te verbeteren.
Notitie
De QTA is niet beschikbaar voor Azure SQL Database- of SQL Managed Instance-databases. Voor deze databases kunt u overwegen om de Azure SQL-migratie-extensie voor Azure Data Studio te gebruiken.
De QTA en automatische correctie van plannen
Wanneer SQL Server een Transact-SQL-query (T-SQL) uitvoert, worden mogelijke plannen geanalyseerd waarmee de query kan worden uitgevoerd. SQL Server slaat plannen op voor query's die correct worden uitgevoerd en hergebruikt wanneer de query's opnieuw worden uitgevoerd.
SQL Server kiest het optimale plan voor een query en gebruikt deze totdat een omstandigheid ervoor zorgt dat er een nieuwe wordt gekozen. Deze omstandigheden kunnen bestaan uit het opnieuw compileren van het plan, het toevoegen of verwijderen van een index of het wijzigen van statistieken.
Het is niet altijd zo dat het nieuwe plan een verbetering van het oude betekent. U kunt de volgende opdracht uitvoeren om te zoeken naar query's met een teruggedraaid plan.
SELECT * FROM sys.dm_db_tuning_recommendations
Vervolgens kunt u de sp_force_plan opgeslagen procedure gebruiken om SQL Server te dwingen een aanbevolen specifiek plan te gebruiken.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
De sp_force_plan procedure is een handmatig proces dat mogelijk tijdrovend is als veel query's terugkeren naar een bijgewerkte database. SQL Server 2017 heeft een nieuwe functie geïntroduceerd met de naam automatische correctie van plannen om query's automatisch af te stemmen en de noodzaak van handmatige interventie te verwijderen. U kunt automatische correctie van plannen inschakelen voor een database door de volgende instructie uit te voeren:
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Wanneer deze instelling op databaseniveau is ingesteld, geeft automatische correctie van plannen SQL Server de opdracht om het laatste goede queryplan te gebruiken. SQL Server blijft het plan bewaken om regressies te vinden terwijl het plan wordt uitgevoerd en om ervoor te zorgen dat het optimale prestaties levert.
Automatische correctie van plannen werkt anders dan de QTA. Het gebruik van het laatste goede plan kan betekenen dat u teruggaat naar een eerdere kardinaliteitsschatter. Omgekeerd voert de QTA de experimenten uit met behulp van de versie van de kardinaliteitsschatter die is toegewezen aan het compatibiliteitsniveau van de doeldatabase.
Samenvatting
De QTA is beschikbaar in SQL Server 2022 en is afhankelijk van de Query Store die moet worden uitgevoerd. De QTA moet de basislijngegevens van Query Store hebben voor een database op het vorige compatibiliteitsniveau, zodat query's kunnen worden bekeken en vergelijkingen kunnen worden gemaakt na een upgrade.
Automatische correctie van plannen, geïntroduceerd in SQL Server 2017, verwijdert de noodzaak om handmatig een queryplan te identificeren en af te dwingen. Automatische correctie van plannen kan worden ingeschakeld op databaseniveau, maar kan de versie van de kardinaliteitsschatter terugdraaien. De QTA gebruikt de versie van de kardinaliteitsschatter die is toegewezen aan het doelcompatibiliteitsniveau.