쿼리 처리 아키텍처 가이드Query Processing Architecture Guide

이 항목은 다음에 적용됩니다. 예SQL Server(2008부터)예Azure SQL Database예Azure SQL Data Warehouse 예병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL Server 데이터베이스 엔진SQL Server Database Engine은 로컬 테이블, 분할된 테이블 및 여러 서버에 분산된 테이블과 같은 다양한 데이터 저장소 아키텍처의 쿼리를 처리합니다.The SQL Server 데이터베이스 엔진SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. 다음 항목에서는 SQL ServerSQL Server가 실행 계획 캐싱을 통해 쿼리를 처리하고 쿼리 재사용을 최적화하는 방법에 대해 설명합니다.The following topics cover how SQL ServerSQL Server processes queries and optimizes query reuse through execution plan caching.

SQL 문 처리SQL Statement Processing

단일 SQL 문 처리는 SQL ServerSQL Server가 SQL 문을 실행하는 가장 기본적인 방법입니다.Processing a single SQL statement is the most basic way that SQL ServerSQL Server executes SQL statements. 이러한 기본 프로세스의 예로 뷰 또는 원격 테이블이 없는 로컬 기본 테이블만 참조하는 단일 SELECT 문을 처리하는 경우를 들 수 있습니다.The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

SELECT 문 최적화Optimizing SELECT Statements

SELECT 문은 프로시저를 통하지 않습니다. 즉, 데이터베이스 서버가 요청한 데이터를 검색하는 데 사용해야 하는 정확한 단계를 지정하고 있지 않습니다.A SELECT statement is non-procedural; it does not state the exact steps that the database server should use to retrieve the requested data. 이는 데이터베이스 서버가 문을 분석하여 요청한 데이터를 추출하는 가장 효율적인 방법을 판단해야 함을 의미합니다.This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. 이것을 SELECT 문 최적화라고 하며This is referred to as optimizing the SELECT statement. 이를 위한 구성 요소를 쿼리 최적화 프로그램이라고 합니다.The component that does this is called the Query Optimizer. 최적화 프로그램에 대한 입력은 쿼리, 데이터베이스 스키마(테이블 및 인덱스 정의) 및 데이터베이스 통계로 이루어집니다.The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. 쿼리 최적화 프로그램의 출력은 쿼리 실행 계획이며 경우에 따라 쿼리 계획이나 그냥 계획이라고도 합니다.The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. 쿼리 계획의 내용은 이 항목의 뒷부분에서 보다 자세히 설명됩니다.The contents of a query plan are described in more detail later in this topic.

다음 도표는 단일 SELECT 문을 최적화하는 동안 쿼리 최적화 프로그램에 입력되는 내용과 출력 내용을 보여 줍니다. query_processor_ioThe inputs and outputs of the Query Optimizer during optimization of a single SELECT statement are illustrated in the following diagram: query_processor_io

SELECT 문은 다음 사항만 정의합니다.A SELECT statement defines only the following:

  • 결과 집합의 서식.The format of the result set. 대부분 SELECT 목록에 지정됩니다.This is specified mostly in the select list. 하지만 ORDER BYGROUP BY 와 같은 다른 절도 결과 집합의 최종 서식에 영향을 줍니다.However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
  • 원본 데이터를 포함하는 테이블.The tables that contain the source data. 테이블은 FROM 절에서 지정됩니다.This is specified in the FROM clause.
  • 테이블이 SELECT 문의 목적과 논리적으로 관련되는 방식.How the tables are logically related for the purposes of the SELECT statement. 조인 사양에 정의되며 WHERE 뒤에 따라오는 ON 절이나 FROM절에 포함될 수 있습니다.This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
  • 원본 테이블의 행이 SELECT 문의 결과에 포함되기 위해 만족시켜야 할 조건.The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. 조건은 WHEREHAVING 절에 지정됩니다.These are specified in the WHERE and HAVING clauses.

쿼리 실행 계획은 다음 사항을 정의합니다.A query execution plan is a definition of the following:

  • 원본 테이블이 액세스되는 순서The sequence in which the source tables are accessed.
    일반적으로 데이터베이스 서버는 다양한 방법으로 기본 테이블에 액세스하여 결과 집합을 작성할 수 있습니다.Typically, there are many sequences in which the database server can access the base tables to build the result set. 예를 들어 SELECT 문이 세 개의 테이블을 참조하는 경우 데이터베이스 서버는 먼저 TableA에 액세스하고, TableA 의 데이터를 사용하여 TableB에서 일치하는 행을 추출한 후 TableB 의 데이터를 사용하여 TableC에서 데이터를 추출합니다.For example, if the SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC. 다음은 데이터베이스 서버가 테이블에 액세스할 수 있는 여러 순서입니다.The other sequences in which the database server could access the tables are:
    TableC, TableB, TableA또는TableC, TableB, TableA, or
    TableB, TableA, TableC또는TableB, TableA, TableC, or
    TableB, TableC, TableA또는TableB, TableC, TableA, or
    TableC, TableA, TableBTableC, TableA, TableB

  • 각 테이블에서 데이터를 추출하는 데 사용하는 방법The methods used to extract data from each table.
    일반적으로 각 테이블의 데이터에 액세스하는 방법에는 여러 가지가 있습니다.Generally, there are different methods for accessing the data in each table. 특정 키 값을 가진 몇몇 행만 필요한 경우 데이터베이스 서버는 인덱스를 사용할 수 있습니다.If only a few rows with specific key values are required, the database server can use an index. 테이블의 모든 행이 필요한 경우 데이터베이스 서버는 인덱스를 무시하고 테이블을 검색할 수 있습니다.If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. 테이블의 모든 행이 필요하지만 키 열이 ORDER BY에 있는 인덱스가 있으면 테이블 검색 대신 인덱스 검색을 수행하여 다른 종류의 결과 집합을 저장할 수 있습니다.If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. 테이블이 매우 작은 경우 테이블 검색은 거의 모든 테이블 액세스를 위한 가장 효율적인 방법일 수 있습니다.If a table is very small, table scans may be the most efficient method for almost all access to the table.

여러 가능한 실행 계획 중에서 하나의 실행 계획을 선택하는 프로세스를 최적화라고 합니다.The process of selecting one execution plan from potentially many possible plans is referred to as optimization. 쿼리 최적화 프로그램은 SQL 데이터베이스 시스템의 가장 중요한 구성 요소 중 하나입니다.The Query Optimizer is one of the most important components of a SQL database system. 쿼리 최적화 프로그램에서 쿼리를 분석하고 계획을 선택할 때 오버헤드가 발생하지만 효율적인 실행 계획을 선택하면 오버헤드가 상당히 절감됩니다.While some overhead is used by the Query Optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the Query Optimizer picks an efficient execution plan. 예를 들어 두 개의 건설 회사에 하나의 집에 대한 동일한 청사진이 제공될 수 있습니다.For example, two construction companies can be given identical blueprints for a house. 한 회사에서는 며칠 동안 그 집을 어떻게 지을지 계획을 하는 동안 다른 회사는 계획 없이 집을 짓기 시작할 경우 프로젝트를 계획하는 데 시간을 소요한 회사가 먼저 작업을 끝내는 것과 같습니다.If one company spends a few days at the beginning to plan how they will build the house, and the other company begins building without planning, the company that takes the time to plan their project will probably finish first.

SQL ServerSQL Server 쿼리 최적화 프로그램은 비용을 기반으로 하는 쿼리 최적화 프로그램입니다.The SQL ServerSQL Server Query Optimizer is a cost-based Query Optimizer. 가능한 각 실행 계획은 사용되는 컴퓨팅 리소스의 양과 관련하여 비용을 추산합니다.Each possible execution plan has an associated cost in terms of the amount of computing resources used. 쿼리 최적화 프로그램은 가능한 실행 계획을 분석하고 예상 비용이 가장 낮은 계획을 선택해야 합니다.The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost. 일부 복잡한 SELECT 문은 가능한 수많은 실행 계획을 포함합니다.Some complex SELECT statements have thousands of possible execution plans. 이 경우에 쿼리 최적화 프로그램은 가능한 모든 조합을 분석하지는 않습니다.In these cases, the Query Optimizer does not analyze all possible combinations. 대신 복잡한 알고리즘을 사용하여 가장 최소 비용에 근접하는 실행 계획을 찾습니다.Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

SQL ServerSQL Server 쿼리 최적화 프로그램이 리소스 비용이 가장 낮은 실행 계획만 선택하는 것은 아닙니다. 쿼리 최적화 프로그램은 타당한 리소스 비용을 사용하여 사용자에게 결과를 반환하고 가장 빠른 결과를 반환하는 계획을 선택합니다.The SQL ServerSQL Server Query Optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. 예를 들어 병렬로 쿼리를 처리하는 것은 대개 직렬로 처리하는 것보다 많은 리소스를 사용하지만 쿼리를 좀 더 빠르게 끝냅니다.For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. SQL ServerSQL Server 쿼리 최적화 프로그램은 서버 로드에 나쁜 영향을 미치지 않는 경우 병렬 실행 계획을 사용하여 결과를 반환합니다.The SQL ServerSQL Server Query Optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

SQL ServerSQL Server 쿼리 최적화 프로그램은 테이블 또는 인덱스에서 정보를 추출하는 다른 방법의 리소스 비용을 예상할 때 배포 통계를 이용합니다.The SQL ServerSQL Server Query Optimizer relies on distribution statistics when it estimates the resource costs of different methods for extracting information from a table or index. 열 및 인덱스에 대해 배포 통계가 보유됩니다.Distribution statistics are kept for columns and indexes. 배포 통계는 특정 인덱스 또는 열에서 값의 선택도를 표시합니다.They indicate the selectivity of the values in a particular index or column. 예를 들어 자동차를 나타내는 테이블에서 많은 차가 동일한 제조업체의 것이지만 각 차는 고유의 차량 등록 번호(VIN)를 갖습니다.For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). VIN에 대한 인덱스는 제조업체에 대한 인덱스보다 좀 더 선택적입니다.An index on the VIN is more selective than an index on the manufacturer. 인덱스 통계가 현재의 데이터가 아니면 쿼리 최적화 프로그램은 테이블의 현재 상태에 대해 최상의 선택을 하지 못할 수 있습니다.If the index statistics are not current, the Query Optimizer may not make the best choice for the current state of the table. 인덱스 통계를 최신 상태로 유지하는 방법에 대한 자세한 내용은 통계를 사용하여 쿼리 성능 향상을 참조하세요.For more information about keeping index statistics current, see Using Statistics to Improve Query Performance.

SQL ServerSQL Server 쿼리 최적화 프로그램은 프로그래머나 데이터베이스 관리자의 입력을 요청하지 않고 데이터베이스 서버가 데이터베이스의 조건 변화에 맞춰 동적으로 조정될 수 있게 하므로 중요합니다.The SQL ServerSQL Server Query Optimizer is important because it enables the database server to adjust dynamically to changing conditions in the database without requiring input from a programmer or database administrator. 이를 통해 프로그래머는 쿼리의 최종 결과를 설명하는 데 주안점을 둘 수 있습니다.This enables programmers to focus on describing the final result of the query. 프로그래머는 문이 실행될 때마다 SQL ServerSQL Server 쿼리 최적화 프로그램이 데이터베이스의 상태에 맞게 효율적인 실행 계획을 세운다는 것을 신뢰할 수 있습니다.They can trust that the SQL ServerSQL Server Query Optimizer will build an efficient execution plan for the state of the database every time the statement is run.

SELECT 문 처리Processing a SELECT Statement

SQL ServerSQL Server가 단일 SELECT 문을 처리하는 데 사용하는 기본 단계는 다음과 같습니다.The basic steps that SQL ServerSQL Server uses to process a single SELECT statement include the following:

  1. 파서는 SELECT 문을 검색하고 그 결과를 키워드, 식, 연산자 및 식별자와 같은 논리 단위로 분류합니다.The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
  2. 시퀀스 트리라고도 하는 쿼리 트리가 작성되어 결과 집합에서 필요로 하는 서식으로 원본 데이터를 변환하는 데 필요한 논리 단계를 정의합니다.A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
  3. 쿼리 최적화 프로그램은 원본 테이블에 액세스할 수 있는 여러 다른 방법을 분석합니다.The Query Optimizer analyzes different ways the source tables can be accessed. 그런 후 리소스 사용을 줄이는 동시에 결과를 가장 빨리 반환하는 일련의 단계를 선택합니다.It then selects the series of steps that returns the results fastest while using fewer resources. 쿼리 트리는 이러한 일련의 단계가 기록되도록 업데이트됩니다.The query tree is updated to record this exact series of steps. 최적화된 최종 쿼리 트리 버전은 실행 계획이라고 합니다.The final, optimized version of the query tree is called the execution plan.
  4. 관계형 엔진이 실행 계획을 실행하기 시작합니다.The relational engine starts executing the execution plan. 기본 테이블의 데이터를 필요로 하는 단계가 처리될 때 관계형 엔진은 저장소 엔진이 관계형 엔진에서 요청된 행 집합의 데이터를 무시하도록 요청합니다.As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.
  5. 관계형 엔진은 저장소 엔진에서 반환된 데이터를 결과 집합에 대해 정의된 서식으로 처리하고 클라이언트에 결과 집합을 반환합니다.The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

다른 문 처리Processing Other Statements

SELECT 문 처리의 기본 단계는 INSERT, UPDATE, DELETE같은 다른 SQL 문에도 적용됩니다.The basic steps described for processing a SELECT statement apply to other SQL statements such as INSERT, UPDATE, and DELETE. UPDATEDELETE 문은 둘 다 수정되거나 삭제될 행 집합을 대상으로 해야 합니다.UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. 이러한 행을 식별하는 프로세스는 SELECT 문의 결과 집합을 구하는 데 사용되는 원본 행을 식별하는 방식과 동일합니다.The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. UPDATEINSERT 문은 모두 업데이트되거나 삽입될 데이터 값을 제공하는 SELECT 문을 포함할 수 있습니다.The UPDATE and INSERT statements may both contain embedded `SELECT statements that provide the data values to be updated or inserted.

CREATE PROCEDURE 또는 ALTER TABL같은 DDL(데이터 정의 언어) 문도 결과적으로 시스템 카탈로그 테이블에 대한 관계형 연산으로 해석되며 ALTER TABLE ADD COLUMN문처럼 데이터 테이블에 대한 관계형 연산으로 해석되는 경우도 있습니다.Even Data Definition Language (DDL) statements, such as CREATE PROCEDURE or ALTER TABLE, are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE ADD COLUMN) against the data tables.

작업 테이블Worktables

관계형 엔진은 SQL 문에 지정된 논리 작업을 수행하기 위해 작업 테이블을 작성해야 합니다.The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. 작업 테이블은 중간 결과를 보관하는 데 사용되는 내부 테이블입니다.Worktables are internal tables that are used to hold intermediate results. 특정 GROUP BY, ORDER BY또는 UNION 쿼리에 대해 작업 테이블이 생성됩니다.Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. 예를 들어 ORDER BY 절이 인덱스 범위에 해당하지 않는 열을 참조하는 경우 관계형 엔진은 요청되는 순서로 결과 집합을 정렬하기 위해 작업 테이블을 만들어야 할 수 있습니다.For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. 작업 테이블은 쿼리 계획 일부의 실행 결과를 임시 보관하는 스풀로 사용되기도 합니다.Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. 작업 테이블은 tempdb 에 작성되며 더 필요 없을 때 자동으로 삭제됩니다.Worktables are built in tempdb and are dropped automatically when they are no longer needed.

뷰 확인View Resolution

SQL ServerSQL Server 쿼리 프로세서에서는 인덱싱된 뷰와 인덱싱되지 않은 뷰가 다르게 처리됩니다.The SQL ServerSQL Server query processor treats indexed and nonindexed views differently:

  • 인덱싱된 뷰의 행은 테이블과 동일한 형식으로 데이터베이스에 저장됩니다.The rows of an indexed view are stored in the database in the same format as a table. 쿼리 프로세서에서 쿼리 계획에 인덱싱된 뷰를 사용하기로 결정하면 인덱싱된 뷰는 기본 테이블과 동일한 방법으로 처리됩니다.If the Query Optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.
  • 인덱싱되지 않은 뷰의 정의만 저장되고 뷰의 행은 저장되지 않습니다.Only the definition of a nonindexed view is stored, not the rows of the view. 쿼리 최적화 프로그램은 인덱싱되지 않은 뷰를 참조하는 SQL 문에 대해 작성하는 실행 계획에 뷰 정의의 논리를 추가합니다.The Query Optimizer incorporates the logic from the view definition into the execution plan it builds for the SQL statement that references the nonindexed view.

SQL ServerSQL Server 쿼리 최적화 프로그램에서 인덱싱된 뷰의 사용 시기를 결정하는 데 사용되는 논리는 테이블 인덱스의 사용 시기를 결정하는 데 사용되는 논리와 유사합니다.The logic used by the SQL ServerSQL Server Query Optimizer to decide when to use an indexed view is similar to the logic used to decide when to use an index on a table. 인덱싱된 뷰의 데이터가 SQL 문의 전체나 일부를 포괄하고 해당 뷰의 인덱스가 저렴한 비용의 액세스 경로로 확인되면, 쿼리에서 이름별로 뷰가 참조되는지 여부와 관계없이 인덱스가 선택됩니다.If the data in the indexed view covers all or part of the SQL statement, and the Query Optimizer determines that an index on the view is the low-cost access path, the Query Optimizer will choose the index regardless of whether the view is referenced by name in the query.

SQL 문에서 인덱싱되지 않은 뷰를 참조할 경우 파서와 쿼리 최적화 프로그램은 SQL 문의 원본과 뷰의 원본을 모두 분석하고 단일 실행 계획을 세웁니다.When an SQL statement references a nonindexed view, the parser and Query Optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. SQL 문과 뷰에 대해 별도의 계획이 있는 것은 아닙니다.There is not one plan for the SQL statement and a separate plan for the view.

예를 들어 다음과 같은 뷰가 있습니다.For example, consider the following view:

USE AdventureWorks2014;
GO
CREATE VIEW EmployeeName AS
SELECT h.BusinessEntityID, p.LastName, p.FirstName
FROM HumanResources.Employee AS h 
JOIN Person.Person AS p
ON h.BusinessEntityID = p.BusinessEntityID;
GO

이 뷰를 기반으로 두 SQL 문이 모두 기본 테이블에 대해 동일한 작업을 수행하고 동일한 결과를 생성합니다.Based on this view, both of these SQL statements perform the same operations on the base tables and produce the same results:

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks2014.dbo.EmployeeName AS EmpN
ON (soh.SalesPersonID = EmpN.BusinessEntityID)
WHERE OrderDate > '20020531';

/* SELECT referencing the Person and Employee tables directly. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.HumanResources.Employee AS e 
JOIN AdventureWorks2014.Sales.SalesOrderHeader AS soh
ON soh.SalesPersonID = e.BusinessEntityID
JOIN AdventureWorks2014.Person.Person AS p
ON e.BusinessEntityID =p.BusinessEntityID
WHERE OrderDate > '20020531';

SQL ServerSQL Server Management Studio 실행 계획 기능을 통해 관계형 엔진이 두 SELECT 문에 대해 동일한 실행 계획을 세우는 것을 알 수 있습니다.The SQL ServerSQL Server Management Studio Showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.

뷰에 힌트 사용Using Hints with Views

쿼리의 뷰에 힌트를 넣으면 뷰가 확장되어 기본 테이블에 액세스할 때 발견되는 다른 힌트와 서로 충돌할 수 있습니다.Hints that are placed on views in a query may conflict with other hints that are discovered when the view is expanded to access its base tables. 이러한 경우 쿼리에서 오류를 반환합니다.When this occurs, the query returns an error. 예를 들어 다음과 같이 뷰 정의에 테이블 힌트가 포함되어 있습니다.For example, consider the following view that contains a table hint in its definition:

USE AdventureWorks2014;
GO
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1, 
    s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID;

다음 쿼리를 입력한다고 가정합니다.Now suppose you enter this query:

SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState WITH (SERIALIZABLE)
WHERE StateProvinceCode = 'WA';

쿼리의 뷰 SERIALIZABLE 에 적용되는 힌트 Person.AddrState 가 뷰 확장 시 뷰의 Person.Address 테이블과 Person.StateProvince 테이블에 모두 전파되기 때문에 이 쿼리는 실패합니다.The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. 그러나 뷰가 확장될 때 NOLOCKPerson.Address힌트도 나타납니다.However, expanding the view also reveals the NOLOCK hint on Person.Address. SERIALIZABLE 힌트와 NOLOCK 힌트가 충돌하기 때문에 결과 쿼리가 올바르지 않습니다.Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

PAGLOCK, NOLOCK, ROWLOCK, TABLOCK또는 TABLOCKX 테이블 힌트도 HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE 테이블 힌트처럼 서로 충돌합니다.The PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX table hints conflict with each other, as do the HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE table hints.

여러 수준의 중첩된 뷰를 통해 힌트가 전파될 수 있습니다.Hints can propagate through levels of nested views. 예를 들어 뷰 HOLDLOCKv1힌트를 적용하는 쿼리가 있다고 가정합니다.For example, suppose a query applies the HOLDLOCK hint on a view v1. v1 이 확장될 때 이 뷰의 정의에 v2 뷰가 포함되어 있음을 확인했습니다.When v1 is expanded, we find that view v2 is part of its definition. v2정의에는 이 뷰의 기본 테이블 중 하나에 대한 NOLOCK 힌트가 있습니다.v2's definition includes a NOLOCK hint on one of its base tables. 그러나 이 테이블에는 HOLDLOCK 뷰의 쿼리로부터 v1힌트도 상속됩니다.But this table also inherits the HOLDLOCK hint from the query on view v1. NOLOCK 힌트와 HOLDLOCK 힌트가 충돌하므로 쿼리가 실패합니다.Because the NOLOCK and HOLDLOCK hints conflict, the query fails.

뷰를 포함하는 쿼리에 FORCE ORDER 힌트를 사용하면 정렬된 구조체에서의 뷰 위치에 따라 뷰 내의 테이블 조인 순서가 결정됩니다.When the FORCE ORDER hint is used in a query that contains a view, the join order of the tables within the view is determined by the position of the view in the ordered construct. 예를 들어 다음 쿼리는 세 개의 테이블과 한 개의 뷰에서 선택합니다.For example, the following query selects from three tables and a view:

SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1 
    AND Table2.Col1 = View1.Col1
    AND View1.Col2 = Table3.Col2;
OPTION (FORCE ORDER);

View1 은 다음과 같이 정의됩니다.And View1 is defined as shown in the following:

CREATE VIEW View1 AS
SELECT Colx, Coly FROM TableA, TableB
WHERE TableA.ColZ = TableB.Colz;

쿼리 계획의 조인 순서는 Table1, Table2, TableA, TableB, Table3입니다.The join order in the query plan is Table1, Table2, TableA, TableB, Table3.

뷰의 인덱스 확인Resolving Indexes on Views

인덱스와 마찬가지로 SQL ServerSQL Server에서는 쿼리 최적화 프로그램에서 쿼리 계획에 인덱싱된 뷰를 사용하는 것이 효과적이라고 판단한 경우에만 인덱싱된 뷰를 사용합니다.As with any index, SQL ServerSQL Server chooses to use an indexed view in its query plan only if the Query Optimizer determines it is beneficial to do so.

모든 버전의 SQL ServerSQL Server에서 인덱싱된 뷰를 만들 수 있습니다.Indexed views can be created in any edition of SQL ServerSQL Server. 일부 SQL ServerSQL Server 버전의 쿼리 최적화 프로그램은 인덱싱된 뷰를 사용할지 자동으로 검토합니다.In some editions of some versions of SQL ServerSQL Server, the Query Optimizer automatically considers the indexed view. 일부 버전의 SQL ServerSQL Server에서 인덱싱된 뷰를 사용하려면 NOEXPAND 테이블 힌트를 사용해야 합니다.In some editions of some versions of SQL ServerSQL Server, to use an indexed view, the NOEXPAND table hint must be used. 세부 내용은 각 버전의 설명서를 참조하세요.For clarification, see the documentation for each version.

다음 조건에 맞을 때 SQL ServerSQL Server 쿼리 최적화 프로그램에서 인덱싱된 뷰를 사용합니다.The SQL ServerSQL Server Query Optimizer uses an indexed view when the following conditions are met:

  • 다음 세션 옵션이 ON으로 설정되어 있습니다.These session options are set to ON:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
    • 세션 옵션 NUMERIC_ROUNDABORT 이 OFF로 설정되어 있습니다.The NUMERIC_ROUNDABORT session option is set to OFF.
  • 쿼리 최적화 프로그램에서 쿼리의 요소와 뷰 인덱스 열 간의 일치 사항을 찾습니다. 예를 들어 다음과 같은 사항이 일치합니다.The Query Optimizer finds a match between the view index columns and elements in the query, such as the following:
    • WHERE 절의 검색 조건 조건자Search condition predicates in the WHERE clause
    • 조인 작업Join operations
    • 집계 함수Aggregate functions
    • GROUP BYGROUP BY clauses
    • 테이블 참조Table references
  • 인덱스 사용 시 예상 비용이 쿼리 최적화 프로그램에서 고려하는 액세스 메커니즘의 비용 중에서 가장 낮습니다.The estimated cost for using the index has the lowest cost of any access mechanisms considered by the Query Optimizer.
  • 인덱싱된 뷰의 테이블 참조에 해당하는 쿼리에서 뷰를 확장하여 기본 테이블에 액세스하는 방식으로 테이블을 참조하거나 직접 테이블을 참조하는 경우 쿼리에서 참조하는 모든 테이블에 같은 힌트 집합이 적용되어 있어야 합니다.Every table referenced in the query (either directly, or by expanding a view to access its underlying tables) that corresponds to a table reference in the indexed view must have the same set of hints applied on it in the query.
참고

이 컨텍스트에서 READCOMMITTED 힌트와 READCOMMITTEDLOCK 힌트는 현재 트랜잭션 격리 수준과 관계없이 항상 다른 힌트로 간주됩니다.The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.

SET 옵션 및 테이블 힌트에 대한 요구 사항을 제외하고 위의 사항은 쿼리 최적화 프로그램에서 쿼리가 테이블 인덱스 범위에 해당하는지 즉, 테이블 인덱스로 쿼리를 처리할 수 있는지 여부를 확인하는 데 사용하는 규칙과 동일합니다.Other than the requirements for the SET options and table hints, these are the same rules that the Query Optimizer uses to determine whether a table index covers a query. 인덱싱된 뷰를 사용하기 위해 쿼리에 아무 것도 추가로 지정할 필요가 없습니다.Nothing else has to be specified in the query for an indexed view to be used.

쿼리 최적화 프로그램에서 인덱싱된 뷰를 사용하도록 쿼리의 FROM 절에서 인덱싱된 뷰를 명시적으로 참조할 필요가 없습니다.A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. 쿼리가 인덱싱된 뷰에도 있는 기본 테이블의 열에 대한 참조를 포함하고 쿼리 최적화 프로그램에서 해당 인덱싱된 뷰를 사용할 때 비용이 가장 저렴한 액세스 메커니즘을 제공할 수 있을 것으로 예상하는 경우 쿼리 최적화 프로그램은 기본 테이블 인덱스가 쿼리에서 직접 참조되지 않을 때 이러한 기본 테이블 인덱스를 선택하는 것과 유사한 방법으로 인덱싱된 뷰를 선택합니다.If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. 쿼리에서 참조하지 않는 열을 포함하는 뷰의 경우 뷰가 쿼리에 지정된 하나 이상의 열을 포괄하기 위한 가장 저렴한 비용 옵션을 제공하면 쿼리 최적화 프로그램에서 이 뷰를 선택할 수 있습니다.The Query Optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.

쿼리 최적화 프로그램은 FROM 절에서 참조하는 인덱싱된 뷰를 표준 뷰로 간주하고 처리합니다.The Query Optimizer treats an indexed view referenced in the FROM clause as a standard view. 쿼리 최적화 프로그램은 최적화 프로세스 시작 시 뷰의 정의를 쿼리로 확장합니다.The Query Optimizer expands the definition of the view into the query at the start of the optimization process. 그런 다음 인덱싱된 뷰 일치가 수행됩니다.Then, indexed view matching is performed. 쿼리 최적화 프로그램에서 선택하는 최종 실행 계획에 인덱싱된 뷰가 사용될 수 있습니다. 또는 계획이 뷰에서 참조하는 기본 테이블에 액세스하여 뷰에서 필요한 데이터를 구체화할 수 있습니다.The indexed view may be used in the final execution plan selected by the Query Optimizer, or instead, the plan may materialize necessary data from the view by accessing the base tables referenced by the view. 쿼리 최적화 프로그램에서는 이 중 가장 저렴한 비용의 방법이 선택됩니다.The Query Optimizer chooses the lowest-cost alternative.

인덱싱된 뷰에 힌트 사용Using Hints with Indexed Views

EXPAND VIEWS 쿼리 힌트를 사용하여 쿼리에 뷰 인덱스가 사용되지 않도록 하거나 NOEXPAND 테이블 힌트를 사용하여 쿼리의 FROM 절에 지정된 인덱싱된 뷰에 인덱스가 사용되도록 할 수 있습니다.You can prevent view indexes from being used for a query by using the EXPAND VIEWS query hint, or you can use the NOEXPAND table hint to force the use of an index for an indexed view specified in the FROM clause of a query. 그러나 쿼리 최적화 프로그램이 각 쿼리에 사용할 최상의 액세스 방법을 동적으로 결정하도록 해야 합니다.However, you should let the Query Optimizer dynamically determine the best access methods to use for each query. EXPANDNOEXPAND 는 성능을 크게 향상하는 것으로 확인된 특정 경우에만 사용합니다.Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.

EXPAND VIEWS 옵션은 쿼리 최적화 프로그램이 전체 쿼리에 뷰 인덱스를 사용하지 않도록 지정합니다.The EXPAND VIEWS option specifies that the Query Optimizer not use any view indexes for the whole query.

뷰에 NOEXPAND 를 지정하면 쿼리 최적화 프로그램은 뷰에 정의된 인덱스의 사용을 고려합니다.When NOEXPAND is specified for a view, the Query Optimizer considers using any indexes defined on the view. 선택적NOEXPAND 절을 사용하여 INDEX() 를 지정하면 쿼리 최적화 프로그램은 지정된 인덱스를 사용합니다.NOEXPAND specified with the optional INDEX() clause forces the Query Optimizer to use the specified indexes. NOEXPAND 는 인덱싱된 뷰에만 지정할 수 있고 인덱싱되지 않은 뷰에는 지정할 수 없습니다.NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.

뷰를 포함하는 쿼리에서 NOEXPANDEXPAND VIEWS 를 지정하지 않으면 뷰가 확장되어 기본 테이블에 액세스합니다.When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. 뷰를 구성하는 쿼리에 테이블 힌트가 포함된 경우 해당 힌트는 기본 테이블로 전파됩니다.If the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. 이 프로세스는 뷰 확인에서 자세히 설명합니다. 뷰의 기본 테이블에 있는 힌트 집합이 모두 동일하면 쿼리를 인덱싱된 뷰와 일치시킬 수 있습니다.(This process is explained in more detail in View Resolution.) As long as the set of hints that exists on the underlying tables of the view are identical to each other, the query is eligible to be matched with an indexed view. 대부분의 경우 이러한 힌트는 뷰에서 직접 상속되기 때문에 서로 일치합니다.Most of the time, these hints will match each other, because they are being inherited directly from the view. 그러나 쿼리가 뷰 대신 테이블을 참조하고 이러한 테이블에 직접 적용된 힌트가 동일하지 않으면 쿼리를 인덱싱된 뷰와 일치시킬 수 없습니다.However, if the query references tables instead of views, and the hints applied directly on these tables are not identical, then such a query is not eligible for matching with an indexed view. 뷰 확장 후 쿼리에서 참조하는 테이블에 INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK또는 XLOCK 힌트가 적용되면 쿼리를 인덱싱된 뷰와 일치시킬 수 없습니다.If the INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK, or XLOCK hints apply to the tables referenced in the query after view expansion, the query is not eligible for indexed view matching.

INDEX (index_val[ ,...n] ) 형식의 테이블 힌트가 쿼리의 뷰를 참조하는 경우 NOEXPAND 힌트를 지정하지 않으면 인덱스 힌트가 무시됩니다.If a table hint in the form of INDEX (index_val[ ,...n] ) references a view in a query and you do not also specify the NOEXPAND hint, the index hint is ignored. 특정 인덱스를 사용하도록 지정하려면 NOEXPAND를 사용합니다.To specify use of a particular index, use NOEXPAND.

일반적으로 쿼리 최적화 프로그램이 인덱싱된 뷰를 쿼리와 일치시키면 쿼리의 테이블이나 뷰에 지정된 모든 힌트가 인덱싱된 뷰에 직접 적용됩니다.Generally, when the Query Optimizer matches an indexed view to a query, any hints specified on the tables or views in the query are applied directly to the indexed view. 쿼리 최적화 프로그램이 인덱싱된 뷰를 사용하지 않도록 선택하면 모든 힌트가 뷰에서 참조하는 테이블로 직접 전파됩니다.If the Query Optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. 자세한 내용은 뷰 확인을 참조하세요.For more information, see View Resolution. 이 전파는 조인 힌트에는 적용되지 않습니다.This propagation does not apply to join hints. 쿼리 내의 원래 위치에서만 적용됩니다.They are applied only in their original position in the query. 쿼리 최적화 프로그램에서 쿼리를 인덱싱된 뷰와 일치시킬 때 조인 힌트는 고려되지 않습니다.Join hints are not considered by the Query Optimizer when matching queries to indexed views. 쿼리 계획에서 조인 힌트가 포함된 쿼리의 일부와 일치하는 인덱싱된 뷰를 사용하는 경우 해당 계획에 조인 힌트가 사용되지 않습니다.If a query plan uses an indexed view that matches part of a query that contains a join hint, the join hint is not used in the plan.

인덱싱된 뷰 정의에는 힌트가 허용되지 않습니다.Hints are not allowed in the definitions of indexed views. 호환 모드 80 이상에서 SQL ServerSQL Server는 인덱싱된 뷰 정의를 유지 관리할 때나 인덱싱된 뷰를 사용하는 쿼리를 실행할 때 인덱싱된 뷰 정의 내의 힌트를 무시합니다.In compatibility mode 80 and higher, SQL ServerSQL Server ignores hints inside indexed view definitions when maintaining them, or when executing queries that use indexed views. 80 호환 모드에서는 인덱싱된 뷰 정의에 힌트를 사용해도 구문 오류가 발생하지 않지만 힌트가 무시됩니다.Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.

분산형 분할 뷰 확인Resolving Distributed Partitioned Views

SQL ServerSQL Server 쿼리 프로세서에서는 분산형 분할 뷰의 성능을 최적화합니다.The SQL ServerSQL Server query processor optimizes the performance of distributed partitioned views. 분산형 분할 뷰 성능의 가장 중요한 측면은 멤버 서버 간에 전송되는 데이터의 양을 최소화하는 것입니다.The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

SQL ServerSQL Server에서는 분산 쿼리를 효율적으로 사용하여 원격 멤버 테이블의 데이터에 액세스하는 지능적이고 동적인 계획을 작성합니다. builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

  • 먼저 쿼리 프로세서는 OLE DB를 사용하여 각 멤버 테이블에서 check 제약 조건 정의를 검색합니다.The Query Processor first uses OLE DB to retrieve the check constraint definitions from each member table. 쿼리 프로세서는 이를 통해 멤버 테이블에 키 값을 분산하여 매핑할 수 있습니다.This allows the query processor to map the distribution of key values across the member tables.
  • The Query Processor compares the key ranges specified in an SQL statement WHERE 절에 지정된 키 범위를 멤버 테이블에 행이 배포되는 방식을 보여 주는 맵과 비교합니다.The Query Processor compares the key ranges specified in an SQL statement WHERE clause to the map that shows how the rows are distributed in the member tables. 그런 다음 쿼리 프로세서는 분산 쿼리를 사용하여 SQL 문을 완료하는 데 필요한 원격 행만 검색하는 쿼리 실행 계획을 작성합니다.The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows that are required to complete the SQL statement. 또한 실행 계획은 데이터 또는 메타데이터가 요청될 때까지 이러한 데이터를 얻기 위해 원격 멤버 테이블에 액세스하는 것을 연기하는 방식으로도 작성됩니다.The execution plan is also built in such a way that any access to remote member tables, for either data or metadata, are delayed until the information is required.

예를 들어 Server1(1부터 3299999까지의CustomerID ), Server2(3300000부터 6599999까지의CustomerID ) 및 Server3(6600000부터 9999999까지의CustomerID )에 걸쳐 customers 테이블이 분할된 시스템이 있다고 가정합니다.For example, consider a system where a customers table is partitioned across Server1 (CustomerID from 1 through 3299999), Server2 (CustomerID from 3300000 through 6599999), and Server3 (CustomerID from 6600000 through 9999999).

Server1에서 실행되는 다음 쿼리에 대해 작성된 실행 계획을 검토합니다.Consider the execution plan built for this query executed on Server1:

SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID BETWEEN 3200000 AND 3400000;

이 쿼리에 대한 실행 계획은 로컬 멤버 테이블에서 3200000에서 3299999까지의 CustomerID 키 값을 포함하는 행을 추출하고 분산 쿼리를 실행하여 Server2에서 3300000에서 3400000까지의 키 값을 포함하는 행을 검색합니다.The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

또한 SQL ServerSQL Server 쿼리 프로세서는 실행 계획이 작성되어야 할 때 키 값이 알려지지 않은 SQL 문에 대해 동적 논리를 쿼리 실행 계획으로 작성할 수 있습니다.The SQL ServerSQL Server Query Processor can also build dynamic logic into query execution plans for SQL statements in which the key values are not known when the plan must be built. 예를 들면 다음 저장 프로시저가 있습니다.For example, consider this stored procedure:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter;

SQL ServerSQL Server에서는 프로시저가 실행될 때마다 @CustomerIDParameter 매개 변수에서 어떤 키 값을 제공하는지 예측할 수 없습니다. cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. 키 값을 예측할 수 없으므로 쿼리 프로세서는 어떤 멤버 테이블을 액세스해야 하는지도 예측할 수 없습니다.Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. 이러한 경우를 처리하기 위해 SQL ServerSQL Server는 어떤 멤버 테이블이 입력 매개 변수 값에 기반하여 액세스되는지 제어하기 위한 조건부 논리(동적 필터)를 포함하는 실행 계획을 작성합니다.To handle this case, SQL ServerSQL Server builds an execution plan that has conditional logic, referred to as dynamic filters, to control which member table is accessed, based on the input parameter value. GetCustomer 저장 프로시저가 Server1에서 실행되었다고 가정했을 때 실행 계획 논리를 다음과 같이 나타낼 수 있습니다.Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

IF @CustomerIDParameter BETWEEN 1 and 3299999
   Retrieve row from local table CustomerData.dbo.Customer_33
ELSE IF @CustomerIDParameter BETWEEN 3300000 and 6599999
   Retrieve row from linked table Server2.CustomerData.dbo.Customer_66
ELSE IF @CustomerIDParameter BETWEEN 6600000 and 9999999
   Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

SQL ServerSQL Server에서는 매개 변수가 없는 쿼리에 대해서도 이러한 유형의 동적 실행 계획을 작성할 때가 있습니다. sometimes builds these types of dynamic execution plans even for queries that are not parameterized. 실행 계획을 다시 사용할 수 있도록 쿼리 최적화 프로그램이 쿼리를 매개 변수화할 수 있습니다.The Query Optimizer may parameterize a query so that the execution plan can be reused. 쿼리 최적화 프로그램이 분할된 뷰를 참조하는 쿼리를 매개 변수화하는 경우 쿼리 최적화 프로그램에서는 지정된 기본 테이블에서 필요한 행이 나오는 것으로 간주하지 않게 되므로If the Query Optimizer parameterizes a query referencing a partitioned view, the Query Optimizer can no longer assume the required rows will come from a specified base table. 실행 계획에서 동적 필터를 사용해야 합니다.It will then have to use dynamic filters in the execution plan.

저장 프로시저 및 트리거 실행Stored Procedure and Trigger Execution

SQL ServerSQL Server는 저장 프로시저와 트리거의 원본만 저장합니다. stores only the source for stored procedures and triggers. 저장 프로시저나 트리거가 먼저 실행될 때 원본은 실행 계획으로 컴파일됩니다.When a stored procedure or trigger is first executed, the source is compiled into an execution plan. 실행 계획이 메모리에서 에이징되기 전에 저장 프로시저나 트리거가 다시 실행되는 경우 관계형 엔진은 기존 계획을 검색하고 다시 사용합니다.If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. 계획이 메모리에서 에이징되면 새 계획이 작성됩니다.If the plan has aged out of memory, a new plan is built. 이 프로세스는 SQL ServerSQL Server에서 모든 SQL 문에 대해 수행하는 프로세스와 유사합니다.This process is similar to the process SQL ServerSQL Server follows for all SQL statements. 성능 면에서 동적 SQL의 일괄 처리와 비교했을 때 SQL ServerSQL Server에서 저장 프로시저와 트리거의 주요 이점은 SQL 문이 항상 동일하다는 것입니다.The main performance advantage that stored procedures and triggers have in SQL ServerSQL Server compared with batches of dynamic SQL is that their SQL statements are always the same. 따라서 관계형 엔진이 기존 실행 계획과 SQL 문을 쉽게 대응시킵니다.Therefore, the relational engine easily matches them with any existing execution plans. 또한 저장 프로시저와 트리거 계획이 쉽게 다시 사용됩니다.Stored procedure and trigger plans are easily reused.

저장 프로시저나 트리거의 실행 계획은 저장 프로시저를 호출하거나 트리거를 실행하는 일괄 처리의 실행 계획과는 별도로 실행됩니다.The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. 따라서 저장 프로시저와 트리거 실행 계획을 더 많이 다시 사용할 수 있습니다.This allows for greater reuse of the stored procedure and trigger execution plans.

실행 계획 캐싱 및 다시 사용Execution Plan Caching and Reuse

SQL ServerSQL Server에는 실행 계획과 데이터 버퍼를 모두 저장하는 데 사용되는 메모리 풀이 있습니다. has a pool of memory that is used to store both execution plans and data buffers. 실행 계획이나 데이터 버퍼에 할당되는 풀 비율은 시스템 상태에 따라 동적으로 변동됩니다.The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. 실행 계획을 저장하는 데 사용되는 메모리 풀 부분을 계획 캐시라고 합니다.The part of the memory pool that is used to store execution plans is referred to as the plan cache.

SQL ServerSQL Server 실행 계획은 다음으로 구성됩니다. execution plans have the following main components:

  • 쿼리 실행 계획 대량 실행 계획은 여러 사용자가 사용하는 재진입용 읽기 전용 데이터 구조입니다.Query Execution Plan The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. 이것을 쿼리 계획이라고 합니다.This is referred to as the query plan. 쿼리 계획에는 사용자 컨텍스트가 저장되지 않습니다.No user context is stored in the query plan. 메모리에는 쿼리 계획의 복사본이 한 개나 두 개만 있습니다. 하나는 모든 직렬 실행용이고 다른 하나는 모든 병렬 실행용입니다.There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. 병렬 복사본은 병렬 처리 수준에 관계없이 모든 병렬 실행에 적용됩니다.The parallel copy covers all parallel executions, regardless of their degree of parallelism.
  • 실행 컨텍스트. 쿼리를 현재 실행하고 있는 각 사용자는 매개 변수 값 등의 해당 실행 관련 데이터를 보유하는 데이터 구조를 갖습니다.Execution Context Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. 이 데이터 구조를 실행 컨텍스트라고 합니다.This data structure is referred to as the execution context. 실행 컨텍스트 데이터 구조는 다시 사용됩니다.The execution context data structures are reused. 사용자가 쿼리를 실행하는 경우 사용 중인 구조가 없으면 새 사용자를 위한 컨텍스트로 다시 초기화됩니다.If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

execution_context

SQL ServerSQL Server에서 SQL 문을 실행할 때 관계형 엔진은 먼저 계획 캐시를 조사하여 동일한 SQL 문에 대해 기존 실행 계획이 있는지 확인합니다.When any SQL statement is executed in SQL ServerSQL Server, the relational engine first looks through the plan cache to verify that an existing execution plan for the same SQL statement exists. 기존 계획을 찾으면 SQL ServerSQL Server는 그것을 재사용하기 때문에 SQL 문을 다시 컴파일하기 위한 오버헤드가 발생하지 않습니다. SQL ServerSQL Server reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. 기존의 실행 계획이 없는 경우 SQL ServerSQL Server에서 쿼리에 대해 새로운 실행 계획이 생성됩니다.If no existing execution plan exists, SQL ServerSQL Server generates a new execution plan for the query.

SQL ServerSQL Server에는 특정 SQL 문에 대한 기존 실행 계획을 찾는 효율적인 알고리즘이 있습니다. has an efficient algorithm to find any existing execution plans for any specific SQL statement. 대부분의 시스템에서 이러한 검색에 사용되는 최소 리소스는 모든 SQL 문을 컴파일하는 대신 기존 계획을 다시 사용함으로써 절약되는 리소스보다도 적습니다.In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every SQL statement.

캐시에서 사용되지 않은 기존 실행 계획과 새 SQL 문을 대응시키는 알고리즘을 적용하려면 모든 개체 참조가 정규화되어야 합니다.The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. 예를 들어 다음에서 첫 번째 SELECT 문은 기존 계획과 일치되지 않지만 두 번째 문은 일치됩니다.For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Person;

SELECT * FROM Person.Person;

계획 캐시에서 실행 계획 제거Removing Execution Plans from the Plan Cache

실행 계획은 이를 저장하기에 충분한 메모리가 있는 한 계획 캐시에 계속 남아 있습니다.Execution plans remain in the plan cache as long as there is enough memory to store them. 메모리의 여유가 많지 않으면 SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 비용을 기반으로 한 방법을 사용하여 계획 캐시에서 어떤 실행 계획을 제거할지 결정합니다.When memory pressure exists, the SQL Server 데이터베이스 엔진SQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. 비용을 기반으로 한 결정을 내리기 위해 SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 다음 요인에 따라 각 실행 계획에 대한 현재 비용 변수를 늘리거나 줄입니다.To make a cost-based decision, the SQL Server 데이터베이스 엔진SQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

사용자 프로세스에서 캐시에 실행 계획을 삽입하는 경우 현재 비용을 원래 쿼리 컴파일 비용과 같게 설정하고, 임시 실행 계획의 경우 사용자 프로세스에서 현재 비용을 0으로 설정합니다.When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. 그 후 사용자 프로세스에서 실행 계획을 참조할 때마다 현재 비용을 원래 컴파일 비용으로 다시 설정하고, 임시 실행 계획의 경우 사용자 프로세스에서 현재 비용을 늘립니다.Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. 모든 계획의 경우 현재 비용의 최대값은 원래 컴파일 비용입니다.For all plans, the maximum value for the current cost is the original compile cost.

메모리의 여유가 많지 않으면 SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 계획 캐시에서 실행 계획을 제거하여 이에 대처합니다.When memory pressure exists, the SQL Server 데이터베이스 엔진SQL Server Database Engine responds by removing execution plans from the plan cache. 어떤 계획을 제거할지 결정하기 위해 SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 각 실행 계획의 상태를 반복 조사하고 현재 비용이 0인 계획을 제거합니다.To determine which plans to remove, the SQL Server 데이터베이스 엔진SQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. 메모리가 부족하다는 이유만으로는 현재 비용이 0인 실행 계획이 자동으로 제거되지 않습니다. SQL Server 데이터베이스 엔진SQL Server Database Engine에서 계획을 조사하여 현재 비용이 0이라는 사실을 확인했을 때만 해당 계획이 제거됩니다.An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the SQL Server 데이터베이스 엔진SQL Server Database Engine examines the plan and the current cost is zero. 실행 계획을 조사할 때 SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 쿼리에 현재 사용되고 있지 않은 계획에 대해 현재 비용을 0에 가깝게 점차 줄여나갑니다.When examining an execution plan, the SQL Server 데이터베이스 엔진SQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 메모리 요구 사항을 충족하기에 충분할 만큼 실행 계획이 제거될 때까지 실행 계획을 되풀이하여 조사합니다.The SQL Server 데이터베이스 엔진SQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. 그 결과로 메모리가 부족한 상태에서 실행 계획의 비용이 여러 차례에 걸쳐 증감할 수 있습니다.While memory pressure exists, an execution plan may have its cost increased and decreased more than once. 충분한 메모리가 다시 확보되면 SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 사용되지 않는 실행 계획의 현재 비용을 더 이상 줄이지 않으며 해당 비용이 0인 계획을 포함한 모든 실행 계획이 계획 캐시에 계속 남습니다.When memory pressure no longer exists, the SQL Server 데이터베이스 엔진SQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

SQL Server 데이터베이스 엔진SQL Server Database Engine에서는 리소스 모니터와 사용자 작업자 스레드를 사용하여 계획 캐시에서 메모리를 확보하여 메모리 부족 문제에 대처합니다.The SQL Server 데이터베이스 엔진SQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. 리소스 모니터와 사용자 작업자 스레드를 통해 계획의 실행 여부를 동시에 조사하여 사용되지 않는 실행 계획 각각의 현재 비용을 줄일 수 있습니다.The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. 전체적인 메모리 부족 현상이 발생하면 리소스 모니터를 통해 계획 캐시에서 실행 계획이 제거됩니다.The resource monitor removes execution plans from the plan cache when global memory pressure exists. 리소스 모니터에서는 시스템 메모리, 프로세스 메모리, 리소스 풀 메모리 및 모든 캐시의 최대 크기에 대한 정책을 따르는 방식으로 메모리를 확보합니다.It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

모든 캐시의 최대 크기는 버퍼 풀 크기에 따라 결정되며 최대 서버 메모리를 초과할 수 없습니다.The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. 최대 서버 메모리를 구성하는 방법에 대한 자세한 내용은 max server memory 에서 sp_configure설정을 참조하세요.For more information on configuring the maximum server memory, see the max server memory setting in sp_configure.

단일 캐시 메모리 부족 현상이 발생하면 사용자 작업자 스레드를 통해 계획 캐시에서 실행 계획이 제거됩니다.The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. 이때는 단일 캐시의 최대 크기와 단일 캐시의 최대 항목 수에 대한 정책을 따릅니다.They enforce policies for maximum single cache size and maximum single cache entries.

다음 예제에서는 어떤 실행 계획이 계획 캐시에서 제거되는지 보여 줍니다.The following examples illustrate which execution plans get removed from the plan cache:

  • 실행 계획은 자주 참조되므로 비용이 절대로 0이 되지 않습니다.An execution plan is frequently referenced so that its cost never goes to zero. 메모리가 부족하지 않고 현재 비용이 0이 아닌 경우 계획이 계획 캐시에 남아 있고 제거되지 않습니다.The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • 임시 실행 계획이 삽입되고 메모리 부족 현상이 발생하기 전에 다시 참조되지 않습니다.An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. 임시 계획의 현재 비용이 0으로 초기화되므로 SQL Server 데이터베이스 엔진SQL Server Database Engine에서 실행 계획을 조사할 때 현재 비용이 0임을 확인하고 해당 계획을 계획 캐시에서 제거합니다.Since ad-hoc plans are initialized with a current cost of zero, when the SQL Server 데이터베이스 엔진SQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. 메모리가 부족하지 않으면 현재 비용이 0인 임시 실행 계획이 계획 캐시에 남아 있습니다.The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

단일 계획이나 모든 계획을 캐시에서 수동으로 제거하려면 DBCC FREEPROCCACHE를 사용하세요.To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE.

실행 계획 다시 컴파일Recompiling Execution Plans

데이터베이스에서 특정 항목을 변경하면 데이터베이스의 새로운 상태에 따라 실행 계획이 비효율적으로 또는 유효하지 않게 될 수 있습니다.Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ServerSQL Server는 이렇게 실행 계획을 무효화하고 계획을 유효하지 않도록 만드는 변경 사항을 감지합니다. detects the changes that invalidate an execution plan and marks the plan as not valid. 이러한 경우에는 쿼리를 실행하는 다음 연결을 위해 새 계획을 다시 컴파일해야 합니다.A new plan must then be recompiled for the next connection that executes the query. 다음과 같은 조건에서 계획이 무효화될 수 있습니다.The conditions that invalidate a plan include the following:

  • 쿼리에서 참조하는 테이블이나 뷰가 변경된 경우(ALTER TABLEALTER VIEW).Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • 단일 프로시저가 변경된 경우. 이 경우 해당 프로시저의 모든 계획이 캐시에서 삭제됩니다(ALTER PROCEDURE).Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • 실행 계획에 사용되는 인덱스가 변경된 경우Changes to any indexes used by the execution plan.
  • UPDATE STATISTICS등의 문에서 명시적으로 생성되거나 자동으로 생성되어 실행 계획에 사용되는 통계가 업데이트된 경우.Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • 실행 계획에 사용되는 인덱스가 삭제된 경우Dropping an index used by the execution plan.
  • sp_recompile에 대한 명시적 호출.An explicit call to sp_recompile.
  • 쿼리에서 참조하는 테이블을 수정하는 다른 사용자가 INSERT 또는 DELETE 문으로 키를 많이 변경한 경우.Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • 트리거가 있는 테이블의 경우 inserted 또는 deleted 테이블의 행 수가 현저하게 증가하는 경우.For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • WITH RECOMPILE 옵션을 사용하여 저장 프로시저를 실행하는 경우.Executing a stored procedure using the WITH RECOMPILE option.

대부분의 다시 컴파일은 문 정확성이나 잠재적으로 더 빠른 쿼리 실행 계획을 얻는 데 필요합니다.Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

SQL ServerSQL Server 2000에서는 일괄 처리 내의 문이 다시 컴파일을 발생시킬 때마다 저장 프로시저, 트리거, 임시 일괄 처리 또는 준비된 문을 통해 제출되었는지에 관계없이 전체 일괄 처리가 다시 컴파일됩니다.In SQL ServerSQL Server 2000, whenever a statement within a batch causes recompilation, the whole batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. SQL Server 2005SQL Server 2005부터 다시 컴파일을 유발하는 일괄 처리 내의 문만 다시 컴파일됩니다.Starting with SQL Server 2005SQL Server 2005, only the statement inside the batch that causes recompilation is recompiled. 이 차이 때문에 SQL ServerSQL Server 2000이상 릴리스의 재컴파일 횟수는 비교할 수 없습니다.Because of this difference, recompilation counts in SQL ServerSQL Server 2000 and later releases are not comparable. 또한 SQL Server 2005SQL Server 2005 이상 버전에서는 기능 집합이 확장되어 더 많은 다시 컴파일 유형을 제공합니다.Also, there are more types of recompilations in SQL Server 2005SQL Server 2005 and later because of its expanded feature set.

다시 컴파일을 유발하고 이에 따라 CPU 시간 및 잠금과 관련하여 성능 저하를 일으키는 문의 수는 대개 적으므로 문 수준 다시 컴파일이 성능에 유리합니다.Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. 문 수준 다시 컴파일을 사용하면 다시 컴파일하지 않아도 되는 일괄 처리 내의 다른 문의 경우 이러한 성능 저하가 발생하지 않습니다.These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

sql_statement_recompile 확장 이벤트(xEvent)는 문 수준의 재컴파일이 있는지 보고합니다.The sql_statement_recompile extended event (xEvent) reports statement-level recompilations. 어떠한 종류의 일괄 처리에서든 문 수준의 재컴파일이 필요한 경우 이 xEvent가 발생합니다.This xEvent occurs when a statement-level recompilation is required by any kind of batch. 여기에는 저장 프로시저, 트리거, 임시 일괄 처리 및 쿼리가 포함됩니다.This includes stored procedures, triggers, ad hoc batches and queries. 일괄 처리는 sp_executesql, 동적 SQL, Prepare 메서드 또는 Execute 메서드를 비롯한 여러 인터페이스를 통해 제출할 수 있습니다.Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. sql_statement_recompile xEvent의 recompile_cause 열에는 다시 컴파일된 이유를 나타내는 정수 코드가 들어 있습니다.The recompile_cause column of sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. 다음 표에는 가능한 이유가 나와 있습니다.The following table contains the possible reasons:

스키마가 변경됨Schema changed 통계가 변경됨Statistics changed
컴파일이 지연됨Deferred compile SET 옵션이 변경됨SET option changed
임시 테이블이 변경됨Temporary table changed 원격 행 집합이 변경됨Remote rowset changed
FOR BROWSE 권한이 변경됨FOR BROWSE permission changed 쿼리 알림 환경이 변경됨Query notification environment changed
분할 뷰가 변경됨Partitioned view changed 커서 옵션이 변경됨Cursor options changed
OPTION (RECOMPILE) 이 요청되었습니다.OPTION (RECOMPILE) requested 매개 변수가 있는 계획이 플러시됨Parameterized plan flushed
데이터베이스 버전에 영향을 주는 계획이 변경됨Plan affecting database version changed 쿼리 저장소 계획 강제 적용 정책이 변경됨Query Store plan forcing policy changed
쿼리 저장소 계획 강제 적용이 실패함Query Store plan forcing failed 쿼리 저장소에 계획이 누락됨Query Store missing the plan
참고

xEvent를 사용할 수 없는 SQL ServerSQL Server 버전에서는 문 수준의 재컴파일을 보고하기 위한 동일한 목적으로 SQL ServerSQL Server 프로파일러 SP:Recompile 추적 이벤트를 사용할 수 있습니다.In SQL ServerSQL Server versions where xEvents are not available, then the SQL ServerSQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations. 추적 이벤트 SQL:StmtRecompile도 문 수준의 재컴파일을 보고하며, 재컴파일을 추적하고 디버그하는 데에도 이 추적 이벤트를 사용할 수 있습니다.The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. SP:Recompile은 저장 프로시저와 트리거에 대해서만 생성되는 반면 SQL:StmtRecompile은 sp_executesql, 준비된 쿼리 및 동적 SQL을 사용하여 실행되는 저장 프로시저, 트리거, 임시 일괄 처리 및 일괄 처리에 대해 생성됩니다.Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql, prepared queries, and dynamic SQL. SP:Recompile 및 SQL:StmtRecompile의 EventSubClass 열에는 다시 컴파일하는 이유를 나타내는 정수 코드가 포함됩니다.The EventSubClass column of SP:Recompile and SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. 코드에 대한 설명은 여기에 있습니다.The codes are described here.

참고

AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 ON으로 설정하면 마지막 실행 이후 통계가 업데이트되거나 카디널리티가 크게 변경된 테이블이나 인덱싱된 뷰를 대상으로 하는 쿼리가 모두 다시 컴파일됩니다.When the AUTO_UPDATE_STATISTICS database option is set to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. 이러한 동작은 일반 사용자 정의 테이블, 임시 테이블 및 DML 트리거로 생성된 삽입 테이블과 삭제 테이블에 적용됩니다.This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. 과도한 재컴파일로 인해 쿼리 성능이 저하되면 이 설정을 OFF로 변경하세요.If query performance is affected by excessive recompilations, consider changing this setting to OFF. AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 OFF로 설정하면 통계나 카디널리티 변경 내용에 따른 재컴파일은 발생하지 않습니다. 단, DML INSTEAD OF 트리거에 의해 생성되는 삽입 테이블과 삭제 테이블은 예외입니다.When the AUTO_UPDATE_STATISTICS database option is set to OFF, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. 두 테이블은 tempdb에 생성되므로 두 테이블에 액세스하는 쿼리의 다시 컴파일은 tempdb의 AUTO_UPDATE_STATISTICS 설정에 따라 결정됩니다.Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. SQL ServerSQL Server 2000에서는 이 설정이 OFF인 경우에도 DML 트리거에 의한 삽입 테이블과 삭제 테이블의 카디널리티 변경 사항에 따라 계속하여 쿼리가 다시 컴파일됩니다.Note that in SQL ServerSQL Server 2000, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is OFF.

매개 변수 및 실행 계획 재사용 Parameters and Execution Plan Reuse

ADO, OLE DB 및 ODBC 응용 프로그램의 매개 변수 표식을 포함하여 매개 변수를 사용하면 실행 계획을 좀 더 많이 재사용할 수 있습니다.The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

경고

또한 최종 사용자가 입력한 값을 갖는 매개 변수 또는 매개 변수 표식을 사용하는 것이 데이터 액세스 API 메서드, EXECUTE 문 또는 sp_executesql 저장 프로시저 중 하나를 사용하여 실행하는 문자열에 값을 연결하는 것보다 안전합니다.Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

다음의 두 SELECT 문 간의 유일한 차이점은 WHERE 절에서 비교된 값이 다르다는 것입니다.The only difference between the following two SELECT statements is the values that are compared in the WHERE clause:

SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

이러한 쿼리에 대한 실행 계획 간의 유일한 차이점은 ProductSubcategoryID 열을 비교할 때 저장되는 값이 다르다는 것입니다.The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. SQL ServerSQL Server에서 항상 해당 문이 기본적으로 동일한 계획을 생성하고 그 계획을 재사용한다는 것을 인식하게 하려는 것이 목적이지만 때때로 SQL ServerSQL Server는 복잡한 SQL 문에서 이러한 사실을 감지하지 못합니다.While the goal is for SQL ServerSQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL ServerSQL Server sometimes does not detect this in complex SQL statements.

매개 변수를 사용하여 SQL 문에서 상수를 분리하면 관계형 엔진이 중복된 계획을 인식하는 데 도움이 됩니다.Separating constants from the SQL statement by using parameters helps the relational engine recognize duplicate plans. 다음 방법으로 매개 변수를 사용할 수 있습니다.You can use parameters in the following ways:

  • Transact-SQL에서는 sp_executesql을 사용합니다.In Transact-SQL, use sp_executesql:

    DECLARE @MyIntParm INT
    SET @MyIntParm = 1
    EXEC sp_executesql
      N'SELECT * 
      FROM AdventureWorks2014.Production.Product 
      WHERE ProductSubcategoryID = @Parm',
      N'@Parm INT',
      @MyIntParm
    

    SQL 문을 동적으로 생성하는 Transact-SQL 스크립트, 저장 프로시저 또는 트리거에 대해서는 이 방법을 사용하는 것이 좋습니다.This method is recommended for Transact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

  • ADO, OLE DB 및 ODBC는 매개 변수 표식을 사용합니다.ADO, OLE DB, and ODBC use parameter markers. 매개 변수 표식은 SQL 문의 상수를 대신하는 물음표(?)로 프로그램 변수에 바인딩됩니다.Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. 예를 들어 ODBC 응용 프로그램에서는 다음을 수행합니다.For example, you would do the following in an ODBC application:

    • SQLBindParameter 를 사용하여 SQL 문에서 정수 변수를 첫째 매개 변수 표식에 바인딩합니다.Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • 변수에 정수 값을 배치합니다.Put the integer value in the variable.
    • 매개 변수 표식(?)을 지정하여 문을 실행합니다.Execute the statement, specifying the parameter marker (?):

      SQLExecDirect(hstmt, 
      "SELECT * 
      FROM AdventureWorks2014.Production.Product 
      WHERE ProductSubcategoryID = ?",
      SQL_NTS);
      

      응용 프로그램에서 매개 변수 표식이 사용된 경우 SQL ServerSQL Server에 포함된 SQL ServerSQL Server Native Client OLE DB 공급자와 SQL ServerSQL Server Native Client ODBC 드라이버는 sp_executesql을 사용하여 문을 SQL ServerSQL Server로 보냅니다.The SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver included with SQL ServerSQL Server use sp_executesql to send statements to SQL ServerSQL Server when parameter markers are used in applications.

  • 기본적으로 매개 변수를 사용하는 저장 프로시저를 디자인합니다.To design stored procedures, which use parameters by design.

응용 프로그램 디자인 내에 매개 변수를 명시적으로 구축하지 않은 경우에는 단순 매개 변수화의 기본 동작을 사용하여 SQL ServerSQL Server 쿼리 최적화 프로그램에서 특정 쿼리를 자동으로 매개 변수화할 수 있습니다.If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL ServerSQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. 또는 ALTER DATABASE 문의 PARAMETERIZATION 옵션을 FORCED로 설정하여 데이터베이스의 모든 쿼리를 강제 매개 변수화하도록 쿼리 최적화 프로그램을 설정할 수 있습니다.Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED.

강제 매개 변수화를 설정한 경우에도 단순 매개 변수화가 계속해서 수행될 수 있습니다.When forced parameterization is enabled, simple parameterization can still occur. 예를 들어 다음 쿼리는 강제 매개 변수화 규칙에 따라 매개 변수화할 수 없습니다.For example, the following query cannot be parameterized according to the rules of forced parameterization:

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

그러나 단순 매개 변수화 규칙에 따르면 매개 변수화할 수 있습니다.However, it can be parameterized according to simple parameterization rules. 강제 매개 변수화를 시도한 후 실패하면 그 다음으로는 단순 매개 변수화를 시도합니다.When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

단순 매개 변수화 Simple Parameterization

SQL ServerSQL Server에서는 Transact-SQL 문에 매개 변수 또는 매개 변수 표식을 사용하여 새 SQL 문을 이전에 컴파일된 기존의 실행 계획과 일치시키는 관계형 엔진의 성능을 향상시킵니다.In SQL ServerSQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans.

경고

최종 사용자가 입력한 값을 갖는 매개 변수 또는 매개 변수 표식을 사용하는 것이 데이터 액세스 API 메서드, EXECUTE 문 또는 sp_executesql 저장 프로시저 중 하나를 사용하여 실행되는 문자열에 값을 연결하는 것보다 안전합니다.Using parameters or parameter markers to hold values typed by end users is more secure than concatenating the values into a string that is then executed using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

매개 변수를 사용하지 않고 SQL 문이 실행되면 SQL ServerSQL Server는 내부적으로 해당 문을 매개 변수화하여 기존 실행 계획과 일치할 가능성을 높입니다.If a SQL statement is executed without parameters, SQL ServerSQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. 이 프로세스를 단순 매개 변수화라고 합니다.This process is called simple parameterization. SQL ServerSQL Server 2000에서는 이 프로세스를 자동 매개 변수화라고 했습니다.In SQL ServerSQL Server 2000, the process was referred to as auto-parameterization.

다음 문을 고려해 보십시오.Consider this statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;

문 끝의 값 1은 매개 변수로 지정될 수 있습니다.The value 1 at the end of the statement can be specified as a parameter. 관계형 엔진은 마치 값 1 대신 매개 변수가 지정된 것처럼 이 일괄 처리에 대해 실행 계획을 작성합니다.The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. 이러한 단순 매개 변수화로 인해 SQL ServerSQL Server는 다음 두 문이 기본적으로 동일한 실행 계획을 생성한다는 것을 확인하고 두 번째 문에 대해 첫 번째 계획을 재사용합니다.Because of this simple parameterization, SQL ServerSQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

복잡한 SQL 문을 처리할 때 관계형 엔진은 매개 변수화할 수 있는 식을 결정하기가 어려울 수 있습니다.When processing complex SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. 복잡한 SQL 문을 사용되지 않은 기존 실행 계획과 일치시키는 관계형 엔진의 성능을 향상하려면 sp_executesql 또는 매개 변수 표식을 사용하여 매개 변수를 명시적으로 지정합니다.To increase the ability of the relational engine to match complex SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers.

참고

+, -, *, / 또는 % 산술 연산자를 사용하여 암시적 또는 명시적으로 int, smallint, tinyint 또는 bigint 상수 값을 float, real, decimal 또는 numeric의 데이터 형식으로 변환할 때 SQL ServerSQL Server는 특정 규칙에 따라 식 결과의 형식과 전체 자릿수를 계산합니다.When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL ServerSQL Server applies specific rules to calculate the type and precision of the expression results. 그러나 이러한 규칙은 쿼리의 매개 변수화 여부에 따라 다릅니다.However, these rules differ, depending on whether the query is parameterized or not. 따라서 쿼리에서 유사한 식을 사용해도 다른 결과가 발생하는 경우가 있습니다.Therefore, similar expressions in queries can, in some cases, produce differing results.

SQL ServerSQL Server는 단순 매개 변수화의 기본 동작에 따라 비교적 작은 클래스의 쿼리를 매개 변수화합니다.Under the default behavior of simple parameterization, SQL ServerSQL Server parameterizes a relatively small class of queries. 그러나 PARAMETERIZATION 명령의 ALTER DATABASE 옵션을 FORCED로 설정하여 데이터베이스의 모든 쿼리를 특정 제한 사항에 따라 매개 변수화하도록 지정할 수 있습니다.However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCED. 이렇게 하면 쿼리 컴파일 빈도를 낮추어 대량의 동시 쿼리가 발생하는 데이터베이스의 성능이 향상될 수 있습니다.Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

또는 구문은 동일하고 매개 변수 값만 다른 쿼리 및 단일 쿼리를 매개 변수화하도록 지정할 수 있습니다.Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

강제 매개 변수화 Forced Parameterization

데이터베이스의 모든 SELECT, INSERT, UPDATEDELETE 문이 특정 제한에 따라 매개 변수화되도록 지정하여 SQL ServerSQL Server의 기본 단순 매개 변수화 동작을 재정의할 수 있습니다.You can override the default simple parameterization behavior of SQL ServerSQL Server by specifying that all SELECT, INSERT, UPDATE, and DELETE statements in a database be parameterized, subject to certain limitations. 강제 매개 변수화는 PARAMETERIZATION 문에서 FORCED 옵션을 ALTER DATABASE 로 설정하면 적용됩니다.Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. 강제 매개 변수화를 사용하여 쿼리 컴파일 및 재컴파일 빈도를 줄여 특정 데이터베이스의 성능을 향상시킬 수 있습니다.Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. 일반적으로 POS(Point of Sale) 응용 프로그램과 같은 원본으로부터 대량의 동시 쿼리를 처리하는 데이터베이스에서 강제 매개 변수화를 사용하면 도움이 될 수 있습니다.Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

PARAMETERIZATION 옵션을 FORCED로 설정하면 임의의 형식으로 전송된 SELECT, INSERT, UPDATE또는 DELETE 문에 표시되는 리터럴 값이 쿼리 컴파일 중에 매개 변수로 변환됩니다.When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE, or DELETE statement, submitted in any form, is converted to a parameter during query compilation. 그러나 다음 쿼리 구문에 나타나는 리터럴은 예외입니다.The exceptions are literals that appear in the following query constructs:

  • INSERT...EXECUTE 문.INSERT...EXECUTE statements.
  • 저장 프로시저, 트리거 또는 사용자 정의 함수의 본문 안에 있는 문.Statements inside the bodies of stored procedures, triggers, or user-defined functions. SQL ServerSQL Server는 이미 이러한 루틴에 대한 쿼리 계획을 재사용하고 있습니다. already reuses query plans for these routines.
  • 클라이언트 쪽 응용 프로그램에서 이미 매개 변수화된 준비된 문Prepared statements that have already been parameterized on the client-side application.
  • XQuery 메서드 호출이 포함된 문. 이러한 문에서는 WHERE 절과 같이 해당 인수가 일반적으로 매개 변수화되는 컨텍스트에서 메서드가 나타납니다.Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. 해당 인수가 매개 변수화되지 않는 컨텍스트에서 메서드가 나타날 경우에는 문의 나머지 부분이 매개 변수화됩니다.If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Transact-SQL 커서(Transact-SQL cursor) 내의 문.Statements inside a Transact-SQL cursor. API 커서 내의SELECT 문은 매개 변수화됩니다.(SELECT statements inside API cursors are parameterized.)
  • 사용되지 않는 쿼리 구문Deprecated query constructs.
  • ANSI_PADDING 또는 ANSI_NULLSOFF로 설정한 컨텍스트에서 실행되는 모든 문.Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
  • 매개 변수화하기에 적합한 리터럴이 2,097개 이상 포함된 문Statements that contain more than 2,097 literals that are eligible for parameterization.
  • WHERE T.col2 >= @bb와 같은 변수를 참조하는 문Statements that reference variables, such as WHERE T.col2 >= @bb.
  • RECOMPILE 쿼리 힌트를 포함하는 문.Statements that contain the RECOMPILE query hint.
  • COMPUTE 절을 포함하는 문.Statements that contain a COMPUTE clause.
  • WHERE CURRENT OF 절을 포함하는 문.Statements that contain a WHERE CURRENT OF clause.

또한 다음 쿼리 절은 매개 변수화되지 않습니다.Additionally, the following query clauses are not parameterized. 이러한 경우 해당 절만 매개 변수화되지 않습니다.Note that in these cases, only the clauses are not parameterized. 동일한 쿼리 내의 다른 절에는 강제 매개 변수화가 적용될 수 있습니다.Other clauses within the same query may be eligible for forced parameterization.

  • 모든 SELECT 문의 <select_list>.The <select_list> of any SELECT statement. 여기에는 하위 쿼리의 SELECT 목록과 INSERT 문 내의 SELECT 목록도 포함됩니다.This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
  • SELECT 문에 나타나는 하위 쿼리 IF 문.Subquery SELECT statements that appear inside an IF statement.
  • 쿼리의 TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO또는 FOR XML 절.The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
  • OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML또는 모든 FULLTEXT 연산자에 대한 직접 인수 또는 하위 식으로서의 인수.Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • LIKE 절의 pattern 및 escape_character 인수.The pattern and escape_character arguments of a LIKE clause.
  • CONVERT 절의 style 인수.The style argument of a CONVERT clause.
  • IDENTITY 절 내의 정수 상수Integer constants inside an IDENTITY clause.
  • ODBC 확장 구문을 사용하여 지정한 상수Constants specified by using ODBC extension syntax.
  • +, -, *, / 및 % 연산자의 인수인 상수 폴딩 가능 식.Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. SQL ServerSQL Server에서는 식이 강제 매개 변수화에 적합한지 결정할 때 다음 조건 중 하나가 True이면 상수 폴딩 가능 식으로 간주합니다.When considering eligibility for forced parameterization, SQL ServerSQL Server considers an expression to be constant-foldable when either of the following conditions is true:
    • 식에 열, 변수 또는 하위 쿼리가 나타나지 않습니다.No columns, variables, or subqueries appear in the expression.
    • 식에 CASE 절이 포함됩니다.The expression contains a CASE clause.
  • 쿼리 힌트 절에 대한 인수.Arguments to query hint clauses. 여기에는 number_of_rows 쿼리 힌트의 FAST 인수, number_of_processors 쿼리 힌트의 MAXDOP 인수 및 MAXRECURSION 쿼리 힌트의 숫자 인수가 포함됩니다.These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

매개 변수화는 개별 Transact-SQL 문 수준에서 수행됩니다.Parameterization occurs at the level of individual Transact-SQL statements. 다시 말해 일괄 처리 내의 개별 문이 매개 변수화됩니다.In other words, individual statements in a batch are parameterized. 컴파일 후 매개 변수가 있는 쿼리는 쿼리가 원래 전송되었던 일괄 처리의 컨텍스트에서 실행됩니다.After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. 쿼리의 실행 계획이 캐시된 경우에는 sys.syscacheobjects 동적 관리 뷰의 sql 열을 참조하여 쿼리가 매개 변수화되었는지 여부를 확인할 수 있습니다.If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. 쿼리가 매개 변수화된 경우 (@1 tinyint)와 같이 이 열에서 매개 변수의 이름 및 데이터 형식은 전송된 일괄 처리 텍스트 앞에 옵니다.If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint).

참고

매개 변수 이름은 임의로 지정하므로Parameter names are arbitrary. 사용자나 응용 프로그램에서는 특정 명명 순서를 따를 필요가 없습니다.Users or applications should not rely on a particular naming order. 또한 매개 변수 이름, 매개 변수화되는 리터럴 선택 항목 및 매개 변수화된 텍스트의 공백은 SQL ServerSQL Server 및 서비스 팩 업그레이드의 버전에 따라 달라질 수 있습니다.Also, the following can change between versions of SQL ServerSQL Server and Service Pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

매개 변수의 데이터 형식Data Types of Parameters

SQL ServerSQL Server에서 리터럴을 매개 변수화하면 매개 변수가 다음 데이터 형식으로 변환됩니다.When SQL ServerSQL Server parameterizes literals, the parameters are converted to the following data types:

  • 정수 리터럴은 그 크기가 int 데이터 형식에 적합하면 int로 매개 변수화됩니다. 비교 연산자와 관련된 조건자의 일부인 큰 정수 리터럴(<, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEENIN 포함)은 numeric(38,0)으로 매개 변수화됩니다.Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN, and IN) parameterize to numeric(38,0). 비교 연산자와 관련된 조건자의 일부가 아닌 큰 리터럴은 전체 자릿수가 리터럴의 크기를 지원할 만큼 크고 소수 자릿수가 0인 numeric으로 매개 변수화됩니다.Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.
  • 비교 연산자와 관련된 조건자의 일부인 고정 소수점 숫자 리터럴은 전체 자릿수가 38이고 소수 자릿수가 리터럴의 크기를 지원할 만큼 큰 numeric으로 매개 변수화됩니다.Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. 비교 연산자와 관련된 조건자의 일부가 아닌 고정 소수점 숫자 리터럴은 전체 자릿수 및 소수 자릿수가 리터럴의 크기를 지원할 만큼 큰 numeric으로 매개 변수화됩니다.Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.
  • 부동 소수점 숫자 리터럴은 float(53)으로 매개 변수화됩니다.Floating point numeric literals parameterize to float(53).
  • 비유니코드 문자열 리터럴은 리터럴의 크기가 8,000자 내일 때는 varchar(8000)로 매개 변수화되고 8,000자보다 클 때는 varchar(max)로 매개 변수화됩니다.Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.
  • 유니코드 문자열 리터럴은 리터럴의 크기가 유니코드 문자로 4,000자 내일 때는 nvarchar(4000)로 매개 변수화되고 4,000자보다 클 때는 nvarchar(max)로 매개 변수화됩니다.Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.
  • 이진 리터럴은 리터럴 크기가 8,000바이트 내일 때는 varbinary(8000)로 매개 변수화되고Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. 8,000바이트보다 클 때는 varbinary(max)로 변환됩니다.If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • 통화 유형 리터럴은 money로 매개 변수화됩니다.Money type literals parameterize to money.

강제 매개 변수화 사용 지침 Guidelines for Using Forced Parameterization

PARAMETERIZATION 옵션을 FORCED로 설정할 때는 다음 사항을 고려해야 합니다.Consider the following when you set the PARAMETERIZATION option to FORCED:

  • 강제 매개 변수화를 적용하면 쿼리 컴파일 시 쿼리의 리터럴 상수가 매개 변수로 변경됩니다.Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. 따라서 쿼리 최적화 프로그램에서는 만족스럽지 못한 쿼리 계획을 선택할 수 있습니다.Therefore, the Query Optimizer might choose suboptimal plans for queries. 특히 쿼리 최적화 프로그램에서는 인덱싱된 뷰 또는 계산 열의 인덱스에 쿼리를 대응시키지 못할 수 있습니다.In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. 또한 분할된 테이블 및 분산형 분할 뷰에 대해 만족스럽지 못한 쿼리 계획을 선택할 수도 있습니다.It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. 계산 열의 인덱스 및 인덱싱된 뷰를 많이 사용하는 환경에서는 강제 매개 변수화를 사용하면 안 됩니다.Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. 일반적으로 PARAMETERIZATION FORCED 옵션은 숙련된 데이터베이스 관리자가 성능에 영향을 주지 않는다는 것을 확인한 후에만 사용해야 합니다.Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
  • 둘 이상의 데이터베이스를 참조하는 분산 쿼리에 강제 매개 변수화를 사용하면 좋습니다. 단, 쿼리가 실행되는 데이터베이스의 컨텍스트에서 PARAMETERIZATION 옵션이 FORCED 로 설정되어 있어야 합니다.Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCED in the database whose context the query is running.
  • PARAMETERIZATION 옵션을 FORCED 로 설정하면 현재 컴파일되거나 다시 컴파일되거나 실행되고 있는 쿼리 계획을 제외한 모든 쿼리 계획이 데이터베이스의 계획 캐시에서 플러시됩니다.Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. 설정을 변경할 때 컴파일 또는 실행 중이었던 쿼리 계획은 다음에 쿼리가 실행될 때 매개 변수화됩니다.Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
  • PARAMETERIZATION 옵션을 설정하는 작업은 온라인으로 수행되므로 데이터베이스 수준의 배타적 잠금이 필요하지 않습니다.Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
  • 현재 PARAMETERIZATION 옵션 설정은 데이터베이스를 다시 연결하거나 복원할 때도 그대로 유지됩니다.The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

단일 쿼리 또는 구문은 동일하고 매개 변수 값만 다른 기타 쿼리는 단순 매개 변수화되지 않도록 지정하여 강제 매개 변수화의 동작을 무시할 수 있습니다.You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. 반대로 데이터베이스에서 강제 매개 변수화가 해제된 경우에도 구문이 동일한 쿼리에 한해 강제 매개 변수화가 수행되도록 지정할 수 있습니다.Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. 이와 같은 작업을 수행할 때계획 지침 을 사용합니다.Plan guides are used for this purpose.

참고

PARAMETERIZATION 옵션이 FORCED로 설정되어 있으면 PARAMETERIZATION 옵션이 SIMPLE로 설정되어 있는 경우와 오류 메시지 보고가 다를 수 있습니다. 강제 매개 변수화에서는 여러 오류 메시지가 보고될 수 있지만 단순 매개 변수화에서는 더 적은 메시지가 보고되며, 오류가 발생한 줄 번호가 잘못 보고될 수 있습니다.When the PARAMETERIZATION option is set to FORCED, the reporting of error messages may differ from when the PARAMETERIZATION option is set to SIMPLE: multiple error messages may be reported under forced parameterization, where fewer messages would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.

SQL 문 준비Preparing SQL Statements

SQL ServerSQL Server 관계형 엔진에서는 SQL 문을 실행하기 전에 문을 준비할 수 있는 기능을 제공합니다.The SQL ServerSQL Server relational engine introduces full support for preparing SQL statements before they are executed. 응용 프로그램에서 SQL 문을 여러 번 실행해야 하는 경우에는 데이터베이스 API를 사용하여 다음을 수행할 수 있습니다.If an application has to execute an SQL statement several times, it can use the database API to do the following:

  • 문을 한 번 준비합니다.Prepare the statement once. 이렇게 하면 SQL 문이 실행 계획으로 컴파일됩니다.This compiles the SQL statement into an execution plan.
  • 문을 실행해야 할 때마다 미리 컴파일한 실행 계획을 실행합니다.Execute the precompiled execution plan every time it has to execute the statement. 이렇게 하면 첫 번째 실행 이후 실행할 때마다 SQL 문을 다시 컴파일할 필요가 없습니다.This prevents having to recompile the SQL statement on each execution after the first time.
    문 준비 및 실행은 API 함수 및 메서드에 의해 제어됩니다.Preparing and executing statements is controlled by API functions and methods. 이 기능은 Transact-SQL 언어가 아닙니다.It is not part of the Transact-SQL language. SQL 문 실행에 대한 준비/실행 모델은 SQL ServerSQL Server Native Client OLE DB 공급자 및 SQL ServerSQL Server Native Client ODBC 드라이버에 의해 지원됩니다.The prepare/execute model of executing SQL statements is supported by the SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver. 준비 요청 시 공급자 또는 드라이버가 문 준비 요청과 함께 문을 SQL ServerSQL Server에 보냅니다.On a prepare request, either the provider or the driver sends the statement to SQL ServerSQL Server with a request to prepare the statement. SQL ServerSQL Server는 실행 계획을 컴파일하고 해당 계획에 대한 핸들을 공급자 또는 드라이버에 반환합니다. compiles an execution plan and returns a handle for that plan to the provider or driver. 실행 요청 시, 공급자 또는 드라이버는 핸들과 관련된 계획의 실행 요청을 서버에 보냅니다.On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.

준비된 문은 SQL ServerSQL Server에서 임시 개체를 만드는 데 사용할 수 없습니다.Prepared statements cannot be used to create temporary objects on SQL ServerSQL Server. 준비된 문은 임시 테이블과 같은 임시 개체를 만드는 시스템 저장 프로시저를 참조할 수 없습니다.Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. 이러한 프로시저는 직접 실행해야 합니다.These procedures must be executed directly.

준비/실행 모델을 과도하게 사용하면 성능이 저하될 수 있습니다.Excess use of the prepare/execute model can degrade performance. 문이 한 번만 실행되는 경우 직접 실행은 서버로의 네트워크 왕복을 1회만 필요로 합니다.If a statement is executed only once, a direct execution requires only one network round-trip to the server. 한 번만 실행되는 SQL 문을 준비하고 실행하면 네트워크 왕복이 추가로 필요합니다. 즉 문을 준비하는 데 한 번, 문을 실행하는 데 한 번이 필요합니다.Preparing and executing an SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.

매개 변수 표식이 사용되는 경우 문을 준비하는 것이 좀 더 효과적입니다.Preparing a statement is more effective if parameter markers are used. 예를 들어 응용 프로그램이 종종 AdventureWorks 샘플 데이터베이스의 제품 정보 검색 요청을 받는 경우를 생각해 봅시다.For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. 응용 프로그램에서는 두 가지 방법으로 이를 처리할 수 있습니다.There are two ways the application can do this.

첫째, 응용 프로그램이 요청된 각 제품에 대해 별도의 쿼리를 실행할 수 있습니다.Using the first way, the application can execute a separate query for each product requested:

SELECT * FROM AdventureWorks2014.Production.Product
WHERE ProductID = 63;

둘째, 응용 프로그램에서 다음을 수행합니다.Using the second way, the application does the following:

  1. 매개 변수 표식(?)을 포함하는 문을 준비합니다.Prepares a statement that contains a parameter marker (?):
    tsql SELECT * FROM AdventureWorks2014.Production.Product WHERE ProductID = ?;
  2. 프로그램 변수를 매개 변수 표식에 바인딩합니다.Binds a program variable to the parameter marker.
  3. 제품 정보가 필요할 때마다 키 값으로 바인딩된 변수를 채우고 문을 실행합니다.Each time product information is needed, fills the bound variable with the key value and executes the statement.

두 번째 방법은 문이 네 번 이상 실행될 때 좀 더 효율적입니다.The second way is more efficient when the statement is executed more than three times.

SQL ServerSQL Server에서는 준비/실행 모델이 직접 실행에 비해 성능상의 큰 이점이 없는데 이는 SQL ServerSQL Server에서 실행 계획을 재사용하기 때문입니다.In SQL ServerSQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL ServerSQL Server reuses execution plans. SQL ServerSQL Server의 알고리즘은 현재 SQL 문을 이전에 이 SQL 문을 실행하기 위해 생성한 실행 계획과 비교할 때 효율적입니다. has efficient algorithms for matching current SQL statements with execution plans that are generated for prior executions of the same SQL statement. 응용 프로그램이 매개 변수 표식을 사용하여 여러 번 SQL 문을 실행하는 경우 SQL ServerSQL Server에서는 해당 계획이 계획 캐시에서 에이징되지 않는 이상 두 번째 실행부터는 첫 번째 실행의 실행 계획을 재사용합니다.If an application executes a SQL statement with parameter markers multiple times, SQL ServerSQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache). 준비/실행 모델은 여전히 다음과 같은 이점을 제공합니다.The prepare/execute model still has these benefits:

  • 식별 핸들로 실행 계획을 찾는 것은 SQL 문을 기존 실행 계획과 비교하는 알고리즘보다 더 효율적입니다.Finding an execution plan by an identifying handle is more efficient than the algorithms used to match an SQL statement to existing execution plans.
  • 응용 프로그램이 실행 계획이 만들어지고 재사용되는 시기를 제어할 수 있습니다.The application can control when the execution plan is created and when it is reused.
  • 준비/실행 모델은 이전 버전의 SQL ServerSQL Server를 비롯한 다른 데이터베이스로 이식 가능합니다.The prepare/execute model is portable to other databases, including earlier versions of SQL ServerSQL Server.

매개 변수 검색 Parameter Sniffing

“매개 변수 검사”는 컴파일 또는 재컴파일을 수행하는 동안 SQL ServerSQL Server에서 현재 매개 변수 값을 “검사”하고 쿼리 최적화 프로그램에 전달하여 해당 값이 더욱 효율적인 쿼리 실행 계획을 생성하는 데 사용될 수 있도록 하는 프로세스를 나타냅니다."Parameter sniffing" refers to a process whereby SQL ServerSQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

컴파일 또는 재컴파일을 수행하는 동안 다음 유형의 일괄 처리에 대해 매개 변수 값을 검사합니다.Parameter values are sniffed during compilation or recompilation for the following types of batches:

  • 저장 프로시저Stored procedures
  • sp_executesql을 통해 제출된 쿼리Queries submitted via sp_executesql
  • 준비된 쿼리Prepared queries
참고

RECOMPILE 힌트를 사용하는 쿼리는 매개 변수 값과 지역 변수의 현재 값을 모두 검사합니다.For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. 검사되는 매개 변수 및 지역 변수 값은 RECOMPILE 힌트가 포함된 문 바로 앞에 있는 일괄 처리 위치의 값입니다.The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. 특히 매개 변수의 경우 일괄 처리 호출과 함께 사용된 값은 검사하지 않습니다.In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

병렬 쿼리 처리Parallel Query Processing

SQL ServerSQL Server에서는 마이크로프로세서(CPU)를 두 개 이상 사용하는 컴퓨터에서 쿼리 실행과 인덱스 작업을 최적화하는 병렬 쿼리 기능을 제공합니다. provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). SQL ServerSQL Server는 여러 개의 운영 체제 작업자 스레드로 쿼리나 인덱스 작업을 병렬 수행할 수 있으므로 작업을 빠르고 효율적으로 완료할 수 있습니다.Because SQL ServerSQL Server can perform a query or index operation in parallel by using several operating system worker threads, the operation can be completed quickly and efficiently.

쿼리를 최적화하는 동안 SQL ServerSQL Server는 병렬 실행에 적합한 쿼리나 인덱스 작업을 찾습니다.During query optimization, SQL ServerSQL Server looks for queries or index operations that might benefit from parallel execution. 이러한 쿼리에 대해 SQL ServerSQL Server는 쿼리 실행 계획에 교환 연산자를 삽입하여 병렬 실행할 쿼리를 준비합니다.For these queries, SQL ServerSQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. 교환 연산자는 프로세스 관리, 데이터 재배포 및 흐름 제어를 제공하는 쿼리 실행 계획의 연산자입니다.An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. 교환 연산자에는 하위 유형으로 Distribute Streams, Repartition StreamsGather Streams 논리 연산자가 포함되며 이 중에서 하나 이상이 병렬 쿼리에 대한 쿼리 계획의 실행 계획 출력에 표시될 수 있습니다.The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

교환 연산자를 삽입하면 병렬 쿼리 실행 계획이 완성됩니다.After exchange operators are inserted, the result is a parallel-query execution plan. 병렬 쿼리 실행 계획은 작업자 스레드를 여러 개 사용할 수 있습니다.A parallel-query execution plan can use more than one worker thread. 병렬이 아닌 쿼리에서 사용하는 직렬 실행 계획은 실행에 한 작업자 스레드만 사용합니다.A serial execution plan, used by a nonparallel query, uses only one worker thread for its execution. 병렬 쿼리에서 사용하는 실제 작업자 스레드 수는 쿼리 계획 실행 초기화 시 결정되며 계획의 복잡성과 병렬 처리 수준에 따라 다릅니다.The actual number of worker threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. 병렬 처리 수준에 따라 사용할 최대 CPU 수가 결정됩니다. 사용할 작업자 스레드 수를 의미하지는 않습니다.Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. 병렬 처리 수준 값은 서버 수준에서 설정되며 sp_configure 시스템 저장 프로시저를 사용하여 수정할 수 있습니다.The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. 또한 MAXDOP 쿼리 힌트나 MAXDOP 인덱스 옵션을 지정하여 개별 쿼리나 인덱스 문에 대해 이 값을 재정의할 수 있습니다.You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

SQL ServerSQL Server 쿼리 최적화 프로그램은 다음 중 해당하는 조건이 있을 경우 병렬 실행 계획을 사용하지 않습니다.The SQL ServerSQL Server Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • 쿼리의 직렬 실행 비용이 크지 않아 병렬 실행 계획을 대안으로 고려할 필요가 없습니다.The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • 직렬 실행 계획이 특정 쿼리에 사용 가능한 병렬 실행 계획보다 더 빠릅니다.A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • 쿼리에 병렬로 실행할 수 없는 스칼라 또는 관계형 연산자가 포함되어 있습니다.The query contains scalar or relational operators that cannot be run in parallel. 특정 연산자는 쿼리 계획의 한 섹션이 직렬 모드로 실행되도록 하거나 전체 계획이 직렬 모드로 실행되도록 할 수 있습니다.Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

병렬 처리 수준 Degree of Parallelism

SQL ServerSQL Server에서는 병렬 쿼리 실행 또는 인덱스 DDL(데이터 정의 언어) 작업 각각의 인스턴스에 대해 가장 적합한 병렬 처리 수준이 자동으로 검색됩니다. automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. 이것은 다음과 조건을 기준으로 수행됩니다.It does this based on the following criteria:

  1. SMP(대칭적 다중 처리) 컴퓨터와 같이 둘 이상의 마이크로프로세서 또는 CPU가 있는 컴퓨터에서 SQL ServerSQL Server가 실행 중인지 여부Whether SQL ServerSQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP).
    두 개 이상의 CPU가 있는 컴퓨터에서만 병렬 쿼리를 사용할 수 있습니다.Only computers that have more than one CPU can use parallel queries.

  2. 사용할 수 있는 작업자 스레드 수가 충분한지 여부Whether sufficient worker threads are available.
    각 쿼리 또는 인덱스 작업을 실행하려면 일정 수의 작업자 스레드가 필요합니다.Each query or index operation requires a certain number of worker threads to execute. 병렬 계획을 실행하려면 직렬 계획보다 많은 작업자 스레드가 필요하고, 필요한 작업자 스레드의 수는 병렬 처리 수준에 따라 증가합니다.Executing a parallel plan requires more worker threads than a serial plan, and the number of required worker threads increases with the degree of parallelism. 특정 병렬 처리 수준에 대한 병렬 계획의 작업자 스레드 요구 사항이 충족되지 않는 경우에는 SQL Server 데이터베이스 엔진SQL Server Database Engine에서 병렬 처리 수준을 자동으로 낮추거나 지정된 작업 컨텍스트의 병렬 계획을 완전히 중단합니다.When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the SQL Server 데이터베이스 엔진SQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. 그런 다음 하나의 작업자 스레드만 사용되는 직렬 계획을 실행합니다.It then executes the serial plan (one worker thread).

  3. 실행한 쿼리 또는 인덱스 작업의 유형The type of query or index operation executed.
    병렬 계획은 인덱스를 새로 작성 또는 다시 작성하거나 클러스터형 인덱스 및 CPU 주기 사용량이 큰 쿼리를 삭제하는 등의 인덱스 작업에 적합합니다.Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. 예를 들어 대형 테이블의 조인, 대규모 집계 및 대형 결과 집합의 정렬이 병렬 쿼리에 적절합니다.For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. 주로 트랜잭션 처리 응용 프로그램에서 사용되는 단순 쿼리의 경우 이 쿼리를 병렬로 실행하는 데 필요한 추가 조정 작업은 성능을 향상시키기보다는 부담이 됩니다.Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. 병렬 처리로 유용한 쿼리와 그렇지 않은 쿼리를 구분하기 위해, SQL Server 데이터베이스 엔진SQL Server Database Engine은 쿼리 또는 인덱스 작업 실행 시 예상 비용을 병렬 처리에 대한 비용 임계값 값과 비교합니다.To distinguish between queries that benefit from parallelism and those that do not benefit, The SQL Server 데이터베이스 엔진SQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. 권장되지는 않지만 사용자들은 sp_configure를 사용하여 기본값 5를 변경할 수 있습니다.Although not recommended, users can change the default value of 5 using sp_configure.

  4. 처리할 행 수가 충분한지 여부Whether there are a sufficient number of rows to process.
    쿼리 최적화 프로그램에서 행 수가 부족하다고 판단하는 경우 행을 배포하기 위해 교환 연산자를 사용하지 않습니다.If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. 결과적으로 연산자는 직렬로 실행됩니다.Consequently, the operators are executed serially. 시작, 배포 및 조정 비용이 병렬 연산자 실행으로 얻은 이익보다 큰 경우 연산자를 직렬 계획으로 실행하면 이 시나리오를 피할 수 있습니다.Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.

  5. 최신 배포 통계를 사용할 수 있는지 여부Whether current distribution statistics are available.
    가장 높은 병렬 처리 수준을 제공할 수 없는 경우 병렬 처리를 중단하기 전에 더 낮은 병렬 처리 수준이 가능한지 확인합니다.If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
    예를 들어 뷰에서 클러스터형 인덱스를 만드는 경우 클러스터형 인덱스가 아직 생성되지 않았으므로 배포 통계를 계산할 수 없습니다.For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. 이 경우 SQL Server 데이터베이스 엔진SQL Server Database Engine은 이 인덱스 작업에는 가장 높은 병렬 처리 수준을 할당하지 않습니다.In this case, the SQL Server 데이터베이스 엔진SQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. 그러나 이 경우에도 정렬 또는 검색과 같은 일부 연산자에는 병렬 처리의 이점이 적용될 수 있습니다.However, some operators, such as sorting and scanning, can still benefit from parallel execution.

참고

병렬 인덱스 작업은 SQL ServerSQL Server Enterprise, Developer 및 Evaluation Edition에서만 사용할 수 있습니다.Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

SQL Server 데이터베이스 엔진SQL Server Database Engine은 실행 시 앞서 설명한 현재 시스템 작업과 구성 정보에서 병렬 실행이 가능한지 확인합니다.At execution time, the SQL Server 데이터베이스 엔진SQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. 병렬 실행이 보장되는 경우 SQL Server 데이터베이스 엔진SQL Server Database Engine은 최적의 작업자 스레드 수를 결정하고 이 작업자 스레드에 병렬 계획을 분산하여 실행합니다.If parallel execution is warranted, the SQL Server 데이터베이스 엔진SQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. 병렬 실행을 위해 쿼리 또는 인덱스 작업이 여러 작업자 스레드에서 실행되기 시작하면 해당 작업이 완료될 때까지 동일한 수의 작업자 스레드가 사용됩니다.When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. SQL Server 데이터베이스 엔진SQL Server Database Engine은 계획 캐시에서 실행 계획을 가져올 때마다 최적의 작업자 스레드 수를 다시 검사합니다.The SQL Server 데이터베이스 엔진SQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. 예를 들어 쿼리를 한 번 실행할 때는 직렬 계획을 사용할 수 있고 동일한 쿼리를 두 번째 실행할 때는 세 개의 작업자 스레드를 사용하는 병렬 계획을 사용할 수 있으며 이 쿼리를 세 번째로 실행할 때는 네 개의 작업자 스레드를 사용하는 병렬 계획을 사용할 수 있습니다.For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

병렬 쿼리 실행 계획에서 삽입, 업데이트 및 삭제 작업은 직렬로 실행됩니다.In a parallel query execution plan, the insert, update, and delete operators are executed serially. 그러나 UPDATE 또는 DELETE 문의 WHERE 절이나 INSERT 문의 SELECT 부분은 병렬로 실행될 수 있습니다.However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. 그런 다음 실제 데이터 변경 내용은 데이터베이스에 직렬로 적용됩니다.The actual data changes are then serially applied to the database.

정적 커서 및 키 집합 커서는 병렬 실행 계획에 따라 채워질 수 있습니다.Static and keyset-driven cursors can be populated by parallel execution plans. 그러나 동적 커서의 동작은 직렬 실행에 의해서만 제공될 수 있습니다.However, the behavior of dynamic cursors can be provided only by serial execution. 쿼리 최적화 프로그램은 동적 커서에 포함된 쿼리에 대해서는 항상 직렬 실행 계획을 생성합니다.The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

병렬 처리 수준 재정의Overriding Degrees of Parallelism

MAXDOP(최대 병렬 처리 수준) 서버 구성 옵션( SQL 데이터베이스SQL Database에서 ALTER DATABASE SCOPED CONFIGURATION)을 사용하여 병렬 계획 실행에 사용할 프로세서 수를 제한할 수 있습니다.You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on SQL 데이터베이스SQL Database ) to limit the number of processors to use in parallel plan execution. 개별 쿼리 및 인덱스 작업 문에 대한 max degree of parallelism 옵션은 MAXDOP 쿼리 힌트나 MAXDOP 인덱스 옵션을 지정하여 재정의할 수 있습니다.The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. MAXDOP 옵션을 사용하면 개별 쿼리 작업과 인덱스 작업을 보다 상세히 제어할 수 있습니다.MAXDOP provides more control over individual queries and index operations. 예를 들어 MAXDOP 옵션을 사용하여 온라인 인덱스 작업 전용으로 사용되는 프로세서의 수를 늘리거나 줄일 수 있습니다.For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. 이런 방법으로 인덱스 작업에 사용되는 리소스와 동시 사용자의 리소스 간에 균형을 유지할 수 있습니다.In this way, you can balance the resources used by an index operation with those of the concurrent users.

최대 병렬 처리 수준 옵션을 0(기본값)으로 설정하면 SQL ServerSQL Server에서 사용 가능한 모든 프로세서(최대 64개)를 병렬 계획 실행에 사용할 수 있습니다.Setting the max degree of parallelism option to 0 (default) enables SQL ServerSQL Server to use all available processors up to a maximum of 64 processors in a parallel plan execution. MAXDOP 옵션을 0으로 설정하면 SQL ServerSQL Server에서 64개 논리적 프로세서의 런타임 대상을 설정해도 필요한 경우 다른 값을 수동으로 설정할 수 있습니다.Although SQL ServerSQL Server sets a runtime target of 64 logical processors when MAXDOP option is set to 0, a different value can be manually set if needed. 쿼리와 인덱스에 대해 MAXDOP를 0으로 설정하면 SQL ServerSQL Server에서 사용 가능한 모든 프로세서(최대 64개)를 병렬 계획 실행의 지정된 쿼리 또는 인덱스에 대해 사용할 수 있습니다.Setting MAXDOP to 0 for queries and indexes allows SQL ServerSQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution. MAXDOP는 일부 병렬 쿼리에만 적용되는 값이나 병렬 처리에 적합한 모든 쿼리의 미정 대상입니다.MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. 즉, 런타임에 사용할 수 있는 작업자 스레드가 충분하지 않은 경우 MAXDOP 서버 구성 옵션보다 낮은 병렬 처리 수준으로 쿼리를 실행할 수 있습니다.This means that if not enough worker threads are available at runtime, a query may execute with a lower degree of parallelism than the MAXDOP server configuration option.

MAXDOP 구성에 대한 모범 사례는 이 Microsoft 지원 문서를 참조하십시오.Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

병렬 쿼리 예제Parallel Query Example

다음 쿼리에서는 2000년 4월 1일에 시작하는 특정 분기 동안의 주문 수를 계산합니다. 이 기간 동안에는 최소한 한 개의 주문 상품이 약속한 날짜보다 늦게 고객에게 배달되었습니다.The following query counts the number of orders placed in a specific quarter, starting on April 1, 2000, and in which at least one line item of the order was received by the customer later than the committed date. 이 쿼리는 각 주문 우선 순위에 따라 그룹화되고 우선 순위를 오름차순으로 정렬하여 각 주문의 수를 표시합니다.This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

다음 예에서는 가상의 테이블 및 열 이름을 사용합니다.This example uses theoretical table and column names.

SELECT o_orderpriority, COUNT(*) AS Order_Count
FROM orders
WHERE o_orderdate >= '2000/04/01'
   AND o_orderdate < DATEADD (mm, 3, '2000/04/01')
   AND EXISTS
         (
          SELECT *
            FROM    lineitem
            WHERE l_orderkey = o_orderkey
               AND l_commitdate < l_receiptdate
         )
   GROUP BY o_orderpriority
   ORDER BY o_orderpriority

다음 인덱스가 lineitemorders 테이블에서 정의된다고 가정합니다.Assume the following indexes are defined on the lineitem and orders tables:

CREATE INDEX l_order_dates_idx 
   ON lineitem
      (l_orderkey, l_receiptdate, l_commitdate, l_shipdate)

CREATE UNIQUE INDEX o_datkeyopr_idx
   ON ORDERS
      (o_orderdate, o_orderkey, o_custkey, o_orderpriority)

다음은 위에 표시된 쿼리에 대해 생성될 수 있는 병렬 계획 중 하나입니다.Here is one possible parallel plan generated for the query previously shown:

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        |    |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        |         |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

parallel_plan DOP가 4이고 두 테이블의 조인을 포함하는 쿼리 계획parallel_plan Query plan with DOP 4, involves a two-table join

위 명령에서는 병렬 처리 수준이 4로 실행되고 두 개의 테이블 조인을 포함하는 쿼리 최적화 프로그램 계획을 보여 줍니다.The illustration shows a Query Optimizer plan executed with a degree of parallelism equal to 4 and involving a two-table join.

이 병렬 계획에는 세 개의 Parallelism 연산자가 포함됩니다.The parallel plan contains three parallelism operators. o_datkey_ptr 인덱스의 Index Seek 연산자와 l_order_dates_idx 인덱스의 Index Scan 연산자가 모두 병렬로 처리됩니다.Both the Index Seek operator of the o_datkey_ptr index and the Index Scan operator of the l_order_dates_idx index are performed in parallel. 몇 개의 배타적 스트림이 생성됩니다.This produces several exclusive streams. 이것은 각각 Index Scan 및 Index Seek 연산자 위의 가장 가까운 Parallelism 연산자에서 결정될 수 있습니다.This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. 두 연산자는 모두 교환 유형을 다시 분할합니다.Both are repartitioning the type of exchange. 즉, 입력 스트림 수와 동일한 수의 출력 스트림을 생성하는 스트림 사이에서 단지 데이터의 순서를 섞는 것입니다.That is, they are just reshuffling data among the streams and producing the same number of streams on their output as they have on their input. 이 스트림 수는 병렬 처리 수준과 같습니다.This number of streams is equal to the degree of parallelism.

l_order_dates_idx Index Scan 연산자 위의 Parallelism 연산자는 L_ORDERKEY 값을 키로 사용하여 입력 스트림을 다시 분할합니다.The parallelism operator above the l_order_dates_idx Index Scan operator is repartitioning its input streams using the value of L_ORDERKEY as a key. 이러한 방식으로 동일한 L_ORDERKEY 값은 동일한 출력 스트림을 생성합니다.In this way, the same values of L_ORDERKEY end up in the same output stream. 동시에 출력 스트림은 L_ORDERKEY 열에서 그 순서를 유지하여 Merge Join 연산자의 입력 요구 사항을 충족시킵니다.At the same time, output streams maintain the order on the L_ORDERKEY column to meet the input requirement of the Merge Join operator.

Index Seek 연산자 위의 Parallelism 연산자는 O_ORDERKEY 값을 사용하여 입력 스트림을 다시 분할합니다.The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY. 이 입력은 O_ORDERKEY 열 값을 기준으로 정렬되지 않았으며 Merge Join 연산자를 통한 조인 열이므로 Parallelism 연산자와 Merge Join 연산자 사이에 있는 Sort 연산자가 Merge Join 연산자에 대한 입력을 조인 열을 기준으로 정렬합니다.Because its input is not sorted on the O_ORDERKEY column values and this is the join column in the Merge Join operator, the Sort operator between the parallelism and Merge Join operators make sure that the input is sorted for the Merge Join operator on the join columns. Sort 연산자는 Merge Join 연산자처럼 병렬로 처리됩니다.The Sort operator, like the Merge Join operator, is performed in parallel.

최상위 Parallelism 연산자는 여러 스트림의 결과를 단일 스트림으로 수집합니다.The topmost parallelism operator gathers results from several streams into a single stream. 그런 다음 Parallelism 연산자 아래의 Stream Aggregate 연산자에서 수행하는 부분 집계는 Parallelism 연산자 위의 Stream Aggregate 연산자의 서로 다른 각각의 O_ORDERPRIORITY 값에 대해 단일 SUM 값으로 누적됩니다.Partial aggregations performed by the Stream Aggregate operator below the parallelism operator are then accumulated into a single SUM value for each different value of the O_ORDERPRIORITY in the Stream Aggregate operator above the parallelism operator. 이 계획에는 병렬 처리 수준이 4인 두 개의 교환 세그먼트가 있으므로 8개의 작업자 스레드가 사용됩니다.Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

병렬 인덱스 작업Parallel Index Operations

인덱스를 만들거나 다시 작성하는 인덱스 작업 또는 클러스터형 인덱스를 삭제하는 인덱스 작업을 위해 작성된 쿼리 계획에서는 여러 마이크로프로세서가 있는 컴퓨터에서 병렬 다중 작업자 스레드 작업을 할 수 있습니다.The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-worker threaded operations on computers that have multiple microprocessors.

참고

병렬 인덱스 작업은 SQL Server 2008SQL Server 2008부터 Enterprise Edition에서만 사용할 수 있습니다.Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008SQL Server 2008.

SQL ServerSQL Server는 다른 쿼리에 사용하는 것과 동일한 알고리즘을 사용하여 인덱스 작업에 대한 병렬 처리 수준(실행할 총 개별 작업자 스레드 수)을 결정합니다. uses the same algorithms to determine the degree of parallelism (the total number of separate worker threads to run) for index operations as it does for other queries. 인덱스 작업에 대한 최대 병렬 처리 수준은 max degree of parallelism 서버 구성 옵션을 따릅니다.The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. CREATE INDEX, ALTER INDEX, DROP INDEX 및 ALTER TABLE 문에서 MAXDOP 인덱스 옵션을 설정하여 개별 인덱스 작업에 대한 max degree of parallelism 값을 재정의할 수 있습니다.You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

SQL Server 데이터베이스 엔진SQL Server Database Engine에서 인덱스 실행 계획을 작성하는 경우 병렬 작업의 수는 다음 중에서 가장 낮은 값으로 설정됩니다.When the SQL Server 데이터베이스 엔진SQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • 컴퓨터의 마이크로프로세서의 수 또는 CPU의 수The number of microprocessors, or CPUs in the computer.
  • max degree of parallelism 서버 구성 옵션에 지정된 수The number specified in the max degree of parallelism server configuration option.
  • SQL ServerSQL Server 작업자 스레드에 대해 수행된 작업의 임계값을 아직 넘지 않은 CPU의 수The number of CPUs not already over a threshold of work performed for SQL ServerSQL Server worker threads.

예를 들어 8개의 CPU가 있으나 최대 병렬 처리 수준이 6으로 설정된 컴퓨터의 경우 인덱스 작업에 대해 생성될 수 있는 최대 병렬 작업자 스레드 수는 6개입니다.For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel worker threads are generated for an index operation. 인덱스 실행 계획을 작성할 때 컴퓨터에 있는 5개의 CPU가 SQL ServerSQL Server 작업의 임계값을 초과하는 경우 실행 계획은 3개의 병렬 작업자 스레드만 지정합니다.If five of the CPUs in the computer exceed the threshold of SQL ServerSQL Server work when an index execution plan is built, the execution plan specifies only three parallel worker threads.

병렬 인덱스 작업의 주요 단계는 다음과 같습니다.The main phases of a parallel index operation include the following:

  • 조정 작업자 스레드는 신속하게 무작위로 테이블을 검색하여 인덱스 키의 분포를 예상합니다.A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. 조정 작업자 스레드는 병렬 작업 수준에 해당하는 여러 키 범위를 만드는 키 경계를 설정합니다. 여기서 각 키 범위는 비슷한 개수의 행을 포함할 수 있도록 결정됩니다.The coordinating worker thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. 예를 들어 테이블에 4백만 개의 행이 있고 병렬 처리 수준이 4인 경우 조정 작업자 스레드는 각 집합에서 백만 개의 행을 갖는 4개의 행 집합을 구분하는 키 값을 결정합니다.For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating worker thread will determine the key values that delimit four sets of rows with 1 million rows in each set. 모든 CPU를 사용하도록 충분한 키 범위를 설정할 수 없는 경우 병렬 처리 수준도 이에 따라 줄어듭니다.If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
  • 조정 작업자 스레드는 병렬 작업 수준에 해당하는 여러 작업자 스레드를 디스패치하고 이러한 작업자 스레드가 작업을 완료할 때까지 대기합니다.The coordinating worker thread dispatches a number of worker threads equal to the degree of parallel operations and waits for these worker threads to complete their work. 각 작업자 스레드는 해당 작업자 스레드에 할당된 범위 내의 키 값을 갖는 행만 검색하는 필터를 사용하여 기본 테이블을 검색합니다.Each worker thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the worker thread. 각 작업자 스레드는 키 범위에서 행의 인덱스 구조를 작성합니다.Each worker thread builds an index structure for the rows in its key range. 분할된 인덱스의 경우 각 작업자 스레드는 지정한 수만큼의 파티션을 작성합니다.In the case of a partitioned index, each worker thread builds a specified number of partitions. 파티션은 작업자 스레드 간에 공유되지 않습니다.Partitions are not shared among worker threads.
  • 모든 병렬 작업자 스레드가 완료된 후 조정 작업자 스레드는 인덱스 하위 단위를 단일 인덱스에 연결합니다.After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. 이 단계는 오프라인 인덱스 작업에만 적용됩니다.This phase applies only to offline index operations.

개별 CREATE TABLE 또는 ALTER TABLE 문에는 인덱스를 생성해야 하는 여러 제약 조건이 있을 수 있습니다.Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. 각 개별 인덱스 만들기 작업은 여러 CPU가 있는 컴퓨터에서 병렬로 수행될 수 있지만 이러한 여러 인덱스 만들기 작업은 연속해서 수행됩니다.These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.

분산 쿼리 아키텍처Distributed Query Architecture

Microsoft SQL ServerSQL Server는 Transact-SQL 문에서 다른 유형의 OLE DB 데이터 원본을 참조할 수 있도록 두 가지 메서드를 지원합니다.Microsoft SQL ServerSQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQL statements:

  • 연결된 서버 이름Linked server names
    시스템 저장 프로시저 sp_addlinkedserversp_addlinkedsrvlogin 은 OLE DB 데이터 원본에 서버 이름을 제공하는 데 사용됩니다.The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. Transact-SQL 문에서는 4부분으로 된 이름을 사용하여 이러한 연결 서버의 개체를 참조할 수 있습니다.Objects in these linked servers can be referenced in Transact-SQL statements using four-part names. 예를 들어 연결된 서버 이름 DeptSQLSrvrSQL ServerSQL Server의 다른 인스턴스에 대해 정의되는 경우 다음 문은 해당 서버의 테이블을 참조합니다.For example, if a linked server name of DeptSQLSrvr is defined against another instance of SQL ServerSQL Server, the following statement references a table on that server:

    SELECT JobTitle, HireDate 
    FROM DeptSQLSrvr.AdventureWorks2014.HumanResources.Employee;
    

    또한 연결된 서버 이름은 OLE DB 데이터 원본에서 행 집합을 열도록 OPENQUERY 문에 지정될 수 있습니다.The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. 그런 다음 이 행 집합은 Transact-SQL 문의 테이블처럼 참조될 수 있습니다.This rowset can then be referenced like a table in Transact-SQL statements.

  • 임의 커넥터 이름Ad hoc connector names
    데이터 원본이 자주 참조되지 않는 경우 연결된 서버에 연결하는 데 필요한 정보로 OPENROWSET 또는 OPENDATASOURCE 함수가 지정됩니다.For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. 그런 다음 행 집합은 테이블이 Transact-SQL 문에서 참조되는 방법과 동일하게 참조될 수 있습니다.The rowset can then be referenced the same way a table is referenced in Transact-SQL statements:

    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';
          Employees);
    

SQL ServerSQL Server는 OLE DB를 사용하여 관계형 엔진과 저장소 엔진 간에 통신합니다. uses OLE DB to communicate between the relational engine and the storage engine. 관계형 엔진은 각 Transact-SQL 문을 기본 테이블의 저장소 엔진에서 연 단순 OLE DB 행 집합에 대한 일련의 작업으로 분류합니다.The relational engine breaks down each Transact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. 이것은 관계형 엔진도 OLE DB 데이터 원본의 단순 OLE DB 행 집합을 열 수 있음을 의미합니다.This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.
oledb_storageoledb_storage
관계형 엔진은 OLE DB API(응용 프로그래밍 인터페이스)를 사용하여 연결된 서버에서 행 집합을 열고, 그 행을 인출하고, 트랜잭션을 관리합니다.The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

연결된 서버로 액세스되는 각 OLE DB 데이터 원본에 대해 OLE DB 공급자는 SQL ServerSQL Server를 실행 중인 서버에 있어야 합니다.For each OLE DB data source accessed as a linked server, an OLE DB provider must be present on the server running SQL ServerSQL Server. 특정 OLE DB 데이터 원본에 대해 사용할 수 있는 Transact-SQL 연산 집합은 OLE DB 공급자의 기능에 따라 다릅니다.The set of Transact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider.

SQL ServerSQL Server의 인스턴스마다 sysadmin 고정 서버 역할의 멤버는 SQL ServerSQL Server DisallowAdhocAccess 속성을 사용하여 OLE DB 공급자에 대한 임의 커넥터 이름의 사용을 설정 또는 해제할 수 있습니다.For each instance of SQL ServerSQL Server, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL ServerSQL Server DisallowAdhocAccess property. 임의 액세스가 활성화되어 있는 경우 해당 인스턴스에 로그온된 모든 사용자는 OLE DB 공급자를 사용하여 액세스할 수 있는 네트워크에서 데이터 원본을 참조하며 임의 커넥터 이름이 있는 SQL 문을 실행할 수 있습니다.When ad-hoc access is enabled, any user logged on to that instance can execute SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. 데이터 원본에 대한 액세스를 제어하기 위해 sysadmin 역할의 멤버는 해당 OLE DB 공급자에 대한 임의 액세스를 비활성화하고 그에 따라 사용자를 관리자가 정의한 연결된 서버 이름에서 참조한 데이터 원본으로만 제한합니다.To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. 기본적으로 임의 액세스는 SQL ServerSQL Server OLE DB 공급자에 대해 활성화되어 있고 기타 모든 OLE DB 공급자에 대해서는 비활성화되어 있습니다.By default, ad-hoc access is enabled for the SQL ServerSQL Server OLE DB provider, and disabled for all other OLE DB providers.

분산 쿼리를 사용하면 SQL ServerSQL Server 서비스가 실행 중인 Microsoft Windows 계정의 보안 컨텍스트에서 다른 데이터 원본(예: 파일, Active Directory 같은 비관계형 데이터 원본 등)에 액세스할 수 있습니다.Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL ServerSQL Server service is running. SQL ServerSQL Server는 Windows 로그인에서는 적절하게 로그인을 가장하지만 SQL ServerSQL Server 로그인에서는 그렇지 못합니다. impersonates the login appropriately for Windows logins; however, that is not possible for SQL ServerSQL Server logins. 이렇게 하면 사용 권한이 없는 다른 데이터 원본에 대한 액세스를 분산 쿼리 사용자에게 허용할 수 있지만 SQL ServerSQL Server 서비스가 실행 중인 계정에는 사용 권한이 없습니다.This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL ServerSQL Server service is running does have permissions. sp_addlinkedsrvlogin 을 사용하여 연결된 해당 서버에 액세스할 권한이 부여된 특정 로그인을 정의할 수 있습니다.Use sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. 이 제어는 임의 이름에 대해 사용할 수 없으므로 임의 액세스에 대해 OLE DB 공급자를 활성화할 경우 조심해야 합니다.This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

SQL ServerSQL Server는 가능하면 조인, 제한, 투영, 정렬, 그룹화 같은 관계형 연산을 연산별로 OLE DB 데이터 원본에 푸시합니다.When possible, SQL ServerSQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. 기본적으로 SQL ServerSQL Server는 기본 테이블을 검색하여 SQL ServerSQL Server에 전송한 후 자체적으로 관계형 연산을 수행하지는 않습니다. SQL ServerSQL Server does not default to scanning the base table into SQL ServerSQL Server and performing the relational operations itself. SQL ServerSQL Server는 OLE DB 공급자에게 공급자에서 지원하는 SQL 문법 수준을 확인하도록 쿼리하고 이 정보에 따라 가능한 한 많은 관계형 연산을 공급자에게 푸시합니다. queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider.

SQL ServerSQL Server는 OLE DB 공급자가 OLE DB 데이터 원본에서 키 값이 배포되는 방식을 나타내는 통계를 반환하기 위한 메커니즘을 지정합니다. specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. 이렇게 하면 SQL ServerSQL Server 쿼리 최적화 프로그램은 각 SQL 문의 요구 사항에 대해 데이터 원본의 데이터의 패턴을 좀 더 잘 분석하므로 최적의 실행 계획을 생성하는 쿼리 최적화 프로그램의 기능을 향상시킬 수 있습니다.This lets the SQL ServerSQL Server Query Optimizer better analyze the pattern of data in the data source against the requirements of each SQL statement, increasing the ability of the Query Optimizer to generate optimal execution plans.

분할된 테이블 및 인덱스에서의 향상된 쿼리 처리Query Processing Enhancements on Partitioned Tables and Indexes

SQL Server 2008SQL Server 2008에서는 여러 병렬 계획에 대해 분할된 테이블에서의 쿼리 처리 성능이 향상되었고, 병렬 및 직렬 계획이 표시되는 방식이 변경되었으며 컴파일 시간 및 런타임 실행 계획에 제공되는 분할 정보가 개선되었습니다. improved query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhanced the partitioning information provided in both compile-time and run-time execution plans. 이 항목에서는 이러한 향상된 기능에 대해 설명하고, 분할된 테이블 및 인덱스의 쿼리 실행 계획을 해석하는 방법에 대해 안내하며, 분할된 개체에서의 쿼리 성능 향상을 위한 최선의 방법을 알려 줍니다.This topic describes these improvements, provides guidance on how to interpret the query execution plans of partitioned tables and indexes, and provides best practices for improving query performance on partitioned objects.

참고

분할된 테이블 및 인덱스는 SQL ServerSQL Server Enterprise, Developer 및 Evaluation Edition에서만 지원됩니다.Partitioned tables and indexes are supported only in the SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

새 파티션 인식 Seek 연산New Partition-Aware Seek Operation

SQL ServerSQL Server의 분할된 테이블을 나타내는 내부 표현이 변경되어 이제 테이블이 선행 열처럼 PartitionID가 있는 다중 열 인덱스로 쿼리 프로세서에 나타납니다.In SQL ServerSQL Server, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column. PartitionID 는 특정 행을 포함하는 파티션의 ID 를 나타내기 위해 내부에서 사용하는 숨겨진 계산 열입니다.PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. 예를 들어 T(a, b, c)로 정의되는 테이블은 a 열에서 분할되고 b 열에 클러스터형 인덱스가 있다고 가정합니다.For example, assume the table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. SQL ServerSQL Server에서 이렇게 분할된 테이블은 내부적으로 T(PartitionID, a, b, c) 스키마와 복합 키 (PartitionID, b)에 클러스터형 인덱스가 있는 분할되지 않은 테이블로 취급됩니다.In SQL ServerSQL Server, this partitioned table is treated internally as a nonpartitioned table with the schema T(PartitionID, a, b, c) and a clustered index on the composite key (PartitionID, b). 이로 인해 쿼리 최적화 프로그램은 분할된 테이블 또는 인덱스에서 PartitionID를 기준으로 seek 연산을 수행할 수 있습니다.This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

이제 파티션 제거는 이 seek 연산에서 수행됩니다.Partition elimination is now done in this seek operation.

In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (논리적 선행 열)과 가능한 다른 인덱스 키 열에서 수행된 후, 두 번째 수준의 seek 연산은 다른 조건을 사용하여 첫 번째 수준의 seek 연산에 대한 제한을 충족시키는 각각의 고유 값에 대해 수행될 수 있습니다.In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation. 즉 skip scan이라고 하는 이 연산을 통해 쿼리 최적화 프로그램은 seek 또는 scan 연산을 하나의 조건을 기준으로 수행하여 액세스할 파티션을 결정하고 해당 연산자에서 두 번째 수준 index seek 연산을 수행하여 다른 조건을 충족시키는 이러한 파티션에서 행을 반환할 수 있습니다.That is, this operation, called a skip scan, allows the Query Optimizer to perform a seek or scan operation based on one condition to determine the partitions to be accessed and a second-level index seek operation within that operator to return rows from these partitions that meet a different condition. 예를 들어 다음 쿼리를 살펴보십시오.For example, consider the following query.

SELECT * FROM T WHERE a < 10 and b = 2;

이 예에서 T(a, b, c)로 정의되는 T 테이블은 a 열에서 분할되고 b 열에 클러스터형 인덱스가 있다고 가정합니다.For this example, assume that table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. T 테이블의 파티션 경계는 다음 파티션 함수로 정의됩니다.The partition boundaries for table T are defined by the following partition function:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

쿼리를 해결하려면 쿼리 프로세서는 첫 번째 수준 seek 연산을 수행하여 T.a < 10조건을 충족시키는 행을 포함하는 모든 파티션을 찾습니다.To solve the query, the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition T.a < 10. 이 작업을 통해 액세스할 파티션을 식별합니다.This identifies the partitions to be accessed. 식별된 각 파티션에서 쿼리 프로세서는 열에서 클러스터형 인덱스로 두 번째 수준 seek 연산을 수행하여 T.b = 2T.a < 10조건을 충족시키는 행을 찾습니다.Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 and T.a < 10.

다음 그림은 skip scan 연산을 논리적으로 표현한 것으로The following illustration is a logical representation of the skip scan operation. Ta 열의 데이터와 함께 b 테이블을 보여 줍니다.It shows table T with data in columns a and b. 파티션은 세로줄 파선으로 표시되는 파티션 경계와 함께 1에서 4까지 번호가 매겨져 있습니다.The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. 파티션에 대한 첫 번째 수준 seek 연산(그림에는 표시되지 않음)에서는 파티션 1, 2 및 3이 a 열에 테이블 및 조건자에 대해 정의된 분할로 포함된 검색 조건을 충족시키는지 확인했습니다.A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column a. T.a < 10입니다.That is, T.a < 10. skip scan 연산의 두 번째 수준 seek 부분에 의해 이동된 경로는 곡선으로 표시됩니다.The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. 기본적으로 skip scan 연산은 b = 2조건을 충족시키는 행을 이러한 파티션마다 검색합니다.Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2. skip scan 연산의 총 비용은 세 가지 개별 index seek 연산의 총 비용과 같습니다.The total cost of the skip scan operation is the same as that of three separate index seeks.

skip_scan

쿼리 실행 계획의 분할 정보 표시Displaying Partitioning Information in Query Execution Plans

분할 테이블과 인덱스에서의 쿼리 실행 계획은 Transact-SQL SETSET SHOWPLAN_XML 또는 SET STATISTICS XML을 사용하거나 SQL ServerSQL Server Management Studio의 그래픽 실행 계획 출력을 사용하여 검사할 수 있습니다.The execution plans of queries on partitioned tables and indexes can be examined by using the Transact-SQL SET statements SET SHOWPLAN_XML or SET STATISTICS XML, or by using the graphical execution plan output in SQL ServerSQL Server Management Studio. 예를 들어 쿼리 편집기 도구 모음에서 예상 실행 계획 표시 를 클릭하여 컴파일 시간 실행 계획을 표시할 수 있고 실제 실행 계획 포함을 클릭하여 런타임 계획을 표시할 수 있습니다.For example, you can display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar and the run-time plan by clicking Include Actual Execution Plan.

이러한 도구를 사용하여 다음 정보를 확인할 수 있습니다.Using these tools, you can ascertain the following information:

  • scans, seeks, inserts, updates, merges, deletes 같은 작업에서 분할된 테이블이나 인덱스에 액세스함.The operations such as scans, seeks, inserts, updates, merges, and deletes that access partitioned tables or indexes.
  • 쿼리에 의해 액세스되는 파티션.The partitions accessed by the query. 예를 들어 액세스한 총 파티션 수 및 액세스한 근접 파티션의 범위가 런타임 실행 계획에서 사용할 수 있습니다.For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • 한 개 이상의 파티션에서 데이터를 검색하기 위해 skip scan 연산이 seek 또는 scan 연산에서 사용되는 경우When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

향상된 파티션 정보 기능Partition Information Enhancements

SQL ServerSQL Server에서는 컴파일 시간과 런타임 실행 계획 모두에 대한 향상된 분할 정보를 제공합니다. provides enhanced partitioning information for both compile-time and run-time execution plans. 실행 계획은 현재 다음 정보를 제공합니다.Execution plans now provide the following information:

  • 분할된 테이블에서 Partitioned , seek, scan, insert, update, merge같은 연산자가 실행되었음을 나타내는 선택적 delete특성.An optional Partitioned attribute that indicates that an operator, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.
  • SeekPredicateNew 에서 범위 검색을 지정하는 선행 인덱스 키 열 및 필터 조건으로 SeekKeys 를 포함하는 새로운 PartitionID 요소와 PartitionID하위 요소.A new SeekPredicateNew element with a SeekKeys subelement that includes PartitionID as the leading index key column and filter conditions that specify range seeks on PartitionID. SeekKeys 하위 요소 두 개의 존재는 PartitionID 에서 skip scan 연산이 사용되는 것을 나타냅니다.The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID is used.
  • 액세스한 총 파티션 수를 제공하는 요약 정보.Summary information that provides a total count of the partitions accessed. 이 정보는 런타임 계획에서만 사용할 수 있습니다.This information is available only in run-time plans.

그래픽 실행 계획 출력 및 XML 실행 계획 출력 모두에 이 정보가 표시되는 방법을 보여 주려면 분할된 테이블 fact_sales의 다음 쿼리를 살펴봅니다.To demonstrate how this information is displayed in both the graphical execution plan output and the XML Showplan output, consider the following query on the partitioned table fact_sales. 이 쿼리는 두 개의 파티션에 있는 데이터를 업데이트합니다.This query updates data in two partitions.

UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;

다음 그림에서는 이 쿼리에 대해 컴파일 시간 실행 계획의 Clustered Index Seek 연산자 속성을 보여 줍니다.The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. fact_sales 테이블의 정의 및 파티션 정의를 보려면 이 항목의 “예”를 참조하세요.To view the definition of the fact_sales table and the partition definition, see "Example" in this topic.

clustered_index_seek

Partitioned 특성Partitioned Attribute

Index Seek 와 같은 연산자가 분할된 테이블 또는 인덱스에서 실행되면 Partitioned 특성이 컴파일 시간 및 런타임 계획에 나타나고 이 특성이 True (1)로 설정됩니다.When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True (1). 이 특성이 False (0)로 설정되면 표시되지 않습니다.The attribute does not display when it is set to False (0).

Partitioned 특성은 다음 물리적 및 논리적 연산자에서 나타날 수 있습니다.The Partitioned attribute can appear in the following physical and logical operators:

  • Table Scan
  • Index Scan
  • Index Seek
  • Insert
  • Update
  • Delete
  • Merge

앞의 그림과 같이 이 특성은 자신이 정의된 연산자의 속성에서 표시됩니다.As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. XML 실행 계획 출력에서 이 특성은 자신이 정의된 연산자의 Partitioned="1" 노드에서 RelOp 로 표시됩니다.In the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

새 검색 조건자New Seek Predicate

XML 실행 계획 출력에서 SeekPredicateNew 요소는 자신이 정의된 연산자에 표시됩니다.In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. 이 출력에는 SeekKeys 하위 요소를 2개까지 포함할 수 있습니다.It can contain up to two occurrences of the SeekKeys sub-element. 첫 번째 SeekKeys 항목은 논리적 인덱스의 파티션 ID 수준에 첫 번째 수준 seek 연산을 지정합니다.The first SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. 즉 이 seek 연산에서는 쿼리 조건을 충족시키기 위해 액세스해야 하는 파티션을 결정합니다.That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. 두 번째 SeekKeys 항목은 첫 번째 수준 seek 연산자에서 식별된 각 파티션에서 발생하는 skip scan 연산의 두 번째 수준 seek 부분을 지정합니다.The second SeekKeys item specifies the second-level seek portion of the skip scan operation that occurs within each partition identified in the first-level seek.

파티션 요약 정보Partition Summary Information

런타임 실행 계획에서 파티션 요약 정보에는 액세스한 파티션의 수 및 액세스한 실제 파티션의 식별이 제공됩니다.In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. 이 정보를 사용하여 쿼리에서 액세스한 올바른 파티션을 확인하면 모든 다른 파티션이 고려 대상에서 제외됩니다.You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.

Actual Partition CountPartitions Accessed에 대한 정보가 제공됩니다.The following information is provided: Actual Partition Count, and Partitions Accessed.

Actual Partition Count 는 쿼리에서 액세스한 총 파티션 개수입니다.Actual Partition Count is the total number of partitions accessed by the query.

XML 실행 계획 출력에서Partitions Accessed는 새 RuntimePartitionSummary 요소 내에 Partitions Accessed가 정의된 연산자의 RelOp 노드에 나타나는 파티션 요약 정보입니다.Partitions Accessed, in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. 다음 예에서는 액세스한 총 2개의 파티션(파티션 2와 3)을 나타내는 RuntimePartitionSummary 요소의 내용을 보여 줍니다.The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

다른 실행 계획 방법을 사용하여 파티션 정보 표시Displaying Partition Information by Using Other Showplan Methods

실행 계획 메서드인 SHOWPLAN_ALL, SHOWPLAN_TEXT, STATISTICS PROFILE 은 다음과 같은 경우를 제외하고 이 항목에서 설명한 파티션 정보를 보고하지 않습니다.The Showplan methods SHOWPLAN_ALL, SHOWPLAN_TEXT, and STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. SEEK 조건자의 일부로서 액세스할 파티션이 파티션 ID를 나타내는 계산 열에서 범위 조건자로 식별됩니다.As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. 다음 예제에서는 SEEK 연산자의 Clustered Index Seek 조건자를 보여 줍니다.The following example shows the SEEK predicate for a Clustered Index Seek operator. 파티션 2와 3에 액세스하고 seek 연산자는 date_id BETWEEN 20080802 AND 20080902조건을 충족시키는 행에 필터를 설정합니다.Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]), 

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] \<= (3) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902)) 

                ORDERED FORWARD)

분할된 힙의 실행 계획 해석Interpreting Execution Plans for Partitioned Heaps

분할된 힙이 파티션 ID의 논리적 인덱스로 취급됩니다.A partitioned heap is treated as a logical index on the partition ID. 분할된 힙에서의 파티션 제거는 파티션 ID의 Table Scan 조건자를 사용하는 SEEK 연산자로 실행 계획에 나타납니다.Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. 다음 예는 제공된 실행 계획 정보를 보여 줍니다.The following example shows the Showplan information provided:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

배치된 조인의 실행 계획 해석Interpreting Execution Plans for Collocated Joins

같거나 상응하는 파티션 함수를 사용하여 두 개의 테이블이 분할되고 조인의 양쪽 면에서 분할 열을 사용하여 쿼리의 조인 조건에 되면 조인 콜러케이션이 발생합니다.Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. 쿼리 최적화 프로그램은 동일한 파티션 ID가 있는 각 테이블의 파티션이 개별적으로 조인되는 계획을 생성할 수 있습니다.The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. 배치된 조인은 메모리와 처리 시간이 덜 필요로 할 수 있으므로, 배치된 조인이 배치되지 않은 조인보다 더 빠를 수 있습니다.Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. 쿼리 최적화 프로그램에서는 비용 예측을 기준으로 배치되지 않은 계획 또는 배치된 계획을 선택합니다.The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

배치된 계획에서 Nested Loops 조인은 내부 측면에서 조인된 테이블 또는 인덱스 파티션을 하나 이상 읽습니다.In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. Constant Scan 연산자의 숫자는 파티션 번호를 나타냅니다.The numbers within the Constant Scan operators represent the partition numbers.

배치된 조인에 대한 병렬 계획을 분할된 테이블 또는 인덱스에 대해 생성하면 Constant ScanNested Loops 조인 연산자 사이에 Parallelism 연산자가 표시됩니다.When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. 이 경우 조인 외부 측면의 여러 작업자 스레드가 각각 서로 다른 파티션을 읽고 작업합니다.In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

다음 그림은 배치된 조인에 대한 병렬 쿼리 계획을 보여 줍니다.The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

분할된 개체에 대한 병렬 쿼리 실행 전략Parallel Query Execution Strategy for Partitioned Objects

쿼리 프로세서에서는 분할된 개체에서 선택하는 쿼리에 대해 병렬 실행 전략을 사용합니다.The query processor uses a parallel execution strategy for queries that select from partitioned objects. 실행 전략의 일부로 쿼리 프로세서에서는 쿼리에 필요한 테이블 파티션과 각 파티션에 할당할 작업자 스레드 수를 결정합니다.As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. 대부분의 경우 쿼리 프로세서는 동일하거나 거의 동일한 수의 작업자 스레드를 각 파티션에 할당한 다음 전체 파티션에서 병렬로 쿼리를 실행합니다.In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. 다음 단락에서는 작업자 스레드 할당에 대해 자세히 설명합니다.The following paragraphs explain worker thread allocation in greater detail.

worker thread1

작업자 스레드 수가 파티션 수보다 적은 경우 쿼리 프로세서는 각 작업자 스레드를 서로 다른 파티션에 할당합니다. 따라서 처음에는 하나 이상의 파티션에 작업자 스레드가 할당되지 않습니다.If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. 파티션에서 작업자 스레드가 실행을 완료하면 쿼리 프로세서는 각 파티션에 하나의 작업자 스레드가 할당될 때까지 이 작업자 스레드를 다음 파티션에 할당합니다.When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. 이런 경우에만 쿼리 프로세서가 작업자 스레드를 다른 파티션에 다시 할당합니다.This is the only case in which the query processor reallocates worker threads to other partitions.
완료 후 재할당된 작업자 스레드를 표시합니다.Shows worker thread reassigned after it finishes. 작업자 스레드 수가 파티션 수와 같은 경우 쿼리 프로세서는 각 파티션에 하나의 작업자 스레드를 할당합니다.If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. 이때 작업자 스레드가 완료되어도 다른 파티션에 다시 할당되지 않습니다.When a worker thread finishes, it is not reallocated to another partition.

worker thread2

작업자 스레드 수가 파티션 수보다 많은 경우 쿼리 프로세서는 각 파티션에 동일한 수의 작업자 스레드를 할당합니다.If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. 작업자 스레드 수가 파티션 수의 정확한 배수가 아닌 경우 쿼리 프로세서는 사용 가능한 작업자 스레드를 모두 사용하기 위해 일부 파티션에 하나의 작업자 스레드를 추가로 할당합니다.If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. 파티션이 하나뿐인 경우 해당 파티션에 모든 작업자 스레드가 할당됩니다.Note that if there is only one partition, all worker threads will be assigned to that partition. 아래 다이어그램에서는 4개의 파티션과 14개의 작업자 스레드가 있습니다.In the diagram below, there are four partitions and 14 worker threads. 이 경우 각 파티션에 3개의 작업자 스레드가 할당되고 총 14개의 작업자 스레드를 할당하기 위해 2개의 파티션에 추가 작업자 스레드가 하나씩 할당됩니다.Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. 이때 작업자 스레드가 완료되어도 다른 파티션에 다시 할당되지 않습니다.When a worker thread finishes, it is not reassigned to another partition.

worker thread3

위의 예에서는 작업자 스레드를 할당하는 간단한 방법을 제시하지만 실제 전략은 더욱 복잡하며 쿼리 실행 시 발생하는 다른 변수들을 고려해야 합니다.Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. 예를 들어 테이블이 분할되어 있고 테이블의 A 열에 클러스터형 인덱스가 있으며 쿼리에 조건자 절 WHERE A IN (13, 17, 25)가 있는 경우 쿼리 프로세서는 각 테이블 파티션이 아니라 이러한 3개의 검색 값(A=13, A=17, A=25)에 각각 하나 이상의 작업자 스레드를 할당합니다.For example, if the table is partitioned and has a clustered index on column A and a query has the predicate clause WHERE A IN (13, 17, 25), the query processor will allocate one or more worker threads to each of these three seek values (A=13, A=17, and A=25) instead of each table partition. 이러한 값을 포함하는 파티션에서만 쿼리를 실행하면 되며 이러한 검색 조건자가 모두 동일한 파티션에 있는 경우 모든 작업자 스레드가 동일한 테이블 파티션에 할당됩니다.It is only necessary to execute the query in the partitions that contain these values, and if all of these seek predicates happen to be in the same table partition, all of the worker threads will be assigned to the same table partition.

또 다른 예로, 경계 포인트(10, 20, 30)가 있는 열 A에는 파티션 4개가 있고 열 B에는 인덱스가 있는 테이블과 조건자 절 WHERE B IN (50, 100, 150)을 갖는 쿼리가 있다고 가정합니다.To take another example, suppose that the table has four partitions on column A with boundary points (10, 20, 30), an index on column B, and the query has a predicate clause WHERE B IN (50, 100, 150). 테이블 파티션은 A 값이 기준이기 때문에 B 값은 어느 테이블 파티션에서도 나타날 수 있습니다.Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. 따라서 쿼리 프로세서는 4개의 각 테이블 파티션에서 3개의 B 값(50, 100, 150)을 각각 검색합니다.Thus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. 쿼리 프로세서는 이러한 12개의 각 쿼리 검색을 병렬로 실행할 수 있도록 작업자 스레드를 균형 있게 할당합니다.The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

A 열을 기반으로 하는 테이블 파티션Table partitions based on column A 각 테이블 파티션에서 B 열 검색Seeks for column B in each table partition
테이블 파티션 1: A < 10Table Partition 1: A < 10 B=50, B=100, B=150B=50, B=100, B=150
테이블 파티션 2: A >= 10 AND A < 20Table Partition 2: A >= 10 AND A < 20 B=50, B=100, B=150B=50, B=100, B=150
테이블 파티션 3: A >= 20 AND A < 30Table Partition 3: A >= 20 AND A < 30 B=50, B=100, B=150B=50, B=100, B=150
테이블 파티션 4: A >= 30Table Partition 4: A >= 30 B=50, B=100, B=150B=50, B=100, B=150

최선의 구현 방법Best Practices

분할된 대형 테이블과 인덱스에서 많은 양의 데이터에 액세스하는 쿼리의 성능을 향상시키려면 다음과 같은 최선의 구현 방법을 권장합니다.To improve the performance of queries that access a large amount of data from large partitioned tables and indexes, we recommend the following best practices:

  • 여러 디스크 간에 각 파티션을 스트라이프합니다.Stripe each partition across many disks. 특히 이 작업은 회전 디스크를 사용할 때 적합합니다.This is especially relevant when using spinning disks.
  • 가능하면 충분한 주 메모리가 있는 서버를 사용하여 자주 액세스하는 파티션이나 모든 파티션을 메모리 크기에 맞춰서 I/O 비용을 줄입니다.When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
  • 쿼리한 데이터가 메모리 크기에 맞지 않을 경우 해당 테이블과 인덱스를 압축합니다.If the data you query will not fit in memory, compress the tables and indexes. 이렇게 하면 I/O 비용이 줄어듭니다.This will reduce I/O cost.
  • 빠른 프로세서와 가능한 많은 프로세서 코어가 장착된 서버를 사용하여 병렬 쿼리 처리 기능을 이용합니다.Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
  • 서버에 충분한 I/O 컨트롤러 대역폭이 있는지 확인합니다.Ensure the server has sufficient I/O controller bandwidth.
  • 모든 분할된 대형 테이블에 클러스터형 인덱스를 만들어 B-트리 검색 최적화를 이용합니다.Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
  • 데이터를 분할된 테이블에 대량으로 로드하는 경우에는 데이터 로드 성능 가이드 백서의 모범 사례 권장 사항을 따르세요.Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

예제Example

다음 예에서는 7개의 파티션이 있는 단일 테이블이 포함된 테스트 데이터베이스를 만듭니다.The following example creates a test database containing a single table with seven partitions. 이 예에서 쿼리를 실행할 때 이전에 설명한 도구를 사용하여 컴파일 시간과 런타임 계획 모두에 대한 분할 정보를 확인합니다.Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

참고

이 예에서는 백만 개 이상의 행을 테이블로 삽입합니다.This example inserts more than 1 million rows into the table. 이 예를 실행하는 데에는 하드웨어에 따라 시간이 몇 분 정도 걸릴 수 있습니다.Running this example may take several minutes depending on your hardware. 이 예를 실행하기 전에 1.5GB 이상의 사용 가능한 디스크 공간이 있는지 확인하십시오.Before executing this example, verify that you have more than 1.5 GB of disk space available.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

더 보기 Additional Reading

실행 계획 논리 및 물리 연산자 참조Showplan Logical and Physical Operators Reference
확장 이벤트Extended Events
쿼리 저장소에 대한 모범 사례Best Practice with the Query Store
카디널리티 추정Cardinality Estimation