저장 프로시저 다시 컴파일Recompile a Stored Procedure

이 항목에서는 SQL Server 2017SQL Server 2017 에서 Transact-SQLTransact-SQL을 사용하여 저장 프로시저를 다시 컴파일하는 방법에 대해 설명합니다.This topic describes how to recompile a stored procedure in SQL Server 2017SQL Server 2017 by using Transact-SQLTransact-SQL. 이렇게 하려면 프로시저 정의에서나 프로시저가 호출될 때 WITH RECOMPILE 옵션을 사용하거나, 개별 문에서 RECOMPILE 쿼리 힌트를 사용하거나, sp_recompile 시스템 저장 프로시저를 사용합니다.There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored procedure. 이 항목에서는 프로시저 정의를 만들고 기존 프로시저를 실행할 때 RECOMPILE 옵션을 사용하는 방법에 대해 설명합니다.This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. 또한 sp_recompile 시스템 저장 프로시저를 사용하여 기존 프로시저를 다시 컴파일하는 방법에 대해서도 설명합니다.It also describes using the sp_recompile system stored procedure to recompile an existing procedure.

항목 내용In This Topic

시작하기 전 주의 사항 Before You Begin

권장 사항 Recommendations

  • 프로시저가 처음 컴파일되거나 다시 컴파일될 때 프로시저의 쿼리 계획은 데이터베이스와 해당 개체의 현재 상태에 맞게 최적화됩니다.When a procedure is compiled for the first time or recompiled, the procedure’s query plan is optimized for the current state of the database and its objects. 데이터베이스의 데이터나 구조가 크게 변경되는 경우 프로시저를 다시 컴파일하면 해당 변경 내용에 대한 프로시저의 쿼리 계획이 업데이트되고 최적화됩니다.If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure’s query plan for those changes. 이에 따라 프로시저의 처리 성능이 향상될 수 있습니다.This can improve the procedure’s processing performance.

  • 프로시저 재컴파일이 강제로 수행되어야 하는 경우도 있고 자동으로 발생하는 경우도 있습니다.There are times when procedure recompilation must be forced and other times when it occurs automatically. 자동 재컴파일은 SQL ServerSQL Server 가 다시 시작될 때마다 발생하고Automatic recompiling occurs whenever SQL ServerSQL Server is restarted. 프로시저에서 참조하는 기본 테이블의 물리적 디자인이 변경되는 경우에도 발생합니다.It also occurs if an underlying table referenced by the procedure has undergone physical design changes.

  • 프로시저를 강제로 다시 컴파일하는 또 다른 이유는 프로시저 컴파일의 "매개 변수 스니핑" 동작을 막기 위한 것입니다.Another reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. SQL ServerSQL Server 에서 프로시저를 실행하면 프로시저 컴파일에 사용되는 모든 매개 변수 값이 쿼리 계획 생성 시 포함됩니다.When SQL ServerSQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. 이러한 매개 변수 값이 나중에 프로시저를 호출할 때 사용되는 일반적인 값을 나타낼 경우 프로시저가 컴파일되고 실행될 때마다 이 쿼리 계획을 이용할 수 있습니다.If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. 프로시저의 매개 변수 값이 흔히 부정형인 경우 여러 가지 매개 변수 값에 따라 새 계획과 프로시저 재컴파일을 강제로 적용하면 성능이 향상될 수 있습니다.If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.

  • SQL ServerSQL Server 에서는 프로시저를 문 수준으로 다시 컴파일할 수 있습니다. features statement-level recompilation of procedures. SQL ServerSQL Server 에서는 저장 프로시저를 다시 컴파일할 때 전체 프로시저가 아닌 재컴파일이 필요한 문만 컴파일됩니다.When SQL ServerSQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure.

  • 프로시저의 특정 쿼리에서 비정형 값이나 임시 값을 정기적으로 사용하는 경우 해당 쿼리 내에서 RECOMPILE 쿼리 힌트를 사용하여 프로시저 성능을 높일 수 있습니다.If certain queries in a procedure regularly use atypical or temporary values, procedure performance can be improved by using the RECOMPILE query hint inside those queries. 전체 프로시저 대신 쿼리 힌트를 사용하는 쿼리만 다시 컴파일되므로 SQL ServerSQL Server의 문 수준 재컴파일 동작이 모방됩니다.Since only the queries using the query hint will be recompiled instead of the complete procedure, SQL ServerSQL Server's statement-level recompilation behavior is mimicked. 그러나 RECOMPILE 쿼리 힌트는 프로시저의 현재 매개 변수 값을 사용할 뿐만 아니라 문을 컴파일할 때 저장 프로시저에 있는 지역 변수의 값도 사용합니다.But in addition to using the procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when you compile the statement. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하세요.For more information, see Query Hint (Transact-SQL).

보안 Security

사용 권한 Permissions

WITH RECOMPILE 옵션WITH RECOMPILE Option
프로시저 정의를 만들 때 이 옵션을 사용하는 경우 데이터베이스에서 CREATE PROCEDURE 권한과 프로시저를 만들고 있는 스키마에 대한 ALTER 권한이 있어야 합니다.If this option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

EXECUTE 문에서 이 옵션을 사용하는 경우 프로시저에 대한 EXECUTE 권한이 있어야 합니다.If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the procedure. EXECUTE 문 자체에 대한 권한은 필요하지 않지만 EXECUTE 문에서 참조되는 프로시저에 대한 실행 권한이 필요합니다.Permissions are not required on the EXECUTE statement itself but execute permissions are required on the procedure referenced in the EXECUTE statement. 자세한 내용은 EXECUTE(Transact-SQL)를 참조하세요.For more information, see EXECUTE (Transact-SQL).

RECOMPILE 쿼리 힌트RECOMPILE Query Hint
이 기능은 프로시저를 만들고 프로시저의 Transact-SQLTransact-SQL 문에 힌트를 포함할 때 사용됩니다.This feature is used when the procedure is created and the hint is included in Transact-SQLTransact-SQL statements in the procedure. 따라서 데이터베이스에서 CREATE PROCEDURE 권한과 프로시저를 만들 스키마에 대한 ALTER 권한이 있어야 합니다.Therefore, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

sp_recompile 시스템 저장 프로시저sp_recompile System Stored Procedure
지정된 프로시저에 대한 ALTER 권한이 있어야 합니다.Requires ALTER permission on the specified procedure.

Transact-SQL 사용 Using Transact-SQL

WITH RECOMPILE 옵션을 사용하여 저장 프로시저를 다시 컴파일하려면To recompile a stored procedure by using the WITH RECOMPILE option

  1. 데이터베이스 엔진Database Engine에 연결합니다.Connect to the 데이터베이스 엔진Database Engine.

  2. 표준 도구 모음에서 새 쿼리를 클릭합니다.From the Standard bar, click New Query.

  3. 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.Copy and paste the following example into the query window and click Execute. 이 예에서는 프로시저 정의를 만듭니다.This example creates the procedure definition.

USE AdventureWorks2012;  
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;  

WITH RECOMPILE 옵션을 사용하여 저장 프로시저를 다시 컴파일하려면To recompile a stored procedure by using the WITH RECOMPILE option

  1. 데이터베이스 엔진Database Engine에 연결합니다.Connect to the 데이터베이스 엔진Database Engine.

  2. 표준 도구 모음에서 새 쿼리를 클릭합니다.From the Standard bar, click New Query.

  3. 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.Copy and paste the following example into the query window and click Execute. 이 예에서는 뷰에서 모든 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환하는 간단한 프로시저를 만듭니다.This example creates a simple procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view.

    그런 다음 두 번째 코드 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.And then copy and paste the second code example into the query window and click Execute. 프로시저가 실행되고 프로시저의 쿼리 계획이 다시 컴파일됩니다.This executes the procedure and recompiles the procedure’s query plan.

USE AdventureWorks2012;  
GO  
EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE;  
GO  

sp_recompile을 사용하여 저장 프로시저를 다시 컴파일하려면To recompile a stored procedure by using sp_recompile

  1. 데이터베이스 엔진Database Engine에 연결합니다.Connect to the 데이터베이스 엔진Database Engine.

  2. 표준 도구 모음에서 새 쿼리를 클릭합니다.From the Standard bar, click New Query.

  3. 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.Copy and paste the following example into the query window and click Execute. 이 예에서는 뷰에서 모든 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환하는 간단한 프로시저를 만듭니다.This example creates a simple procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view.

    그런 다음 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.Then, copy and paste the following example into the query window and click Execute. 프로시저가 실행되지는 않지만 다음에 프로시저가 실행될 때 쿼리 계획이 업데이트될 수 있게 프로시저가 다시 컴파일되도록 표시됩니다.This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.

USE AdventureWorks2012;  
GO  
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';  
GO  

참고 항목See Also

저장 프로시저 만들기 Create a Stored Procedure
저장 프로시저 수정 Modify a Stored Procedure
저장 프로시저 이름 바꾸기 Rename a Stored Procedure
저장 프로시저의 정의 보기 View the Definition of a Stored Procedure
저장 프로시저의 종속성 보기 View the Dependencies of a Stored Procedure
DROP PROCEDURE(Transact-SQL) DROP PROCEDURE (Transact-SQL)