마이그레이션 후 유효성 검사 및 최적화 가이드Post-migration Validation and Optimization Guide

이 항목 적용 대상: 예SQL Server없습니다Azure SQL 데이터베이스없습니다Azure SQL 데이터 웨어하우스 없습니다 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 마이그레이션 후 단계는 데이터 정확도와 완전성을 조정하고 작업의 성능 문제를 파악하는 데 매우 중요합니다. post migration step is very crucial for reconciling any data accuracy and completeness, as well as uncover performance issues with the workload.

일반적인 성능 시나리오Common Performance Scenarios

다음은 SQL ServerSQL Server 플랫폼으로 마이그레션한 후 발생하는 몇 가지 일반적인 성능 시나리오와 해결 방법입니다.Below are some of the common performance scenarios encountered after migrating to SQL ServerSQL Server Platform and how to resolve them. 여기에는 이전 버전 SQL ServerSQL Server에서 새 버전 SQL ServerSQL Server로의 마이그레이션 및 Oracle, DB2, MySQL, Sybase 등의 외래 플랫폼에서 SQL ServerSQL Server로의 마이그레이션에 특정한 시나리오가 포함됩니다.These include scenarios that are specific to SQL ServerSQL Server to SQL ServerSQL Server migration (older versions to newer versions), as well as foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL ServerSQL Server migration.

CE 버전 변경으로 인한 쿼리 성능 저하Query regressions due to change in CE version

적용 대상: SQL ServerSQL Server - SQL ServerSQL Server 마이그레이션.Applies to: SQL ServerSQL Server to SQL ServerSQL Server migration.

이전 버전의 SQL ServerSQL Server에서 SQL Server 2014SQL Server 2014 이상 버전으로 마이그레이션할 때, 그리고 데이터베이스 호환성 수준을 최신으로 업그레이드할 때는 작업이 성능 저하 위험에 노출될 수 있습니다.When migrating from an older versions of SQL ServerSQL Server to SQL Server 2014SQL Server 2014 or newer, and upgrading the database compatibility level to the latest one, a workload may be exposed to the risk of performance regression.

SQL Server 2014SQL Server 2014부터는 쿼리 최적화 프로그램의 모든 변경 내용이 최신 데이터베이스 호환성 수준에 연결되므로 계획이 업그레이드 시점에 즉시 변경되지 않고 사용자가 COMPATIBILITY_LEVEL 데이터베이스 옵션을 최신 상태로 변경하는 경우에 변경됩니다.This is because starting with SQL Server 2014SQL Server 2014, all Query Optimizer changes are tied to the latest database compatibility level, so plans are not changed right at point of upgrade but rather when a user changes the COMPATIBILITY_LEVEL database option to the latest one. 이 기능은 쿼리 저장소와 함께 업그레이드 프로세스에서 쿼리 성능에 대한 뛰어난 제어 수준을 제공합니다.This capability, in combination with Query Store gives you a great level of control over the query performance in the upgrade process.

SQL Server 2014SQL Server 2014에 도입된 쿼리 최적화 프로그램 변경 사항에 대한 자세한 내용은 Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator(SQL Server 2014 카디널리티 평가기로 쿼리 계획 최적화)를 참조하세요.For more information on Query Optimizer changes introduced in SQL Server 2014SQL Server 2014, see Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

해결 단계Steps to resolve

데이터베이스 호환성 수준을 소스 버전으로 변경하고 다음 그림에 나온 권장 업그레이드 워크플로를 따릅니다.Change the database compatibility level to the source version, and follow the recommended upgrade workflow as shown in the following picture:

query-store-usage-5query-store-usage-5

이 항목에 대한 자세한 내용은 Keep performance stability during the upgrade to newer SQL Server(최신 SQL Server로 업그레이드하는 동안 성능 안정성 유지)를 참조하세요.For more information on this topic, see Keep performance stability during the upgrade to newer SQL Server.

매개 변수 검색의 민감도Sensitivity to parameter sniffing

적용 대상: 외래 플랫폼(예: Oracle, DB2, MySQL 및 Sybase)에서 SQL ServerSQL Server로의 마이그레이션Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL ServerSQL Server migration.

참고

SQL ServerSQL Server에서 SQL ServerSQL Server로의 마이그레이션은 이 문제가 원본 SQL ServerSQL Server에 있는 경우 최신 버전의 SQL ServerSQL Server로 그대로 마이그레이션해도 이 시나리오가 해결되지 않습니다.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

SQL ServerSQL Server는 첫 컴파일 시 입력 매개 변수를 검색하고 해당 입력 데이터 분포에 최적화된 매개 변수가 있고 재사용 가능한 계획을 생성하여 저장 프로시저의 쿼리 계획을 컴파일합니다. compiles query plans on stored procedures by using sniffing the input parameters at the first compile, generating a parameterized and reusable plan, optimized for that input data distribution. 저장 프로시저가 아닌 경우에도 간단한 계획을 생성하는 문은 대부분 매개 변수가 가집니다.Even if not stored procedures, most statements generating trivial plans will be parameterized. 계획이 처음 캐시된 후 이후 실행은 모두 기존에 캐시된 계획에 매핑됩니다.After a plan is first cached, any future execution maps to a previously cached plan. 첫 번째 컴파일 시 일반 작업에 대해 가장 일반적인 매개 변수 집합을 사용하지 않았을 경우 문제가 발생할 수 있습니다.A potential problem arises when that first compilation may not have used the most common sets of parameters for the usual workload. 매개 변수가 다르면 같은 실행 계획의 효율이 떨어집니다.For different parameters, the same execution plan becomes inefficient. 이 항목에 대한 자세한 내용은 매개 변수 스니핑을 참조하세요.For more information on this topic, see Parameter Sniffing.

해결 단계Steps to resolve

  1. RECOMPILE 힌트를 사용합니다.Use the RECOMPILE hint. 각 매개 변수 값이 조정될 때마다 계획이 계산됩니다.A plan is calculated every time adapted to each parameter value.
  2. (OPTIMIZE FOR(<input parameter> = <value>)) 옵션을 사용하도록 저장 프로시저를 다시 작성합니다.Rewrite the stored procedure to use the option (OPTIMIZE FOR(<input parameter> = <value>)). 관련 작업 대부분에 적합한 값을 사용할 값으로 결정하여 매개 변수가 있는 값에 효율적인 하나의 계획을 만들고 유지합니다.Decide which value to use that suits most of the relevant workload, creating and maintaining one plan that becomes efficient for the parameterized value.
  3. 프로시저 내의 지역 변수를 사용하여 저장 프로시저를 다시 작성합니다.Rewrite the stored procedure using local variable inside the procedure. 최적화 프로그램은 예상치에 밀도 벡터를 사용하므로 매개 변수 값과 관계없이 계획이 동일합니다.Now the optimizer uses the density vector for estimations, resulting in the same plan regardless of the parameter value.
  4. (OPTIMIZE FOR UNKNOWN) 옵션을 사용하도록 저장 프로시저를 다시 작성합니다.Rewrite the stored procedure to use the option (OPTIMIZE FOR UNKNOWN). 지역 변수 기술을 사용하는 것과 결과가 같습니다.Same effect as using the local variable technique.
  5. DISABLE_PARAMETER_SNIFFING 힌트를 사용하도록 쿼리를 다시 작성합니다.Rewrite the query to use the hint DISABLE_PARAMETER_SNIFFING. OPTION(RECOMPILE), WITH RECOMPILE 또는 OPTIMIZE FOR <value>를 사용하는 경우 외에는 매개 변수 검색을 완전히 사용하지 않도록 설정하므로 지역 변수 기술을 사용하는 것과 결과가 같습니다.Same effect as using the local variable technique by totally disabling parameter sniffing, unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR <value> is used.

Management StudioManagement Studio 계획 분석 기능을 활용하면 이로 인해 문제가 발생하는지 빠르게 식별할 수 있습니다.Leverage the Management StudioManagement Studio Plan Analysis feature to quickly identify if this is an issue. 자세한 내용은 여기를 참조하세요.More information available here.

누락된 인덱스Missing indexes

적용 대상: 외래 플랫폼(예: Oracle, DB2, MySQL 및 Sybase) 및 SQL ServerSQL Server에서 SQL ServerSQL Server로의 마이그레이션Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

인덱스가 잘못되거나 누락되면 추가 I/O가 발생하여 메모리와 CPU가 불필요하게 사용됩니다.Incorrect or missing indexes causes extra I/O that leads to extra memory and CPU being wasted. 다른 조건자 사용, 기존 인덱스 디자인 무효화 등과 같이 작업 프로필이 변경되기 때문일 수 있습니다.This maybe because workload profile has changed such as using different predicates, invalidating existing index design. 잘못된 인덱싱 전략이나 작업 프로필의 변경을 알 수 있는 방법은 다음과 같습니다.Evidence of a poor indexing strategy or changes in workload profile include:

  • 중복되는 인덱스, 거의 사용되지 않거나 완전히 사용되지 않는 인덱스를 찾습니다.Look for duplicate, redundant, rarely used and completely unused indexes.
  • 업데이트가 있는 사용되지 않는 인덱스에 특히 주의합니다.Special care with unused indexes with updates.

해결 단계Steps to resolve

  1. 모든 누락된 인덱스 참조에 대해 그래픽 실행 계획을 활용합니다.Leverage the graphical execution plan for any Missing Index references.
  2. 데이터베이스 엔진 튜닝 관리자에서 생성한 인덱싱 제안 사항을 검토합니다.Indexing suggestions generated by Database Engine Tuning Advisor.
  3. SQL Server 성능 대시보드를 통해 누락된 인덱스 DMV를 활용합니다.Leverage the Missing Indexes DMV or through the SQL Server Performance Dashboard.
  4. 기존 DMV를 사용하여 누락되거나 중복되거나 거의 사용되지 않거나 전혀 사용되지 않는 인덱스에 대한 정보를 제공할 수 있는 기존 스크립트를 활용하고 인덱스 참조가 힌트로 제공되거나 데이터베이스의 기존 프로시저 및 함수에 하드 코딩되었는지 확인합니다.Leverage pre-existing scripts that can use existing DMVs to provide insight into any missing, duplicate, redundant, rarely used and completely unused indexes, but also if any index reference is hinted/hard-coded into existing procedures and functions in your database.

이러한 기존 스크립트의 예로 Index CreationIndex Information을 들 수 있습니다.Examples of such pre-existing scripts include Index Creation and Index Information.

조건자를 사용하여 데이터를 필터링할 수 없음Inability to use predicates to filter data

적용 대상: 외래 플랫폼(예: Oracle, DB2, MySQL 및 Sybase) 및 SQL ServerSQL Server에서 SQL ServerSQL Server로의 마이그레이션Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

참고

SQL ServerSQL Server에서 SQL ServerSQL Server로의 마이그레이션은 이 문제가 원본 SQL ServerSQL Server에 있는 경우 최신 버전의 SQL ServerSQL Server로 그대로 마이그레이션해도 이 시나리오가 해결되지 않습니다.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

SQL ServerSQL Server 쿼리 최적화 프로그램은 컴파일 시간에 알려진 정보만 설명할 수 있습니다. Query Optimizer can only account for information that is known at compile time. 작업에서 실행 시간에만 알 수 있는 조건자를 사용하는 경우 잘못된 계획을 선택할 가능성이 증가합니다.If a workload relies on predicates that can only be known at execution time, then the potential for a poor plan choice increases. 계획의 품질을 개선하려면 조건자가 SARGable 또는 Search Argumentable이어야 합니다.For a better-quality plan, predicates must be SARGable, or Search Argumentable.

SARGable이 아닌 조건자의 몇 가지 예:Some examples of non-SARGable predicates:

  • VARCHAR에서 NVARCHAR로, INT에서 VARCHAR로 등의 암시적 데이터 변환.Implicit data conversions, like VARCHAR to NVARCHAR, or INT to VARCHAR. 실제 실행 계획에서 런타임 CONVERT_IMPLICIT 경고를 찾습니다.Look for runtime CONVERT_IMPLICIT warnings in the Actual Execution Plans. 형식을 다른 형식으로 변환하면 정밀도도 떨어질 수 있습니다.Converting from one type to another can also cause a loss of precision.
  • WHERE UnitPrice + 1 < 3.975 같은 결정되지 않은 복잡한 식이지만 WHERE UnitPrice < 320 * 200 * 32는 아님.Complex undetermined expressions such as WHERE UnitPrice + 1 < 3.975, but not WHERE UnitPrice < 320 * 200 * 32.
  • WHERE ABS(ProductID) = 771 또는WHERE UPPER(LastName) = 'Smith' 등과 같은 함수를 사용하는 식Expressions using functions, such as WHERE ABS(ProductID) = 771 or WHERE UPPER(LastName) = 'Smith'
  • WHERE LastName LIKE '%Smith'이지만 WHERE LastName LIKE 'Smith%'는 아님과 같이 선행 와일드카드 문자를 사용하는 문자열Strings with a leading wildcard character, such as WHERE LastName LIKE '%Smith', but not WHERE LastName LIKE 'Smith%'.

해결 단계Steps to resolve

  1. 항상 변수/매개 변수를 원하는 대상 데이터 형식으로 선언합니다.Always declare variables/parameters as the intended target data type.
    • 이를 위해서는 데이터베이스(예: 저장 프로시저, 사용자 정의 함수 또는 뷰)에 저장된 사용자 정의 코드 구문을 기본 테이블(예: sys.columns)에서 사용되는 데이터 형식에 대한 정보를 저장하는 시스템 테이블과 비교해야 할 수 있습니다.This may involve comparing any user-defined code construct that is stored in the database (such as stored procedures, user-defined functions or views) with system tables that hold information on data types used in underlying tables (such as sys.columns).
  2. 모든 코드를 이전 시점으로 트래버스할 수 없는 경우에는 같은 목적으로 테이블의 데이터 형식을 변수/매개 변수 선언과 일치하도록 변경합니다.If unable to traverse all code to the previous point, then for the same purpose, change the data type on the table to match any variable/parameter declaration.
  3. 다음 구문을 유용성을 생각해 보세요.Reason out the usefulness of the following constructs:
    • 조건자로 사용되는 함수Functions being used as predicates;
    • 와일드카드 검색Wildcard searches;
    • 칼럼 형식 데이터를 기반으로 하는 복잡한 식 - 인덱싱할 수 있는 지속형 계산 열 대신 만들 필요가 있는지 평가Complex expressions based on columnar data – evaluate the need to instead create persisted computed columns, which can be indexed;

참고

위의 모든 작업을 프로그래밍 방식으로 수행할 수 있습니다.All of the above can be done programmatically.

테이블 반환 함수(다중 문 및 인라인) 사용Use of Table Valued Functions (Multi-Statement vs Inline)

적용 대상: 외래 플랫폼(예: Oracle, DB2, MySQL 및 Sybase) 및 SQL ServerSQL Server에서 SQL ServerSQL Server로의 마이그레이션Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

참고

SQL ServerSQL Server에서 SQL ServerSQL Server로의 마이그레이션은 이 문제가 원본 SQL ServerSQL Server에 있는 경우 최신 버전의 SQL ServerSQL Server로 그대로 마이그레이션해도 이 시나리오가 해결되지 않습니다.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

테이블 반환 함수는 뷰 대신 사용할 수 있는 테이블 데이터 형식을 반환합니다.Table Valued Functions return a table data type that can be an alternative to views. 뷰에서는 SELECT 문을 하나만 사용할 수 있지만 사용자 정의 함수에서는 여러 문을 사용할 수 있으므로 뷰에서보다 논리를 더 추가할 수 있습니다.While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more logic than is possible in views.

중요

MSTVF(다중 문 테이블 반환 함수)의 출력 테이블은 컴파일 시간에 생성되지 않으므로 SQL ServerSQL Server 쿼리 최적화 프로그램은 실제 통계가 아닌 추론을 사용하여 행 예상치를 결정합니다.Since the output table of an MSTVF (Multi-Statement Table Valued Function) is not created at compile time, the SQL ServerSQL Server Query Optimizer relies on heuristics, and not actual statistics, to determine row estimations. 이 경우 인덱스를 기본 테이블에 추가하더라도 도움이 되지 않습니다.Even if indexes are added to the base table(s), this is not going to help. MSTVF의 경우 SQL ServerSQL Server는 MSTVF에서 반환할 것으로 예상되는 행 수에 고정 예상치 1( SQL Server 2014SQL Server 2014부터 고정 예상치는 100개 행)을 사용합니다.For MSTVFs, SQL ServerSQL Server uses a fixed estimation of 1 for the number of rows expected to be returned by an MSTVF (starting with SQL Server 2014SQL Server 2014 that fixed estimation is 100 rows).

해결 단계Steps to resolve

  1. 다중 문 TVF에 문이 하나뿐인 경우 인라인 TVF로 변환합니다.If the Multi-Statement TVF is single statement only, convert to Inline TVF.

    CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
    RETURNS @tblAddress TABLE
    ([Address] VARCHAR(60) NOT NULL)
    AS
    BEGIN
      INSERT INTO @tblAddress ([Address])
      SELECT TOP 1 [AddressLine1]
      FROM [Person].[Address]
      WHERE  AddressID = @ID
      ORDER BY [ModifiedDate] DESC
    RETURN
    END
    

    수행할 작업To

    CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int)
    RETURNS TABLE
    AS
    RETURN (
      SELECT TOP 1 [AddressLine1] AS [Address]
      FROM [Person].[Address]
      WHERE  AddressID = @ID
      ORDER BY [ModifiedDate] DESC
    )
    
  2. 더 복잡한 경우 메모리 액세스에 최적화된 테이블 또는 임시 테이블에 저장된 중간 결과를 사용합니다.If more complex, consider using intermediate results stored in Memory-Optimized tables or temporary tables.

더 보기Additional Reading

쿼리 저장소에 대한 모범 사례Best Practice with the Query Store
Memory-Optimized TablesMemory-Optimized Tables
사용자 정의 함수User-Defined Functions
Table Variables and Row Estimations - Part 1(테이블 변수 및 행 예상치 - 1부)Table Variables and Row Estimations - Part 1
Table Variables and Row Estimations - Part 2(테이블 변수 및 행 예상치 - 2부)Table Variables and Row Estimations - Part 2
실행 계획 캐싱 및 다시 사용Execution Plan Caching and Reuse