Een opgeslagen procedure opnieuw compileren

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)SQL-database in Microsoft Fabric Preview

In dit artikel wordt beschreven hoe u een opgeslagen procedure in SQL Server opnieuw compileert met behulp van Transact-SQL. Er zijn drie manieren om dit te doen: de WITH RECOMPILE optie in de proceduredefinitie of wanneer de procedure wordt aangeroepen, de RECOMPILE-query-hint op individuele instructies, of door gebruik te maken van de sp_recompile systeem opgeslagen procedure.

Voordat u begint

Recommendations

• Wanneer een procedure voor het eerst wordt gecompileerd of opnieuw wordt gecompileerd, wordt het queryplan van de procedure geoptimaliseerd voor de huidige status van de database en de bijbehorende objecten. Als een database belangrijke wijzigingen in de gegevens of structuur ondergaat, moet u een procedure bijwerken en het queryplan van de procedure voor deze wijzigingen optimaliseren. Dit kan de verwerkingsprestaties van de procedure verbeteren.

• Er zijn momenten waarop procedurehercompilatie moet worden afgedwongen en andere tijden wanneer deze automatisch plaatsvindt. Automatisch opnieuw compileren vindt plaats wanneer SQL Server opnieuw wordt opgestart. Het treedt ook op als een onderliggende tabel waarnaar wordt verwezen door de procedure fysieke ontwerpwijzigingen heeft ondergaan.

• Een andere reden om een procedure opnieuw te compileren, is om het 'parameter sniffing'-gedrag bij procedurecompilatie tegen te gaan. Wanneer SQL Server procedures uitvoert, worden parameterwaarden die door de procedure worden gebruikt wanneer deze worden gecompileerd, opgenomen als onderdeel van het genereren van het queryplan. Als deze waarden de typische waarden vertegenwoordigen waarmee de procedure vervolgens wordt aangeroepen, profiteert de procedure van het queryplan telkens wanneer deze wordt gecompileerd en uitgevoerd. Als parameterwaarden voor de procedure vaak atypisch zijn, kan het afdwingen van een hercompilatie van de procedure en een nieuw plan op basis van verschillende parameterwaarden de prestaties verbeteren.

• Sql Server bevat hercompilatie op instructieniveau van procedures. Wanneer SQL Server opgeslagen procedures opnieuw compileert, wordt alleen de instructie die de hercompilatie heeft veroorzaakt gecompileerd, in plaats van de volledige procedure.

• Als bepaalde query's in een procedure regelmatig atypische of tijdelijke waarden gebruiken, kunnen de prestaties van de procedure worden verbeterd door binnen die query's de RECOMPILE-queryhint te gebruiken. Omdat alleen de query's die de queryhint gebruiken, opnieuw worden gecompileerd in plaats van de volledige procedure, wordt het recompilatiegedrag op instructieniveau van SQL Server nagebootst. Maar naast het gebruik van de huidige parameterwaarden van de procedure, gebruikt de RECOMPILE-queryhint ook de waarden van lokale variabelen in de opgeslagen procedure bij het compileren van de instructie. Zie Queryhint (Transact-SQL) voor meer informatie.

Security

Permissions

MET OPTIE VOOR OPNIEUW COMPILEREN

Als deze optie wordt gebruikt wanneer de proceduredefinitie wordt gemaakt, is de machtiging CREATE PROCEDURE in de database en ALTER-machtiging vereist voor het schema waarin de procedure wordt gemaakt.

Als deze optie wordt gebruikt in een EXECUTE-instructie, zijn EXECUTE-machtigingen vereist voor de procedure. Machtigingen zijn niet vereist voor de EXECUTE-instructie zelf, maar uitvoermachtigingen zijn vereist voor de procedure waarnaar wordt verwezen in de EXECUTE-instructie. Zie EXECUTE (Transact-SQL)voor meer informatie.

Hint voor recompile-query

Deze functie wordt gebruikt wanneer de procedure wordt gemaakt en de hint wordt opgenomen in Transact-SQL instructies in de procedure. Daarom vereist het create PROCEDURE-machtiging in de database en ALTER-machtiging voor het schema waarin de procedure wordt gemaakt.

sp_recompile systeemopgeslagen procedure

Vereist ALTER-machtiging voor de opgegeven procedure.

Using Transact-SQL

1. Maak verbinding met de database-engine.

2. Selecteer vanaf de Standard--balk Nieuwe query.

3. Kopieer en plak het volgende voorbeeld in het queryvenster en selecteer uitvoeren. In dit voorbeeld wordt de proceduredefinitie gemaakt.

   USE AdventureWorks2022;  
   GO  
   IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
       DROP PROCEDURE dbo.uspProductByVendor;  
   GO  
   CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
   WITH RECOMPILE  
   AS  
       SET NOCOUNT ON;  
       SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
       FROM Purchasing.Vendor AS v   
       JOIN Purchasing.ProductVendor AS pv   
         ON v.BusinessEntityID = pv.BusinessEntityID   
       JOIN Production.Product AS p   
         ON pv.ProductID = p.ProductID  
       WHERE v.Name LIKE @Name;  
   

Een opgeslagen procedure opnieuw compileren met de optie WITH RECOMPILE

Selecteer Nieuwe query en kopieer en plak het volgende codevoorbeeld in het queryvenster en selecteer Uitvoeren. Hiermee wordt de procedure uitgevoerd en wordt het queryplan van de procedure opnieuw gecompileerd.

USE AdventureWorks2022;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

Een opgeslagen procedure opnieuw compileren met behulp van sp_recompile

Selecteer Nieuwe query en kopieer en plak het volgende voorbeeld in het queryvenster en selecteer Uitvoeren. Hiermee wordt de procedure niet uitgevoerd, maar wordt de procedure gemarkeerd die opnieuw moet worden gecompileerd, zodat het queryplan wordt bijgewerkt wanneer de procedure de volgende keer wordt uitgevoerd.

USE AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Next steps

Een opgeslagen procedure maken
een opgeslagen procedure wijzigen
de naam van een opgeslagen procedure wijzigen
de definitie van een opgeslagen procedure weergeven
De afhankelijkheden van een opgeslagen procedure weergeven
DROP PROCEDURE (Transact-SQL)