마이그레이션 후 유효성 검사 및 최적화 가이드
적용 대상: 
SQL Server
SQL Server 마이그레이션 후 단계는 데이터 정확도와 완전성을 조정하고 워크로드의 성능 문제를 파악하는 데 매우 중요합니다.
일반적인 성능 시나리오
다음은 SQL Server 플랫폼으로 마이그레션한 후 발생하는 몇 가지 일반적인 성능 시나리오와 해결 방법입니다. 여기에는 이전 버전 SQL Server에서 새 버전 SQL Server로의 마이그레이션 및 Oracle, DB2, MySQL, Sybase 등의 외래 플랫폼에서 SQL Server로의 마이그레이션에 특정한 시나리오가 포함됩니다.
CE(카디널리티 예측 도구) 버전 변경으로 인한 쿼리 성능 저하
적용 대상: SQL Server에서 SQL Server로의 마이그레이션.
이전 버전의 SQL Server에서 SQL Server 2014(12.x) 이상 버전으로 마이그레이션할 때, 그리고 사용 가능한 최신 상태로 데이터베이스 호환성 수준을 업그레이드할 때는 워크로드가 성능 저하 위험에 노출될 수 있습니다.
SQL Server 2014(12.x)부터는 쿼리 최적화 프로그램의 모든 변경 내용이 최신 데이터베이스 호환성 수준에 연결되므로 계획이 업그레이드 시점에 즉시 변경되지 않고 사용자가 COMPATIBILITY_LEVEL 데이터베이스 옵션을 최신 상태로 변경하는 경우에 변경됩니다. 이 기능은 쿼리 저장소와 함께 업그레이드 프로세스에서 쿼리 성능에 대한 뛰어난 제어 수준을 제공합니다.
Server 2014(12.x)에 도입된 쿼리 최적화 프로그램 변경 사항에 대한 자세한 내용은 Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator(SQL Server 2014 카디널리티 평가기로 쿼리 계획 최적화)를 참조하세요.
CE에 대한 자세한 내용은 카디널리티 추정(SQL Server)을 참조하세요.
해결 단계
데이터베이스 호환성 수준을 원본 버전으로 변경하고 다음 그림과 같이 권장되는 업그레이드 워크플로를 따릅니다.
이 문서에 대한 자세한 내용은 최신 SQL Server로 업그레이드하는 동안 성능 안정성 유지를 참조하세요.
매개 변수 스니핑에 대한 민감도
적용 대상: 외래 플랫폼(Oracle, DB2, MySQL 및 Sybase)에서 SQL Server로의 마이그레이션.
참고 항목
SQL Server에서 SQL Server로 마이그레이션하는 경우 이 이슈가 원본 SQL Server에 있으면 최신 버전의 SQL Server로 마이그레이션해도 이 시나리오가 해결되지 않습니다.
SQL Server는 첫 컴파일 시 입력 매개 변수를 검색하고 해당 입력 데이터 분포에 최적화된 매개 변수가 있고 재사용 가능한 계획을 생성하여 저장 프로시저의 쿼리 계획을 컴파일합니다. 저장 프로시저가 아니더라도 간단한 계획을 생성하는 대부분의 문이 매개 변수화됩니다. 계획이 처음 캐시된 후 이후 실행은 모두 기존에 캐시된 계획에 매핑됩니다.
첫 번째 컴파일에서 일반적인 워크로드에 가장 일반적인 매개 변수 집합을 사용하지 않는 경우 잠재적인 문제가 발생할 수 있습니다. 다른 매개 변수의 경우 동일한 실행 계획이 비효율적입니다. 이 문서에 대한 자세한 내용은 매개 변수 민감도를 참조하세요.
해결 단계
RECOMPILE힌트를 사용합니다. 각 매개 변수 값이 조정될 때마다 계획이 계산됩니다.(OPTIMIZE FOR(<input parameter> = <value>))옵션을 사용하도록 저장 프로시저를 다시 작성합니다. 관련 작업 대부분에 적합한 값을 사용할 값으로 결정하여 매개 변수가 있는 값에 효율적인 하나의 계획을 만들고 유지합니다.- 프로시저 내의 지역 변수를 사용하여 저장 프로시저를 다시 작성합니다. 이제 최적화 프로그램은 예상치에 밀도 벡터를 사용하므로 매개 변수 값과 관계없이 계획이 동일합니다.
(OPTIMIZE FOR UNKNOWN)옵션을 사용하도록 저장 프로시저를 다시 작성합니다. 지역 변수 기술을 사용하는 것과 결과가 같습니다.DISABLE_PARAMETER_SNIFFING힌트를 사용하도록 쿼리를 다시 작성합니다.OPTION(RECOMPILE),WITH RECOMPILE또는OPTIMIZE FOR <value>를 사용하는 경우 외에는 매개 변수 검색을 완전히 사용하지 않도록 설정하므로 지역 변수 기술을 사용하는 것과 결과가 같습니다.
팁
Management Studio 계획 분석 기능을 사용하면 이로 인해 문제가 발생하는지 빠르게 식별할 수 있습니다. 자세한 내용은 SSMS의 새로운 기능: 쿼리 성능 문제 해결이 더 쉬워집니다!를 참조하세요.
누락된 인덱스
적용 대상: 외래 플랫폼(예: Oracle, DB2, MySQL, Sybase) 및 SQL Server에서 SQL Server로의 마이그레이션.
인덱스가 잘못되거나 누락되면 추가 I/O가 발생하여 추가 메모리 및 CPU가 낭비됩니다. 이는 다른 조건자 사용, 기존 인덱스 디자인 무효화 등과 같이 워크로드 프로필이 변경되기 때문일 수 있습니다. 잘못된 인덱싱 전략 또는 워크로드 프로필 변경의 증거는 다음과 같습니다.
- 중복되고, 불필요하고, 거의 사용되지 않으며, 완전히 사용되지 않는 인덱스를 찾습니다.
- 업데이트가 있는 사용되지 않는 인덱스에 특히 주의합니다.
해결 단계
- 누락된 인덱스 참조에 그래픽 실행 계획을 사용합니다.
- 데이터베이스 엔진 튜닝 관리자에서 생성한 인덱싱 제안 사항입니다.
- sys.dm_db_missing_index_details 또는 SQL Server 성능 대시보드를 통해 사용합니다.
- 기존 DMV를 사용하여 누락, 중복, 불필요, 거의 사용되지 않고 완전히 사용되지 않는 인덱스에 대한 인사이트를 제공할 수 있는 기존 스크립트를 사용하지만, 인덱스 참조가 데이터베이스의 기존 프로시저 및 함수에 힌트 또는 하드 코딩된 경우에도 사용할 수 있습니다.
조건자를 사용하여 데이터를 필터링할 수 없음
적용 대상: 외래 플랫폼(예: Oracle, DB2, MySQL, Sybase) 및 SQL Server에서 SQL Server로의 마이그레이션.
참고 항목
SQL Server에서 SQL Server로 마이그레이션하는 경우 이 이슈가 원본 SQL Server에 있으면 최신 버전의 SQL Server로 마이그레이션해도 이 시나리오가 해결되지 않습니다.
SQL Server 쿼리 최적화 프로그램은 컴파일 시간에 알려진 정보만 설명할 수 있습니다. 작업에서 실행 시간에만 알 수 있는 조건자를 사용하는 경우 잘못된 계획을 선택할 가능성이 증가합니다. 계획의 품질을 개선하려면 조건자가 SARGable 또는 Search Argumentable이어야 합니다.
SARGable이 아닌 조건자의 몇 가지 예는 다음과 같습니다.
- varchar에서 nvarchar로, int에서 varchar 등의 암시적 데이터 변환. 실제 실행 계획에서 런타임
CONVERT_IMPLICIT경고를 찾습니다. 형식을 다른 형식으로 변환하면 정밀도도 떨어질 수 있습니다. WHERE UnitPrice < 320 * 200 * 32가 아닌WHERE UnitPrice + 1 < 3.975와 같이 결정되지 않은 복잡한 식.WHERE ABS(ProductID) = 771또는WHERE UPPER(LastName) = 'Smith'등과 같은 함수를 사용하는 식.WHERE LastName LIKE 'Smith%'가 아닌WHERE LastName LIKE '%Smith'와 같은 선행 와일드카드 문자가 있는 문자열.
해결 단계
항상 변수/매개 변수를 원하는 대상 데이터 형식으로 선언합니다.
여기에는 데이터베이스에 저장된 사용자 정의 코드 구문(예: 저장 프로시저, 사용자 정의 함수 또는 뷰)과 기본 테이블에 사용되는 데이터 형식(예: sys.columns(Transact-SQL))에 대한 정보를 포함하는 시스템 테이블과 비교하는 작업이 포함될 수 있습니다.
모든 코드를 이전 시점으로 트래버스할 수 없는 경우에는 같은 목적으로 테이블의 데이터 형식을 변수/매개 변수 선언과 일치하도록 변경합니다.
다음 구문의 유용성을 추론합니다.
- 조건자로 사용되는 함수,
- 와일드카드 검색,
- 열 형식 데이터를 기반으로 한 복잡한 식 - 인덱싱할 수 있는 지속형 계산 열 대신 만들 필요가 있는지 평가합니다.
참고 항목
위의 모든 단계를 프로그래밍 방식으로 수행할 수 있습니다.
테이블 반환 함수 사용(다중 문 및 인라인)
적용 대상: 외래 플랫폼(예: Oracle, DB2, MySQL, Sybase) 및 SQL Server에서 SQL Server로의 마이그레이션.
참고 항목
SQL Server에서 SQL Server로 마이그레이션하는 경우 이 이슈가 원본 SQL Server에 있으면 최신 버전의 SQL Server로 마이그레이션해도 이 시나리오가 해결되지 않습니다.
테이블 반환 함수는 뷰 대신 사용할 수 있는 테이블 데이터 형식을 반환합니다. 뷰에서는 SELECT 문을 하나만 사용할 수 있지만 사용자 정의 함수에서는 여러 문을 사용할 수 있으므로 뷰에서보다 논리를 더 추가할 수 있습니다.
Important
MSTVF(다중 문 테이블 반환 함수)의 출력 테이블은 컴파일 시간에 생성되지 않으므로 SQL Server 쿼리 최적화 프로그램은 실제 통계가 아닌 경험적 접근을 사용하여 행 예상치를 결정합니다. 이 경우 인덱스를 기본 테이블에 추가하더라도 도움이 되지 않습니다. MSTVF의 경우 SQL Server는 MSTVF에서 반환할 것으로 예상되는 행 수에 고정 예상치 1(SQL Server 2014(12.x)부터 고정 예상치는 100개 행)을 사용합니다.
해결 단계
MSTVF가 단일 문만인 경우 인라인 테이블 반환 함수로 변환합니다.
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인라인 형식 예제가 다음에 표시됩니다.
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 )더 복잡한 경우 메모리 액세스에 최적화된 테이블 또는 임시 테이블에 저장된 중간 결과를 사용합니다.