Adviseur voor het Afstemmen van Database-Engines

Van toepassing op:SQL Server

De Microsoft Database Engine Tuning Advisor (DTA) analyseert databases en doet aanbevelingen die u kunt gebruiken om queryprestaties te optimaliseren. U kunt Database Engine Tuning Advisor gebruiken om een optimale set indexen, geïndexeerde weergaven of tabelpartities te selecteren en te maken zonder een deskundig inzicht te hebben in de databasestructuur of de interne functies van SQL Server. Met behulp van de DTA kunt u de volgende taken uitvoeren:

• Problemen oplossen bij de prestaties van een specifieke probleemquery

• Een grote set query's afstemmen op een of meer databases

• Een verkennende "what-if" analyse uitvoeren van mogelijke fysieke ontwerpwijzigingen

• Opslagruimte beheren

Voordelen van Database Engine Tuning Advisor

Het optimaliseren van queryprestaties kan lastig zijn zonder volledig inzicht te krijgen in de databasestructuur en de query's die worden uitgevoerd op de database. De DTA (Database Engine Tuning Advisor) kan deze taak eenvoudiger maken door de huidige cache van het queryplan te analyseren of door een workload van Transact-SQL query's te analyseren die u maakt en een geschikt fysiek ontwerp aanbeveelt. Voor geavanceerdere databasebeheerders biedt DTA een krachtig mechanisme voor het uitvoeren van verkennende wat-als-analyse van verschillende alternatieven voor fysiek ontwerp. De DTA kan de volgende informatie verstrekken.

• Raad de beste combinatie van rowstore- en columnstore-indexen voor databases aan met behulp van de queryoptimalisatie om query's in een workload te analyseren.

• Aanbevelingen voor uitgelijnde of niet-uitgelijnde partities voor databases waarnaar wordt verwezen in een workload.

• Aanbevolen geïndexeerde weergaven voor databases waarnaar wordt verwezen in een workload.

• Analyseer de effecten van de voorgestelde wijzigingen, waaronder indexgebruik, querydistributie tussen tabellen en queryprestaties in de workload.

• Aanbevolen manieren om de database af te stemmen op een kleine set probleemquery's.

• Hiermee kunt u de aanbeveling aanpassen door geavanceerde opties op te geven, zoals schijfruimtebeperkingen.

• Geef rapporten op die de gevolgen van het implementeren van de aanbevelingen voor een bepaalde workload samenvatten.

• Overweeg alternatieven waarin u mogelijke ontwerpkeuzen opgeeft in de vorm van hypothetische configuraties voor Database Engine Tuning Advisor om te evalueren.

• Werkbelastingen afstemmen van verschillende bronnen, waaronder SQL Server Query Store, Plan Cache, SQL Server Profiler Trace-bestand of -tabel, of een .SQL-bestand.

Database Engine Tuning Advisor is ontworpen voor het afhandelen van de volgende typen queryworkloads:

• Alleen OLTP-queries (Online Transactie Verwerking)

• Alleen voor OLAP-query's (Online Analytical Processing)

• Gemengde OLTP- en OLAP-query's

• Werkbelastingen met een hoog aantal query's (met meer query's dan gegevenswijzigingen)

• Werkbelastingen met veel updates (meer gegevenswijzigingen dan query's)

DTA-onderdelen en -concepten

Grafische gebruikersinterface van Database Engine Tuning Advisor
Een gebruiksvriendelijke interface waarin u de workload kunt opgeven en verschillende afstemmingsopties kunt selecteren.

Dta Utility
De opdrachtpromptversie van Database Engine Tuning Advisor. Het dta-hulpprogramma is ontworpen om u in staat te stellen de functionaliteit van Database Engine Tuning Advisor te gebruiken in toepassingen en scripts.

workload
Een Transact-SQL scriptbestand, traceringsbestand of traceringstabel die een representatieve workload bevat voor de databases die u wilt afstemmen. Vanaf SQL Server 2012 (11.x) kunt u de plancache opgeven als de workload. Vanaf SQL Server 2016 (13.x) kunt u de Query Store opgeven als de workload.

XML-invoerbestand
Een bestand met XML-indeling dat Database Engine Tuning Advisor kan gebruiken om workloads af te stemmen. Het XML-invoerbestand ondersteunt geavanceerde afstemmingsopties die niet beschikbaar zijn in de GUI of het dta-hulpprogramma .

Beperkingen en beperkingen

De Database Engine Tuning Advisor heeft de volgende beperkingen en restricties.

• Het kan geen unieke indexen of indexen toevoegen of verwijderen die afdwingen PRIMARY KEY of UNIQUE beperkingen.

• Er kan geen database worden geanalyseerd die is ingesteld op de modus voor één gebruiker.

• Als u een maximale schijfruimte opgeeft voor het afstemmen van aanbevelingen die de werkelijke beschikbare ruimte overschrijden, gebruikt Database Engine Tuning Advisor de waarde die u opgeeft. Wanneer u echter het aanbevelingsscript uitvoert om het te implementeren, kan het script mislukken als er niet eerst meer schijfruimte wordt toegevoegd. Maximale schijfruimte kan worden opgegeven met de optie -B van het dta-hulpprogramma of door een waarde in te voeren in het dialoogvenster Geavanceerde afstemmingsopties .

• Database Engine Tuning Advisor kan om veiligheidsredenen geen workload afstemmen in een traceringstabel die zich op een externe server bevindt. Als u deze beperking wilt omzeilen, kunt u een traceringsbestand gebruiken in plaats van een traceringstabel of de traceringstabel naar de externe server kopiëren.

• Wanneer u beperkingen oplegt, zoals beperkingen die worden opgelegd wanneer u een maximale schijfruimte opgeeft voor het afstemmen van aanbevelingen (met behulp van de optie -B of het dialoogvenster Geavanceerde afstemmingsopties ), kan Database Engine Tuning Advisor worden gedwongen om bepaalde bestaande indexen te verwijderen. In dit geval kan de resulterende aanbeveling Database Engine Tuning Advisor een negatieve verwachte verbetering opleveren.

• Wanneer u een beperking opgeeft om de afstemmingstijd te beperken (met behulp van de optie -A met het hulpprogramma dta of door de optie 'Afstemmingstijd beperken' aan te vinken op het tabblad Afstemmingsopties), kan de Database Engine Tuning Advisor deze tijdslimiet overschrijden om een nauwkeurige verwachte verbetering te produceren en de analyserapporten voor elk deel van de afgehandelde werkbelasting.

• Database Engine Tuning Advisor kan onder de volgende omstandigheden geen aanbevelingen doen:

  1. De tabel die wordt afgestemd, bevat minder dan 10 gegevenspagina's.

  2. De aanbevolen indexen bieden onvoldoende verbetering in de queryprestaties ten opzichte van het huidige ontwerp van de fysieke database.

  3. De gebruiker die Database Engine Tuning Advisor uitvoert, is geen lid van de db_owner-databaserol of de vaste serverfunctie sysadmin . De query's in de workload worden geanalyseerd in de beveiligingscontext van de gebruiker die de Database Engine Tuning Advisor uitvoert. De gebruiker moet lid zijn van de db_owner databaserol.

• Database Engine Tuning Advisor slaat sessiegegevens en andere informatie op in de msdb database. Als er wijzigingen in de msdb database worden aangebracht, loopt u mogelijk het risico dat u afstemmingsgegevens van de sessie verliest. Implementeer een geschikte back-upstrategie voor de msdb database om dit risico te voorkomen.

Prestatieoverwegingen

Database Engine Tuning Advisor kan tijdens de analyse aanzienlijke processor- en geheugenbronnen verbruiken. Volg een van de volgende strategieën om te voorkomen dat uw productieserver wordt vertraagd:

• Uw databases afstemmen wanneer uw server gratis is. Database Engine Tuning Advisor kan de prestaties van onderhoudstaken beïnvloeden.

• Gebruik de functie testserver/productieserver. Zie De belasting van de productieserver beperken voor meer informatie.

• Geef alleen de ontwerpstructuren van de fysieke database op die u wilt dat Database Engine Tuning Advisor moet analyseren. Database Engine Tuning Advisor biedt veel opties, maar geeft alleen de opties op die nodig zijn.

Afhankelijkheid van xp_msver uitgebreide opgeslagen procedures

Database Engine Tuning Advisor is afhankelijk van de xp_msver uitgebreide opgeslagen procedure om volledige functionaliteit te garanderen. Deze uitgebreide opgeslagen procedure is standaard ingeschakeld. Database Engine Tuning Advisor maakt gebruik van deze uitgebreide opgeslagen procedure voor het ophalen van het aantal processors en het beschikbare geheugen op de computer waarop de database die u wilt afstemmen. Als xp_msver niet beschikbaar is, gaat Database Engine Tuning Advisor ervan uit dat deze draait op de hardwarekenmerken van de computer waarop het wordt uitgevoerd. Als de hardwarekenmerken van de computer waarop Database Engine Tuning Advisor wordt uitgevoerd niet beschikbaar zijn, wordt uitgegaan van één processor en 1024 MB (MB's) geheugen.

Deze afhankelijkheid is van invloed op partitioneringsaanbevelingen omdat het aantal aanbevolen partities afhankelijk is van deze twee waarden (aantal processors en beschikbaar geheugen). De afhankelijkheid is ook van invloed op uw afstemmingsresultaten wanneer u een testserver gebruikt om uw productieserver af te stemmen. In dit scenario gebruikt Database Engine Tuning Advisor xp_msver om hardware-eigenschappen op te halen van de productieserver. Na het afstemmen van de workload op de testserver gebruikt Database Engine Tuning Advisor deze hardware-eigenschappen om een aanbeveling te genereren. Zie xp_msver (Transact-SQL)voor meer informatie.

Taken van Database Engine Tuning Advisor

De volgende tabel bevat algemene Database Engine Tuning Advisor-taken en de artikelen waarin wordt beschreven hoe ze moeten worden uitgevoerd.