쿼리 튜닝 권장 사항
일부 쿼리는 다른 쿼리보다 리소스를 더 많이 사용합니다. 예를 들어 대규모 결과 세트를 반환하는 쿼리와 고유하지 않은 WHERE 절이 포함된 쿼리는 항상 리소스를 많이 사용하는 쿼리입니다. 덜 복잡한 쿼리와 비교할 때 이러한 구문에 대한 리소스 비용을 완전히 제거할 수 있는 쿼리 최적화는 없습니다. SQL Server에서는 최적의 액세스 계획이 사용되지만 가능한 일정 수준까지만 최적화할 수 있습니다.
그렇지만 다음과 같은 방법을 사용하면 쿼리 성능을 개선할 수 있습니다.
메모리를 더 많이 추가합니다. 이 방법은 특히 서버에서 복잡한 쿼리를 많이 실행하고 몇 개의 쿼리는 느리게 실행되는 경우에 유용합니다.
두 개 이상의 프로세서를 사용합니다. 여러 개의 프로세서를 사용하면 데이터베이스 엔진에서 병렬 쿼리를 사용할 수 있습니다. 자세한 내용은 병렬 쿼리 처리를 참조하십시오.
쿼리를 다시 작성합니다. 다음 사항을 고려해야 합니다.
쿼리에서 커서를 사용하는 경우에는 빨리 감기 전용과 같은 더 효율적인 커서 유형 또는 단일 쿼리를 통해 커서 쿼리를 작성할 수 있는지 여부를 확인합니다. 단일 쿼리는 대체로 커서 작업보다 성능이 좋습니다. 커서 문의 집합은 대체로 외부 루프의 각 행이 내부 문을 사용하여 한 번 처리되는 외부 루프 작업이므로 GROUP BY 또는 CASE 문이나 하위 쿼리 사용을 대신 고려하십시오. 자세한 내용은 커서 유형(데이터베이스 엔진) 및 쿼리 기본 사항을 참조하십시오.
응용 프로그램에서 루프를 사용하는 경우에는 루프를 쿼리 안에 두는 것을 고려하십시오. 응용 프로그램에는 매개 변수가 있는 쿼리가 들어 있는 루프가 포함되는 경우가 많습니다. 매개 변수가 있는 쿼리는 여러 차례 실행되며, 응용 프로그램을 실행하는 컴퓨터와 SQL Server 간 네트워크 왕복을 필요로 합니다. 그 대신 임시 테이블을 사용하여 단일의, 더 복잡한 쿼리를 만듭니다. 네트워크 왕복은 한 번만 필요하며, 쿼리 최적화 프로그램은 단일 쿼리 최적화를 더욱 잘 수행할 수 있습니다. 자세한 내용은 절차적 Transact-SQL 및 Transact-SQL 변수를 참조하십시오.
인덱스 교차를 시뮬레이트하기 위해 동일한 쿼리에서 단일 테이블에 대해 여러 개의 별칭을 사용하지 않습니다. SQL Server는 인덱스 교차를 자동으로 고려하며 동일한 쿼리에서 같은 테이블에 대해 여러 개의 인덱스를 사용할 수 있으므로 이 방법은 더 이상 필요하지 않습니다. 다음은 예제 쿼리입니다.
SELECT * FROM lineitem WHERE partkey BETWEEN 17000 AND 17100 AND shipdate BETWEEN '1/1/1994' AND '1/31/1994'SQL Server는 partkey와 shipdate 열 모두에 인덱스를 사용한 다음 두 하위 집합 사이에 해시 일치를 수행하여 인덱스 교차를 구할 수 있습니다.
쿼리 매개 변수화를 사용하여 캐시된 쿼리 실행 계획을 다시 사용할 수 있습니다. 일련의 쿼리에서 쿼리 해시 및 쿼리 계획 해시가 동일한 경우 하나의 매개 변수가 있는 쿼리를 만들어서 성능을 향상시킬 수 있습니다. 리터럴 값으로 여러 쿼리를 호출하는 대신 매개 변수를 사용하여 하나의 쿼리를 호출하면 캐시된 쿼리 실행 계획을 다시 사용할 수 있습니다. 자세한 내용은 쿼리 및 쿼리 계획 해시를 사용하여 비슷한 쿼리 검색 및 튜닝 및 실행 계획 캐싱 및 다시 사용을 참조하십시오.
응용 프로그램을 수정할 수 없는 경우에는 템플릿 계획 지침에서 강제 매개 변수화를 사용하여 비슷한 결과를 얻을 수 있습니다. 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하십시오.
필요한 경우에만 쿼리 힌트를 사용합니다. 이전 버전의 SQL Server에 대해 실행되는 힌트를 사용하는 쿼리는 지정된 힌트 없이 테스트되어야 합니다. 힌트로 인해 쿼리 최적화 프로그램이 더 좋은 실행 계획을 선택하지 못할 수 있습니다. 자세한 내용은 SELECT(Transact-SQL)를 참조하십시오.
query_plan_hash를 사용하여 쿼리에 대해 쿼리 실행 계획을 캡처 및 저장하고 시간에 따라 비교할 수 있습니다. 예를 들어 시스템 구성을 변경한 후 중요 쿼리의 쿼리 계획 해시 값을 원래의 쿼리 계획 해시 값과 비교할 수 있습니다. 쿼리 계획 해시 값의 차이를 보면 시스템 구성 변경으로 인해 중요 쿼리에 대한 쿼리 실행 계획이 업데이트되었는지를 확인할 수 있습니다. 또한 sys.dm_exec_requests의 쿼리 계획 해시가 우수한 성능이 확인된 기준 쿼리 계획 해시와 다를 경우 현재 장기 실행 쿼리의 실행을 중지하도록 결정할 수 있습니다. 자세한 내용은 쿼리 및 쿼리 계획 해시를 사용하여 비슷한 쿼리 검색 및 튜닝을 참조하십시오.
query governor 구성 옵션을 사용합니다. query governor 구성 옵션을 사용하여 실행 시간이 긴 쿼리에서 시스템 리소스를 소모하지 않도록 방지할 수 있습니다. 기본적으로 해당 옵션은 쿼리의 실행 시간에 관계없이 모든 쿼리를 실행할 수 있도록 설정되어 있습니다. 그러나 모든 연결에 대해 모든 쿼리를 실행하거나 특정 연결에 대한 쿼리만 실행하도록 query governor에서 최대 초 수를 제한하도록 설정할 수 있습니다. query governor는 실제 경과된 시간보다는 예상 쿼리 비용을 기반으로 하므로 런타임 오버헤드가 없습니다. 또한 실행 시간이 긴 쿼리를 미리 정의된 제한에 도달할 때까지 계속 실행하기보다는 쿼리가 시작되기 전에 중지합니다. 자세한 내용은 쿼리 관리자 비용 제한 옵션 및 SET QUERY_GOVERNOR_COST_LIMIT(Transact-SQL)를 참조하십시오.
계획 캐시에서 쿼리 계획의 재사용을 최적화합니다. 데이터베이스 엔진에서는 가능한 재사용에 대해 쿼리 계획을 캐시합니다. 쿼리 계획을 캐시하지 않으면 다시 사용할 수 없습니다. 대신 캐시되지 않은 쿼리 계획은 실행될 때마다 컴파일해야 하므로 성능이 저하됩니다. 다음 Transact-SQL SET 문 옵션은 캐시된 쿼리 계획이 재사용되지 않도록 합니다. 이러한 SET 옵션이 설정되어 있는 Transact-SQL 일괄 처리는 SET 옵션이 해제된 상태로 컴파일된 동일 일괄 처리와 쿼리 계획을 공유할 수 없습니다.
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET ARITHABORT
SET CONCAT_NULL_YIELDS_NULL
SET DATEFIRST
SET DATEFORMAT
SET FORCEPLAN
SET LANGUAGE
SET NO_BROWSETABLE
SET NUMERIC_ROUNDABORT
SET QUOTED_IDENTIFIER
SET TEXTSIZE
또한 SET ANSI_DEFAULTS 옵션은 ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS 및 QUOTED_IDENTIFIER SET 옵션을 변경하는 데 사용될 수 있기 때문에 캐시된 쿼리 계획의 재사용에 영향을 줍니다. SET ANSI_DEFAULTS으로 변경될 수 있는 SET 옵션은 대부분 쿼리 계획의 재사용에 영향을 줄 수 있는 SET 옵션으로 나열됩니다.
다음 방법을 사용하여 이러한 SET 옵션 중 일부를 변경할 수 있습니다.
서버 수준 변경에는 sp_configure 저장 프로시저를 사용합니다. 자세한 내용은 sp_configure(Transact-SQL)를 참조하십시오.
ALTER DATABASE 문의 SET 절을 사용합니다. 자세한 내용은 ALTER DATABASE(Transact-SQL)를 참조하십시오.
OLE DB 및 ODBC 연결 설정을 변경합니다. 자세한 내용은 클라이언트 네트워크 구성을 참조하십시오.
[!참고]
SET 옵션에 따라 쿼리 계획이 다시 컴파일되지 않도록 하려면 연결 시간에 SET 옵션을 설정하고 연결 기간 동안 변경하지 않도록 해야 합니다. 일부 SET 옵션은 계산된 열에서 인덱싱된 뷰 또는 인덱스를 사용하도록 특정 값으로 설정해야 합니다. 자세한 내용은 결과에 영향을 주는 SET 옵션을 참조하십시오.